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;
}
以上就是【C# 根据DataTable的数据变更动态生成SQL语句保存到数据库】的全部内容了,欢迎留言评论进行交流!