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();
}
}
}