【技巧】C# SQL数据库操作类

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace MyCorporation.DepartMent.DataBase
{
///
/// 通用数据库类
///
public class DataBase
{

private string ConnStr = null;

public DataBase()
{
  ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
}
public DataBase(string Str)
{
  try
  {
  this.ConnStr = Str;

  }
  catch(Exception ex)
  {
  throw ex;
  }
}

///
/// 返回connection对象
///
///
public SqlConnection ReturnConn()
{
  SqlConnection Conn = new SqlConnection(ConnStr);
  Conn.Open();
  return Conn;
}
public void Dispose(SqlConnection Conn)
{
  if(Conn!=null)
  {
  Conn.Close();
  Conn.Dispose();
  }
  GC.Collect();
}
///
/// 运行SQL语句
///
///
public void RunProc(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlCommand Cmd ;
  Cmd = CreateCmd(SQL, Conn);
  try
  {
  Cmd.ExecuteNonQuery();
  }
  catch
  {
  throw new Exception(SQL);
  }
  Dispose(Conn);
  return;
}  

///
  /// 运行SQL语句返回DataReader
///
  ///
  /// SqlDataReader对象.
public SqlDataReader RunProcGetReader(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlCommand Cmd ;
  Cmd = CreateCmd(SQL, Conn);
  SqlDataReader Dr;
  try
  {
  Dr = Cmd.ExecuteReader(CommandBehavior.Default);
  }
catch
  {
  throw new Exception(SQL);
  }
  //Dispose(Conn);
  return Dr;
}

///
/// 生成Command对象
///
///
///
///
public SqlCommand CreateCmd(string SQL, SqlConnection Conn)
{
  SqlCommand Cmd ;
  Cmd = new SqlCommand(SQL, Conn);
  return Cmd;
}

///
/// 生成Command对象
///
///
///
public SqlCommand CreateCmd(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlCommand Cmd ;
  Cmd = new SqlCommand(SQL, Conn);
  return Cmd;
}
///
/// 返回adapter对象
///
///
///
///
public SqlDataAdapter CreateDa(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlDataAdapter Da;
  Da = new SqlDataAdapter(SQL, Conn);
  return Da;
}

///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
public DataSet RunProc(string SQL ,DataSet Ds)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlDataAdapter Da;
  //Da = CreateDa(SQL, Conn);
  Da = new SqlDataAdapter(SQL,Conn);
  try
  {
  Da.Fill(Ds);
  }
  catch(Exception Err)
  {
  throw Err;
  }
  Dispose(Conn);
  return Ds;
}
///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
/// 表名
public DataSet RunProc(string SQL ,DataSet Ds,string tablename)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlDataAdapter Da;
  Da = CreateDa(SQL);
  try
  {
  Da.Fill(Ds,tablename);
  }
  catch(Exception Ex)
  {
  throw Ex;
  }
  Dispose(Conn);
  return Ds;
}

///
/// 运行SQL语句,返回DataSet对象
///
/// SQL语句
/// DataSet对象
/// 表名
public DataSet RunProc(string SQL , DataSet Ds ,int StartIndex ,int PageSize, string tablename )
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlDataAdapter Da ;
  Da = CreateDa(SQL);
  try
  {
  Da.Fill(Ds, StartIndex, PageSize, tablename);
  }
  catch(Exception Ex)
  {
  throw Ex;
  }
Dispose(Conn);
  return Ds;
}

///
/// 检验是否存在数据
///
///
public bool ExistDate(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlDataReader Dr ;
  Dr = CreateCmd(SQL,Conn).ExecuteReader();
  if (Dr.Read())
  {
  Dispose(Conn);
  return true;
  }
  else
  {
  Dispose(Conn);
  return false;
  }
}

///
/// 返回SQL语句执行结果的第一行第一列
///
/// 字符串
public string ReturnValue(string SQL)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  string result;
  SqlDataReader Dr ;
  try
{
  Dr = CreateCmd(SQL,Conn).ExecuteReader();
  if (Dr.Read())
  {
  result = Dr[0].ToString();
  Dr.Close();
  }
  else
  {
  result = "";
  Dr.Close();
  }
  }
  catch
  {
  throw new Exception(SQL);
  }
  Dispose(Conn);
  return result;
}

///
/// 返回SQL语句第一列,第ColumnI列,
///
/// 字符串
public string ReturnValue(string SQL, int ColumnI)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  string result;
  SqlDataReader Dr ;
  try
  {
  Dr = CreateCmd(SQL,Conn).ExecuteReader();
  }
  catch
  {
  throw new Exception(SQL);
  }
  if (Dr.Read())
  {
  result = Dr[ColumnI].ToString();
  }
else
  {
  result = "";
  }
  Dr.Close();
  Dispose(Conn);
  return result;
}

///
/// 生成一个存储过程使用的sqlcommand.
///
/// 存储过程名.
/// 存储过程入参数组.
/// sqlcommand对象.
public SqlCommand CreateCmd(string procName, SqlParameter[] prams)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlCommand Cmd = new SqlCommand(procName, Conn);
  Cmd.CommandType = CommandType.StoredProcedure;
  if (prams != null)
  {
  foreach (SqlParameter parameter in prams)
  {
  if(parameter != null)
  {
    Cmd.Parameters.Add(parameter);
  }
  }
  }
  return Cmd;
}
///
/// 为存储过程生成一个SqlCommand对象
///
/// 存储过程名
/// 存储过程参数
/// SqlCommand对象
private SqlCommand CreateCmd(string procName, SqlParameter[] prams,SqlDataReader Dr)
{
  SqlConnection Conn;
  Conn = new SqlConnection(ConnStr);
  Conn.Open();
  SqlCommand Cmd = new SqlCommand(procName, Conn);
  Cmd.CommandType = CommandType.StoredProcedure;
  if (prams != null)
  {
  foreach (SqlParameter parameter in prams)
  Cmd.Parameters.Add(parameter);
  }
  Cmd.Parameters.Add(
  new SqlParameter("ReturnValue", SqlDbType.Int, 4,
  ParameterDirection.ReturnValue, false, 0, 0,
  string.Empty, DataRowVersion.Default, null));

  return Cmd;
}

///
/// 运行存储过程,返回.
///
/// 存储过程名
/// 存储过程参数
/// SqlDataReader对象
public void RunProc(string procName, SqlParameter[] prams, SqlDataReader Dr)
{

  SqlCommand Cmd = CreateCmd(procName, prams, Dr);
  Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  return;
}

///
/// 运行存储过程,返回.
///
/// 存储过程名
/// 存储过程参数
public string RunProc(string procName, SqlParameter[] prams)
{
  SqlDataReader Dr;
  SqlCommand Cmd = CreateCmd(procName, prams);
  Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  if(Dr.Read())
  {
  return Dr.GetValue(0).ToString();
  }
  else
  {
  return "";
  }
}

///
/// 运行存储过程,返回dataset.
///
/// 存储过程名.
/// 存储过程入参数组.
/// dataset对象.
public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)
{
  SqlCommand Cmd = CreateCmd(procName,prams);
  SqlDataAdapter Da = new SqlDataAdapter(Cmd);
  try
  {
  Da.Fill(Ds);
  }
  catch(Exception Ex)
  {
  throw Ex;
  }
  return Ds;
}

}
}