一些类库的研究-sqlhelper

首先看看sqlhelper,就是Ado.net的辅助数据库操作类，传的是sql语句。

1.常量定义

        private SqlConnection objSqlConnection = new SqlConnection();
        private SqlCommand objSqlcommand = new SqlCommand();
        private SqlDataAdapter objSDA = new SqlDataAdapter();
        private static object objLock = new object();
        public static string sqlConnString = string.Empty;

2.构造函数

　　public SqlHelper()

　　{

　　sqlConnString=ConfigValue.SqlConnection;

　　}

3.打开连接

　　private void OpenConn()
 　　{
 　　objSqlConnection.ConnectionString = SqlConnString;
 　　 try
　　　　{
 　　　　objSqlConnection.Open();
            }
　　 catch (Exception Err)
　　　　{
　　　　throw new Exception(Err.Message);
            }          
　　}

4.关闭连接

　　private void CloseConn()
　　{
　　 objSqlConnection.Close();
　　}

5.返回dataReader

 　　public SqlDataReader GetSqlRD(string strSqlCommand)
　　{
　　 SqlDataReader objRD = null;

　　 ock (objLock)
　　　　{
　　　　 try
　　　　　　{
　　　　　　 OpenConn();
                  objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
                  objRD = objSqlcommand.ExecuteReader(CommandBehavior.CloseConnection);
                 }
　　　　 catch (Exception Err)
　　　　　　{
                   //寫入出錯Log檔
                   throw new Exception(Err.Message);
                   CloseConn();
                 }
　　　　 finally
　　　　　　 {
                    CloseConn();
                  }
　　　　}
 　　 return objRD;
　　}

6.返回dataset

　　public DataSet getDataSet(string strSqlCommand)
       {
　　   DataSet objDS = null;
         lock (objLock)
　　　　{
              try
　　　　　　{
                   OpenConn();
                   objDS = new DataSet();
                   objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
                   SqlDataAdapter objDA = new SqlDataAdapter(objSqlcommand);
                   objDA.Fill(objDS);
                 }
              catch (Exception Err)
                  {
                    //寫入Log檔
                    throw new Exception(Err.Message);
                  }
              finally
                  {
                     CloseConn();
                  }
            }
　　 return objDS;

　　}

7.返回dataTable

　　public DataTable getDataTable(string strSqlCommand, string tableName)
        {
            DataTable objDT = null;
            lock (objLock)
            {
                try
                {
                    OpenConn();
                    objDT = new DataTable(tableName);
                    objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
                    SqlDataAdapter objDA = new SqlDataAdapter(objSqlcommand);
                    objDA.Fill(objDT);
                }
                catch (SqlException Err)
                {
                }
                finally
                {
                    CloseConn();
                }
            }
            return objDT;

        }

8.返回受影响的行数

 　　public int ExecuteSqlCommand(string strSqlCommand)
        {
            int count = 0;
            lock (objLock)
            {
                SqlTransaction sqlTran = null;
                try
                {
                    OpenConn();
                    sqlTran = objSqlConnection.BeginTransaction();
                    objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection, sqlTran);
                    count += objSqlcommand.ExecuteNonQuery();
                    sqlTran.Commit();
                }
                catch (Exception Err)
                {
                    //寫入Log檔
                    sqlTran.Rollback();
                    throw new Exception(Err.Message);
                }
                finally
                {
                    CloseConn();
                }
            }
            return count;
        }

 9.检查dataset中是否有资料

 　　public bool chkDataSet(DataSet objDS)
        {
            if (objDS.Tables[0].Rows.Count == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

 10.返回DataAdapter

　　 public SqlDataAdapter getDataAdapter(string strSqlCommand)
        {
            lock (objLock)
            {
                try
                {
                    OpenConn();
                    objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection);
                    objSDA.SelectCommand = objSqlcommand;
                }
                catch (Exception Err)
                {
                    //寫入出錯Log檔
                    throw new Exception(Err.Message);
                }
                finally
                {
                    CloseConn();
                }
            }
            return objSDA;
        }

 11.返回第一行第一列

 　　public string getExecuteSqlCommandFrist(string strSqlCommand, params SqlParameter[] paras)
        {
            string strReturn = string.Empty;
            lock (objLock)
            {
                SqlTransaction sqlTran = null;
                try
                {
                    OpenConn();
                    sqlTran = objSqlConnection.BeginTransaction();
                    objSqlcommand = new SqlCommand(strSqlCommand, objSqlConnection, sqlTran);
                    if (paras != null && paras.Length > 0)
                    {
                        for (int i = 0; i < paras.Length; i++)
                        {
                            objSqlcommand.Parameters.Add(paras[i].ParameterName, paras[i].Value);
                        }
                    }
                    strReturn = (string)objSqlcommand.ExecuteScalar();
                    sqlTran.Commit();
                }
                catch (Exception Err)
                {
                    //寫入Log檔
                    sqlTran.Rollback();
                    throw new Exception(Err.Message);
                }
                finally
                {
                    CloseConn();
                }
            }
            return strReturn;
        }