Dbhelp

public static class SqlHelper
{
private static readonly string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

/// <summary>
/// 执行增删改的
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}

/// <summary>
/// 封装一个执行返回单个值的方法
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}

/// <summary>
/// 返回SqlDataReader对象的方法
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, CommandType cmdType = CommandType.Text, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
try
{
con.Open();
//这里第二个参数代表在函数外部，如果掉了close()方法，则con会随之一起销毁
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
con.Close();
con.Dispose();
throw;
}
}
}

/// <summary>
/// //执行操作SQL语句,返回DataTable
/// </summary>
/// <param name="sql"></param>
/// <param name="cmdType"></param>
/// <param name="pms"></param>
/// <returns></returns>
public static DataTable ExecuteToDataTable(string sql,CommandType cmdType = CommandType.Text,params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}

return dt;
}

/// <summary>
/// 执行操作SQL语句,返回DataSet
/// </summary>
/// <param name="sql"></param>
public static DataSet ExecuteToDataSet(string sql, CommandType cmdType = CommandType.Text, params IDataParameter[] pms)
{
DataSet ds = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(ds);
}
return ds;
}

/// <summary>
/// DataTable 转换为List 集合
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public static IList<T> DataTableToList<T>(DataTable dt) where T : class,new()
{
//创建一个属性的列表
List<PropertyInfo> prlist = new List<PropertyInfo>();
//获取T的类型实例 反射的入口
Type t = typeof(T);
//获得T 的所有的Public 属性 并找出T属性和DataTable的列名称相同的属性(PropertyInfo) 并加入到属性列表
Array.ForEach<PropertyInfo>(t.GetProperties(), p => { if (dt.Columns.IndexOf(p.Name) != -1) prlist.Add(p); });
//创建返回的集合
List<T> oblist = new List<T>();

foreach (DataRow row in dt.Rows)
{
//创建T的实例
T ob = new T();
//找到对应的数据 并赋值
prlist.ForEach(p => { if (row[p.Name] != DBNull.Value) p.SetValue(ob, row[p.Name], null); });
//放入到返回的集合中.
oblist.Add(ob);
}
return oblist;
}

/// <summary>
/// List集合 转换为一个DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="value"></param>
/// <returns></returns>
public static DataTable ListToDataTable<T>(IEnumerable<T> value) where T : class
{
//创建属性的集合
List<PropertyInfo> pList = new List<PropertyInfo>();
//获得反射的入口
Type type = typeof(T);
DataTable dt = new DataTable();
//把所有的public属性加入到集合 并添加DataTable的列
Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name); });
foreach (var item in value)
{
//创建一个DataRow实例
DataRow row = dt.NewRow();
//给row 赋值
pList.ForEach(p => row[p.Name] = p.GetValue(item, null));
//加入到DataTable
dt.Rows.Add(row);
}
return dt;
}

//IDataReder转实体类列表
public static List<T> ReaderToList<T>(IDataReader DataReader)
{
using (DataReader)
{
List<string> field = new List<string>(DataReader.FieldCount);
for (int i = 0; i < DataReader.FieldCount; i++)
{
field.Add(DataReader.GetName(i).ToLower());
}
List<T> list = new List<T>();
while (DataReader.Read())
{
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
//if (!IsNullOrDBNull(DataReader[property.Name]))
if (DataReader[property.Name] != DBNull.Value)
{
property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
}
}
}
list.Add(model);
}
return list;
}
}

/// <summary>
/// IDataReder转实体类列表 空字符串和dbnull都返回null
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="DataReader"></param>
/// <returns></returns>
public static List<T> ReaderToListNull<T>(IDataReader DataReader)
{
using (DataReader)
{
List<string> field = new List<string>(DataReader.FieldCount);
for (int i = 0; i < DataReader.FieldCount; i++)
{
field.Add(DataReader.GetName(i).ToLower());
}
List<T> list = new List<T>();
while (DataReader.Read())
{
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
if (!IsNullOrDBNull(DataReader[property.Name]))
//if (DataReader[property.Name] != DBNull.Value)
{
property.SetValue(model, HackType(DataReader[property.Name], property.PropertyType), null);
}
}
}
list.Add(model);
}
return list;
}
}

//IDataReder转实体类列表
public static List<dynamic> ReaderToDynamicList(IDataReader DataReader)
{
using (DataReader)
{
List<dynamic> list = new List<dynamic>();
while (DataReader.Read())
{
dynamic DataObject = new ExpandoObject();
var DataRow = DataObject as IDictionary<string, object>;

for (int i = 0; i < DataReader.FieldCount; i++)
{
DataRow.Add(DataReader.GetName(i).ToUpper(), DataReader[i]);
}
list.Add(DataRow);
}
return list;
}
}

//这个类对可空类型进行判断转换，要不然会报错
private static object HackType(object value, Type conversionType)
{
if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
{
if (value == null)
return null;

System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
conversionType = nullableConverter.UnderlyingType;
}
return Convert.ChangeType(value, conversionType);
}

private static bool IsNullOrDBNull(object obj)
{
return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;
}
}