c# sql server数据库连接类

//为了避免数据库连接关闭不及时的问题，需要保持连接的查询需要自己控制连接的关闭。

//用.net一定要注意关闭用完的连接和资源，因为.net的垃圾回收不理想，而.net程序不能占用超过1g的内存

using System;

using System.Data;

using System.Threading;

using System.Collections;

using System.Runtime.Remoting.Lifetime;

using System.Data.SqlClient;

 

namespace WebIndex

{

 

     /// <summary>

     /// DataAccess 的摘要说明。

     /// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>

     /// </summary>

     public class SqlConn

     {

         #region  属性

 

         public struct OConntion

         {

              public SqlConnection conn;

              public bool connLock;

              public DateTime outTime;

              public string sqlStr;

              public string error;

         }

         public struct SqlError

         {

              public string sqlStr;

              public string errInfo;

              public DateTime errTime;

         }

 

         public static SqlError[] sqlError;

         public static OConntion[] sconn;

         public static Queue opened = new Queue();

         public static Queue closed = new Queue();

         public static int nConn=100;

         public static int mastConn=0;

         public static int exConn=0;

         public static int errorConn=0;

         public static int otherError=0;

         public static int OKConn=0;

         protected static bool format=true;

 

         #endregion

 

         public SqlConn()

         {

         }

 

         #region 内部函数 数据库连接池

         private static int getOpened()

         {

              int ret = -1;

              int peek = 0;

              for(int i=0;i<opened.Count;i++)

              {

                   if(opened.Count == 0) return -1;

                   peek = (int)opened.Peek();

                   if(sconn[peek].conn.State == ConnectionState.Closed)

                   {

                       //关闭的连接回到关闭队列

                       sconn[peek].conn.Dispose();

                       sconn[peek].connLock = false;

                       closed.Enqueue(opened.Dequeue());

                       continue;

                   }

                   if(sconn[peek].connLock == false)

                   {

                       //未被锁闭的连接

                       opened.Enqueue(opened.Dequeue());

                       ret = peek;

                       break;

                   }

                   else

                   {

                       opened.Enqueue(opened.Dequeue());

                   }

              }

              return ret;

         }

 

         private static int getClosed()

         {

              int ret = -1;

              if(closed.Count>0)

              {

                   ret = (int)closed.Dequeue();

                   opened.Enqueue(ret);

              }

              return ret;

         }

 

         private static int getConn()

         {

              int ret = -1;

              lock(typeof(SqlConn))

              {

                   if(format)

                   {

                       //格式化数据

                       sconn = new OConntion[nConn];

                       sqlError = new SqlError[nConn];

                       for(int f=0;f<nConn;f++)

                       {

                            sconn[f].conn = new SqlConnection();

                            sconn[f].connLock = false;

                            sconn[f].sqlStr = "";

                            sconn[f].error = "";

                            //sclosed.Push(nConn-f-1);

                            closed.Enqueue(f);

                       }

                       format = false;

                   }

                   ret = getOpened();

                   if(ret < 0) ret = getClosed();

                   if(ret < 0)

                   {

                        //没有找到可用连接，强行分配一个！

                       mastConn++;

                       ret = (int)opened.Dequeue();

                       opened.Enqueue(ret);

                   }

                   sconn[ret].connLock = true;

              }

              OKConn++;

              exConn = ret;

              if(sconn[ret].conn.State != ConnectionState.Closed)

                   sconn[ret].conn.Close();

              sconn[ret].conn.Dispose();

              return ret;

         }

 

         private static void freeConn(int freeConn)

         {

              if(sconn[freeConn].conn.State == ConnectionState.Open)

                   sconn[freeConn].conn.Close();

              sconn[freeConn].conn.Dispose();

              sconn[freeConn].connLock=false;

         }

 

         public static int newConn()

         {

              return getConn();

         }

 

         public static void closeConn( int i)

         {

              freeConn(i);

         }

 

         public static void logError(Exception e,string sql)

         {

              if(otherError>99) otherError = 0;

              sqlError[otherError].sqlStr = sql;

              sqlError[otherError].errInfo = e.ToString();

              sqlError[otherError].errTime = System.DateTime.Now;

              otherError++;

         }

         #endregion

 

         #region 内部函数  静态方法中不会执行 Ooconn()构造函数

 

         /// <summary>

         /// 打开数据库连接

         /// </summary>

         private static void openoconnection(int iConn)

         {

              if(sconn[iConn].conn.State != ConnectionState.Open)

              {

                   //判断是否为培训服务器

                   if(sconn[iConn].conn.State != ConnectionState.Closed)sconn[iConn].conn.Close();

                   sconn[iConn].conn.ConnectionString =  System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];

                   try

                   {

                       sconn[iConn].conn.Open();

                   }

                   catch(Exception e)

                   {

                       errorConn++;

                       sconn[iConn].error = e.ToString();

                       throw new Exception(e.Message);

                   }

              }

         }

 

         /// <summary>

         /// 打开数据库连接

         /// </summary>

