SqlHelper类

using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Configuration; using System.Collections; using System.Data; namespace IOSS3.Item.DBUtility { public abstract class SqlHelper { //Database connection strings public static readonly string EpicItemConnectionString = ConfigurationManager.ConnectionStrings["SQLConnEpicItem"].ConnectionString; public static readonly string OldT4pConnectionString = "Data Source=123.123.123.123,64434;Initial Catalog=db;User ID=t;Pooling=True;Min Pool Size=0;Max Pool Size=5;Password=sa"; // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a 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 ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } } /// <summary> /// Execute a SqlCommand (that returns no resultset) against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</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 ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute a SqlCommand (that returns no resultset) using an existing SQL Transaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="trans">an existing sql transaction</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 ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a 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>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(connectionString); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch { conn.Close(); throw; } } /// <summary> /// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a 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 object that should be converted to the expected type using Convert.To{Type}</returns> 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 val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } /// <summary> /// Execute a SqlCommand that returns the first column of the first record against an existing database connection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="conn">an existing database connection</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 object that should be converted to the expected type using Convert.To{Type}</returns> 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 val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// add parameter array to the cache /// </summary> /// <param name="cacheKey">Key to the parameter cache</param> /// <param name="cmdParms">an array of SqlParamters to be cached</param> public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) { parmCache[cacheKey] = commandParameters; } /// <summary> /// Retrieve cached parameters /// </summary> /// <param name="cacheKey">key used to lookup parameters</param> /// <returns>Cached SqlParamters array</returns> public static SqlParameter[] GetCachedParameters(string cacheKey) { SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; if (cachedParms == null) return null; SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; for (int i = 0, j = cachedParms.Length; i < j; i++) clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); return clonedParms; } /// <summary> /// Prepare a command for execution /// </summary> /// <param name="cmd">SqlCommand object</param> /// <param name="conn">SqlConnection object</param> /// <param name="trans">SqlTransaction object</param> /// <param name="cmdType">Cmd type e.g. stored procedure or text</param> /// <param name="cmdText">Command text, e.g. Select * from Products</param> /// <param name="cmdParms">SqlParameters to use in the command</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 parm in cmdParms) cmd.Parameters.Add(parm); } } /// <summary> /// 查询数据返回DataTable /// </summary> /// <param name="connectstring"></param> /// <param name="type"></param> /// <param name="cmd"></param> /// <param name="parms"></param> /// <returns></returns> public static DataTable ExecuteQuery(string connectstring, CommandType type, string cmd, SqlParameter[] parms) { DataTable dt = new DataTable(); using (SqlConnection sqlcon = new SqlConnection(connectstring)) { sqlcon.Open(); SqlCommand sqlcmd = new SqlCommand(cmd, sqlcon); sqlcmd.CommandType = type; sqlcmd.CommandTimeout = 600; sqlcmd.Parameters.AddRange(parms); SqlDataAdapter sqladp = new SqlDataAdapter(sqlcmd); sqladp.Fill(dt); sqlcmd.Parameters.Clear(); sqlcon.Close(); return dt; } } } }

下面是DataAccess类

using System; using System.Collections.Generic; using System.Text; namespace IOSS3.Item.DALFactory { /// <summary> /// 装备系统数据层工厂 /// </summary> /// <typeparam name="T"></typeparam> public class DataAccess<T> { static string Path = System.Configuration.ConfigurationManager.AppSettings["ItemDAL"]; /// <summary> /// 创建数据层操作类 /// </summary> /// <param name="classname"></param> /// <returns></returns> public static T CreateDALClassInstance(string classname) { classname = Path + "." + classname; return (T)System.Reflection.Assembly.Load(Path).CreateInstance(classname); } } }

下面是数据层实现的基础抽象类

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Collections; namespace IOSS3.Item.SQLServerDAL { /// <summary> /// 数据层实现的基础抽象类 /// </summary> /// <typeparam name="T">数据表映射对象</typeparam> public abstract class DALBase<T> : IDAL.IDALBase<T> { /// <summary> /// 添加一条信息 /// </summary> /// <param name="t1"></param> /// <returns></returns> public virtual bool Add(T info) { Dictionary<string ,object> members = GetMember(info); StringBuilder sql = new StringBuilder(); StringBuilder fields = new StringBuilder(); StringBuilder parmname = new StringBuilder(); List<SqlParameter> parms = new List<SqlParameter>(); sql.Append("Insert into "+info.GetType().Name); int i = 0; foreach (string key in members.Keys) { if (members[key] == null || members[key].ToString().Trim() == string.Empty) { i++; continue; } if (i > 0) { fields.Append(","); parmname.Append(","); } fields.Append(key); parmname.Append("@" + key); parms.Add(new SqlParameter("@" + key, members[key])); i++; } sql.Append(" (" + fields.ToString() + ")"); sql.Append(" values (" + parmname.ToString() + ")"); int counter = DBUtility.SqlHelper.ExecuteNonQuery(DBUtility.SqlHelper.EpicItemConnectionString, CommandType.Text, sql.ToString(), parms.ToArray()); if (counter > 0) return true; else return false; } /// <summary> /// 修改一条信息 /// </summary> /// <param name="value"></param> /// <param name="guid"></param> /// <returns></returns> public virtual bool Update(T info) { Dictionary<string, object> members = GetMember(info); StringBuilder sql = new StringBuilder(); List<SqlParameter> parms = new List<SqlParameter>(); sql.Append("Update " + info.GetType().Name+" set "); int i=0; int j = 0; foreach (string key in members.Keys) { if (members[key] == null) { i++; continue; } if (j >0) sql.Append(","); sql.Append(key+" = @"+key); parms.Add(new SqlParameter("@" + key, members[key])); i++; j++; } sql.Append(" where Guid=@Guid"); int counter = DBUtility.SqlHelper.ExecuteNonQuery(DBUtility.SqlHelper.EpicItemConnectionString, CommandType.Text, sql.ToString(), parms.ToArray()); if (counter > 0) return true; else return false; } /// <summary> /// 删除一条信息 /// </summary> /// <param name="info"></param> /// <param name="guid"></param> /// <returns></returns> public virtual bool Remove(T info,Guid guid) { Dictionary<string, object> members = GetMember(info); StringBuilder sql = new StringBuilder(); sql.Append("Delete from " + info.GetType().Name + " where Guid=@Guid"); SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Guid", guid) }; int counter = DBUtility.SqlHelper.ExecuteNonQuery(DBUtility.SqlHelper.EpicItemConnectionString, CommandType.Text, sql.ToString(), parms); if (counter > 0) return true; else return false; } /// <summary> /// 获取一条信息 /// </summary> /// <param name="guid"></param> /// <returns></returns> public virtual T GetInfo(T t, Guid guid) { StringBuilder sql = new StringBuilder(); sql.Append("Select * from " + t.GetType().Name + " where Guid=@Guid"); SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Guid", guid) }; SqlDataReader sqlreader = DBUtility.SqlHelper.ExecuteReader(DBUtility.SqlHelper.EpicItemConnectionString, CommandType.Text, sql.ToString(), parms); if (sqlreader.HasRows) { try { if (sqlreader.Read()) { System.Reflection.PropertyInfo[] properties = t.GetType().GetProperties(); for (int i = 0; i < properties.Length; i++) { if (sqlreader[properties[i].Name] == DBNull.Value) continue; properties[i].SetValue(t, sqlreader[properties[i].Name], null); } } } finally { sqlreader.Dispose(); sqlreader.Close(); } } return t; } /// <summary> /// 获取指定条件下的信息列表 /// </summary> /// <param name="where"></param> /// <returns></returns> public abstract IList<T> GetList(System.Collections.Hashtable where); /// <summary> /// 使用反射获取类的成员 /// </summary> /// <param name="t1"></param> /// <returns></returns> private Dictionary<string, object> GetMember(T t1) { System.Reflection.PropertyInfo[] propertylist = t1.GetType().GetProperties(); Dictionary<string ,object> memberlist = new Dictionary<string,object>(); for (int i = 0; i < propertylist.Length; i++) { memberlist.Add(propertylist[i].Name, propertylist[i].GetValue(t1, null)); } return memberlist; } } }

下面是使用案例

public class PackageInfo:DALBase <Model .Itm_PackageInfo>,IDAL .IPackageInfo { /// <summary> /// 添加套餐和套餐产品 /// </summary> /// <param name="package"></param> /// <param name="products"></param> /// <returns></returns> public bool AddPackage(Itm_PackageInfo package, IList<Itm_PackageProducts> products) { string sql = "Insert into dbo.Itm_PackageInfo(Guid,Name,Summary,IcoGuid,PictureGuid,HotType,TypeGuid,SeriesType,Price,PromotionType,PromotionPrice,WebSiteMark,KeyWords,ShowIndex,ProfessionType,Property) values(@Guid,@Name,@Summary,@IcoGuid,@PictureGuid,@HotType,@TypeGuid,@SeriesType,@Price,@PromotionType,@PromotionPrice,@WebSiteMark,@KeyWords,@ShowIndex,@ProfessionType,@Property)"; List<SqlParameter> parms = new List<SqlParameter>(); parms.Add(new SqlParameter("@Guid", package.Guid)); parms.Add(new SqlParameter("@Name", package.Name)); parms.Add(new SqlParameter("@Summary", package.Summary)); parms.Add(new SqlParameter("@IcoGuid", package.IcoGuid)); parms.Add(new SqlParameter("@PictureGuid", package.PictureGuid)); parms.Add(new SqlParameter("@HotType", package.HotType)); parms.Add(new SqlParameter("@TypeGuid", package.TypeGuid)); parms.Add(new SqlParameter("@SeriesType", package.SeriesType)); parms.Add(new SqlParameter("@Price", package.Price)); parms.Add(new SqlParameter("@PromotionType", package.PromotionType)); parms.Add(new SqlParameter("@PromotionPrice", package.PromotionPrice)); parms.Add(new SqlParameter("@WebSiteMark", package.WebSiteMark)); parms.Add(new SqlParameter("@KeyWords", package.KeyWords)); parms.Add(new SqlParameter("@ShowIndex", package.ShowIndex)); parms.Add(new SqlParameter("@ProfessionType", package.ProfessionType)); parms.Add(new SqlParameter("@Property", package.Property)); string psql = "Insert into dbo.Itm_PackageProducts (Guid,PackageGuid,ProductGuid,ProductPrice) values (@Guid,@PackageGuid,@ProductGuid,@ProductPrice)"; bool result = false; using (SqlConnection sqlcon = new SqlConnection(SqlHelper.EpicItemConnectionString)) { sqlcon.Open(); SqlTransaction sqltran = sqlcon.BeginTransaction(); try { SqlCommand sqlcmd = new SqlCommand(sql, sqlcon); sqlcmd.Transaction = sqltran; sqlcmd.Parameters.AddRange(parms.ToArray()); sqlcmd.ExecuteNonQuery(); if (products != null && products.Count > 0) { sqlcmd.CommandText = psql; for (int i = 0; i < products.Count; i++) { SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@Guid", products[i].Guid), new SqlParameter("@PackageGuid", products[i].PackageGuid), new SqlParameter("@ProductGuid", products[i].ProductGuid), new SqlParameter("@ProductPrice",products[i].ProductPrice) }; sqlcmd.Parameters.Clear(); sqlcmd.Parameters.AddRange(parm); sqlcmd.ExecuteNonQuery(); } } sqltran.Commit(); result = true; } catch { result = false; sqltran.Rollback(); } finally { sqltran.Dispose(); sqlcon.Dispose(); sqlcon.Close(); } } return result; } }