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 string connectionstring = "";

 public Dbconn()
 {
     connectionstring = ConfigurationManager.AppSettings["connectionstring"].ToString(); //从web.config中读取数据库连接字符串
     scn = new SqlConnection();
     scn.ConnectionString = connectionstring;
 }
 
 /// <summary>
 /// 数据库打开
 /// </summary>
 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();
  }
 }

 /// <summary>
 /// 数据库释放
 /// </summary>
 public void DbClose()
 {
     if ( scn != null)
     {
  scn.Close();
  scn.Dispose();
  scn = null;
     }
 }

 /// <summary>
 /// 返回DataSet
 /// </summary>
 /// <param name="strselect">SQL语句</param>
 /// <returns>数据集</returns>
 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;
 }

 /// <summary>
 /// 存储过程返回记录集
 /// </summary>
 /// <param name="procName">存储过程名称</param>
 /// <param name="sqlparameter">参数</param>
 /// <returns>符合条件的记录集</returns>
 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();
 }

 /// <summary>
 /// 删除一条信息
 /// </summary>
 /// <param name="strdelete">sql语句</param>
 /// <returns>删除条数</returns>
 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;   
 }

 /// <summary>
 /// 取得第一行第一个单元格的值
 /// </summary>
 /// <param name="strselect">SQL语句</param>
 /// <returns>返回记录</returns>
 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;
 }

 /// <summary>
 /// 调用存储过程
 /// </summary>
 /// <param name="procedureName">存储过程名</param>
 /// <param name="sqlparameter">存储过程参数</param>
 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;
 }

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

 /// <summary>
 /// 返回一个command
 /// </summary>
 /// <param name="procName">存储过程名称</param>
 /// <param name="sqlparameter">存储过程参数</param>
 /// <returns>command对象</returns>
 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();
 }

 /// <summary>
 /// 执行删除，更新语句
 /// </summary>
 /// <param name="procName">存储过程名称</param>
 /// <param name="sqlparameter">存储过程参数</param>
 public void execuCommand( string procName, SqlParameter[] sqlparameter)
 {
     DbOpen();
     SqlCommand cm = getCommand(procName, sqlparameter);
     cm.ExecuteNonQuery();
     DbClose();    
 }
    }
}