根据DTO结构，生成 insert Update SQL 语句

最新推荐文章于 2025-10-10 00:54:34 发布

原创最新推荐文章于 2025-10-10 00:54:34 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

Linq 专栏收录该内容

2 篇文章

订阅专栏

本文探讨如何基于DTO（Data Transfer Object）对象的结构，自动生成用于数据库插入和更新操作的SQL语句，简化开发过程并提高效率。

private string GetInsertSql<T>(T dto, string tableOwnerSchema)
{
string sqlTemplate = @"INSERT INTO [{0}].[{1}] ({2}) values ({3});";

string columnName = string.Empty;
string columnVal = string.Empty;

var propList = typeof(T).GetProperties();
foreach (PropertyInfo prop in propList)
{
if (prop.Name != "columnNameA" && prop.Name != "columnNameB")
{
columnName += "[" + prop.Name + "],";
columnVal += "@" + prop.Name + ",";
}
}
columnVal.Remove(columnVal.Length - 1, 1);//del ,
return string.Format(sqlTemplate, tableOwnerSchema, typeof(T).Name, columnName.Substring(0, columnName.Length - 1), columnVal.Substring(0, columnVal.Length - 1));

}

private string GetUpdateSql<T>(T dto, string tableOwnerSchema)
{
string sqlTemplate = @"Update [{0}].[{1}] Set {2} WHere Id = @Id;";

string strsql = string.Empty;
var propList = typeof(T).GetProperties();
foreach (PropertyInfo prop in propList)
{
if (prop.Name != "columnNameA" && prop.Name != "columnNameB")
{
strsql += "[" + prop.Name + "] = @" + prop.Name + ",";
}
}
return string.Format(sqlTemplate, tableOwnerSchema, typeof(T).Name, strsql.Substring(0, strsql.Length - 1));
}