我正在使用的一个SQL Server 2000/2005/2008 数据库访问类-SqlCommon(C#)

SqlCommon类简介

SqlCommon类是我在工作中逐步完善的一个用C#编写的基于 .Net Framework 2.0/3.0/3.5 的一个 SQL Server 2000/2005/2008 访问类。以简单的手段实现了数据库的访问，并且可以根据参数名获得所执行的存储过的传出参数的值。可以为刚刚接触编程ADO.NET编程的开发人员做一个简单的参考。

SqlCommon类源代码using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Collections; namespace SqlCommon { /// <summary> /// SqlCommon类使用说明 /// SqlCommon可以实现快捷的执行查询语句或存储过程，并能得到多个返回数据表。 /// 可以使用AddParameter方法增加任意多个的查询参数，并使用RemoveParameter删除任意参数，ClearParameter清除所有参数。 /// 每次查询完成后请注意清理参数 /// </summary> public class SqlCommon : IDisposable { #region dispose private bool m_disposed; //Dispose 标志位 public void Dispose() { try { } catch { } finally { //conn.Dispose(); } Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!m_disposed) { if (disposing) { // Release managed resources } // Release unmanaged resources m_disposed = true; } } #endregion #region 变量定义 private string _connectionString = String.Empty; //连接字符串 private string _commandText = String.Empty; //Sql语句或存储过程名 private bool _execRight = false; //执行是否成功 private int _countAllTablesRows = 0; //返回表的总行数 private int _countTables = 0; //返回表的总数 private bool _logException = false; //对于SQL执行异常是否记录 /// <summary> /// 记录每一个返回表的行项目数 /// </summary> public ArrayList CountTableRows = new ArrayList(); SqlDataAdapter myDataAdapter = new SqlDataAdapter(); //SqlDataAdapter SqlCommand myCommand = new SqlCommand(); //SqlCommand SqlConnection myConnection = new SqlConnection(); //SqlConnection private string _errorMessage = String.Empty; //执行错误时保存错误消息 private string _errorSource = String.Empty; //错误源 private string _errorStackTrace = String.Empty; //堆栈跟踪信息 #endregion #region 属性赋值 /// <summary> /// 需要执行的Sql语句或存储过程名 /// </summary> public string CommandText { set { _commandText = value; } get { return _commandText; } } /// <summary> /// sql语句是否执行正确 /// </summary> /// <returns></returns> public bool ExecuteRight { get { return _execRight; } } /// <summary> /// 统计所有返回表中的行数之和 /// </summary> public int CountAllTableRows { get { return _countAllTablesRows; } } /// <summary> /// 统计返回表的数目 /// </summary> public int CountTables { get { return _countTables; } } /// <summary> /// 返回连接对象 /// </summary> public SqlConnection Connection { get { return myConnection; } } /// <summary> /// 错误消息 /// </summary> public string ErrorMessage { get { return _errorMessage; } } /// <summary> /// 错误源 /// </summary> public string ErrorSource { get { return _errorSource; } } /// <summary> /// 错误堆栈跟踪信息 /// </summary> public string ErrorStackTrace { get { return _errorStackTrace; } } /// <summary> /// SqlCommand /// </summary> public SqlCommand Command { set { myCommand = value; } get { return myCommand; } } #endregion #region 构造和析构函数 /* 连接字符串举例 * <connectionStrings> * <add name="minipdm" connectionString="Data Source=siminfo.simcom-sh.com;Initial Catalog=minipdm-dev;User ID = minipdm; Password = minipdmminipdm; max pool size = 200 ; min pool size =100;Connect Timeout = 60" providerName="System.Data.SqlClient" /> * </connectionStrings> */ /// <summary> /// SqlCommon构造函数，不启用执行异常日志记录 /// </summary> /// <param name="connectionStrings">数据库链接字符串</param> /// <param name="sqlCommandType">SqlCommon连接数据库时执行的语句类型 SQL语句或存储过程</param> public SqlCommon(string connectionStrings, CommandType sqlCommandType) { _connectionString = connectionStrings; myConnection.ConnectionString = _connectionString; myCommand.Connection = myConnection; myCommand.CommandType = sqlCommandType; myDataAdapter.SelectCommand = myCommand; } /// <summary> /// SqlCommon构造函数，可以设置启用执行异常日志记录 /// </summary> /// <param name="connectionStrings">数据库连接字符串</param> /// <param name="sqlCommandType">SqlCommon连接数据库时执行的语句类型 SQL语句或存储过程</param> /// <param name="LogSqlException">是否记将SQL执行错误记录到本地文件</param> public SqlCommon(string connectionStrings, CommandType sqlCommandType, bool LogSqlException) { _logException = LogSqlException; _connectionString = connectionStrings; myConnection.ConnectionString = _connectionString; myCommand.Connection = myConnection; myCommand.CommandType = sqlCommandType; myDataAdapter.SelectCommand = myCommand; } ~SqlCommon() { try { myConnection.Dispose(); myCommand.Dispose(); myDataAdapter.Dispose(); } catch { } } #endregion #region SqlCommon 参数维护方法 /// <summary> /// 为SqlCommon对象增加参数，out和Return参数 /// </summary> /// <param name="parameterName">参数名</param> /// <param name="parameterDirection">传出还是return传值</param> /// <param name="parameterType">参数数据类型，根据存储过程参数返回类型实际情况确定</param> /// <param name="parmeterLength">参数的长度，根据存储过程参数返回类型实际情况确定</param> public void AddParameter(string parameterName, ParameterDirection parameterDirection, SqlDbType parmeterType, int parmeterLength) { myCommand.Parameters.Add(parameterName, parmeterType, parmeterLength); myCommand.Parameters[parameterName].Direction = parameterDirection; } /// <summary> /// 为SqlCommon对象增加参数，Direction Input /// </summary> /// <param name="parameterName">参数名</param> /// <param name="value">值</param> public void AddParameter(string parameterName, object value) { myCommand.Parameters.AddWithValue(parameterName, value); } /// <summary> /// 清空SqlCommon对象中的所有参数 /// </summary> public void ClearParameter() { myDataAdapter.SelectCommand.Parameters.Clear(); } /// <summary> /// 从SqlCommon对象中移除参数 /// </summary> /// <param name="parameterName">参数名</param> public void RemoveParameter(string parameterName) { myCommand.Parameters.RemoveAt(parameterName); } /// <summary> /// 得到参数的值 /// </summary> /// <param name="parameterName">参数名</param> /// <returns></returns> public object GetParameterValue(string parameterName) { return myCommand.Parameters[parameterName].Value; } #endregion #region 执行SQL语句或存储过程 /// <summary> /// 执行SQL语句或存储过程 /// </summary> /// <returns></returns> public DataSet ExecuteSql() { DataSet myDs = new DataSet(); _execRight = true; _errorMessage = String.Empty; _errorSource = String.Empty; _errorStackTrace = String.Empty; _countAllTablesRows = 0; _countTables = 0; myDataAdapter.SelectCommand.CommandText = _commandText.ToUpper(); try { if (myConnection.State.ToString() == "Closed") { myConnection.Open(); } myDataAdapter.Fill(myDs); } catch (Exception e) { _errorMessage = e.Message; _errorSource = e.Source; _errorStackTrace = e.StackTrace; _execRight = false; if (_logException) { LocalLog myLog = new LocalLog(); myLog.LogMe(e.Message + "/n" + e.Source + "/n" + e.StackTrace); myLog.Dispose(); } } finally { _countTables = myDs.Tables.Count; if (_countTables == 0) { _countAllTablesRows = 0; } else { for (int i = 0; i < myDs.Tables.Count; i++) { CountTableRows.Add(myDs.Tables[i].Rows.Count); _countAllTablesRows = _countAllTablesRows + myDs.Tables[i].Rows.Count; } } if (myConnection.State.ToString() == "Open") { myConnection.Close(); } } return myDs; } #endregion } //本地文件日志记录类 public class LocalLog : IDisposable { #region dispose private bool m_disposed; //Dispose 标志位 public void Dispose() { try { } catch { } finally { //conn.Dispose(); } Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!m_disposed) { if (disposing) { // Release managed resources } // Release unmanaged resources m_disposed = true; } } #endregion /// <summary> /// 日志文件名 /// </summary> protected string LOGFILE { get { return "SqlCommonLog.txt"; } } /// <summary> /// 记录日志 /// </summary> /// <param name="message">日志需要记录的内容</param> public void LogMe(string message) { CreateLogFile(); WriteLog(message); } /// <summary> /// 如果日志文件不存在就创建 /// </summary> private void CreateLogFile() { if (!File.Exists(LOGFILE)) { StreamWriter sw = File.CreateText(LOGFILE); sw.Close(); } else { return; } } /// <summary> /// 写入日志 /// </summary> /// <param name="message">写入的消息</param> private void WriteLog(string message) { using (StreamWriter sw = File.AppendText(LOGFILE)) { sw.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + " :/n " + message + "/n"); sw.Close(); } } } }  

SqlCommon使用手册

构造函数

 

SqlCommon(string connectionStrings, CommandType sqlCommandType)	SqlCommon构造函数，不启用执行异常日志记录
SqlCommon(string connectionStrings, CommandType sqlCommandType, bool LogSqlException)	SqlCommon构造函数，可以设置启用执行异常日志记录

属性

Command	SqlCommand
CommandText	需要执行的Sql语句或存储过程名
Connection	返回连接对象
CountAllTableRows	统计所有返回表中的行数之和
CountTables	统计返回表的数目
ErrorMessage	错误消息
ErrorSource	错误源
ErrorStackTrace	错误堆栈跟踪信息
ExecuteRight	sql语句是否执行正确

方法

AddParameter(string parameterName, object value)	为SqlCommon对象增加参数，Direction Input
AddParameter(string parameterName, ParameterDirection parameterDirection, SqlDbType parmeterType, int parmeterLength)	为SqlCommon对象增加参数，out和Return参数
ClearParameter()	清空SqlCommon对象中的所有参数
ExecuteSql()	执行SQL语句或存储过程
GetParameterValue(string parameterName)	得到参数的值
RemoveParameter(string parameterName)	从SqlCommon对象中移除参数

 

使用说明

建立SqlCommon对象

1. SqlCommon.SqlCommon mySqlCommon = new SqlCommon.SqlCommon([数据库连接字符串], [要执行的SQL命令类型(存储过程CommandType.StoredProcedure或SQL语句CommandType.Text)]);
2. SqlCommon.SqlCommon mySqlCommon = new SqlCommon.SqlCommon([数据库连接字符串], [要执行的SQL命令类型], [是否在本地文件中记录SQL执行异常(true记录/false不记录默认不记录)]);

 

确定需要执行的存储过程或SQL命令

mySqlCommon.CommandText = "test1"; //test1为存储过程名
mySqlCommon.CommandText = "SELECT bomno FROM bomhead WHERE bomnotxt = @bomnotxt"; //@bomnotxt 为在语句中定义的输入变量。

添加参数

mySqlCommon.AddParameter("@bomnotxt",tbBomnotxt.Text); //添加正常存储过程输入参数或SQL语句参数
mySqlCommon.AddParameter("@bomno", ParameterDirection.Output,SqlDbType.NVarChar,20); //添加存储过程输出参数 ParameterDirection.Output 为参数类型，SqlDbType.NVarChar为输出参数的值类型不可省略，20输出参数的值长度不可省略
mySqlCommon.AddParameter("@return", ParameterDirection.ReturnValue,SqlDbType.Int,10); //添加存储过程return参数 ParameterDirection.ReturnValue 为参数类型，SqlDbType.Int为return参数的值类型不可省略，10return参数的值长度不可省略

添加执行

DataSet ds = mySqlCommon.ExecuteSql(); //申明DataSet接收返回DataSet
mySqlCommon.GetParameterValue("@bomno"); //得到输出参数@bomno的输出值
mySqlCommon.GetParameterValue("@return"); //得到return参数@return的输出值

 

 

调用SqlCommon的一个例子

调用存储过程

SqlCommon.SqlCommon mySqlCommon = new SqlCommon.SqlCommon(WebConfigurationManager.ConnectionStrings["minipdm"].ConnectionString, CommandType.StoredProcedure);
mySqlCommon.CommandText = "test1";
mySqlCommon.AddParameter("@bomnotxt",tbBomnotxt.Text); //添加参数
mySqlCommon.AddParameter("@bomno", ParameterDirection.Output,SqlDbType.NVarChar,20); //添加存储过程输出参数
mySqlCommon.AddParameter("@return", ParameterDirection.ReturnValue,SqlDbType.Int,10); //添加存储过程return参数
DataSet ds = new DataSet();
ds = mySqlCommon.ExecuteSql();
Label1.Text = "TableBomno: " + ds.Tables[0].Rows[0]["bomno"].ToString();
Label2.Text = "OutputBomno: " + mySqlCommon.GetParameterValue("@bomno");
Label3.Text = "ReturnState: " + mySqlCommon.GetParameterValue("@return");

直接执行语句

SqlCommon.SqlCommon mySqlCommon = new SqlCommon.SqlCommon(WebConfigurationManager.ConnectionStrings["minipdm"].ConnectionString, CommandType.Text);
mySqlCommon.CommandText = "SELECT bomno FROM bomhead WHERE bomnotxt = @bomnotxt";
mySqlCommon.AddParameter("@bomnotxt", tbBomnotxt.Text); //添加参数
DataSet ds = new DataSet();
ds = mySqlCommon.ExecuteSql();
Label1.Text = "TableBomno: " + ds.Tables[0].Rows[0]["bomno"].ToString();