public interface IDBHelper
{
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql"></param>
void ExecuteNonQuery(string sql, SqlParameter[] sqlParams = null);
/// <summary>
/// 执行sql返回单一结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
object ExecuteScalar(string sql, SqlParameter[] sqlParams = null);
/// <summary>
/// 事务执行sql
/// </summary>
/// <param name="sql"></param>
void ExecuteTrans(string sql, SqlParameter[] sqlParams = null);
/// <summary>
/// 获取单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
T QuerySingle<T>(T model,string tableName = null) where T : class, new();
/// <summary>
/// 查询数据表的全部数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
IEnumerable<T> QueryAll<T>(string tableName = null) where T : class, new();
/// <summary>
/// 插入实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
void Insert<T>(T model, string tableName = null) where T : class, new();
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
void InsertList<T>(IEnumerable<T> list, string tableName = null) where T : class, new();
/// <summary>
/// 更新实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
void Update<T>(T model, string tableName = null) where T : class, new();
/// <summary>
/// 根据Id删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
void Delete<T>(T model, string tableName = null) where T : class, new();
}
public class SQLHelper : IDBHelper
{
private string connString;
public SQLHelper(string connString = null)
{
if(!string.IsNullOrEmpty(connString))
{
this.connString = connString;
}
else
{
this.connString = ConfigurationManager.ConnectionStrings["connString"].ToString();
}
}
#region 执行sql语句
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sql"></param>
public void ExecuteNonQuery(string sql, SqlParameter[] sqlParams = null)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 执行sql语句，返回单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, SqlParameter[] sqlParams = null)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 事务执行sql
/// </summary>
/// <param name="sql"></param>
public void ExecuteTrans(string sql, SqlParameter[] sqlParams = null)
{
SqlTransaction trans = null;
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
trans = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand(sql, conn);
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
cmd.ExecuteNonQuery();
trans.Commit();
}
}
catch (Exception ex)
{
if (trans != null && trans.Connection != null)
trans.Rollback();
throw ex;
}
}
#endregion
#region 根据实体增删改查
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public void Delete<T>(T model, string tableName = null) where T : class, new()
{
Type type = typeof(T);
if (type.GetProperty("Id") == null)
{
throw new ArgumentNullException(string.Format("实体{0}必须包含主键Id字段", type.Name));
}
string _tableName = GetTableName<T>(tableName);
var Id = type.GetProperty("Id").GetValue(model);
string txtSql = string.Format("Delete from [{0}] Where Id=@id", _tableName);
ExecuteNonQuery(txtSql, new[] { new SqlParameter("@id", Id) });
}
/// <summary>
/// 插入
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public void Insert<T>(T model, string tableName = null) where T : class, new()
{
Type type = typeof(T);
SqlParameter[] sqlParams = type.GetProperties().Where(p => p.Name != "Id").Select(s => new SqlParameter(string.Format("@{0}", s.Name), s.GetValue(model) ?? DBNull.Value)).ToArray();
string txtSql = GetInsertSql(model, tableName);
var result = ExecuteScalar(txtSql, sqlParams);
var idProperty = type.GetProperty("Id");
idProperty.SetValue(model,Convert.ChangeType(result, idProperty.PropertyType));//将插入生成的Id赋值给model
}
/// <summary>
/// 插入多个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <param name="tableName"></param>
public void InsertList<T>(IEnumerable<T> list, string tableName = null) where T : class, new()
{
Type type = typeof(T);
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
foreach (var item in list)
{
SqlParameter[] sqlParams = type.GetProperties().Where(p => p.Name != "Id").Select(s => new SqlParameter(string.Format("@{0}", s.Name), s.GetValue(item) ?? DBNull.Value)).ToArray();
string txtSql = GetInsertSql<T>(item, tableName);
SqlCommand cmd = new SqlCommand(txtSql, conn);
if (sqlParams != null)
cmd.Parameters.AddRange(sqlParams);
var result = cmd.ExecuteScalar();
var idProperty = type.GetProperty("Id");
idProperty.SetValue(item, Convert.ChangeType(result, idProperty.PropertyType));//将插入生成的Id赋值给model
}
}
}
/// <summary>
/// 查询单个实体
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public T QuerySingle<T>(T model, string tableName = null) where T : class, new()
{
Type type = typeof(T);
if (type.GetProperty("Id") == null)
{
throw new ArgumentNullException(string.Format("实体{0}必须包含主键Id字段", type.Name));
}
string _tableName = GetTableName<T>(tableName);
var Id = type.GetProperty("Id").GetValue(model);
string selectField = string.Join(",", type.GetProperties().Select(s => string.Format("[{0}]", s.Name)));
string txtSql = string.Format("select {0} from [{1}] where Id =@id", selectField, _tableName);
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(txtSql, conn);
cmd.Parameters.Add(new SqlParameter("@id",Id));
SqlDataReader reader = cmd.ExecuteReader();
return ReaderToEntity<T>(reader);
}
}
/// <summary>
/// 查询所有
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName"></param>
/// <returns></returns>
public IEnumerable<T> QueryAll<T>(string tableName = null) where T : class, new()
{
Type type = typeof(T);
string selectField = string.Join(",", type.GetProperties().Select(p => string.Format("[{0}]", p.Name)));
string txtSql = string.Format("select {0} from [{1}]", selectField, type.Name);
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(txtSql, conn);
SqlDataReader reader = cmd.ExecuteReader();
return ReaderToList<T>(reader);
}
}
/// <summary>
/// 更新
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="tableName"></param>
public void Update<T>(T model, string tableName = null) where T : class, new()
{
Type type = typeof(T);
if (type.GetProperty("Id") == null)
{
throw new ArgumentNullException(string.Format("实体{0}必须包含主键Id字段", type.Name));
}
string _tableName = GetTableName<T>(tableName);
string setCloums = string.Join(",", type.GetProperties().Where(p => p.Name != "Id").Select(s => string.Format("[{0}]=@{0}", s.Name)));
string txtSql = string.Format("update [{0}] set {1} where Id =@id",_tableName,setCloums);
SqlParameter[] sqlParams = type.GetProperties().Select(s => new SqlParameter(string.Format("@{0}", s.Name), s.GetValue(model) ?? DBNull.Value)).ToArray();
ExecuteNonQuery(txtSql, sqlParams);
}
#endregion
#region private
private string GetTableName<T>(string tableName)
{
string result = tableName;
if (string.IsNullOrEmpty(result))//如果参数tableName为空
{
//1.检查[tableName]属性
Type type = typeof(T);
var tableNameAttribute = (TableNameAttribute)type.GetCustomAttributes(typeof(TableNameAttribute), false).FirstOrDefault();
if (tableNameAttribute != null)
{
result = tableNameAttribute.TableName;
}
if (string.IsNullOrEmpty(result))
{
//2.[tableName]属性为空 用实体名称
result = type.Name;
}
}
return result;
}
private string GetInsertSql<T>(T model,string tableName)
{
Type type = typeof(T);
if (type.GetProperty("Id") == null)
{
throw new ArgumentNullException(string.Format("实体{0}必须包含主键Id字段", type.Name));
}
string _tableName = GetTableName<T>(tableName);
string txtColums = string.Join(",", type.GetProperties().Where(p => p.Name != "Id").Select(s => string.Format("[{0}]", s.Name)));
string txtValues = string.Join(",", type.GetProperties().Where(p => p.Name != "Id").Select(s => string.Format("@{0}", s.Name)));
string txtSql = string.Format("insert into [{0}] ({1}) values({2});select @@identity;", _tableName, txtColums, txtValues);
return txtSql;
}
private T ReaderToEntity<T>(SqlDataReader reader) where T : class, new()
{
T entity = new T();
if (reader.Read())
{
Type type = typeof(T);
foreach (var item in type.GetProperties())
{
object oValue = reader[item.Name];
if (oValue is DBNull)
{
item.SetValue(entity, null);
}
else
{
item.SetValue(entity, oValue);
}
}
}
return entity;
}
private IEnumerable<T> ReaderToList<T>(SqlDataReader reader) where T : class, new()
{
List<T> list = new List<T>();
if (reader.Read())
{
Type type = typeof(T);
do
{
T entity = new T();
foreach (var item in type.GetProperties())
{
object oValue = reader[item.Name];
if (oValue is DBNull)
{
item.SetValue(entity, null);
}
else
{
item.SetValue(entity, oValue);
}
}
list.Add(entity);
}
while (reader.Read());
}
return list;
}
#endregion
}
