using System.Configuration;
namespace DBUtility
{
using Powertech.Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
public static class SqlHelper
{
public static readonly string Constr = ConfigurationManager.AppSettings["ConnectionString"];
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
public static DataTable ExenonQuery(string sql)
{
using (SqlConnection conn = new SqlConnection(Constr))
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int num = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return num;
}
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlDataReader reader2;
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
reader2 = reader;
}
catch
{
conn.Close();
throw;
}
return reader2;
}
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object obj2 = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return obj2;
}
}
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] parameterArray = (SqlParameter[])parmCache[cacheKey];
if (parameterArray == null)
{
return null;
}
SqlParameter[] parameterArray2 = new SqlParameter[parameterArray.Length];
int index = 0;
int length = parameterArray.Length;
while (index < length)
{
parameterArray2[index] = (SqlParameter)((ICloneable)parameterArray[index]).Clone();
index++;
}
return parameterArray2;
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans,
CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
cmd.Parameters.Add(parameter);
}
}
}
public static T DataReaderToEntity<T>(this SqlDataReader reader) where T : Entity, new()
{
T t = new T();
if (reader.Read())
{
do
{
Type type = t.GetType();
var properties = type.GetProperties();
foreach (PropertyInfo p in properties)
{
// p.SetValue(t, Convert.ChangeType(reader[p.Name], p.PropertyType));
var value = reader[p.Name];
if (!p.PropertyType.IsGenericType)
{
p.SetValue(t, value == null ? null : Convert.ChangeType(value, p.PropertyType));
}
else
{
Type genericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
if (genericTypeDefinition == typeof(Nullable<>))
{
p.SetValue(t, value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(p.PropertyType)));
}
}
}
} while (reader.Read());
}
return t;
}
public static List<T> DataTableToList<T>(this DataTable dt) where T : Entity, new()
{
List<T> list = new List<T>();
for (int i = 0; i < dt.Rows.Count; i++)
{
T t = new T();
foreach (PropertyInfo p in typeof(T).GetProperties())
{
//p.SetValue(t, Convert.ChangeType(dt.Rows[i][p.Name], p.PropertyType));
var value = dt.Rows[i][p.Name];
if (!p.PropertyType.IsGenericType)
{
p.SetValue(t, value == null ? null : Convert.ChangeType(value, p.PropertyType));
}
else
{
Type genericTypeDefinition = p.PropertyType.GetGenericTypeDefinition();
if (genericTypeDefinition == typeof(Nullable<>))
{
p.SetValue(t, value == null ? null : Convert.ChangeType(value, Nullable.GetUnderlyingType(p.PropertyType)));
}
}
}
list.Add(t);
}
return list;
}
#region 增删改查
/// <summary>
/// 增
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public static bool Insert<T>(T t) where T : Entity, new()
{
return ExecuteNonQuery(Constr, CommandType.Text, GetInsertSql<T>(t)) > 0;
}
#endregion
#region 私有
/// <summary>
/// 获取添加sql
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
private static string GetInsertSql<T>(T t) where T : Entity, new()
{
StringBuilder sb = new StringBuilder();
sb.Append("Insert into T_" + t.GetType().Name);
PropertyInfo[] props = typeof(T).GetProperties();
StringBuilder pros = new StringBuilder("");
StringBuilder values = new StringBuilder("");
for (int i = 0; i < props.Length; i++)
{
if (!props[i].Name.Equals("Id",StringComparison.OrdinalIgnoreCase))
{
pros.Append(props[i].Name + ",");
values.Append("'" + props[i].GetValue(t) + "',");
}
}
sb.Append("(" + pros.ToString().TrimEnd(',') + ")values (" + values.ToString().TrimEnd(',') + " )");
return sb.ToString();
}
private static string GetDeleteSql<T>(int Id) where T : Entity, new()
{
StringBuilder sb = new StringBuilder();
sb.Append("Delete from T_" + typeof(T).Name + " where Id=" + Id);
return sb.ToString();
}
private static string GetUpdateSql<T>(T t) where T : Entity, new()
{
StringBuilder sb = new StringBuilder();
StringBuilder values = new StringBuilder("");
PropertyInfo[] props = typeof(T).GetProperties();
for (int i = 1; i < props.Length; i++)
{
values.Append("" + props[i].Name + "='" + props[i].GetValue(t) + "',");
}
sb.Append("Update T_" + typeof(T).FullName + " set " + values + " where Id=" + props[0].GetValue(t) + " ");
return sb.ToString();
}
#endregion
public static bool Delete<T>(int Id) where T : Entity, new()
{
return ExecuteNonQuery(Constr, CommandType.Text, GetDeleteSql<T>(Id)) > 0;
}
public static bool Update<T>(T t) where T : Entity, new()
{
return ExecuteNonQuery(Constr, CommandType.Text, GetUpdateSql<T>(t)) > 0;
}
public static T GetModel<T>(int Id) where T : Entity, new()
{
string sql = "select * from T_" + typeof(T).Name + " where Id= " + Id;
return ExecuteReader(Constr, CommandType.Text, sql).DataReaderToEntity<T>();
}
public static List<T> Set<T>() where T : Entity, new()
{
string sql = "select * from T_" + typeof(T).Name;
return ExenonQuery(sql).DataTableToList<T>();
}
}
}
c#SqlHelper
最新推荐文章于 2021-04-04 14:13:33 发布