C# 根据DataTable的数据变更动态生成SQL语句保存到数据库

        public static DataTable GetDataTableInfo(string ConnectionString, string tablename)
        {
            string sql = string.Format(@"select c.colid,ColumnName=c.name,type=t.name,c.status,
                (case when left(t.name,5)='ntext'  then 1073741823  when left(t.name,5)='text'  then 214483647
                       else (case when left(t.name,1)='n'  then  c.length/2 else c.length end) end) as Length,c.colstat,c.isnullable,c.xprec,c.xscale
                from syscolumns c left join systypes t on c.xtype=t.xtype and t.name <>'sysname'  where id = object_id('{0}') 
                order by c.colid", tablename);
            DataTable tabResult = GetDataTable(ConnectionString, sql, tablename + "_Struct");
            tabResult.PrimaryKey = new DataColumn[] { tabResult.Columns["ColumnName"] };
            return tabResult;
        } 

        public static string GetAddSQLScript(DataTable dtStruct, DataTable dtSource, DataRow drNew)
        {
            StringBuilder sbInsert = new StringBuilder("exec sp_executesql N'Insert Into " + dtSource.TableName + "(");
            StringBuilder sbValues = new StringBuilder();
            StringBuilder sbDefine = new StringBuilder();
            StringBuilder sbParamer = new StringBuilder();
            string strColumnName = string.Empty;
            for (int i = 0; i < dtStruct.Rows.Count; i++)
            {
                strColumnName = dtStruct.Rows[i]["ColumnName"].ToString();
                if (dtStruct.Rows[i]["status"].ToString().Equals("128") || dtStruct.Rows[i]["status"].ToString().Equals("72")) continue;

                if (dtSource.Columns.Contains(strColumnName) == false || drNew[strColumnName] == DBNull.Value)
                {
                    continue;
                }
                sbInsert.Append(strColumnName + ",");
                sbValues.AppendFormat("@{0},", strColumnName);
                sbDefine.AppendFormat("{0},", GetSQLParamerDefine(dtStruct.Rows[i]));

                if (dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "int" || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "decimal"
                    || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "tinyint" || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "smallint"
                    || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "money" || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "float"
                    || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "numeric" || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "bigint"
                    || dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "long")
                {
                    if (drNew[strColumnName].ToString() != "")
                    {
                        sbParamer.AppendFormat("@{0}={1},", strColumnName, drNew[strColumnName].ToString());
                    }
                    else
                    {
                        sbParamer.AppendFormat("@{0}=NULL,", strColumnName);
                    }
                }
                else if (dtStruct.Rows[i]["Type"].ToString().ToLower().Trim() == "datetime")
                {
                    if (drNew[strColumnName].ToString() != "")
                    {
                        if (GetDateTimeString(drNew[strColumnName], "yyyy/MM/dd") == DateTime.MinValue.ToString("yyyy/MM/dd"))
                        {
                            sbParamer.AppendFormat("@{0}=NULL,", strColumnName);
                        }
                        else
                        {
                            sbParamer.AppendFormat("@{0}='{1}',", strColumnName, drNew[strColumnName].ToString());
                        }
                    }
                    else
                    {
                        sbParamer.AppendFormat("@{0}=NULL,", strColumnName);
                    }
                }
                else
                {
                    sbParamer.AppendFormat("@{0}='{1}',", strColumnName, drNew[strColumnName].ToString().Replace("'", "''"));
                }

            }

            sbInsert.Length = sbInsert.Length - 1;
            sbValues.Length = sbValues.Length - 1;
            sbDefine.Length = sbDefine.Length - 1;
            sbParamer.Length = sbParamer.Length - 1;

            sbInsert.Append(")").AppendLine().Append("Values(").Append(sbValues.ToString()).AppendLine(")',");
            sbInsert.Append("N'").Append(sbDefine.ToString()).AppendLine("',");
            sbInsert.Append(sbParamer.ToString()).AppendLine(";");
            sbInsert.AppendLine("select @@Identity");
            return sbInsert.ToString();
        }

        public static string GetModifySQLScript(DataTable dtStruct, DataTable dtSource, DataRow drModify)
        {
            DataRow[] _drs = dtStruct.Select("colstat =1"); ;
            string _primkey = string.Empty;
            if (_drs != null && _drs.Length > 0) _primkey = _drs[0]["ColumnName"].ToString();
            return GetModifySQLScript(dtStruct, dtSource, drModify, _primkey);
        }

        public static string GetModifySQLScript(DataTable dtStruct, DataTable dtSource, DataRow drModify, string strkeyColumn)
        {
            StringBuilder sbModify = new StringBuilder("exec sp_executesql N'Update " + dtSource.TableName + " Set ");
            StringBuilder sbDefine = new StringBuilder();
            StringBuilder sbParamer = new StringBuilder();
            DataRow _drCurrentStruct = null;
            bool isChanged = false;
            string ColumnName = "";

            for (int i = 0; i < dtSource.Columns.Count; i++)
            {
                ColumnName = dtSource.Columns[i].ColumnName.ToString();
                _drCurrentStruct = dtStruct.Rows.Find(ColumnName);
                if (_drCurrentStruct == null) continue;
                //PrimaryKey or Computer Column
                if (_drCurrentStruct["Status"].ToString().Equals("128") || _drCurrentStruct["status"].ToString().Equals("72")) continue;
                //Column Un Changed
                if (drModify[ColumnName, DataRowVersion.Current].ToString() == drModify[ColumnName, DataRowVersion.Original].ToString()) continue;

                sbModify.AppendFormat(" {0}=@{0},", ColumnName);
                sbDefine.Append(GetSQLParamerDefine(_drCurrentStruct)).Append(",");
                if (_drCurrentStruct["Type"].ToString().ToLower().Trim() == "int" || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "decimal"
                    || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "tinyint" || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "smallint"
                    || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "money" || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "float"
                    || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "numeric" || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "bigint"
                    || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "long")
                {
                    if (drModify[ColumnName, DataRowVersion.Current].ToString() != "")
                    {
                        isChanged = true;
                        sbParamer.AppendFormat("@{0}={1},", ColumnName, drModify[ColumnName, DataRowVersion.Current].ToString());
                    }
                    else
                    {
                        isChanged = true;
                        sbParamer.AppendFormat("@{0}=NULL,", ColumnName);
                    }
                }
                else if (_drCurrentStruct["Type"].ToString().ToLower().Trim() == "datetime" || _drCurrentStruct["Type"].ToString().ToLower().Trim() == "date")
                {

                    if (drModify[ColumnName, DataRowVersion.Current].ToString() != "")
                    {
                        if (GetDateTimeString(drModify[ColumnName, DataRowVersion.Current], "yyyy/MM/dd") == DateTime.MinValue.ToString("yyyy/MM/dd"))
                        {
                            isChanged = true;
                            sbParamer.AppendFormat("@{0}=NULL,", ColumnName);
                        }
                        else
                        {
                            isChanged = true;
                            sbParamer.AppendFormat("@{0}='{1}',", ColumnName, GetDateTimeString(drModify[ColumnName, DataRowVersion.Current], "yyyy/MM/dd HH:mm:ss"));
                        }
                    }
                    else
                    {
                        isChanged = true;
                        sbParamer.AppendFormat("@{0}=NULL,", ColumnName);
                    }
                }
                else
                {
                    isChanged = true;
                    sbParamer.AppendFormat("@{0}='{1}',", ColumnName, drModify[ColumnName, DataRowVersion.Current].ToString().Replace("'", "''"));
                }

            }
            if (!isChanged) return string.Empty;

            sbModify.Length = sbModify.Length - 1;
            sbDefine.Length = sbDefine.Length - 1;
            sbParamer.Length = sbParamer.Length - 1;
            sbModify.AppendFormat(" where {0}=''{1}''", strkeyColumn, drModify[strkeyColumn].ToString().Trim());
            sbModify.AppendLine("',").Append("N'").Append(sbDefine.ToString()).AppendLine("',");
            sbModify.Append(sbParamer.ToString());
            return sbModify.ToString();
        }

        private static string GetSQLParamerDefine(DataRow _RowColumnInfo)
        {
            string SQLParamer = string.Empty;
            switch (_RowColumnInfo["Type"].ToString())
            {
                case "int":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "decimal":
                    SQLParamer = string.Format("@{0} {1}({2}, {3})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["xprec"], _RowColumnInfo["xscale"]);
                    break;
                case "text":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "smallint":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "varchar":
                    SQLParamer = string.Format("@{0} {1}({2})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["length"]);
                    break;
                case "datetime":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "numeric":
                    SQLParamer = string.Format("@{0} {1}({2}, {3})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["xprec"], _RowColumnInfo["xscale"]);
                    break;
                case "tinyint":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "nchar":
                    SQLParamer = string.Format("@{0} {1}({2})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["length"]);
                    break;
                case "char":
                    SQLParamer = string.Format("@{0} {1}({2})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["length"]);
                    break;
                case "bigint":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "nvarchar":
                    SQLParamer = string.Format("@{0} {1}({2})", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"], _RowColumnInfo["length"]);
                    break;
                case "bit":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "money":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                case "float":
                    SQLParamer = string.Format("@{0} {1}", _RowColumnInfo["ColumnName"], _RowColumnInfo["Type"]);
                    break;
                default:
                    break;
            }
            return SQLParamer;
        }
赞(0) 踩(0)
发表我的评论

最新评论

  1. 暂无评论