DataSet 更新数据库 微软自己写的例子

using System;using System.Data;using System.Data.SqlTypes;using System.Data.SqlClient;using System.Data.Common;namespace SQLDataAdapterConsole{class Class1{public static void Main(){SqlConnection cn = new SqlConnection();SqlDataAdapter sqlDa;//Set the connection string of the SqlConnection object to connect//to the SQL Server database where you created the sample table.cn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=northwind;Data Source=ServerName"; cn.Open();//Initialize the SqlDataAdapter object by specifying a Select command //that retrieves data from the sample table.sqlDa = new SqlDataAdapter("select * from custTest", cn);//This function uses Uptade method of DataAdapter to update the modified DataSet data in the source database//Notice that the error raised by the Update Command Stored Procedure cannot be trappedUpdateUsingDataAdapter(sqlDa);//This function uses DataReader to update the modified data in the source database//Notice that the error raised by the Update Command Stored Procedure can be trappedUpdateUsingDataReader(sqlDa);cn.Close();Console.ReadLine();}// This function uses Update method of SqlDataAdapter for Updating the datapublic static void UpdateUsingDataAdapter(SqlDataAdapter da) {Console.WriteLine("Updating Database using Update Command of SqlDataAdapter");SqlCommand updateCmd;DataSet CustomersDataSet = new DataSet();//Create update command for SqlDataAdapterupdateCmd = new SqlCommand("exec updateCustTest @pCustId, @pCustName", da.SelectCommand.Connection);//Add parameters for the stored procedureupdateCmd.Parameters.Add(new SqlParameter("@pCustName", SqlDbType.VarChar));updateCmd.Parameters["@pCustName"].SourceVersion = DataRowVersion.Current;updateCmd.Parameters["@pCustName"].SourceColumn = "CustName";updateCmd.Parameters.Add(new SqlParameter("@pCustId", SqlDbType.VarChar));updateCmd.Parameters["@pCustId"].SourceVersion = DataRowVersion.Original;updateCmd.Parameters["@pCustId"].SourceColumn = "CustId";//Initialize the UpdateCommand of SqlDataAdapterda.UpdateCommand = updateCmd;//Populate the DataSet by running the Fill method of the SqlDataAdapter.da.Fill(CustomersDataSet,"custTest");//Write out the value in the CustName field before updating the data using the DataSet.Console.WriteLine("Customer Name before Update : " + CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"]);//Modify the value of the CustName field.CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"] = "Bill";// Get the dataSet that is modified.DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified);try{da.Update(modifiedDS,"CustTest");Console.WriteLine("Customer name is updated to 'Bill'.");Console.WriteLine("The Stored Procedure error is not trapped.");}catch{Console.WriteLine("Stored Procedure error trapped.");}}//This function uses DataReader for updating the public static void UpdateUsingDataReader(SqlDataAdapter da){Console.WriteLine("/n/nUpdating Database using DataReader to trap the Stored Procedure error.");DataSet CustomersDataSet = new DataSet();SqlDataReader mySqlDataReader;//Populate the DataSet by running the Fill method of the SqlDataAdapter.da.Fill(CustomersDataSet,"custTest");//Modify the value of the CustName field.CustomersDataSet.Tables["CustTest"].Rows[0]["CustName"] = "Michel";// Get the dataSet that is modified.DataSet modifiedDS = CustomersDataSet.GetChanges(DataRowState.Modified);SqlCommand updateCmd;foreach (DataRow dr in modifiedDS.Tables[0].Rows){// Set the update command for modified data in the DataSet.updateCmd = new SqlCommand("updateCustTest '" + dr[0] +"', '" + dr[1] + "'", da.SelectCommand.Connection);try{// Execute the update command.mySqlDataReader = updateCmd.ExecuteReader();while(!mySqlDataReader.IsClosed){while (mySqlDataReader.Read()){Console.WriteLine("Customer name returned by the procedure :" + mySqlDataReader.GetValue(1));}// Get the results set returned by the update command.mySqlDataReader.NextResult();Console.WriteLine("/n");}mySqlDataReader.Close();Console.WriteLine("Customer Name updated successfully");}catch(SqlException e){// Get the error collection and write the error on the console.Console.WriteLine("Error Number is : " + e.Number);Console.WriteLine("Error Message is : " + e.Message);}}}}}