         private static void openoconnection(SqlConnection Conn)

         {

              OKConn++;

              if( Conn.State != ConnectionState.Open)

              {

                   //判断是否为培训服务器

                   if(Conn.State != ConnectionState.Closed)Conn.Close();

                   Conn.ConnectionString =  System.Configuration.ConfigurationSettings.AppSettings["SqlConnectionString"];

                   try

                   {

                       Conn.Open();

                   }

                   catch(Exception e)

                   {

                       errorConn++;

                       throw new Exception(e.Message);

                   }

              }

         }

         /// <summary>

         /// 关闭当前数据库连接

         /// </summary>

         private static void closeoconnection()

         {

         }

         #endregion

 

         /// <summary>

         /// 执行Sql查询语句

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         public static void ExecuteSql(string sqlstr)

         {

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   ocomm.ExecuteNonQuery();

                   ocomm.Dispose();

                  

              }

              catch(Exception e)

              {

                   errorConn++;

                   logError(e,sqlstr);

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

         }

 

         /// <summary>

         /// 执行存储过程

         /// </summary>

         /// <param name="procName">存储过程名</param>

         /// <param name="coll">SqlParameters 集合</param>

         public static void ExecutePorcedure(string procName,SqlParameter[] coll)

         {

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = procName;

                   openoconnection(i);

                   for(int j=0;j<coll.Length;j++)

                   {

                       ocomm.Parameters .Add(coll[j]);

                   }

                   ocomm.CommandType=CommandType.StoredProcedure ;

                   ocomm.CommandText =procName;

                   ocomm.ExecuteNonQuery();

                   ocomm.Parameters.Clear();

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   errorConn++;

                   logError(e,procName);

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

         }

 

 

         /// <summary>

         /// 执行Sql查询语句并返回SqlDataAdapter

         /// </summary>

         /// <param name="numConn">连接号</param>

         /// <param name="sqlStr">传入的Sql语句</param>

         public static SqlDataAdapter dataAdapter(SqlConnection Conn,String sqlStr)

         {

              SqlDataAdapter ret=null;

              try

              {

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = Conn;

                   openoconnection(Conn);

                   ret = new SqlDataAdapter(sqlStr,Conn);

                   ocomm.Dispose();

 

              }

              catch( Exception e )

              {

                   logError(e,sqlStr);

                   Conn.Close();

                   Conn.Dispose();

                   throw new Exception(e.Message);

              }

              finally

              {

              }

              return ret;

         }

 

         /// <summary>

         /// 执行Sql查询语句并返回SqlDataAdapter

         /// </summary>

         /// <param name="numConn">连接号</param>

         /// <param name="sqlStr">传入的Sql语句</param>

         public static SqlDataAdapter dataAdapter(int numConn,String sqlStr)

         {

              SqlDataAdapter ret=null;

              int i=0;

              try

              {

                   i = numConn;

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlStr;

                   openoconnection(i);

                   ret = new SqlDataAdapter(sqlStr,sconn[i].conn);

                   ocomm.Dispose();

 

              }

              catch( Exception e )

              {

                   errorConn++;

                   logError(e,sqlStr);

                   sconn[i].error = e.ToString();

                   freeConn(i);

                   throw new Exception(e.Message);

              }

              finally

              {

              }

              return ret;

         }

 

         /// <summary>

         /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <returns>object 返回值 </returns>

         public static object ExecuteScalar(string sqlstr)

         {

              object obj=new object();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   obj=ocomm.ExecuteScalar();

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   errorConn++;

                   logError(e,sqlstr);

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

              return obj;

         }

 

         /// <summary>

         /// 执行Sql查询语句,同时进行事务处理

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         public static void ExecuteSqlWithTransaction(string sqlstr)

         {

              SqlTransaction trans ;

              int i=0;

              i = getConn();

              SqlCommand   ocomm = new SqlCommand();

              ocomm.Connection = sconn[i].conn;

              sconn[i].sqlStr = sqlstr;

              openoconnection(i);

              trans=sconn[i].conn.BeginTransaction();

              ocomm.Transaction =trans;

              try

              {

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   ocomm.ExecuteNonQuery();

                   trans.Commit();

              }

              catch(Exception e)

              {

                   errorConn++;

                   logError(e,sqlstr);

                   sconn[i].error = e.ToString();

                   trans.Rollback();

                   throw new Exception(e.Message);

              }

              finally

              {

                   ocomm.Dispose();

                   freeConn(i);

              }

         }

 

         /// <summary>

         /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeoconnection()来关闭数据库连接

         /// 方法关闭数据库连接

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <returns>SqlDataReader对象</returns>

         public static SqlDataReader dataReader(SqlConnection Conn,string sqlstr)

