微软在petShop中使用了sqlServerHelper类,通过调用静态方法实现对数据库的 *** 作,使用起来非常简单顺手,下面整理了该类的代码,并加以说明:
//------------------------------------------------------------// All Rights Reserved,copyright (C) 2010,lusens //------------------------------------------------------------using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.sqlClIEnt;namespace Utility.Data{ /// <summary> /// sqlServer数据库 *** 作类 /// 本类主要包括sqlServer数据库的基本 *** 作 /// /// 修改纪录 /// /// 2010.10.22 版本:1.1 lusens 新增了用户传入SQL语句列表的事务 *** 作 /// 2010.09.05 版本:1.0 lusens 创建。 /// /// 版本:1.1 /// /// <author> /// <name>lusens</name> /// <date>2010.09.05</date> /// <EMail>[email protected]</EMail> /// </author> /// </summary> public abstract class sqlServerHepler { //public static Readonly string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString; #region public static string GetConnectionStringByConfig() /// <summary> /// 读取配置文件的ConnectionString字符串 /// </summary> /// <returns>Connection连接字符串</returns> public static string GetConnectionStringByConfig() { return System.Configuration.ConfigurationManager.ConnectionStrings["sqlConnString"].ConnectionString; } #endregion #region 执行sql,返回被 *** 作的行数,public static int ExecuteNonquery(string connectionString,CommandType cmdType,string cmdText,params sqlParameter[] commandParameters) /// <summary> /// 执行SQL语句,返回被 *** 作的行数 /// 使用using语句进行conn对象的释放 /// </summary> /// <param name="connectionString">连接字符</param> /// <param name="cmdType">Command类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns>返回被 *** 作行数</returns> public static int ExecuteNonquery(string connectionString,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; } } #endregion #region 执行sql,返回被 *** 作的行数,public static int ExecuteNonquery(sqlConnection connection,params sqlParameter[] commandParameters) /// <summary> /// 执行SQL语句,返回被 *** 作的行数 /// 这里默认使用外面传入的conn对象,使用完成后不会对conn对象进行释放,需要自己在外面进行数据库连接释放 /// </summary> /// <param name="connection">数据库连接对象</param> /// <param name="cmdType">command命令类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static int ExecuteNonquery(sqlConnection connection,params sqlParameter[] commandParameters) { sqlCommand cmd = new sqlCommand(); PrepareCommand(cmd,connection,commandParameters); int val = cmd.ExecuteNonquery(); cmd.Parameters.Clear(); return val; } #endregion #region 执行SQL语句,返回被 *** 作的行数,public static int ExecuteNonquery(sqlTransaction trans,params sqlParameter[] commandParameters) /// <summary> /// 执行SQL语句,返回被 *** 作的行数 /// 这里默认使用外面传入的conn对象,使用完成后不会对conn对象进行释放,需要自己在外面进行数据库连接释放 /// </summary> /// <param name="trans">sql事务</param> /// <param name="cmdType">command类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static int ExecuteNonquery(sqlTransaction trans,trans.Connection,trans,commandParameters); int val = cmd.ExecuteNonquery(); cmd.Parameters.Clear(); return val; } #endregion #region 执行sql,返回sqlDataReader,public static sqlDataReader ExecuteReader(string connectionString,params sqlParameter[] commandParameters) /// <summary> /// 执行sql,返回sqlDataReader /// 返回一个连接,所以不能进行conn释放,在外界代码中使用完DataReader后,注意需要释放reader对象 /// 当返回连接对象报错时,这里进行数据库连接的关闭,保证数据库连接使用完成后保持关闭 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="cmdType">command命令类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static sqlDataReader ExecuteReader(string connectionString,params sqlParameter[] commandParameters) { sqlCommand cmd = new sqlCommand(); sqlConnection conn = new sqlConnection(connectionString); try { PrepareCommand(cmd,commandParameters); sqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception e) { conn.Close(); throw e; } } #endregion #region 执行sql,返回sqlDataReader,public static sqlDataReader ExecuteReader(sqlConnection connection,params sqlParameter[] commandParameters) /// <summary> /// 执行sql,返回sqlDataReader /// 返回一个连接,所以不能进行conn释放,在外界代码中使用完DataReader后,注意需要释放reader对象 /// 当返回连接对象报错时,这里进行数据库连接的关闭,保证数据库连接使用完成后保持关闭 /// </summary> /// <param name="connection">sqlConnection数据库连接对象</param> /// <param name="cmdType">command命令类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static sqlDataReader ExecuteReader(sqlConnection connection,params sqlParameter[] commandParameters) { sqlCommand cmd = new sqlCommand(); try { PrepareCommand(cmd,commandParameters); sqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception e) { connection.Close(); throw e; } } #endregion #region 执行sql,返回Datatable, public static Datatable ExecuteDatatable(string connectionString,params sqlParameter[] commandParameters) /// <summary> /// 执行sql,返回Datatable /// 使用using语句进行conn对象的释放 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="cmdType">command命令类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static Datatable ExecuteDatatable(string connectionString,params sqlParameter[] commandParameters) { Datatable dt = new Datatable(); sqlCommand cmd = new sqlCommand(); using (sqlConnection conn = new sqlConnection(connectionString)) { PrepareCommand(cmd,commandParameters); using (sqlDataAdapter sda = new sqlDataAdapter()) { sda.SelectCommand = cmd; sda.Fill(dt); } } return dt; } #endregion #region 执行sql,返回Datatable,public static Datatable ExecuteDatatable(sqlConnection connection,params sqlParameter[] commandParameters) /// <summary> /// 执行sql,返回Datatable /// 这里默认使用外面传入的conn对象,使用完成后不会对conn对象进行释放,需要自己在外面进行数据库连接释放 /// </summary> /// <param name="connection">数据库连接对象</param> /// <param name="cmdType">Command命令类型,SQL语句或存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static Datatable ExecuteDatatable(sqlConnection connection,params sqlParameter[] commandParameters) { Datatable dt = new Datatable(); sqlCommand cmd = new sqlCommand(); PrepareCommand(cmd,commandParameters); using (sqlDataAdapter sda = new sqlDataAdapter()) { sda.SelectCommand = cmd; sda.Fill(dt); } return dt; } #endregion #region 返回第一行第一列,public static object ExecuteScalar(string connectionString,params sqlParameter[] commandParameters) /// <summary> /// 返回第一行第一列 /// 使用using语句进行conn对象的释放 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="cmdType">Command命令类型,SQL语句还是存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static object ExecuteScalar(string connectionString,params sqlParameter[] commandParameters) { sqlCommand cmd = new sqlCommand(); using (sqlConnection connection = new sqlConnection(connectionString)) { PrepareCommand(cmd,commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } } #endregion #region 返回第一行第一列,public static object ExecuteScalar(sqlConnection connection,params sqlParameter[] commandParameters) /// <summary> /// 返回第一行第一列 /// 使用外面传入的conn对象,使用完成后不会对conn对象进行释放,需要自己在外面进行数据库连接释放 /// </summary> /// <param name="connection">sqlConnection数据库连接对象</param> /// <param name="cmdType">Command命令类型,SQL语句还是存储过程</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="commandParameters">参数数组</param> /// <returns></returns> public static object ExecuteScalar(sqlConnection connection,params sqlParameter[] commandParameters) { sqlCommand cmd = new sqlCommand(); PrepareCommand(cmd,commandParameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } #endregion #region 以事务执行SQL语句列表,public static bool ExecuteTransaction(sqlConnection conn,List<string> cmdTextes,List<sqlParameter[]> commandParameterses) /// <summary> /// 以事务执行SQL语句列表,返回事务执行是否成功 /// </summary> /// <param name="conn">数据库连接对象</param> /// <param name="cmdTextes">SQL语句列表</param> /// <param name="commandParameterses">SQL语句列表对应的参数列表,参数列表必须与SQL语句列表匹配</param> /// <returns>事务执行是否成功</returns> public static bool ExecuteTransaction(sqlConnection conn,List<sqlParameter[]> commandParameterses) { bool flag = false; if (cmdTextes.Count == commandParameterses.Count) { sqlTransaction sqlTran = conn.BeginTransaction(); try { for (int i = 0; i < cmdTextes.Count; i++) { ExecuteNonquery(sqlTran,CommandType.Text,cmdTextes[i],commandParameterses[i]); } sqlTran.Commit(); flag = true; } catch (Exception e) { sqlTran.Rollback(); } } return flag; } #endregion #region 以事务执行SQL语句列表,返回事务执行是否成功,public static bool ExecuteTransaction(string connectionString,List<sqlParameter[]> commandParameterses) /// <summary> /// 以事务执行SQL语句列表,返回事务执行是否成功 /// </summary> /// <param name="connectionString">数据库连接字符串</param> /// <param name="cmdTextes">SQL语句列表</param> /// <param name="commandParameterses">SQL语句列表对应的参数列表,参数列表必须与SQL语句列表匹配</param> /// <returns></returns> public static bool ExecuteTransaction(string connectionString,List<sqlParameter[]> commandParameterses) { bool flag = false; if (cmdTextes.Count == commandParameterses.Count) { using (sqlConnection conn = new sqlConnection(connectionString)) { sqlTransaction sqlTran = conn.BeginTransaction(); try { for (int i = 0; i < cmdTextes.Count; i++) { ExecuteNonquery(sqlTran,commandParameterses[i]); } sqlTran.Commit(); flag = true; } catch (Exception e) { sqlTran.Rollback(); } } } return flag; } #endregion #region 构造sqlCommand,private static voID PrepareCommand(sqlCommand cmd,sqlConnection conn,sqlTransaction trans,sqlParameter[] cmdParms) /// <summary> /// 构造sqlCommand /// </summary> /// <param name="cmd">Command对象</param> /// <param name="conn">sqlConnection数据库连接对象</param> /// <param name="trans">sql事务</param> /// <param name="cmdType">Command命令类型</param> /// <param name="cmdText">SQL语句或存储过程名称</param> /// <param name="cmdParms">参数数组</param> private static voID PrepareCommand(sqlCommand cmd,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); } } #endregion }}
注意:该类并没有完全封装所有数据对象的使用 ,所以在外面使用类时,也会自行使用sqlConnection、sqlCommand等对象;
可以在 http://download.csdn.net/detail/luxin10/3830325 下载,无需积分
总结以上是内存溢出为你收集整理的SqlServerHelper类,整理微软提供的helper全部内容,希望文章能够帮你解决SqlServerHelper类,整理微软提供的helper所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)