namespace HowTo.Samples.ADONET {using System; using System.Data; using System.Data.SqlClient;public class updatingdata { public static void Main() { updatingdata myupdatingdata = new updatingdata(); myupdatingdata.Run(); } public void Run() { SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind"); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers", myConnection); SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from orders", myConnection); // Restore database to it's original condition so sample will work correctly. Cleanup(); try { DataSet myDataSet = new DataSet(); DataRow myDataRow; // Create command builder. This line automatically generates the update commands for you, so you don't // have to provide or create your own. SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter); // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary // key & unique key information to be retrieved unless AddWithKey is specified. mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; mySqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey; mySqlDataAdapter.Fill(myDataSet,"Customers"); Console.WriteLine("已将数据从 Customers 表加载到数据集中。"); mySqlDataAdapter1.Fill(myDataSet,"Orders"); Console.WriteLine("已将数据从 Orders 表加载到数据集中。"); // ADD RELATION myDataSet.Relations.Add("CustOrders",myDataSet.Tables["Customers"].Columns["CustomerId"],myDataSet.Tables["Orders"].Columns["CustomerId"]); // EDIT myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach"; // ADD myDataRow = myDataSet.Tables["Customers"].NewRow(); myDataRow["CustomerId"] ="新 ID"; myDataRow["ContactName"] = "新姓名"; myDataRow["CompanyName"] = "新公司名称"; myDataSet.Tables["Customers"].Rows.Add(myDataRow); Console.WriteLine("已将新行插入 Customers。"); // Update Database with SqlDataAdapter mySqlDataAdapter.Update(myDataSet, "Customers"); Console.WriteLine("已将更新发送到数据库。"); Console.WriteLine("数据集处理已成功完成!"); } catch(Exception e) { Console.WriteLine(e.ToString()); } } public void Cleanup() { SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind"); try { // Restore database to it's original condition so sample will work correctly. myConnection.Open(); SqlCommand CleanupCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerId = 'NewID'", myConnection); CleanupCommand.ExecuteNonQuery(); } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { myConnection.Close(); } } }}
1、首先得到数据库连接
2、为DataSet中的每一个DataTable生成Select语句,敷给对应的DataAdapter,利用CommandBuilder产生对应的DataAdapter的Insert\Update\Delete命令,使用DataAdapter的UpDate可以自动更新如果数据集中的表和数据库没有对应关系,那只有用Sql循环插入数据
{using System;
using System.Data;
using System.Data.SqlClient;public class updatingdata
{
public static void Main()
{
updatingdata myupdatingdata = new updatingdata();
myupdatingdata.Run();
} public void Run()
{ SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers", myConnection);
SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from orders", myConnection); // Restore database to it's original condition so sample will work correctly.
Cleanup(); try
{
DataSet myDataSet = new DataSet();
DataRow myDataRow; // Create command builder. This line automatically generates the update commands for you, so you don't
// have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter); // Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
mySqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey; mySqlDataAdapter.Fill(myDataSet,"Customers");
Console.WriteLine("已将数据从 Customers 表加载到数据集中。"); mySqlDataAdapter1.Fill(myDataSet,"Orders");
Console.WriteLine("已将数据从 Orders 表加载到数据集中。"); // ADD RELATION
myDataSet.Relations.Add("CustOrders",myDataSet.Tables["Customers"].Columns["CustomerId"],myDataSet.Tables["Orders"].Columns["CustomerId"]); // EDIT
myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach"; // ADD
myDataRow = myDataSet.Tables["Customers"].NewRow();
myDataRow["CustomerId"] ="新 ID";
myDataRow["ContactName"] = "新姓名";
myDataRow["CompanyName"] = "新公司名称";
myDataSet.Tables["Customers"].Rows.Add(myDataRow);
Console.WriteLine("已将新行插入 Customers。"); // Update Database with SqlDataAdapter
mySqlDataAdapter.Update(myDataSet, "Customers");
Console.WriteLine("已将更新发送到数据库。"); Console.WriteLine("数据集处理已成功完成!");
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
} public void Cleanup()
{
SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind"); try
{
// Restore database to it's original condition so sample will work correctly.
myConnection.Open();
SqlCommand CleanupCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerId = 'NewID'", myConnection);
CleanupCommand.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConnection.Close();
}
}
}}