数据库连接封装类

using  System;
using  System.Data;
using  System.Data.SqlClient;
using  System.Configuration;
using  System.IO;

namespace  DBclass
... {
    /**//// <summary>
    /// DBClass 的摘要说明。
    /// </summary>
    public class DBclass
    ...{
        //protected static string strConn = ConfigurationSettings.AppSettings["strConnection"];
        protected static string strConn=ConfigurationSettings.AppSettings["Asp600.ConnectionString"];
        protected static SqlConnection myConn=new SqlConnection(strConn);
        protected static string mySql;
        private TextWriter Debug_Output;

        "***** Debug Setting *****"#region "***** Debug Setting *****"        
        //判断是否处于调试状态
        private bool JudgeDebug()
        ...{
            bool IsDebug=false;
            string DebugStr=ConfigurationSettings.AppSettings["Asp600.Debug"];
            IsDebug=((DebugStr!=null && DebugStr.Equals("true"))?true:false);
            /**//*
            if (DebugStr == null && DebugStr.Equals("true"))
            {
                IsDebug = false;
            }
            else
            {
                IsDebug = true;
            }
            */

            return IsDebug;

        }
        //信息输出 
        private void debug(object Debug_Infor)
        ...{
            if(Debug_Output!=null)
            ...{
                Debug_Output.Write(Debug_Infor);
                Debug_Output.Write("<br>");
            }
        }        
        //启动Debug 
        public void OpenDebug(TextWriter Do_Output)        
        ...{
            if(JudgeDebug())
                this.Debug_Output=Do_Output;
        }
        #endregion

        "***** Functions of DataClass *****"#region "***** Functions of DataClass *****"

        /**//// <summary>
        /// executing SQL commands
        /// </summary>
        ///执行insert delete update语句
        /// <param name="mySql">string</param>
        /// <returns>return int</returns>
        public int ExecuteSql(string mySql)
        ...{                    
            debug("现在执行的是DBClass中的ExecuteSql(string)方法，返回类型为：int ");
            SqlCommand myCmd = new SqlCommand(mySql,myConn);
            try
            ...{
                debug("执行语句为："+mySql);
                myConn.Open();
                return myCmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                //throw ex;
                return -99;
                
            }
            finally
            ...{
                myCmd.Dispose();
                myConn.Close();
            }
        }
        //执行SqlCommand
        public int ExecuteSql(SqlCommand scmd)
        ...{                    
            debug("现在执行的是DBClass中的ExecuteSql(SqlCommand)方法，返回类型为：int ");
            scmd.Connection=myConn;
            try
            ...{
                debug("执行语句为："+scmd.CommandText.ToString());
                myConn.Open();
                return scmd.ExecuteNonQuery();
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                //throw ex;
                return -99;
                
            }
            finally
            ...{
                scmd.Dispose();
                myConn.Close();
            }
        }
        /**//// <summary>
        ///executing SQL commands查询返回植是一个植
        /// </summary>
        /// <param name="mySql">要执行的SQL语句,为字符串类型string</param>
        /// <returns>返回执行情况,整形int</returns>
        public int ExecScalar(string mySql)
        ...{            
            debug("现在执行的是DBClass中的ExecScalar(mySql)方法，返回类型为：int ");
            SqlCommand myCmd = new SqlCommand(mySql,myConn);            
            try
            ...{
                debug("执行语句为："+mySql);
                myConn.Open();                
                SqlDataReader myDr = myCmd.ExecuteReader();
                
                if(myDr.Read())
                ...{
                    return 1;
                }    
                else
                ...{
                    return 0;
                }
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                return -99;
                //throw ex;
            }
            finally
            ...{
                myCmd.Dispose();
                myConn.Close();
            }
        }

        //执行SqlCommand
        public int ExecScalar(SqlCommand scmd)
        ...{                    
            debug("现在执行的是DBClass中的ExecScalar(SqlCommand)方法，返回类型为：int ");
            scmd.Connection=myConn;        
            try
            ...{
                debug("执行语句为："+scmd.CommandText.ToString());
                myConn.Open();                
                SqlDataReader myDr = scmd.ExecuteReader();
                
                if(myDr.Read())
                ...{
                    return 1;
                }    
                else
                ...{
                    return 0;
                }
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                //throw ex;
                return -99;
            }
            finally
            ...{
                scmd.Dispose();
                myConn.Close();
            }
        }

        public string str(string str)
        ...{
            string result="'"+str.Replace("'",""")+"'";
            return result;

        }
        public string sear(string str)
        ...{
            string result=str.Replace("'",""");
            return result;
        }

        /**//// <summary>
        /// get dataset
        /// </summary>执行select语句返回dataset
        /// <param name="mySql">(string)</param>
        /// <returns>(DataSet)</returns>
        public DataSet ExecuteSqlDs(string mySql ,string myTable)
        ...{    
            debug("现在执行的是DBClass中的ExecuteSqlDs(string,string)方法，返回类型为：DataSet ");
            SqlDataAdapter myDa =new SqlDataAdapter(mySql, myConn);
            DataSet myDs=new DataSet();
            try
            ...{
                debug("执行语句为："+mySql);
                myConn.Open();
                myDa.Fill(myDs, myTable);
                return myDs;
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                return new DataSet();
                //throw ex;
            }
            finally
            ...{
                myDa.Dispose();
                myConn.Close();
            }        
        }

        public DataSet ExecuteSqlDs(SqlCommand scmd,string myTable)
        ...{
            debug("现在执行的是DBClass中的ExecuteSqlDs(SqlCommand,string)方法，参数类型为SqlCommand，返回类型为：DataSet ");
            scmd.Connection=myConn;
            SqlDataAdapter myDa =new SqlDataAdapter();
            myDa.SelectCommand=scmd;
            DataSet ds=new DataSet();
            try
            ...{
                debug("执行语句为："+scmd.CommandText.ToString());
                myConn.Open();
                myDa.Fill(ds,myTable);
                return ds;
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                return new DataSet();
            }
            finally
            ...{
                //myCmd.Dispose();
                myConn.Close();
            }
            
        }

        //定义分页
        public  DataSet ExecuteSqlDsReapter(string mySql ,int repeatestr1,int repeatestr2,string myTable)
        ...{    
            debug("现在执行的是DBClass中的ExecuteSqlDsReapter(string,int,int,string)方法，返回类型为：DataSet ");
            SqlCommand myCmd = new SqlCommand(mySql,myConn);
            SqlDataAdapter myDa = new SqlDataAdapter(myCmd);
            DataSet dsReapter = new DataSet();
            try
            ...{
                //myConn.Open();
                debug("执行语句为："+mySql);
                myDa.Fill(dsReapter,repeatestr1,repeatestr2,myTable);
                return dsReapter;
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                return new DataSet();
                //throw ex;
            }
            finally
            ...{
                myDa.Dispose();
                //myConn.Close();
            }
        }
        //定义嵌套循环
        public  DataSet ExecuteSqlChannelReapter(string mySql1 ,string mySql2 ,string myTable1,string myTable2)
        ...{                    
            SqlCommand myCmd1 = new SqlCommand(mySql1,myConn);
            SqlCommand myCmd2 = new SqlCommand(mySql2,myConn);
            SqlDataAdapter myDa1 = new SqlDataAdapter(myCmd1);                            
            SqlDataAdapter myDa2 = new SqlDataAdapter(myCmd2);
            DataSet dsReapter = new DataSet();
            try
            ...{
                //myConn.Open();
                myDa1.Fill(dsReapter,myTable1);
                myDa2.Fill(dsReapter,myTable2);                
                return dsReapter;
            }
            catch(Exception ex)
            ...{
                throw ex;
            }
            finally
            ...{
                myDa1.Dispose();
                myDa2.Dispose();
                //myConn.Close();
            }
        }
        /**//// <summary>
        /// datareader
        /// </summary>
        /// <param name="mySql">string</param>
        /// <returns>SqlDataReader</returns>
        public SqlDataReader ExecuteSqlDataReader(string mySql)
        ...{    
            debug("现在执行的是DBClass中的ExecuteSqlDataReader(string)方法，返回类型为：SqlDataReader ");
            SqlCommand myCmd = new SqlCommand(mySql,myConn);
            SqlDataReader myReader;
            try
            ...{
                debug("执行语句为："+mySql);
                myConn.Open();
                myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
                return myReader;
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());                ;
                return null;
                //throw ex;
            }
            finally
            ...{
                myCmd.Dispose();
                //myConn.Close();
            }
        }

        /**//// <summary>
        /// get single value
        /// </summary>
        /// <param name="mySql">(string)</param>
        /// <returns>(int)</returns>
        public  int ExecuteCount(string mySql)
        ...{    
            debug("现在执行的是DBClass中的ExecuteCount(string)方法，返回类型为：int ");
            SqlCommand myCmd = new SqlCommand(mySql,myConn);
            myCmd.CommandText = mySql;
            try
            ...{
                debug("执行语句为："+mySql);
                myConn.Open();
                return (int)myCmd.ExecuteScalar();
            }
            catch(Exception ex)
            ...{
                debug("异常信息为："+ex.ToString());
                return -99;
                //throw ex;
            }
            finally
            ...{
                myCmd.Dispose();
                myConn.Close();
            }
        }        


        /**//// <summary>
        /// get object
        /// </summary>
        /// <param name="mySql">(string)</param>
        /// <returns>(object)</returns>
        public object ExecuteSql4ValueEx(string mySql)
        ...{            
            SqlCommand myCmd = new SqlCommand(mySql,myConn);
            try
            ...{
                myConn.Open();
                object r = myCmd.ExecuteScalar();
                if(Object.Equals(r,null))
                ...{
                    throw new Exception("object unavailable!");
                }
                else
                ...{
                    return r;
                }                
            }
            catch(Exception ex)
            ...{
                throw ex;
            }
            finally
            ...{
                myCmd.Dispose();
                myConn.Close();
            }
        }
        #endregion
    
        "***** Functions of Procedure *****"#region "***** Functions of Procedure *****"

        public SqlCommand BuildIntCommand(string ProcName, IDataParameter[] parameters)
        ...{
            SqlCommand myCmd = BuildQueryCommand(ProcName, parameters );            
            myCmd.Parameters.Add( new SqlParameter ( "ReturnValue",
                SqlDbType.Int,
                4, /**//* Size */
                ParameterDirection.ReturnValue,
                false, /**//* is nullable */
                0, /**//* byte precision */
                0, /**//* byte scale */
                string.Empty,
                DataRowVersion.Default,
                null ));
            return myCmd;
        }
        
        public SqlCommand BuildQueryCommand(string ProcName, IDataParameter[] parameters)
        ...{
            SqlCommand myCmd = new SqlCommand(ProcName,myConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            ...{
                myCmd.Parameters.Add( parameter );
            }
            return myCmd;
        }

        public int RunProcedure(string ProcName, IDataParameter[] parameters, out int rowsAffected )
        ...{
            int result;
            myConn.Open();
            SqlCommand myCmd = BuildIntCommand(ProcName, parameters );
            rowsAffected = myCmd.ExecuteNonQuery();
            result = (int)myCmd.Parameters["ReturnValue"].Value;
            myConn.Close();
            return result;
        }

        public SqlDataReader RunProcedure(string ProcName, IDataParameter[] parameters )
        ...{
            SqlDataReader myReader;
            myConn.Open();
            SqlCommand myCmd = BuildQueryCommand(ProcName, parameters );
            myCmd.CommandType = CommandType.StoredProcedure;
            myReader = myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            return myReader;
        }

        public DataSet RunProcedure(string ProcName, IDataParameter[] parameters, string tableName )
        ...{
            DataSet myDs = new DataSet();            
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuildQueryCommand(ProcName, parameters );
            myDa.Fill(myDs, tableName );
            myConn.Close();
            return myDs;
        }

        public void RunProcedure(string ProcName, IDataParameter[] parameters, DataSet myDs, string tableName )
        ...{
            myConn.Open();
            SqlDataAdapter myDa = new SqlDataAdapter();
            myDa.SelectCommand = BuildIntCommand(ProcName, parameters );
            myDa.Fill(myDs, tableName );
            myConn.Close();            
        }

        #endregion
    }
}

 

 

 

 

