C#使用反射根据实体生成sql语句（添加和批量添加）

最新推荐文章于 2021-08-20 14:26:07 发布

转载最新推荐文章于 2021-08-20 14:26:07 发布 · 3.2k 阅读

本文介绍了一个实用的SQL语句生成工具，名为SqlBuilderHelper。该工具能够将对象转换为Insert和BulkInsert SQL语句，适用于批量数据插入场景。通过反射获取对象属性，构造SQL语句，提高开发效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/// <summary>
/// 对象拼接sql语句
/// </summary>
public class SqlBuilderHelper
{
/// <summary>
/// Insert SQL语句
/// </summary>
/// <param name="obj">要转换的对象，不可空</param>
/// <param name="tableName">要添加的表明，不可空</param>
/// <returns>
/// 空
/// sql语句
/// </returns>
public static string InsertSql<T>(T t, string tableName) where T : class
{
if (t == null || string.IsNullOrEmpty(tableName))
{
return string.Empty;
}
string columns = GetColmons(t);
if (string.IsNullOrEmpty(columns))
{
return string.Empty;
}
string values = GetValues(t);
if (string.IsNullOrEmpty(values))
{
return string.Empty;
}
StringBuilder sql = new StringBuilder();
sql.Append("Insert into " + tableName);
sql.Append("(" + columns + ")");
sql.Append(" values(" + values + ")");
return sql.ToString();
}

/// <summary>
/// BulkInsert SQL语句（批量添加）
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="objs">要转换的对象集合，不可空</param>
/// <param name="tableName">>要添加的表明，不可空</param>
/// <returns>
/// 空
/// sql语句
/// </returns>
public static string BulkInsertSql<T>(List<T> objs, string tableName) where T : class
{
if (objs == null || objs.Count == 0 || string.IsNullOrEmpty(tableName))
{
return string.Empty;
}
string columns = GetColmons(objs[0]);
if (string.IsNullOrEmpty(columns))
{
return string.Empty;
}
string values = string.Join(",", objs.Select(p => string.Format("({0})", GetValues(p))).ToArray());
StringBuilder sql = new StringBuilder();
sql.Append("Insert into " + tableName);
sql.Append("(" + columns + ")");
sql.Append(" values " + values + "");
return sql.ToString();
}

/// <summary>
/// 获得类型的列名
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string GetColmons<T>(T obj)
{
if (obj == null)
{
return string.Empty;
}
return string.Join(",", obj.GetType().GetProperties().Select(p => p.Name).ToList());
}

/// <summary>
/// 获得值
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string GetValues<T>(T obj)
{
if (obj == null)
{
return string.Empty;
}
return string.Join(",", obj.GetType().GetProperties().Select(p => string.Format("'{0}'", p.GetValue(obj))).ToArray());
}
}