批量更新

首先借助于一个DataHelper类

using System;
using System.Data;
using System.Data.SqlClient;
namespace NET.MST.Ninth.BatchUpdate
{
    class DataHelper
    {
        //连接字符串
        static readonly String conn_String = "Server=localhost;Integrated Security=true;database=NetTest";
        //选择、更新、删除和插入的SQL命令
        static readonly String SQL_SELECT = "SELECT * FROM DepartCost";
        static readonly String SQL_UPDATE = "UPDATE DepartCost SET Department=@Department,Item=@Item,Number=@Number where Id=@Id";
        static readonly String SQL_DELETE = "DELETE FROM DepartCost where Id=@Id";
        static readonly String SQL_INSERT = "Insert INTO DepartCost (Department,Item,Number) VALUES (@Department,@Item,@Number)";
        /// <summary>
        /// 得到SqlDataAdapter，私有方法
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        private static SqlDataAdapter GetDataAdapter(SqlConnection con)
        {
            SqlDataAdapter sda = new SqlDataAdapter();
            sda.SelectCommand = new SqlCommand(SQL_SELECT, con);
            sda.UpdateCommand = new SqlCommand(SQL_UPDATE, con);
            sda.DeleteCommand = new SqlCommand(SQL_DELETE, con);
            sda.InsertCommand = new SqlCommand(SQL_INSERT, con);
            sda.UpdateCommand.Parameters.AddRange(GetUpdatePars());
            sda.InsertCommand.Parameters.AddRange(GetInsertPars());
            sda.DeleteCommand.Parameters.AddRange(GetDeletePars());
            return sda;
        }
        //三个SqlCommand的参数
        private static SqlParameter[] GetInsertPars()
        {
            SqlParameter[] pars = new SqlParameter[3];
            pars[0] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department");
            pars[1] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item");
            pars[2] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number");
            return pars;
        }
        private static SqlParameter[] GetUpdatePars()
        {
            SqlParameter[] pars = new SqlParameter[4];
            pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id");
            pars[1] = new SqlParameter("@Department", SqlDbType.VarChar, 50, "Department");
            pars[2] = new SqlParameter("@Item", SqlDbType.VarChar, 50, "Item");
            pars[3] = new SqlParameter("@Number", SqlDbType.Int, 4, "Number");
            return pars;
        }
        private static SqlParameter[] GetDeletePars()
        {
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@Id", SqlDbType.VarChar, 50, "Id");
            return pars;
        }
        /// <summary>
        /// 检索数据，内部使用SelectCommand
        /// </summary>
        /// <returns>返回DataSet对象</returns>
        public static DataSet GetDataSet()
        {
            SqlConnection con = new SqlConnection(conn_String);
            DataSet ds = new DataSet();
            try
            {
                con.Open();
                using (SqlDataAdapter sda = GetDataAdapter(con))
                {
                    sda.Fill(ds);
                    return ds;
                }
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        /// <summary>
        /// 更新数据库，使用批量更新
        /// </summary>
        public static void Update(DataSet ds)
        {
            SqlConnection con = new SqlConnection(conn_String);
            try
            {
                con.Open();
                using (SqlDataAdapter sda = GetDataAdapter(con))
                {
                    //设置BatchUpdate
                    sda.UpdateBatchSize = 0;
                    sda.Update(ds);
                }
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
    }
}

调用执行：

using System;
using System.Data;
using System.Data.SqlClient;
namespace NET.MST.Ninth.BatchUpdate
{
    /// <summary>
    /// 进行测试
    /// </summary>
    partial class User
    {
        static void Main(string[] args)
        {
            //打印初始表
            Console.WriteLine("表DepartCost：");
            DataSet ds = DataHelper.GetDataSet();
            PrintDataSet(ds);
            //尝试插入一行
            Console.WriteLine("执行插入后的表DepartCost：");
            InsertNewRow(ds);
            DataHelper.Update(ds);
            ds = DataHelper.GetDataSet();
            PrintDataSet(ds);
            //修改新插入行的值
            Console.WriteLine("执行修改后的表DepartCost：");
            EditRow(ds);
            DataHelper.Update(ds);
            ds = DataHelper.GetDataSet();
            PrintDataSet(ds);
            //删除新插入的行
            Console.WriteLine("执行删除后的表DepartCost：");
            DeleteRow(ds);
            DataHelper.Update(ds);
            ds = DataHelper.GetDataSet();
            PrintDataSet(ds);
            //等待
            Console.Read();
        }
        static void PrintDataSet(DataSet ds)
        {
            //打印结果
            DataTable dt = ds.Tables[0];
            //打印列名
            foreach (DataColumn column in dt.Columns)
                Console.Write("{0}  ", column.ColumnName);
            Console.Write("\r\n");
            //打印表
            foreach (DataRow row in dt.Rows)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    Console.Write("{0}  ", row[i].ToString());
                }
                Console.Write("\r\n");
            }
        }
    }
    /// <summary>
    /// 插入、修改、删除DataSet中的行
    /// </summary>
    partial class User
    {
        static void InsertNewRow(DataSet ds)
        {
            DataRow newrow = ds.Tables[0].NewRow();
            newrow["Department"] = "F5";
            newrow["Item"] = "Item5";
            newrow["Number"] = 5;
            ds.Tables[0].Rows.Add(newrow);
        }
        static void EditRow(DataSet ds)
        {
            int count = ds.Tables[0].Rows.Count;
            ds.Tables[0].Rows[count - 1]["Department"] = "newfac";
        }
        static void DeleteRow(DataSet ds)
        {
            int count = ds.Tables[0].Rows.Count;
            ds.Tables[0].Rows[count - 1].Delete();
        }
    }
}