ADO内存数据对象SqlDataAdapter类使用SqlDataAdapter在数据库和DataSet之间架起桥梁:
- void UseDataAdapter()
- {
- //1 连接数据库
- //2 创建一个SqlDataAdapter对象
- SqlDataAdapter adapter = new SqlDataAdapter();
- //3 利用SqlDataAdapter查询数据,并把数据放在DataSet中
- SqlCommand selectCmd = new SqlCommand();
- selectCmd.Connection = myCon;
- selectCmd.CommandText = "SELECT SupplierID, CompanyName FROM Suppliers;";
- adapter.SelectCommand = selectCmd;
- DataSet dataSet = new DataSet("Suppliers");
- adapter.Fill(dataSet);//Fill数据
- Page.Response.Write("已连查询数据,并放入DataSet中<br />");
- //4 利用SqlDataAdapter对象操作语句
- //4.1 Update
- SqlCommand updateCmd = new SqlCommand();
- updateCmd.Connection = myCon;
- updateCmd.CommandText = "UPDATE Customers SET CompanyName='KFC' WHERE CustomerID = 'ALFKI'";
- adapter.UpdateCommand = updateCmd;
- //4.2 Delete
- SqlCommand deleteCmd = new SqlCommand();
- deleteCmd.Connection = myCon;
- deleteCmd.CommandText = "DELETE Customers WHERE CustomerID = 'ALFKI'";
- adapter.DeleteCommand= deleteCmd;
- //4.3 Insert
- SqlCommand insertCmd = new SqlCommand();
- insertCmd.Connection = myCon;
- insertCmd.CommandText = "INSERT INTO Customers(CustomerId) values ('ALFKI')";
- adapter.InsertCommand = insertCmd;
- //4.4 执行操作
- adapter.Update(dataSet);
- Page.Response.Write("已连操作数据,包括:修改、删除、插入<br />");
- //5 关闭数据库连接
- myCon.Close();
- Page.Response.Write("已关闭数据库<br />");
- }