曾经有碰到过一个表中几十上百个字段,各个部门均需要修改记录,因数据库架构早定型,修改的话工作量会比较大,所以针对这种情况特意写了个方法,根据当前datatable 数据修改的记录以及数据库表的数据结构,动态生成SQL语句。
如下获取数据库中表的结构:
public static DataTable GetDataTableInfo(string ConnectionString, string tablename)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine("select c.colid,ColumnName=c.name,type=t.name,c.status,");
sql.AppendLine(" Length=(case when left(t.name,5)='ntext' then 1073741823 when left(t.name,5)='text' then 214483647 ");
sql.AppendLine(" else (case when left(t.name,1)='n' then c.length/2 else c.length end) end),");
sql.AppendLine(" c.isnullable,c.xprec,c.xscale,colstat=case when c.colid =k.colid then 1 else null end");
sql.AppendLine("from syscolumns c ");
sql.AppendLine("left join systypes t on c.xtype=t.xtype and t.name <>'sysname' ");
sql.AppendLine("left join sysobjects o on c.id = o.parent_obj and o.xtype='PK'");
sql.AppendLine(" inner join sysindexes i on o.parent_obj = i.id and o.name = i.name ");
sql.AppendLine(" inner join sysindexkeys k on o.parent_obj = k.id and k.indid=i.indid");
sql.AppendLine("where c.id = object_id('" + tablename + "') ");
sql.AppendLine("order by c.colid");
DataTable tabResult = GetDataTable(ConnectionString, sql.ToString(), tablename);
tabResult.PrimaryKey = new DataColumn[] { tabResult.Columns["ColumnName"] };
return tabResult;
}
根据上面获取的数据表结构,动态生成的SQL语句只会针对修改的行与列,同一行中,未修改的字段不会生成。
同时也避免了因数据库中表字段增加、减少或长度更改而必须修改相应代码的问题。
示例代码:
foreach (DataRow _row in tab.Rows)
{
if (_row.RowState == DataRowState.Added)
{
sql = SQLHelper.GetAddSQLScript(dtStruct, tab, _row);
object obj = SQLHelper.ExecuteScalar(strConnection, sql);
_row[strPrimkey] = obj;
}
else if (_row.RowState == DataRowState.Modified)
{
sql = SQLHelper.GetModifySQLScript(dtStruct, tab, _row);
SQLHelper.ExecuteNonQuery(strConnection, sql);
}
}
具体代码请参考链接:https://download.csdn.net/download/cftyqing/12001755