比较下列代码为相同的东西：

********************（1）*****************

 /// <summary>
  /// 执行Transact-SQL语句,对数据库记录做插入,修改,删除等操作
  /// </summary>
  /// <param name="sSQL">Transact-SQL语句</param>
  public void ExeSql(string sSQL)
  {  
   ConnOpen();
   SqlCommand1 = new SqlCommand(sSQL, SqlConnection1);
   SqlCommand1.ExecuteNonQuery();
   SqlCommand1.Dispose();
  }

*********************************************

 

--------------------------(2)--------------------------

/// <summary>
  /// executing SQL commands
  /// </summary>
        ///执行insert delete update语句
  /// <param name="mySql">string</param>
  /// <returns>return int</returns>
  public int ExecuteSql(string mySql)
  {     
   debug("现在执行的是DBClass中的ExecuteSql(string)方法，返回类型为：int ");
   SqlCommand myCmd = new SqlCommand(mySql,myConn);
   try
   {
    debug("执行语句为："+mySql);
    myConn.Open();
    return myCmd.ExecuteNonQuery();
   }
   catch(Exception ex)
   {
    debug("异常信息为："+ex.ToString());
    //throw ex;
    return -99;
    
   }
   finally
   {
    myCmd.Dispose();
    myConn.Close();
   }
  }

