C#.NET连接各种数据库及数据操作Demo

C#.NET连接数据库及数据操作Demo 1.连接Access string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\MY\\DataBase\\Access\\testdb.mdb;Persist Security Info=True"; OleDbConnection myCon = new OleDbConnection(strCon); myCon.Open(); string strSQL = "SELECT * FROM student"; OleDbCommand myCom = new OleDbCommand(strSQL,myCon); OleDbDataReader dtReader = myCom.ExecuteReader(); Response.Write("

2.连接SQL string strCon = "Server=.;Initial Catalog=Northwind;user id=sa;password=sa;"; SqlConnection conn = new SqlConnection(strCon); conn.Open(); string strSQL = "SELECT [CustomerID],[CompanyName],[ContactName],[ContactTitle]"+ "FROM [Northwind].[dbo].[Customers]"+ "WHERE [ContactTitle] LIKE '%Sales%'"; SqlCommand command = new SqlCommand(strSQL,conn); SqlDataReader sqlread = command.ExecuteReader(); Response.Write("

3.连接读取配置文件插入操作 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString); SqlCommand command = new SqlCommand(); command.Connection = conn; conn.Open(); command.CommandText = "INSERT INTO Employees(FirstName,LastName,BirthDate) Values(@FirstName,@LastName,@BirthDate)"; command.Parameters.Add("@FirstName",SqlDbType.NVarChar); command.Parameters.Add("@LastName",SqlDbType.NVarChar); command.Parameters.Add("@BirthDate",SqlDbType.NVarChar); command.Parameters["@FirstName"].Value = FirstName.Text; command.Parameters["@LastName"].Value = LastName.Text; command.Parameters["@BirthDate"].Value = DateTime.Parse(BirthDate.Text.ToString()); command.ExecuteNonQuery();

4. SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString); try { conn.Open(); string sql = "SELECT OrderID,OrderDate FROM Orders Where OrderDate>='1998-01-01'"; SqlDataAdapter sqladpt = new SqlDataAdapter(sql, conn); SqlCommandBuilder bb = new SqlCommandBuilder(sqladpt); DataSet dt = new DataSet(); sqladpt.Fill(dt); DataTable table = new DataTable(); table = dt.Tables[0]; Response.Write("

5.数据填充 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString); conn.Open(); string sql = "SELECT * FROM Employees WHERE EmployeeID=0"; SqlDataAdapter adapter = new SqlDataAdapter(sql, conn); SqlCommandBuilder byy = new SqlCommandBuilder(adapter); DataTable table = new DataTable(); adapter.Fill(table); DataRow row = table.NewRow(); row["FirstName"] = FirstName.Text; row["LastName"] = LastName.Text; row["BirthDate"] = DateTime.Parse(BirthDate.Text.ToString()); table.Rows.Add(row); adapter.Update(table);

6．数据填充 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN"].ConnectionString); SqlTransaction trans = null; SqlCommand command = new SqlCommand(); try { conn.Open(); trans = conn.BeginTransaction(); command.Connection = conn; command.Transaction = trans; command.CommandText = "INSERT INTO Employees(FirstName,LastName,BirthDate) Values(@FirstName,@LastName,@BirthDate)"; command.Parameters.Add("@FirstName", SqlDbType.NVarChar); command.Parameters.Add("@LastName", SqlDbType.NVarChar); command.Parameters.Add("@BirthDate", SqlDbType.NVarChar); command.Parameters["@FirstName"].Value = FirstName.Text; command.Parameters["@LastName"].Value = LastName.Text; command.Parameters["@BirthDate"].Value = DateTime.Parse(BirthDate.Text.ToString()); command.ExecuteNonQuery(); trans.Commit(); Response.Write("添加成功！");