利用反射自动生成SQL语句（仿Linq）
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data;
public class SQLHelper
{
/// <summary>
/// 插入单个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public static int Insert<T>(T entity)
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "INSERT INTO [" + type.Name + "] (";
string colsName = string.Empty;
string colsValues = string.Empty;
foreach (PropertyInfo item in type.GetProperties())
{
if (item.Name.ToLower() == "id") //不插入自动增长列
continue;
if (item.GetValue(entity, null) == null) //null值也不插入
continue;
colsName += "[" + item.Name + "],";
if (item.PropertyType.ToString().Contains("String") || item.PropertyType.ToString().Contains("Date"))
colsValues += "'" + item.GetValue(entity, null) + "',";
else
colsValues += item.GetValue(entity, null) + ",";
}
colsName = colsName.Substring(0, colsName.LastIndexOf(',')); //不要最后一个 ,
colsValues = colsValues.Substring(0, colsValues.LastIndexOf(',')); //不要最后一个 ,
sql += colsName + ") VALUES ( " + colsValues + ")";
#endregion
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 插入多个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ListEntity"></param>
public static void Insert<T>(List<T> ListEntity)
{
#region 生成 SQL 语句
List<string> sqlList = new List<string>();
foreach (var entity in ListEntity)
{
Type type = entity.GetType();
string sql = "INSERT INTO [" + type.Name + "] (";
string colsName = string.Empty;
string colsValues = string.Empty;
foreach (PropertyInfo item in type.GetProperties())
{
if (item.Name.ToLower() == "id") //不插入自动增长列
continue;
if (item.GetValue(entity, null) == null) //null值也不插入
continue;
colsName += "[" + item.Name + "],";
if (item.PropertyType.ToString().Contains("String") || item.PropertyType.ToString().Contains("Date"))
colsValues += "'" + item.GetValue(entity, null) + "',";
else
colsValues += item.GetValue(entity, null) + ",";
}
colsName = colsName.Substring(0, colsName.LastIndexOf(',')); //不要最后一个 ,
colsValues = colsValues.Substring(0, colsValues.LastIndexOf(',')); //不要最后一个 ,
sql += colsName + ") VALUES ( " + colsValues + ");";
sqlList.Add(sql);
}
#endregion
DBHelperSQL.ExcuteTransactionSql(sqlList);
}
/// <summary>
/// 删除单个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Id"></param>
/// <returns></returns>
public static int Delete<T>(int Id)
{
Type type = typeof(T);
string sql = "DELETE [" + type.Name + "] WHERE Id IN ( " + Id + ")";
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 删除单个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="Id"></param>
/// <returns></returns>
public static int Delete<T>(T entity)
{
Type type = typeof(T);
string sql = "DELETE [" + type.Name + "] WHERE Id IN ( ";
foreach (PropertyInfo item in type.GetProperties())
{
if (item.Name.ToLower() == "id") //不插入自动增长列
{
sql += item.GetValue(entity,null) + ")";
break;
}
}
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 删除多个实体
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="Ids">Id 数组</param>
/// <returns></returns>
public static int Delete<T>(int[] Ids)
{
Type type = typeof(T);
string sql = "DELETE [" + type.Name + "] WHERE Id IN ( ";
foreach (var item in Ids)
{
sql += Ids + ",";
}
sql = sql.Substring(0, sql.LastIndexOf(',')); //不要最后一个 ，
sql += " )";
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 删除多个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="listEntity">实体数组</param>
/// <returns></returns>
public static int Delete<T>(List<T> listEntity)
{
if (listEntity == null || listEntity.Count == 0)
{
return 0;
}
Type type = typeof(T);
string sql = "DELETE [" + type.Name + "] WHERE Id IN ( ";
foreach (var entity in listEntity)
{
foreach (PropertyInfo property in entity.GetType().GetProperties())
{
if (property.Name.ToLower() == "id") //不插入自动增长列
{
sql += property.GetValue(entity, null) + ",";
break;
}
}
}
sql = sql.Substring(0, sql.LastIndexOf(',')); //不要最后一个 ，
sql += " )";
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 修改单个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public static int Update<T>(T entity)
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "UPDATE [" + type.Name + "] SET ";
object ID = 0;
foreach (PropertyInfo item in type.GetProperties())
{
if (item.Name.ToLower() == "id") //不插入自动增长列
{
ID = item.GetValue(entity, null);
continue;
}
if (item.GetValue(entity, null) == null) //null值也不插入
continue;
sql += "[" + item.Name + "]=";
if (item.PropertyType.ToString().Contains("String") || item.PropertyType.ToString().Contains("Date"))
sql += "'" + item.GetValue(entity, null) + "',";
else
sql += item.GetValue(entity, null) + ",";
}
sql = sql.Substring(0, sql.LastIndexOf(',')); //不要最后一个,
sql += " WHERE ID=" + ID;
#endregion
return DBHelperSQL.ExecuteNonQuery(sql);
}
/// <summary>
/// 根据ID获取单个实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ID"></param>
/// <returns></returns>
public static T Get<T>(int ID)
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "SELECT * FROM [" + type.Name + "] WHERE ID=" + ID;
#endregion
return DBHelperSQL.ExecuteDataTable(sql).ToSingleEntity<T>();
}
/// <summary>
/// 获取所有实例
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static List<T> Get<T>()
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "SELECT * FROM [" + type.Name + "]";
#endregion
return DBHelperSQL.ExecuteDataTable(sql).ToListEntity<T>();
}
/// <summary>
/// 根据条件查询得到多个实例 (不分页)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public static List<T> Get<T>(T condition)
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "SELECT * FROM [" + type.Name + "] WHERE 1=1";
foreach (PropertyInfo item in type.GetProperties())
{
#region 不加条件处理
if (item.GetValue(condition, null) == null) //null值 不加条件
continue;
if (item.PropertyType.ToString().Contains("Int")) //整形为 -1 时不加条件
{
if (item.GetValue(condition, null).ToString().Equals("-1"))
{
continue;
}
}
else if (item.PropertyType.ToString().Contains("Decimal")) //整形为 -1 时不加条件
{
if (item.GetValue(condition, null).ToString().Equals("-1"))
{
continue;
}
}
#endregion
sql += " AND [" + item.Name + "] ";
if (item.PropertyType.ToString().Contains("String") ) //字符串形用模糊查询
sql += " LIKE "+ "'%" + item.GetValue(condition, null) + "%'";
else if (item.PropertyType.ToString().Contains("Date")){ //日期类型
}else{
sql += " = "+ item.GetValue(condition, null) + "";
}
}
#endregion
return DBHelperSQL.ExecuteDataTable(sql).ToListEntity<T>();
}
/// <summary>
/// 根据条件查询得到多个实例 (带分页)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="condition"></param>
/// <returns></returns>
public static List<T> Get<T>(T condition, int pageIndex, int pageSize, ref int totalCount)
{
#region 生成 SQL 语句
Type type = typeof(T);
string sql = "SELECT TOP " + pageSize + " * FROM [" + type.Name + "] WHERE (ID NOT IN (SELECT TOP " + (pageIndex - 1) * pageSize + " ID FROM [" + type.Name + "] WHERE 1=1 ";
StringBuilder sb = new StringBuilder();
foreach (PropertyInfo item in type.GetProperties())
{
#region 不加条件处理
if (item.GetValue(condition, null) == null) //null值 不加条件
continue;
if (item.PropertyType.ToString().Contains("Int")) //整形为 -1 时不加条件
{
if (item.GetValue(condition, null).ToString().Equals("-1"))
{
continue;
}
}
else if (item.PropertyType.ToString().Contains("Decimal")) //整形为 -1 时不加条件
{
if (item.GetValue(condition, null).ToString().Equals("-1"))
{
continue;
}
}
#endregion
sb.Append(" AND [" + item.Name + "] ");
if (item.PropertyType.ToString().Contains("String")) //字符串形用模糊查询
sql += " LIKE " + "'%" + item.GetValue(condition, null) + "%'";
else if (item.PropertyType.ToString().Contains("Date"))
{ //日期类型
}
else
{
sql += " = " + item.GetValue(condition, null) + "";
}
}
sql += sb.ToString() + "ORDER BY ID )) ORDER BY ID ";
//总记录数
string sqlForCount = "SELECT COUNT(*) FROM [" + type.Name + "] WHERE 1=1 " + sb.ToString();
string count = DBHelperSQL.ExecuteString(sqlForCount);
totalCount = string.IsNullOrEmpty(count) ? 0 : int.Parse(count);
#endregion
return DBHelperSQL.ExecuteDataTable(sql).ToListEntity<T>();
}
}