------------------------------------------------

***************************(3)*********************

 /// <param name="mySql">要执行的SQL语句,为字符串类型string</param>
  /// <returns>返回执行情况,整形int</returns>
  public int ExecScalar(string mySql)
  {   
   debug("现在执行的是DBClass中的ExecScalar(mySql)方法，返回类型为：int ");
   SqlCommand myCmd = new SqlCommand(mySql,myConn);   
   try
   {
    debug("执行语句为："+mySql);
    myConn.Open();    
    SqlDataReader myDr = myCmd.ExecuteReader();
    
    if(myDr.Read())
    {
     return 1;
    } 
    else
    {
     return 0;
    }
   }
   catch(Exception ex)
   {
    debug("异常信息为："+ex.ToString());
    return -99;
    //throw ex;
   }
   finally
   {
    myCmd.Dispose();
    myConn.Close();
   }
  }

************************************************

-----------------------------------(4)------------------------------------

 public int ExecScalar(string mySql)
        {
            myConn.Open();
            SqlCommand myCom = new SqlCommand(mySql,myConn);
            myCom.CommandType = CommandType.Text;
            SqlDataReader myDr = myCom.ExecuteReader();
            if (myDr.Read())
            {
                return 1;

            }
            else
            {
                return 0;
            }
            myDr.Close();
            myCom.Dispose();
            myConn.Close();

 

        }

-----------------------------------------------