/// <summary>
/// 根据sql查询得到数据表
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable QueryData(string sql)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 执行DML（增删改）的SQL语句，返回受影响的行数
/// </summary>
/// <param name="sql">带有占位符的SQL</param>
/// <param name="sqlParams">参数化查询的数组</param>
/// <returns></returns>
public static int ExecuteMyDML(string sql, SqlParameter[] sqlParams)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (null != sqlParams && sqlParams.Length > 0)
{
CheckNull2DbNull(sqlParams);
cmd.Parameters.AddRange(sqlParams);
}
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
int res = cmd.ExecuteNonQuery();
return(res);
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(-1);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 获取第一行第一列的值
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlParams"></param>
/// <returns></returns>
public static object ExecuteFirstValue(string sql, SqlParameter[] sqlParams)
{
SqlConnection conn = new SqlConnection(CONNSTR);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
if (null != sqlParams && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{sql}\n{Params2String(cmd.Parameters)}");
return(cmd.ExecuteScalar());
}
catch (Exception ex)
{
//Console.WriteLine(ex.Message);
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
}
/// <summary>
/// 返回单个实体，第一行数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="cmdText"></param>
/// <param name="sqlParams"></param>
/// <param name="cmdType"></param>
/// <returns></returns>
public static T ExecuteReaderFirst <T>(string cmdText, SqlParameter[] sqlParams,
CommandType cmdType = CommandType.Text) where T : class, new()
{
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{cmdText}\n{Params2String(cmd.Parameters)}");
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
// 创建指定类型的实例
// 等同于 T entity = new T();
T entity = Activator.CreateInstance <T>();
//遍历reader字段
for (int i = 0; i < reader.FieldCount; i++)
{
//判断字段值是否为空或不存在
if (!IsNullOrDbNull(reader[i]))
{
//根据reader序列返回对应名称，并反射找到匹配的属性
PropertyInfo pi = typeof(T).GetProperty(reader.GetName(i),
BindingFlags.GetProperty | BindingFlags.Public
| BindingFlags.Instance | BindingFlags.IgnoreCase);
if (pi != null)
{
//设置对象中匹配属性的值
pi.SetValue(entity, CheckType(reader[i], pi.PropertyType), null);
}
}
}
return(entity);
}
return(null);
}
}
catch (Exception ex)
{
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
/// <summary>
/// 查询，返回泛型集合，需要注意约束类型的属性名称需要和数据库表字段名称保持一致，但不区分大小写
/// </summary>
/// <typeparam name="T">约束类型</typeparam>
/// <param name="cmdText">SQL语句</param>
/// <param name="sqlParams">SQL参数</param>
/// <param name="cmdType">命令类型</param>
/// <returns>返回约束的类型集合</returns>
public static List <T> ExecuteReader <T>(string cmdText, SqlParameter[] sqlParams,
CommandType cmdType = CommandType.Text) where T : new()
{
//connStr 为web.config中数据库连接字符串
SqlConnection conn = new SqlConnection(CONNSTR);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
if (sqlParams != null && sqlParams.Length > 0)
{
cmd.Parameters.AddRange(sqlParams);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
try
{
// 打印sql脚本和参数值到日志信息
MyLogHelper.Info($"{cmdText}\n{Params2String(cmd.Parameters)}");
using (SqlDataReader reader = cmd.ExecuteReader())
{
List <T> lstRes = new List <T>();
//获取指定的数据类型
Type modelType = typeof(T);
//遍历reader
while (reader.Read())
{
//创建指定类型的实例
T entity = Activator.CreateInstance <T>();
//遍历reader字段
for (int i = 0; i < reader.FieldCount; i++)
{
//判断字段值是否为空或不存在
if (!IsNullOrDbNull(reader[i]))
{
//根据reader序列返回对应名称，并反射找到匹配的属性
PropertyInfo pi = typeof(T).GetProperty(reader.GetName(i),
BindingFlags.GetProperty | BindingFlags.Public
| BindingFlags.Instance | BindingFlags.IgnoreCase);
if (pi != null)
{
//设置对象中匹配属性的值
pi.SetValue(entity, CheckType(reader[i], pi.PropertyType), null);
}
}
}
lstRes.Add(entity);
}
return(lstRes);
}
}
catch (Exception ex)
{
// 应该在这里记录崩溃，保存异常信息
//MyLogHelper.Write("发生异常", ex.Message);
MyLogHelper.Error(ex);
return(null);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}