构造SqlDataAdapter InsertCommand和UpdateCommand 帮助类解决并发冲突

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DataBaseManager
{
    /// <summary>
    /// 构造SqlCommand语句
    /// </summary>
    internal sealed class DBCommand
    {
        #region CommandBuilder() 构造InsertCommand和UpdateCommand
        /// <summary>
        /// 构造InsertCommand和UpdateCommand中的字段赋值参数
        /// </summary>
        /// <param name="fda">SqlDataAdapter</param>
        /// <param name="fcField">字段</param>
        /// <param name="fnLength">字段长度</param>
        /// <param name="fcType">字段类型</param>
        internal static void CommandBuilder(SqlDataAdapter fda, string fcField, int fnLength, string fcType)
        {
            switch (fcType)
            {
                case ("bit"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.Bit, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.Bit, fnLength, fcField);
                    break;
                case ("bigint"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.BigInt, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.BigInt, fnLength, fcField);
                    break;
                case ("char"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.Char, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.Char, fnLength, fcField);
                    break;
                case ("datetime"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.DateTime, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.DateTime, fnLength, fcField);
                    break;
                case ("int"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.Int, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.Int, fnLength, fcField);
                    break;
                case ("smalldatetime"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.SmallDateTime, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.SmallDateTime, fnLength, fcField);
                    break;
                case ("smallint"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.SmallInt, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.SmallInt, fnLength, fcField);
                    break;
                case ("tinyint"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.TinyInt, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.TinyInt, fnLength, fcField);
                    break;
                case ("varchar"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.VarChar, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.VarChar, fnLength, fcField);
                    break;
                case ("nvarchar"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.NVarChar, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.NVarChar, fnLength, fcField);
                    break;
                case ("numeric"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.Decimal, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.Decimal, fnLength, fcField);
                    break;
                case ("ntext"):
                    fda.InsertCommand.Parameters.Add("@" + fcField, SqlDbType.NText, fnLength, fcField);
                    fda.UpdateCommand.Parameters.Add("@" + fcField, SqlDbType.NText, fnLength, fcField);
                    break;
                default:
                    throw new ArgumentException(fcField + " 类型字段未处理!");

            }

        }
        #endregion

        #region CommandBuilder() 构造InsertCommand和UpdateCommand的原值参数
        /// <summary>
        /// 构造InsertCommand和UpdateCommand的原值参数
        /// </summary>
        /// <param name="fda">SqlDataAdapter</param>
        /// <param name="fcField">字段</param>
        /// <param name="fnLength">字段长度</param>
        /// <param name="fcType">字段类型</param>
        /// <param name="sp">SqlParameter</param>
        internal static void CommandBuilder(SqlDataAdapter fda, string fcField, int fnLength, string fcType, SqlParameter sp)
        {
            switch (fcType)
            {
                case ("bit"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.Bit, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.Bit, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;
                case ("bigint"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.BigInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.BigInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;
                case ("char"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.Char, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.Char, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;
                case ("datetime"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.DateTime, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.DateTime, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("int"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.Int, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.Int, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("smalldatetime"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.SmallDateTime, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.SmallDateTime, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("smallint"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.SmallInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.SmallInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("tinyint"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.TinyInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.TinyInt, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("varchar"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.VarChar, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.VarChar, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("numeric"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.Decimal, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.Decimal, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("ntext"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.NText, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.NText, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                case ("nvarchar"):

                    sp = fda.UpdateCommand.Parameters.Add("@old_" + fcField, SqlDbType.NVarChar, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    sp = fda.DeleteCommand.Parameters.Add("@old_" + fcField, SqlDbType.NVarChar, fnLength, fcField);
                    sp.SourceColumn = fcField;
                    sp.SourceVersion = DataRowVersion.Original;

                    break;

                default:
                    throw new ArgumentException(fcField + " 类型字段未处理!");

            }
        }
        #endregion
    }
}