using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;
using System.Collections;
using System.Web;
using System.Web.Caching;
public class SqlDbHelper
{
public static string constr = ConfigurationManager.AppSettings["MESProduction"].ToString();
#region 验证数据库是否连接
/// <summary>
/// 测试数据库是否可以正常连接,time如果默认就不填写 如果填写则加timeout连接字符串
/// </summary>
/// <returns></returns>
public static bool ChechSqlServer(int timeout)
{
string connnectstring = "";
if (timeout != 0)
{
connnectstring = constr + ";Connect Timeout=" + timeout;
}
else { connnectstring = constr; }
//constr里不要加;Connect Timeout=5000参数如果加参数此处需要修改
bool succeed = false;
try
{
SqlConnection p_connectionString = new SqlConnection(connnectstring);
p_connectionString.Open();
SqlCommand p_selectCommand = new SqlCommand("SELECT 1", p_connectionString);
SqlDataReader reader = p_selectCommand.ExecuteReader();
if (reader.HasRows)
succeed = true;
reader.Dispose();
reader.Close();
p_connectionString.Close();
}
catch
{ }
return succeed;
}
#endregion
#region 根据实体类名获取表名
/// <summary>
/// 根据实体类名返回数据库表名和其字段
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="t">实体对象</param>
/// <returns>数据表名和字段</returns>
public static Dictionary<string, PropertyInfo[]> GetTableName<T>(T t)
{
if (t == null)
{
return null;
}
PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (propertys.Length <= 0)
{
return null;
}
Dictionary<string, PropertyInfo[]> dic = new Dictionary<string, PropertyInfo[]>();
string fname = t.ToString();
string cname = typeof(T).Name;
dic[cname] = propertys;
return dic;
}
#endregion
#region 增加一个记录
/// <summary>
/// 添加一条数据到数据库,排除主键在SQL中出现
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否写入成功</returns>
public static bool InsertModel<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParams<T>(t, out sql, out sp);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 添加一条数据到数据库,排除主键在SQL中出现
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <returns>是否写入成功</returns>
public static bool InsertModel<T>(T t)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParams<T>(t, out sql, out sp);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 添加一条数据到数据库,主键在SQL中出现
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否写入成功</returns>
public static bool InsertModelGuid<T>(T t)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 添加一条数据到数据库,主键在SQL中出现
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否写入成功</returns>
public static bool InsertModelGuid<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParamsNoParimary<T>(t, out sql, out sp, constr);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 添加一条数据到数据库,不进行数据表字段对比
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">实体对象</param>
/// <param name="constr">连接字符串</param>
/// <returns>是否写入成功</returns>
public static bool InsertModelnoEq<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
if (InsertSqlParamsnoEp<T>(t, out sql, out sp))
{
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
return false;
}
/// <summary>
/// 添加一条数据到数据库，并返回主标识id
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="id">数据成功插入后返回主标识id</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>数据插入是否成功</returns>
public static bool InsertModel<T>(T t, out int id, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
id = ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
if (id > 0)
{
return true;
}
return false;
}
/// <summary>
/// 插入一条数据并返回主键ID
/// </summary>
/// <typeparam name="T">数据实体</typeparam>
/// <param name="t">实体参数</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>主键</returns>
public static int InsertModelReturnIdentity<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
InsertSqlParamsWithIdentity<T>(t, out sql, out sp);
return ExecuteNonQueryWithIdentity(conn, CommandType.Text, sql, sp);
}
public static bool InsertSqlParamsWithIdentity<T>(T t, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
try
{
string cname = typeof(T).Name;
//构造参数化SQL命令
StringBuilder ExeSql = new StringBuilder();
StringBuilder Params = new StringBuilder();
ExeSql.Append("insert into " + cname + " (");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0)
{
return false;
}
int counts = dic.Count;
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
if (!item.IsPrimary)
{
object v = pi.GetValue(t, null);
if (v != null)//只对赋值了的属性进行操作
{
ExeSql.Append(pi.Name + ",");
Params.Append("@" + pi.Name + ",");
list.Add(CP.cPa(item, v));
}
}
}
}
SqlParameter sp = new SqlParameter("@" + ParameterDirection.ReturnValue.ToString(), SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
list.Add(sp);
sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ");select @" + ParameterDirection.ReturnValue.ToString() + "=SCOPE_IDENTITY()";
paras = list.ToArray();
return true;
}
catch { return false; }
}
/// <summary>
/// 循环T类型属性，表主键不插入方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static bool InsertSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
try
{
StringBuilder ExeSql = new StringBuilder();
StringBuilder Params = new StringBuilder();
string cname = typeof(T).Name;
ExeSql.Append("insert into " + cname + " (");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0)
{
return false;
}
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
if (!item.IsPrimary)
{
object v = pi.GetValue(t, null);
if (v != null)//只对赋值了的属性进行操作
{
ExeSql.Append(pi.Name + ",");
Params.Append("@" + pi.Name + ",");
list.Add(CP.cPa(item, v));
}
}
}
}
sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
paras = list.ToArray();
return true;
}
catch { return false; }
}
/// <summary>
/// 循环T类型属性，表主键插入方法
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static bool InsertSqlParamsNoParimary<T>(T t, out string sql, out SqlParameter[] paras, string constr)
{
sql = ""; paras = null;
try
{
StringBuilder ExeSql = new StringBuilder();
StringBuilder Params = new StringBuilder();
string cname = typeof(T).Name;
ExeSql.Append("insert into " + cname + " (");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0)
{
return false;
}
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
object v = pi.GetValue(t, null);
if (v != null)//只对赋值了的属性进行操作
{
ExeSql.Append(pi.Name + ",");
Params.Append("@" + pi.Name + ",");
list.Add(CP.cPa(item, v));
}
}
}
sql = ExeSql.ToString().TrimEnd(',') + ") values (" + Params.ToString().TrimEnd(',') + ")";
paras = list.ToArray();
return true;
}
catch { return false; }
}
/// <summary>
/// 构造添加执行语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns>是否异常</returns>
public static bool InsertSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
StringBuilder ExeSql = new StringBuilder();
StringBuilder Params = new StringBuilder();
try
{
string cname = typeof(T).Name;
List<SqlParameter> list = new List<SqlParameter>();
ExeSql.Append("insert into " + cname + " (");
foreach (PropertyInfo item in typeof(T).GetProperties())
{
object o = item.GetValue(t, null);
if (o != null)
{
if (item.Name.ToLower() != "id")
{
ExeSql.Append(item.Name + ",");
Params.Append("@" + item.Name + ",");
list.Add(createParam(item, o));
}
}
}
sql = ExeSql.ToString().TrimEnd(',') + ")values(" + Params.ToString().TrimEnd(',') + ")";
paras = list.ToArray();
return true;
}
catch
{
return false;
}
}
/// <summary>
/// 添加一条数据到数据库
/// </summary>
/// <param name="Fields">表字段</param>
/// <param name="Table">表名</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否成功完成操作</returns>
public static bool InsertRecord(Dictionary<string, object> Fields, string Table, string constr)
{
SqlConnection connection = new SqlConnection(constr);
StringBuilder builder = new StringBuilder("");
Table = Table.Replace("'", "");
builder.Append("insert into " + Table + " (");
IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
StringBuilder builder2 = new StringBuilder();
List<SqlParameter> pitems = new List<SqlParameter>();
while (enumerator.MoveNext())
{
KeyValuePair<string, object> current = enumerator.Current;
string key = current.Key;
object obj = current.Value;
//只处理已赋值的项
if (!string.IsNullOrEmpty(key) && (obj != null))
{
builder.Append(key + ",");
builder2.Append("@" + key + ",");
pitems.Add(createParam(key, obj));
}
}
string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ")";
if (ExecuteNonQuery(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
return true;
return false;
}
/// <summary>
/// 添加一条数据到数据库
/// </summary>
/// <param name="Fields">表字段</param>
/// <param name="Table">表名</param>
/// <param name="id">数据插入后返回主标识(自动增长字段值)</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否成功完成操作</returns>
public static bool InsertRecord(Dictionary<string, object> Fields, string Table, out int id, string constr)
{
id = 0;
SqlConnection connection = new SqlConnection(constr);
StringBuilder builder = new StringBuilder("");
Table = Table.Replace("'", "");
builder.Append("insert into " + Table + " (");
IEnumerator<KeyValuePair<string, object>> enumerator = Fields.GetEnumerator();
StringBuilder builder2 = new StringBuilder();
List<SqlParameter> pitems = new List<SqlParameter>();
while (enumerator.MoveNext())
{
KeyValuePair<string, object> current = enumerator.Current;
string key = current.Key;
object obj = current.Value;
//只处理已赋值的项
if (!string.IsNullOrEmpty(key) && (obj != null))
{
builder.Append(key + ",");
builder2.Append("@" + key + ",");
pitems.Add(createParam(key, obj));
}
}
SqlParameter sp = new SqlParameter("@out_return_id", SqlDbType.Int);
sp.Direction = ParameterDirection.Output;
pitems.Add(sp);
string sql = builder.ToString().TrimEnd(',') + ") values (" + builder2.ToString().TrimEnd(',') + ") select @out_return_id=SCOPE_IDENTITY()";
if (ExecuteNonQueryOutID(connection, CommandType.Text, sql, pitems.ToArray()) > 0)
return true;
return false;
}
#endregion
#region 更新一个记录
/// <summary>
/// 更新一个记录，按主键
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns></returns>
public static bool UpdateModel<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
UpdateSqlParams<T>(t, out sql, out sp);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 更新某条记录，按主键 更新多个字段
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="p">字典参数</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns></returns>
public static bool UpdateModel<T>(Dictionary<string, object> p, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
UpdateSqlParams<T>(p, out sql, out sp);
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
/// <summary>
/// 更新某条记录，按主键 更新多个字段
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">实体对象</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns></returns>
public static bool UpdateModelnoEp<T>(T t, string constr)
{
SqlConnection conn = new SqlConnection(constr);
string sql = string.Empty; SqlParameter[] sp = null;
if (UpdateSqlParamsnoEp<T>(t, out sql, out sp))
{
return (ExecuteNonQuery(conn, CommandType.Text, sql, sp) > 0);
}
return false;
}
public static bool UpdateSqlParams<T>(T t, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
PropertyInfo[] propertys = typeof(T).GetProperties();
string cname = typeof(T).Name;
//构造参数化SQL命令
StringBuilder ExeSql = new StringBuilder();
ExeSql.Append("update " + cname + " set ");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0)
{
return false;
}
string primaryKey = string.Empty;
foreach (PropertyInfo pi in propertys)
{
var propertyType = Nullable.GetUnderlyingType(pi.PropertyType) ?? pi.PropertyType;//得到属性定义类型
string t_p = propertyType.ToString().ToLower();//转化为小写
t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
object v = pi.GetValue(t, null);
if ((t_p == "string" || t_p == "char") && v == null)
v = "";
//object v = pi.GetValue(t, null);
if (v != null)//只对赋值了的属性进行操作
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
if (!item.IsPrimary)
{
ExeSql.Append(pi.Name + "=@" + pi.Name + ",");
}
else
{
primaryKey = pi.Name;
}
list.Add(CP.cPa(item, v));
}
}
}
sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
paras = list.ToArray();
return true;
}
public static bool UpdateSqlParams<T>(Dictionary<string, object> p, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
try
{
PropertyInfo[] propertys = typeof(T).GetProperties();
string cname = typeof(T).Name;
StringBuilder ExeSql = new StringBuilder();
//构造参数化SQL命令
ExeSql.Append("update " + cname + " set ");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0)
{
return false;
}
//集合枚举
IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
string primaryKey = string.Empty;//主键参数
foreach (PropertyInfo pi in propertys)
{
while (enumerator.MoveNext())
{
KeyValuePair<string, object> current = enumerator.Current;
string key = current.Key;
object obj = current.Value;
if (!string.IsNullOrEmpty(key) && (obj != null))
{
CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == key.ToLower());
if (item != null)
{
if (item.IsPrimary)
{
primaryKey = pi.Name;
}
else
{
ExeSql.Append(key + "=@" + key + ",");
}
list.Add(CP.cPa(item, obj));
}
}
}
}
sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey + "=@" + primaryKey;
paras = list.ToArray();
return true;
}
catch { return false; }
}
public static bool UpdateSqlParamsnoEp<T>(T t, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
try
{
string cname = typeof(T).Name;
//构造参数化SQL命令
StringBuilder ExeSql = new StringBuilder();
ExeSql.Append("update " + cname + " set ");
//循环补充字段和参数
List<SqlParameter> list = new List<SqlParameter>();
string primaryKey = string.Empty; //主键
foreach (PropertyInfo item in typeof(T).GetProperties())
{
object v = item.GetValue(t, null);
if (v != null)
{
if (item.Name.ToLower() != "id")
{
ExeSql.Append(item.Name + "=@" + item.Name + ",");
}
else
{
primaryKey = item.Name + "=@" + item.Name;
}
list.Add(createParam(item, v));
}
}
sql = ExeSql.ToString().TrimEnd(',') + " where " + primaryKey;
paras = list.ToArray();
return true;
}
catch { return false; }
}
#endregion
#region 得到一个记录
/// <summary>
/// 根据主键标识id获取一个记录
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="id">主键标识id</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>一个数据实体所有信息</returns>
public static T GetModel<T>(int id, string constr) where T : new()
{
// 获得此模型的类型
T t = new T();
string sql = string.Empty; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
GetSqlParams<T>(id, out sql, out sp, out propertys);
DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
if (ds.Tables.Count == 0)
{
return default(T);
}
DataTable dt = ds.Tables[0];
if (dt.Rows.Count <= 0)
{
return default(T);
}
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value && value != null)
pi.SetValue(t, value, null);
}
}
}
return t;
}
public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
{
sql = ""; paras = null;
//获取数据实体信息
string cname = typeof(T).Name;
py = typeof(T).GetProperties();
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
string searchCols = string.Empty;
string primaryKey = string.Empty;
foreach (PropertyInfo pi in py)
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
if (item.IsPrimary)
{
primaryKey = item.ColName;
list.Add(CP.cPa(item, id));
}
searchCols += pi.Name + ",";
}
}
searchCols = searchCols.TrimEnd(',');
sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
paras = list.ToArray();
return true;
}
public static bool GetSqlParams<T>(int id, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
// 获得此模型的类型
string cname = typeof(T).Name;
//获取数据实体信息
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
if (dic == null || dic.Count == 0) { return false; }
string searchCols = string.Empty;
string primaryKey = string.Empty;
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
CP.ColumnInfo item = dic.Find(p => p.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
if (item.IsPrimary)
{
primaryKey = item.ColName;
list.Add(CP.cPa(item, id));
}
searchCols += pi.Name + ",";
}
}
searchCols = searchCols.TrimEnd(',');
sql = "select " + searchCols + " from " + cname + " where " + primaryKey + "=@" + primaryKey;
paras = list.ToArray();
return true;
}
/// <summary>
/// 根据一个或多个实体赋值字段做为查询条件得到一个新的实体
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>一个数据实体所有信息</returns>
public static T GetModel<T>(IDictionary<string, object> p, string constr) where T : new()
{
string sql = ""; SqlParameter[] sp = null; PropertyInfo[] propertys = null;
T t = new T();
GetSqlParams<T>(p, out sql, out sp, out propertys);
DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
if (ds.Tables.Count == 0)
{
return default(T);
}
DataTable dt = ds.Tables[0];
if (dt.Rows.Count <= 0)
{
return default(T);
}
string tempName = "";
foreach (DataRow dr in dt.Rows)
{
// 获得此模型的公共属性
foreach (PropertyInfo pi in propertys)
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (dt.Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value && value != null)
pi.SetValue(t, value, null);
}
}
}
return t;
}
public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras)
{
sql = ""; paras = null;
string cname = typeof(T).Name;
//获取数据实体信息
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = null;
//判断T是否为视图
dic = cname.StartsWith("V") ? CP.TCV(cname, constr) : CP.TC(cname, constr);
string searchCols = string.Empty;
string primaryKey = string.Empty;
string temp = "";
bool Isfirst = true;
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
searchCols += pi.Name + ",";
}
}
//检查查询字典，如果为空，直接返回
if (p != null && p.Count != 0)
{
IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
while (enumera.MoveNext())
{
KeyValuePair<string, object> current = enumera.Current;
string key = current.Key;
object v = current.Value;
if (!string.IsNullOrEmpty(key) && (v != null))
{
CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
if (item != null)
{
list.Add(CP.cPa(item, v));
if (Isfirst)
{
temp += item.ColName + "=@" + item.ColName;
Isfirst = false;
}
else
{
temp += " and " + item.ColName + "=@" + item.ColName;
}
}
}
}
}
else
{
temp = "1=1";
}
searchCols = searchCols.TrimEnd(',');
sql = "select " + searchCols + " from " + cname + " where " + temp;
paras = list.ToArray();
return true;
}
public static bool GetSqlParams<T>(IDictionary<string, object> p, out string sql, out SqlParameter[] paras, out PropertyInfo[] py)
{
sql = ""; paras = null;
py = typeof(T).GetProperties();
string cname = typeof(T).Name;
//获取数据实体信息
List<SqlParameter> list = new List<SqlParameter>();
List<CP.ColumnInfo> dic = CP.TC(cname, constr);
string searchCols = string.Empty;
string primaryKey = string.Empty;
string temp = "";
bool Isfirst = true;
foreach (PropertyInfo pi in py)
{
CP.ColumnInfo item = dic.Find(c => c.ColName.ToLower() == pi.Name.ToLower());
if (item != null)
{
searchCols += pi.Name + ",";
}
}
//检查查询字典，如果为空，直接返回
if (p == null || p.Count == 0) { return false; }
IEnumerator<KeyValuePair<string, object>> enumera = p.GetEnumerator();
while (enumera.MoveNext())
{
KeyValuePair<string, object> current = enumera.Current;
string key = current.Key;
object v = current.Value;
if (!string.IsNullOrEmpty(key) && (v != null))
{
CP.ColumnInfo item = dic.Find(m => m.ColName.ToLower() == key.ToLower());
if (item != null)
{
list.Add(CP.cPa(item, v));
if (Isfirst)
{
temp += item.ColName + "=@" + item.ColName;
Isfirst = false;
}
else
{
temp += " and " + item.ColName + "=@" + item.ColName;
}
}
}
}
searchCols = searchCols.TrimEnd(',');
sql = "select " + searchCols + " from " + cname + " where " + temp;
paras = list.ToArray();
return true;
}
#endregion
#region 删除一个记录
/// <summary>
/// 删除一个记录
/// </summary>
/// <param name="id">标示ID</param>
/// <param name="tableName">数据表名</param>
/// <returns>成功失败</returns>
public static bool DelSingle(string sql, string constr)
{
using (SqlConnection conn = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, CommandType.Text, sql, null);
int count = 0;
try
{
count = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(conn);
}
if (count > 0)
return true;
return false;
}
}
/// <summary>
/// 从数据库表中删除一个记录，按主键标识id
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="t">数据实体对象</param>
/// <param name="constr">数据连接字符串</param>
/// <returns>是否删除成功</returns>
public static bool DeleteModel<T>(T t, string constr)
{
if (t == null)
{
return false;
}
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
if (propertys.Length <= 0)
{
return false;
}
string fname = t.ToString();
string cname = fname.Substring(fname.LastIndexOf(".") + 1, fname.Length - fname.LastIndexOf(".") - 1);
bool result = false;
foreach (PropertyInfo pi in propertys)
{
object v = pi.GetValue(t, null);
if (v != null)//只对赋值了的属性进行操作,
{
if (pi.Name.ToString().ToLower() == "id")
{
SqlParameter[] pitems = { new SqlParameter("@" + pi.Name, SqlDbType.Int) };
pitems[0].Value = int.Parse(v.ToString());
string sql = "delete from " + cname + " where id=@" + pi.Name + "";
SqlConnection conn = new SqlConnection(constr);
if (ExecuteNonQuery(conn, CommandType.Text, sql, pitems) > 0)
{
result = true;
}
break;
}
}
}
return result;
}
/// <summary>
/// 根据主键ID删除一条记录，注：主键必须是ID，且存在
/// </summary>
/// <typeparam name="T">删除实体</typeparam>
/// <param name="id">ID</param>
/// <param name="constr">数据库连接</param>
/// <returns>成功失败</returns>
public static bool DeleteModel<T>(int id, string constr) where T : new()
{
if (id == 0)
return false;
string cname = typeof(T).Name;
string sql = "delete from " + cname + " where id=@id";
SqlParameter para = new SqlParameter("@id", id);
SqlConnection conn = new SqlConnection(constr);
if (ExecuteNonQuery(conn, CommandType.Text, sql, para) > 0)
return true;
return false;
}
/// <summary>
/// 删除多条记录，按条件
/// </summary>
/// <typeparam name="T">要删除的实体类</typeparam>
/// <param name="p">键值对</param>
/// <param name="constr">数据库连接</param>
/// <returns>成功失败</returns>
public static bool DeleteModel<T>(IDictionary<string, object> p, string constr)
{
if (p == null || p.Count == 0)
return false;
string tablename = typeof(T).Name;
StringBuilder ExeSql = new StringBuilder();
ExeSql.Append("delete from " + tablename);
IEnumerator<KeyValuePair<string, object>> enumerator = p.GetEnumerator();
List<SqlParameter> list = new List<SqlParameter>();
bool isFirst = true;
while (enumerator.MoveNext())
{
KeyValuePair<string, object> current = enumerator.Current;
string key = current.Key;
object obj = current.Value;
if (!string.IsNullOrEmpty(key) && (obj != null))
{
if (isFirst)
{
ExeSql.Append(" where " + key + "=@" + key);
isFirst = false;
}
else
{
ExeSql.Append(" and " + key + "=@" + key);
}
list.Add(createParam(key, obj));
}
}
SqlConnection conn = new SqlConnection(constr);
try
{
if (ExecuteNonQuery(conn, CommandType.Text, ExeSql.ToString(), list.ToArray()) > 0)
return true;
return false;
}
catch { return false; }
}
#endregion
#region 得到多个记录
/// <summary>
/// 根据ID得到多个记录
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">SQL查询命令</param>
/// <param name="constr">数据库连接</param>
/// <param name="paras">可变参数</param>
/// <returns></returns>
public static List<T> GetMoreModel<T>(string sql, string constr, params SqlParameter[] paras) where T : new()
{
List<T> list = new List<T>();
DataSet ds = Query(sql, paras);
int count = ds.Tables[0].Rows.Count;
if (count == 0)
{
return null;
}
string tempName = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
T v = new T();
// 获得此模型的公共属性
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (ds.Tables[0].Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value && value != null)
pi.SetValue(v, value, null);
}
}
list.Add(v);
}
return list;
}
/// <summary>
/// 根据键值对获取多个记录
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="p">条件</param>
/// <param name="constr">数据库连接</param>
/// <param name="param">参数</param>
/// <returns></returns>
public static List<T> GetMoreModel<T>(Dictionary<string, object> p, string constr, params SqlParameter[] param) where T : new()
{
List<T> list = new List<T>();
string sql = string.Empty; SqlParameter[] sp = null;
GetSqlParams<T>(p, out sql, out sp);
DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
int count = ds.Tables[0].Rows.Count;
if (count == 0)
{
return null;
}
string tempName = "";
foreach (DataRow dr in ds.Tables[0].Rows)
{
T v = new T();
// 获得此模型的公共属性
foreach (PropertyInfo pi in typeof(T).GetProperties())
{
tempName = pi.Name;
// 检查DataTable是否包含此列
if (ds.Tables[0].Columns.Contains(tempName))
{
// 判断此属性是否有Setter
if (!pi.CanWrite) continue;
object value = dr[tempName];
if (value != DBNull.Value && value != null)
pi.SetValue(v, value, null);
}
}
list.Add(v);
}
return list;
}
/// <summary>
/// 根据键值对获取多个记录
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="p">条件</param>
/// <param name="sort">条件排序 id desc</param>
/// <param name="constr">数据库连接</param>
/// <param name="param">参数</param>
/// <returns></returns>
//public static List<T> GetMoreModel<T>(Dictionary<string, object> p,string sort, string constr, params SqlParameter[] param) where T : new()
//{
// List<T> list = new List<T>();
// string sql = string.Empty; SqlParameter[] sp = null;
// GetSqlParams<T>(p, out sql, out sp);
// if (!string.IsNullOrEmpty(sort)) { sql += " order by "+sort; }
// DataSet ds = ExecuteDataset(constr, CommandType.Text, sql, sp);
// int count = ds.Tables[0].Rows.Count;
// if (count == 0)
// {
// return null;
// }
// string tempName = "";
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// T v = new T();
// // 获得此模型的公共属性
// foreach (PropertyInfo pi in typeof(T).GetProperties())
// {
// tempName = pi.Name;
// // 检查DataTable是否包含此列
// if (ds.Tables[0].Columns.Contains(tempName))
// {
// // 判断此属性是否有Setter
// if (!pi.CanWrite) continue;
// object value = dr[tempName];
// if (value != DBNull.Value && value != null)
// pi.SetValue(v, value, null);
// }
// }
// list.Add(v);
// }
// return list;
//}
#endregion
#region 根据SQL获取单值object
/// <summary>
/// 执行一条SQL语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">SQL命令</param>
/// <param name="constr">数据库连接串</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, string constr)
{
using (SqlConnection connection = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
try
{
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
}
}
}
/// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
trans.Rollback();
throw e;
}
finally
{
CloseConnection(connection);
}
}
}
/// <summary>
/// 是否存在该记录
/// </summary>
/// <param name="strSql">语句</param>
/// <param name="cmdParms">参数</param>
/// <returns>结果</returns>
public static bool Exists(string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 根据SQL语句执行SQL指令 返回int
/// <summary>
/// 执行单纯的SQL语句
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="constr">数据库连接</param>
/// <returns>结果(int)</returns>
public static int ExecuteCmd(string SQLString, string constr)
{
int result = 0;
using (SqlConnection conn = new SqlConnection(constr))
{
try
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, null);
result = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(conn);
}
return result;
}
}
/// <summary>
/// 执行SQL指令带参数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <param name="constr">数据库连接字符串</param>
/// <param name="para">参数</param>
/// <returns>结果(int)</returns>
public static int ExecuteCmd(string SQLString, string constr, params SqlParameter[] para)
{
int result = 0;
using (SqlConnection conn = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, conn, null, CommandType.Text, SQLString, para);
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(conn);
}
return result;
}
}
#endregion
#region 执行Sql命令
/// <summary>
/// 执行Cmd
/// </summary>
/// <param name="connection">连接对象</param>
/// <param name="commandType">类型</param>
/// <param name="commandText">内容</param>
/// <param name="commandParameters">参数</param>
/// <returns>结果</returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
int retval = 0;
try
{
retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception ee)
{
throw ee;
}
finally
{
CloseConnection(connection);
}
return retval;
}
/// <summary>
/// 执行cmd命令返回主键 ID
/// </summary>
/// <param name="connection"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static int ExecuteNonQueryWithIdentity(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
int retval = 0;
try
{
cmd.ExecuteNonQuery();
retval = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
return retval;
}
/// <summary>
/// Execute a SqlCommand (that returns no resultset) against the specified SqlConnection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
/// </remarks>
/// <param name="connection">A valid SqlConnection</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
/// <returns>An int representing the number of rows affected by the command</returns>
public static int ExecuteNonQueryOutID(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
int retval = 0;
try
{
cmd.ExecuteNonQuery();
retval = int.Parse(cmd.Parameters["@out_return_id"].Value.ToString());
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
return retval;
}
/// <summary>
/// 返回带返回参数的过程
/// </summary>
/// <param name="connection">数据库连接对象</param>
/// <param name="commandType">数据类型</param>
/// <param name="commandText">存储过程名或SQL语句</param>
/// <param name="commandParameters">参数</param>
/// <returns>参数</returns>
public static SqlParameter[] ExecuteNonQueryOutPara(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlParameter[] para = null;
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
try
{
cmd.ExecuteNonQuery();
if (commandParameters != null)
{
para = commandParameters;
for (int i = 0; i < commandParameters.Length; i++)
{
if (commandParameters[i].Direction == ParameterDirection.Output || commandParameters[i].Direction == ParameterDirection.ReturnValue)
{
para[i].ParameterName = commandParameters[i].ParameterName;
para[i].Value = commandParameters[i].Value;
}
}
}
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
return para;
}
/// <summary>
/// 事务处理，执行SQL语句，返回影响的记录数，只执行一个命令，不关闭连接，连接关闭在调用端执行
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction事件</param>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString)
{
int rows = 0;
using (SqlCommand cmd = new SqlCommand())
{
PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, null);
try
{
rows = cmd.ExecuteNonQuery();
}
catch
{
trans.Rollback();
}
finally
{
CloseConnection(connection);
}
return rows;
}
}
/// <summary>
/// 事务处理，执行SQL语句，返回影响的记录数，连接关闭在调用端执行
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="commandType">Sql命令类型</param>
/// <param name="SQLString">Sql查询语句</param>
/// <param name="cmdParms">Sql命令参数数组</param>
/// <returns>影响的记录数</returns>
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("transaction");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
// Create a command and prepare it for execution
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
// Finally, execute the command
int retval = 0;
try
{
retval = cmd.ExecuteNonQuery();
// Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear();
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
CloseConnection(transaction.Connection);
}
return retval;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public static void ExecuteSqlTranWithIndentity(List<CommandInfo> SQLStringList, out int identity)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
identity = 0;
//循环
foreach (CommandInfo myDE in SQLStringList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = identity;
}
}
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.Output)
{
identity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
finally
{
CloseConnection(conn);
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL命令集合</param>
public static int ExecuteSqlTran(List<CommandInfo> cmdList)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int count = 0;
//循环
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE.CommandText;
SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
PrepareCommand(cmd, conn, trans, CommandType.Text, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
count += val;
cmd.Parameters.Clear();
}
trans.Commit();
return count;
}
catch
{
trans.Rollback();
throw;
}
finally
{
CloseConnection(conn);
}
}
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(constr))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return 0;
}
finally
{
CloseConnection(conn);
}
}
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(constr))
{
int rows = 0;
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
CloseConnection(connection);
}
}
return rows;
}
}
/// <summary>
/// 执行SQL语句，返回影响行数
/// </summary>
/// <param name="commandType">操作类型</param>
/// <param name="commandText">SQL语句</param>
/// <param name="constr">数据库连接字符串</param>
/// <param name="para">参数</param>
/// <returns>影响的行数</returns>
public static int ExecuteQuery(CommandType commandType, string commandText, string constr, params SqlParameter[] para)
{
using (SqlConnection conn = new SqlConnection(constr))
{
return ExecuteNonQuery(conn, commandType, commandText, para);
}
}
/// <summary>
/// 执行SQL语句，返回影响行数并返回主键ID
/// </summary>
/// <param name="commandType">操作类型</param>
/// <param name="commandText">SQL语句</param>
/// <param name="constr">数据库连接</param>
/// <param name="id">抛出的主键ID</param>
/// <param name="para">参数</param>
/// <returns>影响的行数</returns>
public static int ExecuteQuery(CommandType commandType, string commandText, string constr, out int id, params SqlParameter[] para)
{
id = 0;
int result = -1;
using (SqlConnection conn = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, para);
try
{
result = cmd.ExecuteNonQuery();
id = int.Parse(cmd.Parameters["@" + ParameterDirection.ReturnValue.ToString()].Value.ToString());
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(conn);
}
}
return result;
}
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction对象</param>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
int rows = 0;
try
{
PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException e)
{
trans.Rollback();
throw e;
}
finally
{
CloseConnection(connection);
}
return rows;
}
}
#endregion
#region 获取数据集，含分页
/// <summary>
/// 对应object数组到SqlParameter数组
/// </summary>
/// <param name="commandParameters">SqlParameter参数数组</param>
/// <param name="parameterValues">object参数数组</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters != null) && (parameterValues != null))
{
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
int index = 0;
int length = commandParameters.Length;
while (index < length)
{
if (parameterValues[index] is IDbDataParameter)
{
IDbDataParameter parameter = (IDbDataParameter)parameterValues[index];
if (parameter.Value == null)
{
commandParameters[index].Value = DBNull.Value;
}
else
{
commandParameters[index].Value = parameter.Value;
}
}
else if (parameterValues[index] == null)
{
commandParameters[index].Value = DBNull.Value;
}
else
{
commandParameters[index].Value = parameterValues[index];
}
index++;
}
}
}
/// <summary>
/// 获取数据集 存储过程使用
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="commandType">SqlCommand命令类型</param>
/// <param name="commandText">命令内容</param>
/// <param name="commandParameters">命令用到参数集合</param>
/// <returns>数据集</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params object[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
SqlParameter[] spParameterSet = SqlHelperParameterCache.GetSpParameterSet(connectionString, commandText);
AssignParameterValues(spParameterSet, commandParameters);
PrepareCommand(cmd, conn, null, commandType, commandText, spParameterSet);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet);
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
CloseConnection(conn);
}
return dataSet;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, commandParameters);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
cmd.Parameters.Clear();
}
catch
{
throw;
}
finally
{
CloseConnection(conn);
}
return ds;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(constr))
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, null);
try
{
SqlDataAdapter command = new SqlDataAdapter(cmd);
command.Fill(ds, "ds");
command.Dispose();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection(connection);
}
return ds;
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(constr))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, CommandType.Text, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
CloseConnection(connection);
}
return ds;
}
}
}
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(SqlConnection connection, SqlTransaction trans, string SQLString, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, trans, CommandType.Text, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
trans.Rollback();
throw new Exception(ex.Message);
}
finally
{
CloseConnection(connection);
}
return ds;
}
}
/// <summary>
/// Pagination 获取分页数据
/// </summary>
/// <param name="item">分页实体类，包含分页存储过程所用到的各个参数</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="ProName">存储过程名称</param>
/// <returns>某一页所有记录</returns>
public static DataSet GetPageContent(Pagination item, string connectionString, string ProName)
{
object[] commandParameters = new object[] { item.Tables, item.PrimaryKey, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter, item.Group };
return ExecuteDataset(connectionString, CommandType.StoredProcedure, ProName, commandParameters);
}
/// <summary>
/// PaginationByRowNumber获取分页数据
/// </summary>
/// <param name="item">分页实体</param>
/// <param name="constr">数据库链接</param>
/// <param name="ProName">过程名称</param>
/// <returns>数据集</returns>
public static DataSet GetPageContent(PaginationByRowNumber item, string constr, string ProName)
{
object[] commandParameter = new object[] { item.Table, item.Sort, item.CurrentPage, item.PageSize, item.Fields, item.Filter };
return ExecuteDataset(constr, CommandType.StoredProcedure, ProName, commandParameter);
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="pt">分页实体类，包含分页存储过程所用到的各个参数</param>
/// <param name="ProName">存储过程名称</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>某一页所有记录</returns>
public static DataSet GetItems(Pagination pt, string ProName, string constr)
{
return GetPageContent(pt, constr, ProName);
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="commandType">Sql命令类型</param>
/// <param name="commandText">Sql命令内容</param>
/// <param name="constr">数据库连接字符串</param>
/// <returns>数据集</returns>
public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr)
{
return ExecuteDataset(constr, commandType, commandText, null);
}
/// <summary>
/// 获取数据集
/// </summary>
/// <param name="commandType">数据类型</param>
/// <param name="commandText">SQL语句或过程</param>
/// <param name="constr">数据库连接</param>
/// <param name="cmdParms">参数</param>
/// <returns>DataSet</returns>
public static DataSet SqlTextDataset(CommandType commandType, string commandText, string constr, params SqlParameter[] cmdParms)
{
return ExecuteDataset(constr, commandType, commandText, cmdParms);
}
/// <summary>
/// 事务处理，执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="connection">SqlConnection对象</param>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public static object GetSingle(SqlConnection connection, SqlTransaction trans, CommandType commandType, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, trans, commandType, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (Exception e)
{
trans.Rollback();
throw e;
}
finally
{
connection.Close();
}
}
}
#endregion
#region 操作帮助方法
/// <summary>
/// SqlCommand参数初始化
/// </summary>
/// <param name="cmd">Sql命令</param>
/// <param name="conn">数据库连接对象</param>
/// <param name="trans">事务对象</param>
/// <param name="cmdType">Sql命令类型</param>
/// <param name="cmdText">Sql命令内容</param>
/// <param name="cmdParms">Sql命令参数集合</param>
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)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
/// <summary>
/// 关闭数据库连接 释放资源
/// </summary>
/// <param name="connection">连接对象</param>
public static void CloseConnection(SqlConnection connection)
{
if (connection.State != ConnectionState.Closed)
{
connection.Dispose();
connection.Close();
}
}
#endregion
#region 建立一个参数 包括Dictionary和T
/// <summary>
/// OOP根据实体类和所属某个属性返回一个SQL参数
/// </summary>
/// <typeparam name="T">数据实体类</typeparam>
/// <param name="p">属性</param>
/// <param name="t">数据实体对象</param>
/// <returns>一个SQL参数，已经初始化</returns>
private static SqlParameter createParam(PropertyInfo p, object v)
{
SqlParameter sp = null;
var propertyType = Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType;//得到属性定义类型
string t_p = propertyType.ToString().ToLower();//转化为小写
t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
// int->Int32 decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
switch (t_p)
{
case "byte":
sp = new SqlParameter("@" + p.Name, SqlDbType.TinyInt);
sp.Value = int.Parse(v.ToString());
break;
case "int16":
sp = new SqlParameter("@" + p.Name, SqlDbType.SmallInt);
sp.Value = int.Parse(v.ToString());
break;
case "int32":
sp = new SqlParameter("@" + p.Name, SqlDbType.Int);
sp.Value = int.Parse(v.ToString());
break;
case "decimal":
sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
sp.Value = decimal.Parse(v.ToString());
break;
case "single":
sp = new SqlParameter("@" + p.Name, SqlDbType.Float);
sp.Value = float.Parse(v.ToString());
break;
case "double":
sp = new SqlParameter("@" + p.Name, SqlDbType.Decimal);
sp.Value = decimal.Parse(v.ToString());
break;
case "boolean":
sp = new SqlParameter("@" + p.Name, SqlDbType.Bit);
if (v.ToString().ToLower() == "true")
{
sp.Value = 1;
}
else if (v.ToString().ToLower() == "false")
{
sp.Value = 0;
}
break;
case "datetime":
sp = new SqlParameter("@" + p.Name, SqlDbType.DateTime);
sp.Value = (DateTime)v;
break;
case "string":
//程序测试时注意数据库表中字段是双字节和单字节可变类型情况
if (System.Text.RegularExpressions.Regex.IsMatch(v.ToString(), @"[^\x00-\xff]+"))
{
string name = p.Name.ToLower();
if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
{
sp = new SqlParameter("@" + p.Name, SqlDbType.NVarChar, 2000);
}
else
{
sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
}
}
else
{
sp = new SqlParameter("@" + p.Name, SqlDbType.VarChar, 50);
}
sp.Value = v.ToString();
break;
case "char":
sp = new SqlParameter("@" + p.Name, SqlDbType.Char, 1);
sp.Value = v.ToString();
break;
}
return sp;
}
/// <summary>
/// 利用Key-Value创建一个参数
/// </summary>
/// <param name="current">键值对</param>
/// <returns>SQL参数，已经初始化</returns>
public static SqlParameter createParam(string key, object obj)
{
SqlParameter sp = null;
string t_p = obj.GetType().ToString().ToLower();//转化为小写
t_p = t_p.Substring(t_p.IndexOf('.') + 1, t_p.Length - t_p.IndexOf('.') - 1);
// int->Int32 decimal->Decimal float->Single double->Double bool->Boolean datetime->DateTime string->String char->Char
switch (t_p)
{
case "byte":
sp = new SqlParameter("@" + key, SqlDbType.TinyInt);
sp.Value = int.Parse(obj.ToString());
break;
case "int16":
sp = new SqlParameter("@" + key, SqlDbType.SmallInt);
sp.Value = int.Parse(obj.ToString());
break;
case "int32":
sp = new SqlParameter("@" + key, SqlDbType.Int);
sp.Value = int.Parse(obj.ToString());
break;
case "decimal":
sp = new SqlParameter("@" + key, SqlDbType.Decimal);
sp.Value = decimal.Parse(obj.ToString());
break;
case "single":
sp = new SqlParameter("@" + key, SqlDbType.Float);
sp.Value = float.Parse(obj.ToString());
break;
case "double":
sp = new SqlParameter("@" + key, SqlDbType.Decimal);
sp.Value = decimal.Parse(obj.ToString());
break;
case "boolean":
sp = new SqlParameter("@" + key, SqlDbType.Bit);
if (obj.ToString().ToLower() == "true")
{
sp.Value = 1;
}
else if (obj.ToString().ToLower() == "false")
{
sp.Value = 0;
}
break;
case "datetime":
sp = new SqlParameter("@" + key, SqlDbType.DateTime);
sp.Value = (DateTime)obj;
break;
case "string":
if (System.Text.RegularExpressions.Regex.IsMatch(obj.ToString(), @"[^\x00-\xff]+"))
{
string name = key.ToLower();
if (name.IndexOf("remark") >= 0 || name.IndexOf("content") >= 0 || name.IndexOf("description") >= 0)
{
sp = new SqlParameter("@" + key, SqlDbType.NVarChar, 2000);
}
else
{
sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
}
}
else
{
sp = new SqlParameter("@" + key, SqlDbType.VarChar, 50);
}
sp.Value = obj.ToString();
break;
case "char":
sp = new SqlParameter("@" + key, SqlDbType.Char, 1);
sp.Value = obj.ToString();
break;
}
return sp;
}
#endregion
}
/// <summary>
/// 构造SQL命令和对应参数
/// </summary>
public class CommandInfo
{
public string CommandText;
public System.Data.Common.DbParameter[] Parameters;
public CommandInfo(string sqlText, SqlParameter[] para)
{
this.CommandText = sqlText;
this.Parameters = para;
}
}
#region 分页类实体,借助存储过程实现
public class Pagination
{
public int CurrentPage
{
get;
set;
}
public string Fields
{
get;
set;
}
public string Filter
{
get;
set;
}
public string Group
{
get;
set;
}
public int PageSize
{
get;
set;
}
public string PrimaryKey
{
get;
set;
}
public string Sort
{
get;
set;
}
public string Tables
{
get;
set;
}
}
/// <summary>
/// RowNumber分页
/// </summary>
public class PaginationByRowNumber
{
/// <summary>
/// 表名
/// </summary>
public string Table { get; set; }
/// <summary>
/// 排序 需要指明顺序 asc/desc
/// </summary>
public string Sort { get; set; }
/// <summary>
/// 当前页
/// </summary>
public int CurrentPage { get; set; }
/// <summary>
/// 行数
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// 字段
/// </summary>
public string Fields { get; set; }
/// <summary>
/// 条件
/// </summary>
public string Filter { get; set; }
}
#endregion
#region 使用存储过程时缓存参数
public sealed class SqlHelperParameterCache
{
private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
private SqlHelperParameterCache()
{
}
public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
{
if ((connectionString == null) || (connectionString.Length == 0))
{
throw new ArgumentNullException("connectionString");
}
if ((commandText == null) || (commandText.Length == 0))
{
throw new ArgumentNullException("commandText");
}
string str = connectionString + ":" + commandText;
paramCache[str] = commandParameters;
}
private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
{
SqlParameter[] parameterArray = new SqlParameter[originalParameters.Length];
int index = 0;
int length = originalParameters.Length;
while (index < length)
{
parameterArray[index] = (SqlParameter)((ICloneable)originalParameters[index]).Clone();
index++;
}
return parameterArray;
}
private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if ((spName == null) || (spName.Length == 0))
{
throw new ArgumentNullException("spName");
}
SqlCommand command = new SqlCommand(spName, connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlCommandBuilder.DeriveParameters(command);
connection.Close();
if (!includeReturnValueParameter)
{
command.Parameters.RemoveAt(0);
}
SqlParameter[] array = new SqlParameter[command.Parameters.Count];
command.Parameters.CopyTo(array, 0);
foreach (SqlParameter parameter in array)
{
parameter.Value = DBNull.Value;
}
return array;
}
public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
{
if ((connectionString == null) || (connectionString.Length == 0))
{
throw new ArgumentNullException("connectionString");
}
if ((commandText == null) || (commandText.Length == 0))
{
throw new ArgumentNullException("commandText");
}
string str = connectionString + ":" + commandText;
SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
if (originalParameters == null)
{
return null;
}
return CloneParameters(originalParameters);
}
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
using (SqlConnection connection2 = (SqlConnection)((ICloneable)connection).Clone())
{
return GetSpParameterSetInternal(connection2, spName, includeReturnValueParameter);
}
}
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
{
if ((connectionString == null) || (connectionString.Length == 0))
{
throw new ArgumentNullException("connectionString");
}
if ((spName == null) || (spName.Length == 0))
{
throw new ArgumentNullException("spName");
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
}
}
private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if (connection == null)
{
throw new ArgumentNullException("connection");
}
if ((spName == null) || (spName.Length == 0))
{
throw new ArgumentNullException("spName");
}
string str = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");
SqlParameter[] originalParameters = paramCache[str] as SqlParameter[];
if (originalParameters == null)
{
SqlParameter[] parameterArray2 = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
paramCache[str] = parameterArray2;
originalParameters = parameterArray2;
}
return CloneParameters(originalParameters);
}
}
#endregion
/// <summary>
/// 创建SqlParameter
/// </summary>
public class CP
{
public class ColumnInfo
{
public string ColName { get; set; }
public SqlDbType ColType { get; set; }
public int ColLength { get; set; }
public bool IsPrimary { get; set; }
public string Description { get; set; }
}
/// <summary>
/// 读取用户表及每个表字段信息保存到字典对象，加入缓存
/// </summary>
/// <param name="specifyTable">指定表名，如果为空，则查询所有表</param>
/// <param name="connStr">数据库连接串</param>
/// <returns></returns>
public static List<ColumnInfo> TC(string specifyTable, string connStr)
{
if (string.IsNullOrEmpty(specifyTable))
{
return null;
}
var vl = HttpRuntime.Cache.Get("utable_" + specifyTable);
if (vl == null)
{
List<ColumnInfo> p = new List<ColumnInfo>();
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder(1000);
sb.Append("SELECT a.name,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '0' end) isprimary,b.name [type],COLUMNPROPERTY(a.id,a.name,'PRECISION') as [length],isnull(g.value,'') as [description] FROM syscolumns a ");
sb.Append("left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'");
sb.Append("left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id where d.name =@tname order by a.id,a.colorder");
conn.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
cmd.Parameters[0].Value = specifyTable;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
int count = dt.Rows.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = (dt.Rows[i]["isprimary"].ToString() == "1" ? true : false), Description = dt.Rows[i]["description"].ToString() };
p.Add(item);
}
HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
}
}
return p;
}
else
{
return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
}
}
/// <summary>
/// 读取用户表及每个表字段信息保存到字典对象，加入缓存 只针对视图
/// </summary>
/// <param name="specifyTable">指定表名，如果为空，则查询所有表</param>
/// <param name="connStr">数据库连接串</param>
/// <returns></returns>
public static List<ColumnInfo> TCV(string specifyTable, string connStr)
{
if (string.IsNullOrEmpty(specifyTable))
{
return null;
}
if (HttpRuntime.Cache["utable_" + specifyTable] == null)
{
List<ColumnInfo> p = new List<ColumnInfo>();
using (SqlConnection conn = new SqlConnection(connStr))
{
StringBuilder sb = new StringBuilder(1000);
sb.Append("select c.name ,t.name as type ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as length ");
sb.Append("from syscolumns c inner join systypes t on c.xusertype=t.xusertype ");
sb.Append("where objectproperty(c.id,'IsView')=1 and c.id=object_id(@tname) ");
conn.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
cmd.Parameters.Add(new SqlParameter("@tname", SqlDbType.VarChar, 30));
cmd.Parameters[0].Value = specifyTable;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
int count = dt.Rows.Count;
if (count > 0)
{
for (int i = 0; i < count; i++)
{
ColumnInfo item = new ColumnInfo() { ColName = dt.Rows[i]["name"].ToString(), ColLength = int.Parse(dt.Rows[i]["length"].ToString()), ColType = StringToSqlType(dt.Rows[i]["type"].ToString()), IsPrimary = false, Description = "" };
p.Add(item);
}
HttpRuntime.Cache.Insert("utable_" + specifyTable, p, null, Cache.NoAbsoluteExpiration, new TimeSpan(0, 30, 0));
}
}
return p;
}
else
{
return (List<ColumnInfo>)HttpRuntime.Cache["utable_" + specifyTable];
}
}
//SqlServer数据库类型转换方法
public static SqlDbType StringToSqlType(string String)
{
SqlDbType dbType = SqlDbType.Variant;//默认为Object
switch (String)
{
case "int":
dbType = SqlDbType.Int;
break;
case "varchar":
dbType = SqlDbType.VarChar;
break;
case "bit":
dbType = SqlDbType.Bit;
break;
case "datetime":
dbType = SqlDbType.DateTime;
break;
case "decimal":
dbType = SqlDbType.Decimal;
break;
case "float":
dbType = SqlDbType.Float;
break;
case "image":
dbType = SqlDbType.Image;
break;
case "money":
dbType = SqlDbType.Money;
break;
case "ntext":
dbType = SqlDbType.NText;
break;
case "nvarchar":
dbType = SqlDbType.NVarChar;
break;
case "smalldatetime":
dbType = SqlDbType.SmallDateTime;
break;
case "smallint":
dbType = SqlDbType.SmallInt;
break;
case "text":
dbType = SqlDbType.Text;
break;
case "bigint":
dbType = SqlDbType.BigInt;
break;
case "binary":
dbType = SqlDbType.Binary;
break;
case "char":
dbType = SqlDbType.Char;
break;
case "nchar":
dbType = SqlDbType.NChar;
break;
case "numeric":
dbType = SqlDbType.Decimal;
break;
case "real":
dbType = SqlDbType.Real;
break;
case "smallmoney":
dbType = SqlDbType.SmallMoney;
break;
case "sql_variant":
dbType = SqlDbType.Variant;
break;
case "timestamp":
dbType = SqlDbType.Timestamp;
break;
case "tinyint":
dbType = SqlDbType.TinyInt;
break;
case "uniqueidentifier":
dbType = SqlDbType.UniqueIdentifier;
break;
case "varbinary":
dbType = SqlDbType.VarBinary;
break;
case "xml":
dbType = SqlDbType.Xml;
break;
}
return dbType;
}
#region 建立一个参数
/// <summary>
/// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
/// </summary>
/// <typeparam name="item">表列信息集合</typeparam>
/// <param name="property">属性</param>
/// <param name="v">属性对应值</param>
/// <param name="pd">参数方向</param>
/// <returns>一个SQL参数，已经初始化</returns>
public static SqlParameter cPa(ColumnInfo item, object v, ParameterDirection pd)
{
if (item == null)
{
return null;
}
SqlParameter sp = null;
if (item != null)
{
sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
sp.Direction = pd;
sp.Value = v;
}
return sp;
}
/// <summary>
/// 根据表列信息集合和表对应实体类所属某个属性返回一个SQL参数
/// </summary>
/// <typeparam name="item">表列信息集合</typeparam>
/// <param name="property">属性</param>
/// <param name="v">属性对应值</param>
/// <returns>一个SQL参数，已经初始化</returns>
public static SqlParameter cPa(ColumnInfo item, object v)
{
if (item == null)
{
return null;
}
SqlParameter sp = null;
if (item != null)
{
sp = new SqlParameter("@" + item.ColName, item.ColType, item.ColLength);
sp.Direction = ParameterDirection.Input;
sp.Value = v;
}
return sp;
}
#endregion
/// <summary>
/// Author:付裕
/// Date: 2014-09-19
/// Desc: DataTable与泛型转换类
/// </summary>
public class WorkCommon
{
/// <summary>
/// DataTable Convert To List<T>
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="dt">数据集合</param>
/// <returns>泛型集合</returns>
public static List<T> ConvertTo<T>(DataTable dt) where T : new()
{
if (dt == null) return null;
if (dt.Rows.Count <= 0) return null;
List<T> list = new List<T>();
try
{
List<string> columnsName = new List<string>();
foreach (DataColumn dataColumn in dt.Columns)
{
columnsName.Add(dataColumn.ColumnName);//得到所有的表头
}
list = dt.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsName)); //转换
return list;
}
catch
{
return null;
}
}
/// <summary>
/// 转换函数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="row"></param>
/// <param name="columnsName"></param>
/// <returns></returns>
private static T getObject<T>(DataRow row, List<string> columnsName) where T : new()
{
T obj = new T();
try
{
string columnname = "";
string value = "";
PropertyInfo[] Properties = typeof(T).GetProperties();
foreach (PropertyInfo objProperty in Properties) //遍历T的属性
{
columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower()); //寻找可以匹配的表头名称
if (!string.IsNullOrEmpty(columnname))
{
value = row[columnname].ToString();
if (!string.IsNullOrEmpty(value))
{
if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null) //存在匹配的表头
{
value = row[columnname].ToString().Replace("$", "").Replace(",", ""); //从dataRow中提取数据
objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null); //赋值操作
}
else
{
value = row[columnname].ToString().Replace("%", ""); //存在匹配的表头
objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);//赋值操作
}
}
}
}
return obj;
}
catch
{
return obj;
}
}
/// <summary>
/// 将泛型集合类转换成DataTable
/// </summary>
/// <typeparam name="T">集合项类型</typeparam>
/// <param name="list">集合</param>
/// <param name="propertyName">需要返回的列的列名</param>
/// <returns>数据集(表)</returns>
public static DataTable ListToDataTable<T>(IList<T> list, params string[] propertyName)
{
List<string> propertyNameList = new List<string>();
if (propertyName != null)
propertyNameList.AddRange(propertyName);
DataTable result = new DataTable();
if (list != null && list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
result.Columns.Add(pi.Name, pi.PropertyType);
}
else
{
if (propertyNameList.Contains(pi.Name))
result.Columns.Add(pi.Name, pi.PropertyType);
}
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
if (propertyNameList.Count == 0)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
else
{
if (propertyNameList.Contains(pi.Name))
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
}
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
}
}