batchhelper.cs

我的一个Ado.Net一个框架设计

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Library;
namespace DAL
{
    public static  partial class BatchHelper
    {
        /// <summary>
        /// 批量更新一个已经经过update的表
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="changeTable">变化（修改过的）后的表</param>
        /// <param name="batchSize">一次批量更新的大小</param>
        /// <param name="setColumn">要更新的列</param>
        /// <param name="whereColumn">要更新列的条件</param>
        /// <param name="columnInfo">参数信息，包括变量名，类型，大小和变量的值，即表中的要改变列的列名</param>
        /// <returns>返回受影响的行数</returns>
        public static int BatchUpdate(string connString, DataTable updatingTable, Int32 batchSize,string[] setColumn, string[] whereColumn, SqlParameter[] columnInfo)
        {
            string columnSetName = GetUpdateColumn(setColumn);
            string whereValue = GetWhereString(whereColumn);
            string sqlUpdate = "update " + updatingTable.TableName + " set " + columnSetName  + whereValue ;
            string sqlInsert = "Insert into " + updatingTable.TableName + " values(" + columnSetName + ")";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.UpdateCommand = new SqlCommand(sqlUpdate, conn);                
                foreach (SqlParameter param  in columnInfo)
                {
                    adapter.UpdateCommand.Parameters.Add(param);                    
                }
                adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;                
                adapter.UpdateBatchSize = batchSize;
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    int affectedRow = adapter.Update(updatingTable);
                    return affectedRow;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 批量插入一个表中行状态维Insert的行
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="addingTable">要添加的表</param>
        /// <param name="batchSize">批量更新的大小</param>
        /// <param name="columnInfo">参数信息，包括变量名，类型，大小和变量的值，即表中的要改变列的列名</param>
        /// <returns>返回受影响的行数</returns>
        public static int BatchInsert(string connString, DataTable addingTable, Int32 batchSize, SqlParameter[] columnInfo)
        {
            string columnParam;
            string columnName = GetInsertColumn(addingTable,out columnParam);
            string sqlInsert = "Insert into " + addingTable.TableName + "(" + columnName + ") values(" + columnParam + ")";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.InsertCommand = new SqlCommand(sqlInsert, conn);
                foreach (SqlParameter param in columnInfo)
                {
                    adapter.InsertCommand.Parameters.Add(param);
                }
                adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                adapter.UpdateBatchSize = batchSize;
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    int affectedRow = adapter.Update(addingTable);
                    return affectedRow;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 批量删除一个表中行状态维Delete的表
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="deletingTable">要删除的表</param>
        /// <param name="batchSize">批量更新大小</param>
        /// <param name="whereColumn">要更新列的条件</param>
        /// <param name="columnInfo">参数信息，包括变量名，类型，大小和变量的值，即表中的要改变列的列名</param>
        /// <returns></returns>
        public static int BatchDelete(string connString, DataTable deletingTable, Int32 batchSize, string[] whereColumn, SqlParameter[] columnInfo)
        {
            string columnParam = GetWhereString(whereColumn);
            string sqlDelete = "delete from " + deletingTable.TableName + columnParam;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.DeleteCommand = new SqlCommand(sqlDelete, conn);
                foreach (SqlParameter param in columnInfo)
                {
                    adapter.DeleteCommand.Parameters.Add(param);
                }
                adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
                adapter.UpdateBatchSize = batchSize;
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    int affectedRow = adapter.Update(deletingTable);
                    return affectedRow;
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
        }
    }
}