从 DataSet 对象更新数据库

 

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Collections;

 

namespace IDataAdapter

{

     ///<summary>

     /// AdHelper 的摘要说明。

     ///</summary>

     public abstract class AdSqlHelper

     {

         ///<summary>

         /// Database connection strings.

         ///</summary>

         ///<remarks>

         ///

         ///</remarks>

         public static string CONN = "";

 

         ///<summary>

         ///

         ///</summary>

         ///<remarks>

         ///

          ///</remarks>

         ///<param name="tableName"></param>

         ///<param name="dataRows"></param>

         ///<returns></returns>

         public static DataTable FillSchema(string tableName, string selectCommandText)

         {            

              SqlConnection objConn = new SqlConnection(CONN);

              objConn.Open();

              SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, objConn);

              DataSet ds = new DataSet(tableName);

              DataTable dt = new DataTable();

              try

              {                 

                   adapter.FillSchema(ds, SchemaType.Source, tableName);

                   adapter.Fill(ds, tableName);

                   dt = ds.Tables[tableName];

                   return dt;

              }

              catch// (Exception e)

              {

                   return null;

              }

              finally

              {

                   objConn.Close();

                   dt.Dispose();

                   ds.Dispose();

                   adapter.Dispose();

              }

         }

 

         ///<summary>

          ///

         ///</summary>

         ///<remarks>

         ///

         ///</remarks>

         ///<param name="tableName"></param>

         ///<param name="dataRows"></param>

         ///<returns></returns>

         public static bool Insert(string tableName, DataRowCollection dataRows)

         {

              SqlConnection objConn = new SqlConnection(CONN);

              objConn.Open();

              SqlDataAdapter adapter = new SqlDataAdapter("Select top 0 * From " + tableName + " ", objConn);

              DataSet ds = new DataSet(tableName);

              DataTable dt = new DataTable();

              try

              {

                  adapter.FillSchema(ds, SchemaType.Source, tableName);

                   adapter.Fill(ds, tableName);

                   dt = ds.Tables[tableName];

 

                   DataRow drCurrent;

                   foreach(DataRow dataRow in dataRows)

                   {

                       drCurrent = dt.NewRow();

                       foreach(DataColumn dataColumn in dataRow.Table.Columns)

                       {

                            drCurrent[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];

                       }

                       dt.Rows.Add(drCurrent);

                   }

 

                   SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);

                   adapter.Update(ds, tableName);

 

                   return true;

              }

              catch// (Exception e)

              {

                   return false;

              }

              finally

              {

                   objConn.Close();

                   dt.Dispose();

                   ds.Dispose();

                   adapter.Dispose();

              }

         }  

        

         ///<summary>

         ///

         ///</summary>

         ///<remarks>

         ///

         ///</remarks>

         ///<param name="tableName"></param>

         ///<param name="selectCommandText"></param>

         ///<param name="dataRows"></param>

         ///<returns></returns>

         public static bool Update(string tableName, string selectCommandText, DataRowCollection dataRows)

         {

              SqlConnection objConn = new SqlConnection(CONN);

              objConn.Open();

              SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, objConn);

              DataSet ds = new DataSet(tableName);

              DataTable dt = new DataTable();

              try

              {

                   adapter.FillSchema(ds, SchemaType.Source, tableName);

                   adapter.Fill(ds, tableName);

                   dt = ds.Tables[tableName];

 

                   DataRow drCurrent;

                   foreach(DataRow dataRow in dataRows)

                   {

                       DataColumn[] primaryKey = dataRow.Table.PrimaryKey;

                       object[] arrayKey = new object[primaryKey.Length];

                       for (int i = 0; i < arrayKey.Length; i++)

                       {

                            arrayKey[i] = dataRow[primaryKey[i].ColumnName];

                       }

 

                       drCurrent = dt.Rows.Find(arrayKey);

                       drCurrent.BeginEdit();

                       foreach(DataColumn dataColumn in dataRow.Table.Columns)

                       {

                            if(!dataColumn.ReadOnly)

                            {

                                 drCurrent[dataColumn.ColumnName] = dataRow[dataColumn.ColumnName];

                            }

                       }

                       drCurrent.EndEdit();                     

                   }

 

                   SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);

                   adapter.Update(ds, tableName);

 

                   return true;

              }

              catch//(Exception e)

              {                 

                   return false;

              }

              finally

              {

                   objConn.Close();

                   dt.Dispose();

                   ds.Dispose();

                   adapter.Dispose();

              }

         }  

 

         ///<summary>

         ///

         ///</summary>

         ///<remarks>

         ///

         ///</remarks>

         ///<param name="tableName"></param>

         ///<param name="selectCommandText"></param>

         ///<param name="dataRows"></param>

         ///<returns></returns>

         public static bool Delete(string tableName, string selectCommandText, DataRowCollection dataRows)

         {

              SqlConnection objConn = new SqlConnection(CONN);

              objConn.Open();

              SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, objConn);

              DataSet ds = new DataSet(tableName);

              DataTable dt = new DataTable();

              try

              {

                   adapter.FillSchema(ds, SchemaType.Source, tableName);

                   adapter.Fill(ds, tableName);

                   dt = ds.Tables[tableName];

 

                   DataRow drCurrent;

                   foreach(DataRow dataRow in dataRows)

                   {

                       DataColumn[] primaryKey = dataRow.Table.PrimaryKey;

                       object[] arrayKey = new object[primaryKey.Length];

                       for (int i = 0; i < arrayKey.Length; i++)

                       {

                            arrayKey[i] = dataRow[primaryKey[i].ColumnName];

                       }

 

                       drCurrent = dt.Rows.Find(arrayKey);

                       drCurrent.Delete();                      

                   }

 

                   SqlCommandBuilder objCommandBuilder = new SqlCommandBuilder(adapter);

                   adapter.Update(ds, tableName);

 

                   return true;

              }

              catch// (Exception e)

              {

                   return false;

              }

              finally

              {

                   objConn.Close();

                   dt.Dispose();

                   ds.Dispose();

                   adapter.Dispose();

              }

         }  

     }

}

  

