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());
                        }