         {

              SqlDataReader dr=null;

              try

              {

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = Conn;

                   openoconnection(Conn);

                   ocomm.CommandText =sqlstr;

                   ocomm.CommandType =CommandType.Text ;

                   dr=ocomm.ExecuteReader(CommandBehavior.CloseConnection);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   try

                   {

                       if(dr!=null && !dr.IsClosed)

                            dr.Close();

                       if(Conn.State != ConnectionState.Closed)

                            Conn.Close();

                       Conn.Dispose();

                   }

                   catch

                   {

                   }

              }

              finally

              {

              }

              return dr;

         }

 

         /// <summary>

         /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeoconnection()来关闭数据库连接

         /// 方法关闭数据库连接

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <param name="dr">传入的ref DataReader 对象</param>

         public static void dataReader(SqlConnection Conn,string sqlstr,ref SqlDataReader dr)

         {

              try

              {

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = Conn;

                   openoconnection(Conn);

                   ocomm.CommandText =sqlstr;

                   ocomm.CommandType =CommandType.Text ;

                   dr=ocomm.ExecuteReader(CommandBehavior.CloseConnection);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   errorConn++;

                   try

                   {

                       if(dr!=null && !dr.IsClosed)

                            dr.Close();

                       if(Conn.State != ConnectionState.Closed)

                            Conn.Close();

                       Conn.Dispose();

                   }

                   catch

                   {

                   }

              }

              finally

              {

              }

         }

 

 

         /// <summary>

         /// 返回指定Sql语句的DataSet

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <returns>DataSet</returns>

         public static DataSet dataSet(string sqlstr)

         {

              DataSet ds= new DataSet();

              SqlDataAdapter da=new SqlDataAdapter();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   da.SelectCommand =ocomm;

                   da.Fill(ds);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   errorConn++;

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

              return ds;

         }

 

         /// <summary>

         /// 返回指定Sql语句的DataSet

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <param name="ds">传入的引用DataSet对象</param>

         public static void dataSet(string sqlstr,ref DataSet ds)

         {

              SqlDataAdapter da=new SqlDataAdapter();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   da.SelectCommand =ocomm;

                   da.Fill(ds);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   errorConn++;

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

         }

 

         /// <summary>

         /// 返回指定Sql语句的DataTable

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <returns>DataTable</returns>

         public static DataTable dataTable(string sqlstr)

         {

              SqlDataAdapter da=new SqlDataAdapter();

              DataTable datatable=new DataTable();

              int i=0;

              SqlConnection Conn = new SqlConnection();

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = Conn;

                   openoconnection(Conn);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   da.SelectCommand =ocomm;

                   da.Fill(datatable);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   throw new Exception(e.Message);

              }

              finally

              {

                   Conn.Close();

                   Conn.Dispose();

              }

              return datatable;

         }

 

         /// <summary>

         /// 执行指定Sql语句,同时给传入DataTable进行赋值

         /// </summary>

         /// <param name="sqlstr">传入的Sql语句</param>

         /// <param name="dt">ref DataTable dt </param>

         public static void dataTable(string sqlstr,ref DataTable dt)

         {

              SqlDataAdapter da=new SqlDataAdapter();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType =CommandType.Text ;

                   ocomm.CommandText =sqlstr;

                   da.SelectCommand =ocomm;

                   da.Fill(dt);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,sqlstr);

                   errorConn++;

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

         }

 

         /// <summary>

         /// 执行带参数存储过程并返回数据集合

         /// </summary>

         /// <param name="procName">存储过程名称</param>

         /// <param name="parameters">SqlParameterCollection 输入参数</param>

         /// <returns></returns>

         public static DataTable dataTable(string procName,SqlParameterCollection parameters)

         {

              SqlDataAdapter da=new SqlDataAdapter();

              DataTable datatable=new DataTable();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = procName;

                   openoconnection(i);

                   ocomm.Parameters.Clear();

                   ocomm.CommandType=CommandType.StoredProcedure ;

                   ocomm.CommandText =procName;

                   foreach(SqlParameter para in parameters)

                   {

                       SqlParameter p=(SqlParameter)para;

                       ocomm.Parameters.Add(p);

                   }

                   da.SelectCommand =ocomm;

                   da.Fill(datatable);

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   logError(e,procName);

                   errorConn++;

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

              return datatable;

         }

        

 

         public static DataView dataView(string sqlstr)

         {

              SqlDataAdapter da=new SqlDataAdapter();

              DataView dv=new DataView();

              DataSet ds=new DataSet();

              int i=0;

              try

              {

                   i = getConn();

                   SqlCommand   ocomm = new SqlCommand();

                   ocomm.Connection = sconn[i].conn;

                   sconn[i].sqlStr = sqlstr;

                   openoconnection(i);

                   ocomm.CommandType=CommandType.Text;

                   ocomm.CommandText =sqlstr;

                   da.SelectCommand =ocomm;

                   da.Fill(ds);

                   dv=ds.Tables[0].DefaultView;

                   ocomm.Dispose();

              }

              catch(Exception e)

              {

                   errorConn++;

                   logError(e,sqlstr);

                   sconn[i].error = e.ToString();

                   throw new Exception(e.Message);

              }

              finally

              {

                   freeConn(i);

              }

              return dv;

         }

 

     }

 

 

}