C# 数据库连接类

using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace XXD.HZD.DBCONN { public class Dbconn { private SqlConnection scn = null; private strin

using system;
using system.collections.generic;
using system.text;
using system.data;
using system.data.sqlclient;
using system.configuration;

 

namespace XXD.HZD.DBCONN
{
    public class Dbconn
    {

 private SqlConnection scn = null;
 
 private string connectionstring = "";

 public Dbconn()
 {
     connectionstring = ConfigurationManager.AppSettings["connectionstring"].ToString(); //从web.config中读取数据库连接字符串
     scn = new SqlConnection();
     scn.ConnectionString = connectionstring;
 }
 
 ///

 /// 数据库打开
 ///

 private void DbOpen()
 {
     if ( scn == null)
     {
  scn = new SqlConnection();
  scn.ConnectionString = connectionstring;
     }
     if ( scn.State == ConnectionState.Closed)
  {
      scn.Open();
  }
  else if (scn.State == ConnectionState.Broken)
  {
      scn.Close();
      scn.Open();
  }
 }

 ///

 /// 数据库释放
 ///

 public void DbClose()
 {
     if ( scn != null)
     {
  scn.Close();
  scn.Dispose();
  scn = null;
     }
 }

 ///

 /// 返回DataSet
 ///

 /// SQL语句
 /// 数据集
 public DataSet getDataSet(string strselect)
 {
     DataSet ds = new DataSet();
     SqlDataAdapter sda = new SqlDataAdapter();
     DbOpen();
     using ( sda = new SqlDataAdapter())
  {
      sda.SelectCommand = new SqlCommand();
      sda.SelectCommand.Connection = scn;
      sda.SelectCommand.CommandType = CommandType.Text;
      sda.SelectCommand.CommandText = strselect;
      sda.SelectCommand.ExecuteNonQuery();
      sda.Fill(ds);
  }
     DbClose();
     return ds;
 }

 ///

 /// 存储过程返回记录集
 ///

 /// 存储过程名称
 /// 参数
 /// 符合条件的记录集
 public DataSet getDataSet(string procName, SqlParameter[] sqlparameter)
 {
     DataSet ds = new DataSet();
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     SqlDataAdapter sda = new SqlDataAdapter(cm);
     sda.Fill(ds);
     DbClose();
     return ds;
 }

 public void getDataSet(string procName, SqlParameter[] sqlparameter, out DataSet ds)
 {
     ds = new DataSet();
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     SqlDataAdapter sda = new SqlDataAdapter(cm);
     sda.Fill(ds);
     DbClose();
 }

 ///

 /// 删除一条信息
 ///

 /// sql语句
 /// 删除条数
 public int delItem(string strdelete)
 {
     int i = 0 ;
     DbOpen();
     using ( SqlCommand cm = new SqlCommand())
     {
     cm.Connection = scn;
     cm.CommandType = CommandType.Text;
     cm.CommandText = strdelete;
     try
     {
  i = cm.ExecuteNonQuery();
     }
     catch (SqlException ex)
     {  
  throw;
     }
     } 
     return i;   
 }

 ///

 /// 取得第一行第一个单元格的值
 ///

 /// SQL语句
 /// 返回记录
 public string getScalar(string strselect)
 {
     DbOpen();
     SqlCommand cm = new SqlCommand();
     cm.Connection = scn;
     cm.CommandType = CommandType.Text;
     cm.CommandText = strselect;
     string gradename = cm.ExecuteScalar().ToString();
     DbClose();
     return gradename;
 }

 ///

 /// 调用存储过程
 ///

 /// 存储过程名
 /// 存储过程参数
 public int executrProc(string procedureName, SqlParameter[] sqlparameter)
 {
     SqlCommand  cm = null;
     int returnvalue = 0;
     DbOpen();
     using (cm = new SqlCommand())
     {
  cm.Connection = scn;
  cm.CommandType = CommandType.StoredProcedure;
  cm.CommandText = procedureName;

  foreach (SqlParameter sqlpara in sqlparameter)
  {
      cm.Parameters.Add(sqlpara);
  }

  cm.ExecuteNonQuery();
  returnvalue = Convert.ToInt32(cm.Parameters[8].Value);
     }
     DbClose();
     return returnvalue;
 }

 ///

 /// 存储过程有返回值
 ///

 /// 存储过程名称
 /// 存储过程参数
 /// 存储过程返回值
 public string returnProc(string procName, SqlParameter[] sqlparameter)
 {
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     cm.ExecuteNonQuery();
     DbClose();
     return cm.Parameters[sqlparameter.Length-1].Value.ToString();
 }

 ///

 /// 返回一个command
 ///

 /// 存储过程名称
 /// 存储过程参数
 /// command对象
 private SqlCommand getCommand ( string procName, SqlParameter[] sqlparameter)
 {
         SqlCommand cm = new SqlCommand();
     cm.Connection = scn;
     cm.CommandType = CommandType.StoredProcedure;
     cm.CommandText = procName;
     foreach(SqlParameter sp in sqlparameter)
     {
  cm.Parameters.Add(sp);
     }
     return cm;
 }

 public SqlDataReader getReader(string procName, SqlParameter[] sqlparameter)
 {
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     return cm.ExecuteReader();
 }

 ///

 /// 执行删除，更新语句
 ///

 /// 存储过程名称
 /// 存储过程参数
 public void execuCommand( string procName, SqlParameter[] sqlparameter)
 {
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     cm.ExecuteNonQuery();
     DbClose();    
 }
    }
}