这篇文章给大家分享的是有关C#如何实现SQL封装的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
SQLHelper.as , 代码如下:
using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Web; namespace DBDome { /// 此类为抽象类, /// 不允许实例化,在应用时直接调用即可 /// </summary> public sealed class SqlHelper { private SqlHelper() { } /// <summary> /// 数据库连接字符串 /// </summary> public static readonly string connectionString = ConfigurationManager.ConnectionStrings["con"].ToString(); // Hashtable to store cached parameters private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); #region//ExecteNonQuery方法 /// <summary> ///执行一个不需要返回值的SqlCommand命令,通过指定专用的连接字符串。 /// 使用参数数组形式提供参数列表 /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQuery(string connectionString, CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { //通过PrePareCommand方法将参数逐个加入到SqlCommand的参数集合中 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); int val = cmd.ExecuteNonQuery(); //清空SqlCommand中的参数列表 cmd.Parameters.Clear(); return val; } } /// <summary> ///执行一个不需要返回值的SqlCommand命令,通过指定专用的连接字符串。 /// 使用参数数组形式提供参数列表 /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQuery(CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { return ExecteNonQuery(connectionString, cmdType, cmdText, commandParameters); } /// <summary> ///存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQueryProducts(string cmdText, SqlParameter[] commandParameters) { return ExecteNonQuery(CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> ///Sql语句专用 /// </summary> /// <param name="cmdText">T_Sql语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns> public static int ExecteNonQueryText(string cmdText, SqlParameter[] commandParameters) { return ExecteNonQuery(CommandType.Text, cmdText, commandParameters); } #endregion #region//GetTable方法 /// <summary> /// 执行一条返回结果集的SqlCommand,通过一个已经存在的数据库连接 /// 使用参数数组提供参数 /// </summary> /// <param name="connecttionString">一个现有的数据库连接</param> /// <param name="cmdTye">SqlCommand命令类型</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection GetTable(string connecttionString, CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); DataSet ds = new DataSet(); using (SqlConnection conn = new SqlConnection(connecttionString)) { PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(ds); } DataTableCollection table = ds.Tables; return table; } /// <summary> /// 执行一条返回结果集的SqlCommand,通过一个已经存在的数据库连接 /// 使用参数数组提供参数 /// </summary> /// <param name="cmdTye">SqlCommand命令类型</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection GetTable(CommandType cmdTye, string cmdText, SqlParameter[] commandParameters) { return GetTable(cmdTye, cmdText, commandParameters); } /// <summary> /// 存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection GetTableProducts(string cmdText, SqlParameter[] commandParameters) { return GetTable(CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// Sql语句专用 /// </summary> /// <param name="cmdText"> T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个表集合(DataTableCollection)表示查询得到的数据集</returns> public static DataTableCollection GetTableText(string cmdText, SqlParameter[] commandParameters) { return GetTable(CommandType.Text, cmdText, commandParameters); } #endregion /// <summary> /// 为执行命令准备参数 /// </summary> /// <param name="cmd">SqlCommand 命令</param> /// <param name="conn">已经存在的数据库连接</param> /// <param name="trans">数据库事物处理</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">Command text,T-SQL语句 例如 Select * from Products</param> /// <param name="cmdParms">返回带参数的命令</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> /// Execute a SqlCommand that returns a resultset against the database specified in the connection string /// using the provided parameters. /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>A SqlDataReader containing the results</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, 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; } } #region//ExecuteDataSet方法 /// <summary> /// return a dataset /// </summary> /// <param name="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); return ds; } catch { conn.Close(); throw; } } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, cmdType, cmdText, commandParameters); } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSetProducts(string cmdText, SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// 返回一个DataSet /// </summary> /// <param name="cmdText">T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>return a dataset</returns> public static DataSet ExecuteDataSetText(string cmdText, SqlParameter[] commandParameters) { return ExecuteDataSet(connectionString, CommandType.Text, cmdText, commandParameters); } public static DataView ExecuteDataSet(string connectionString, string sortExpression, string direction, CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = cmd; da.Fill(ds); DataView dv = ds.Tables[0].DefaultView; dv.Sort = sortExpression + " " + direction; return dv; } catch { conn.Close(); throw; } } #endregion #region // ExecuteScalar方法 /// <summary> /// 返回第一行的第一列 /// </summary> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalar(CommandType cmdType, string cmdText, SqlParameter[] commandParameters) { return ExecuteScalar(SqlHelper.connectionString, cmdType, cmdText, commandParameters); } /// <summary> /// 返回第一行的第一列存储过程专用 /// </summary> /// <param name="cmdText">存储过程的名字</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalarProducts(string cmdText, SqlParameter[] commandParameters) { return ExecuteScalar(SqlHelper.connectionString, CommandType.StoredProcedure, cmdText, commandParameters); } /// <summary> /// 返回第一行的第一列Sql语句专用 /// </summary> /// <param name="cmdText">者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param> /// <returns>返回一个对象</returns> public static object ExecuteScalarText(string cmdText, SqlParameter[] commandParameters) { return ExecuteScalar(SqlHelper.connectionString, CommandType.Text, cmdText, commandParameters); } /// <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">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</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, 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="connectionString">一个有效的数据库连接字符串</param> /// <param name="cmdType">SqlCommand命令类型 (存储过程, T-SQL语句, 等等。)</param> /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param> /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</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, SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } #endregion /// <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, 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> /// 检查是否存在 /// </summary> /// <param name="strSql">Sql语句</param> /// <returns>bool结果</returns> public static bool Exists(string strSql) { int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, null)); if (cmdresult == 0) { return false; } else { return true; } } /// <summary> /// 检查是否存在 /// </summary> /// <param name="strSql">Sql语句</param> /// <param name="cmdParms">参数</param> /// <returns>bool结果</returns> public static bool Exists(string strSql, SqlParameter[] cmdParms) { int cmdresult = Convert.ToInt32(ExecuteScalar(connectionString, CommandType.Text, strSql, cmdParms)); if (cmdresult == 0) { return false; } else { return true; } } } }
本篇博客只涉及到添加数据 (insert into) 并陆续的更新其他SQL操作, 本人希望封装一个全套基本的SQL操作。在以后如果从事后端可以在此扩展 , 不喜勿喷。
本人希望用SqlParameter的方式来构建T-SQL
一 : 构建基本的T-SQL( 没有SqlParameter参数注入 )
using DBDome.model; using System; using System.Collections.Generic; using System.Linq; using System.Reflection; using System.Text; namespace DBDome.com { /// <summary> /// SQL构建器(还没与赋值) /// </summary> public sealed class SQL_Structure { private SQL_Structure() { } /// <summary> /// 添加一个数据的T-SQL的构成 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> public static string ADD_T_SQL<T>( T model ) where T : BaseModel { string base_add = @"insert into [{0}] ({1}) values ({2})";//表名 , 列明 , 对应的值 Type model_type = model.GetType(); string[] arr = model_type.Name.Split(new char[] { '.' }); string table_name = arr[arr.Length - 1];//获得表的名称 PropertyInfo[] p_intos = model_type.GetProperties(); StringBuilder table_field_name = new StringBuilder(); StringBuilder table_field_value = new StringBuilder(); PropertyInfo item = null; for (int i = 0; i < p_intos.Length; i++) { item = p_intos[i]; if (item.Name != "id")//不应添加主键 { table_field_name.Append(item.Name + ","); table_field_value.Append("@" + item.Name + ","); } } return String.Format(base_add, table_name, table_field_name.ToString().Substring(0, table_field_name.ToString().Length-1), table_field_value.ToString().Substring(0,table_field_value.ToString().Length-1)); } } }
二 : 好了我现在构造与SQL表映射的模型类
①,所有model的基类:
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace DBDome.model { public abstract class BaseModel { protected int _id; /// <summary> /// 主键ID号 /// </summary> public virtual int id { get { return this._id; } set { this._id = value; } } } }
② , 继承model的一个类User也是我的一个SQL表名
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace DBDome.model { public sealed class User : BaseModel { public string name { get; set; } public short sex{ get; set; } public int lv{ get; set; } public string username{ get; set; } public string userpwd{ get; set; } } }
三 , 我们添加一条数据 , 需要构建参数 SqlParameter[]
using DBDome.model; using System; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Data; namespace DBDome.com { /// <summary> /// SqlParameter 构建 /// </summary> public sealed class SqlParameter_Structure { private SqlParameter_Structure() { } /// <summary> /// 构建T-SQL参数 ADD /// </summary> /// <typeparam name="T"></typeparam> /// <param name="model"></param> /// <returns></returns> public static SqlParameter[] ADD_T_SQL<T>(T model) where T : BaseModel { Type model_type = model.GetType(); PropertyInfo[] p_intos = model_type.GetProperties(); SqlParameter[] sql_param = new SqlParameter[p_intos.Length - 1];//不需要主键 PropertyInfo item = null; string p_name = ""; SqlParameter cell = null; int j = 0; for (int i = 0; i < p_intos.Length; i++) { item = p_intos[i]; p_name = item.Name; if (p_name == "id") continue; // Console.WriteLine("字段 {0} 的类型为 {1} ", p_name ,item.PropertyType); cell = new SqlParameter(p_name, SqlParameter_Structure.Get_SqlDbType_SqlType(item.PropertyType)); cell.Value = item.GetValue(model, null); sql_param[j] = cell; j++; } return sql_param; } private static SqlDbType Get_SqlDbType_SqlType(Type cshaper) { string[] arr = cshaper.ToString().Split(new char[] { '.' }); string toLow = arr[arr.Length - 1].ToLower(); switch (toLow) { case "string": return SqlDbType.NVarChar; break; case "int16": return SqlDbType.Bit; break; case "int32": return SqlDbType.Int; break; default: throw new Exception(String.Format("T-SQL参数没有配对的类型 {0}" ,cshaper)); break; } } } }
感谢各位的阅读!关于“C#如何实现SQL封装”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。