#region Example

        ///// <summary>

        ///// Insert

        ///// </summary>

        ///// <param name="sender"></param>

        ///// <param name="e"></param>

        //private void button1_Click(object sender, System.EventArgs e)

        //{

        //    DataTable dt = AdSqlHelper.FillSchema("AdapterSample", "SELECT top 0 * FROM AdapterSample");

        //    DataRowCollection drList = dt.Rows;

        //    DataRow dr ;

 

        //    dr = dt.NewRow();

        //    dr["p2"] = "1";

        //    dr["p3"] = "1111111";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p2"] = "2";

        //    dr["p3"] = "222222";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p2"] = "3";

        //    dr["p3"] = "333333";

        //    drList.Add(dr);

 

        //    AdSqlHelper.Insert("AdapterSample", drList);

 

        //    BindDataSource();

        //}

 

        ///// <summary>

        ///// Update

        ///// </summary>

        ///// <param name="sender"></param>

        ///// <param name="e"></param>

        //private void button2_Click(object sender, System.EventArgs e)

        //{

        //    DataTable dt = AdSqlHelper.FillSchema("AdapterSample", "SELECT top 0 * FROM AdapterSample");

        //    DataRowCollection drList = dt.Rows;

        //    DataRow dr ;

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox1.Text;

        //    dr["p2"] = "1";

        //    dr["p3"] = "444444";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox2.Text;

        //    dr["p2"] = "2";

        //    dr["p3"] = "555555";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox3.Text;

        //    dr["p2"] = "3";

        //    dr["p3"] = "666666";

        //    drList.Add(dr);

 

        //    AdSqlHelper.Update("AdapterSample", "SELECT * FROM AdapterSample WHERE p2 = '1' or p2 = '2' or p2 = '3'" , drList);

 

        //    BindDataSource();

        //}

 

        ///// <summary>

        ///// Delete

        ///// </summary>

        ///// <param name="sender"></param>

        ///// <param name="e"></param>

        //private void button3_Click(object sender, System.EventArgs e)

        //{

        //    DataTable dt = AdSqlHelper.FillSchema("AdapterSample", "SELECT top 0 * FROM AdapterSample");

        //    DataRowCollection drList = dt.Rows;

        //    DataRow dr ;

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox1.Text;

        //    dr["p2"] = "1";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox2.Text;

        //    dr["p2"] = "2";

        //    drList.Add(dr);

 

        //    dr = dt.NewRow();

        //    dr["p1"] = this.textBox3.Text;

        //    dr["p2"] = "3";

        //    drList.Add(dr);

 

        //    AdSqlHelper.Delete("AdapterSample", "SELECT * FROM AdapterSample WHERE p2 = '1' or p2 = '2' or p2 = '3'" , drList);

 

        //    BindDataSource();

        //}

 

        //private void Form1_Load(object sender, System.EventArgs e)

        //{

        //    BindDataSource();

        //}

 

        //private void BindDataSource()

        //{

        //    this.dataGrid1.DataSource = SqlHelper.ExecuteDataSet("SELECT * FROM AdapterSample").Tables[0].DefaultView;         

        //}

#endregion