调用《构造SqlDataAdapter InsertCommand和UpdateCommand 帮助类解决并发冲突》方法

 

#region BuilderDataAdapter 构造DataAdapter对象,解决并发问题,并填充DataSet对象
        /// <summary>
        /// 构造DataAdapter对象解决并发问题,并填充DataSet对象,使用此方法时需要在数据库中建立名为：v_TableFiled 的视图（参照《SQL2000、SQL2005和SQL2008用户表字典视图》）
        /// </summary>
        /// <remarks>
        /// 示例：
        /// BuilderDataAdapter("select a.aaa,a.bbb as abb,b.aaa as baa,b.bbb from tablea a,tableb","tablea","baa,bbb",true,da,ds,conn);
        /// </remarks>
        /// <param name="selectCommandText">SQL Select 语句</param>
        /// <param name="tableName">需要更新的表名</param>
        /// <param name="primaryKey">Update 关键字</param>
        /// <param name="noUpdateField">不需要更新的字段</param>
        /// <param name="isInit">是否第一次处理</param>
        /// <param name="da">有效的数据配置器</param>
        /// <param name="ds">数据集</param>
        /// <param name="connection">有效的数据库连接对象</param>
        public static void BuilderDataAdapter(string selectCommandText, string tableName, string primaryKey, string noUpdateField,bool isInit, SqlDataAdapter da, DataSet ds, SqlConnection connection)
        {
            #region 1 第一次啟用時進行SQL語句處理及构造InserrCommand、UpdateCommand、DeleteCommand

            if (isInit)
            {
                #region 1.1 selectCommandText、NoUpdateField、Key等字符串规范化处理
                //*************************************************************
                //注意：
                //1 selectCommandText语句中，需含有select 和 from关键字
                //2 selectCommandText语句中，支持isnull,ltrim,rtrim,convert,case when 等关键字
                //3 cSelectSQL语句中，若非原表字段，则需加as 如：rtrim(isnull(dep_bmmc,'')) as dep_bmmc
                //*************************************************************

                #region 1.1.1 selectCommandText、NoUpdateField、Key等字符串合法性校验
                //1selectSQL、NoUpdateField等字符串合法性校验
                if (string.IsNullOrEmpty(selectCommandText) || string.IsNullOrEmpty(tableName))
                {
                    throw new ArgumentException("selectCommandText、Key等字符串有空值，无法解析!");                  
                }
                #endregion

                #region 1.1.2 selectCommandText语句处理

                string lcSQLStr = selectCommandText.ToLower();
                lcSQLStr = lcSQLStr.Substring(lcSQLStr.IndexOf("select") + 6, lcSQLStr.IndexOf("from") - (lcSQLStr.IndexOf("select") + 6)).Trim() + ",";
                lcSQLStr = lcSQLStr.Replace("\n", "").Replace("\r", "").Trim();     //移除回车换行符

                string lcSQLStrNew = "";

                string[] lcSQLField = lcSQLStr.Split(',');
                for (int i = 0; i < lcSQLField.Length; i++)
                {
                    lcSQLField[i] = lcSQLField[i].Trim();

                    if (lcSQLField[i].IndexOf('(') >= 0 && lcSQLField[i].IndexOf(" as ") <= 0)       // 有左括号但没有 AS,忽略
                    {
                        continue;
                    }
                    if (lcSQLField[i].IndexOf(" as ") >= 0)             //有 AS
                    {
                        lcSQLStrNew += lcSQLField[i].Substring(lcSQLField[i].IndexOf(" as ") + 4).Trim() + '\n';
                        continue;
                    }

                    if (lcSQLField[i].IndexOf(' ') < 0)      // 无空格符,直接是字段名.
                    {
                        lcSQLStrNew += lcSQLField[i] + '\n';
                        continue;
                    }
                    else
                    {
                        throw new ArgumentException("未能处理的Select语句,请检查查询语句!");
                    }
                }
                lcSQLStrNew = lcSQLStrNew.Replace("\n\n", "\n");
                #endregion

                #region 1.1.3 noUpdateField处理

                noUpdateField = noUpdateField.Replace(";", ",");
               
                if (noUpdateField.Substring(noUpdateField.Length - 1, 1) != ",")
                {
                    noUpdateField += ",";       // 在noUpdateField后补入逗号,用于在判断是否包含时加逗号判断.以避免字段名前部分字符相同的情况.
                }
                #endregion

                #endregion

                #region 1.2 构造InserSQL、字段信息WhereSQL

                string lcInsertSQL1 = " insert " + tableName + "(";                 //Insert SQL语句1
                string lcInsertSQL2 = " values( ";                                      //Insert SQL语句2
                string lcInsertSQL = "";                                                //Inser SQL语句                            

                string[] lcSQLField1 = lcSQLStrNew.Split('\n');
                string lcWhereField = "(";                                                       //Where  SQL语句
                for (int i = 0; i < lcSQLField1.Length; i++)
                {
                    if (noUpdateField.IndexOf(lcSQLField1[i] + ",") < 0)
                    {
                        lcWhereField += "'" + lcSQLField1[i] + "',";
                        lcInsertSQL1 += lcSQLField1[i] + ",";
                        lcInsertSQL2 += "@" + lcSQLField1[i] + ",";
                    }
                }

                //对字符串尾部再进行处理一下，构造成符合语法的结构
                lcWhereField = lcWhereField.Substring(0, lcWhereField.LastIndexOf(",")) + ")";

                #endregion

                #region 1.3 查詢表結構信息,構造UpdateSQL、DeleteSQL、WhereSQL

                string lcUpdateSQL = " update " + tableName + " set ";            //Update SQL语句
                string lcUpdateSQLWhere = " where 1 = 1 ";
                string lcDeleteSQL = " delete " + tableName + " where 1=1 ";  // Delete SQL 语句

                string lcSelectViewSQL = "";
                lcSelectViewSQL += " select field,datatype,length,isnullable from v_TableFiled ";
                lcSelectViewSQL += " where tables='" + tableName + "'";
                lcSelectViewSQL += " and field in " + lcWhereField;

                SqlCommand parameterCommand = new SqlCommand(lcSelectViewSQL, connection);
                SqlDataReader parameterReader;
                try
                {
                    parameterReader = parameterCommand.ExecuteReader();
                }
                catch (SqlException e)
                {
                    string errorMessages = "查询表结构出错，错误原因是：\n";
                    for (int ii = 0; ii < e.Errors.Count; ii++)
                    {
                        errorMessages += e.Errors[ii].Message + "\n";
                    }
                    throw new ArgumentException(errorMessages);
                }

                SqlParameter[] sp = new SqlParameter[lcSQLField1.Length];
                int nKeyFldNo = 0;
                List<DBColumnInfo> columnList = new List<DBColumnInfo>();
                DBColumnInfo columnInfo;

                while (parameterReader.Read())
                {
                    columnInfo = new DBColumnInfo();
                    columnInfo.Field = parameterReader.GetString(0).ToString().Trim();
                    columnInfo.DataType = parameterReader.GetString(1).ToString().Trim();
                    columnInfo.Length = int.Parse(parameterReader.GetString(2).ToString());
                    columnInfo.IsNullAble = parameterReader.GetInt32(3);

                    columnList.Add(columnInfo);

                    //SET語句
                    lcUpdateSQL += columnInfo.Field + "=@" + columnInfo.Field + ",";
                    //判斷字段是否允許為空,並給DeleteCommand和UpdateCommand語句添加原值比較
                    if (columnInfo.IsNullAble == 1)
                    {
                        lcDeleteSQL += " and (" + columnInfo.Field.Trim() + " is null OR " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim() + ")";
                        lcUpdateSQLWhere += " and (" + columnInfo.Field.Trim() + " is null OR " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim() + ")";
                    }
                    else
                    {
                        lcDeleteSQL += " and " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim();
                        lcUpdateSQLWhere += " and " + columnInfo.Field.Trim() + "=@old_" + columnInfo.Field.Trim();
                    }

                }
                parameterReader.Close();

                lcInsertSQL1 = lcInsertSQL1.Substring(0, lcInsertSQL1.LastIndexOf(",")) + ")";
                lcInsertSQL2 = lcInsertSQL2.Substring(0, lcInsertSQL2.LastIndexOf(",")) + ")";
                lcUpdateSQL = lcUpdateSQL.Substring(0, lcUpdateSQL.LastIndexOf(","));

                lcInsertSQL += lcInsertSQL1 + lcInsertSQL2;
                lcUpdateSQL += lcUpdateSQLWhere;       //+ primaryKey + "=@old_" + primaryKey;

                #endregion

                #region 1.4 构造InserrCommand、UpdateCommand、DeleteCommand

                da.InsertCommand = new SqlCommand(lcInsertSQL, connection);

                da.UpdateCommand = new SqlCommand(lcUpdateSQL, connection);

                da.DeleteCommand = new SqlCommand(lcDeleteSQL, connection);

                //構造Command參數
                for (int i = 0; i < columnList.Count; i++)
                {
                    columnInfo = columnList[i];
                    DBCommand.CommandBuilder(da, columnInfo.Field, columnInfo.Length, columnInfo.DataType);
                    //設置保存原值
                    nKeyFldNo++;
                    sp[nKeyFldNo] = new SqlParameter();
                    DBCommand.CommandBuilder(da, columnInfo.Field, columnInfo.Length, columnInfo.DataType, sp[nKeyFldNo]);

                }

                #endregion
            }

            #endregion

            #region 2 查询数据
            da.SelectCommand = new SqlCommand(selectCommandText, connection);
            if (ds.Tables.Contains(tableName) == true)
            {
                ds.Tables[tableName].Clear();
            }
            try
            {
                da.Fill(ds, tableName);
            }
            catch (SqlException e)
            {
                string errorMessages = "查询后台数据出错，错误原因是：\n";

                for (int ii = 0; ii < e.Errors.Count; ii++)
                {
                    errorMessages += e.Errors[ii].Message + "\n";
                }
                throw new ArgumentException(errorMessages);
            }
            #endregion
        }