C#.NET操作数据库通用类（MS SQL Server篇）

下面给出了一个C#操作MS SQL Server数据库的通用类，通过该类可以对数据库进行任何操作，包括执行SQL语句、执行存储过程。以下是其详细实现过程，希望大家共同修改优化之。稍后将介绍如何使用它实现N层的程序设计。

配置web.config文件的链接参数

C#代码

using System;
using System.Data;
using System.Data.SqlClient;

namespace Com.LXJ.Database
{
/// <summary>
/// ConnDB 的摘要说明。
///</summary>
public class ConnDB
{
protected SqlConnectionConnection;
private stringconnectionString;

  ///<summary>
  /// 默认构造函数
  ///</summary>
  public ConnDB()
  {
   stringconnStr;
   connStr =System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();

   connectionString= connStr;
   Connection =new SqlConnection(connectionString);
  }

  ///<summary>
  /// 带参数的构造函数
  ///</summary>
  /// <paramname="newConnectionString">数据库联接字符串</param>
  public ConnDB(stringnewConnectionString)
  {
   connectionString= newConnectionString;
   Connection =new SqlConnection(connectionString);
  }

  ///<summary>
  /// 完成SqlCommand对象的实例化
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  ///<returns></returns>
  private SqlCommandBuildCommand(string storedProcName,IDataParameter[]parameters)
  {
   SqlCommandcommand = BuildQueryCommand(storedProcName,parameters);
   command.Parameters.Add(newSqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
   returncommand;
  }

  ///<summary>
  /// 创建新的SQL命令对象(存储过程)
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  ///<returns></returns>
  private SqlCommandBuildQueryCommand(string storedProcName,IDataParameter[]parameters)
  {
   SqlCommandcommand = new SqlCommand(storedProcName,Connection);
   command.CommandType= CommandType.StoredProcedure;
   foreach(SqlParameter parameter in parameters)
   {
    command.Parameters.Add(parameter);
   }
   returncommand;
  }

  ///<summary>
  /// 执行存储过程,无返回值
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  public voidExecuteProcedure(string storedProcName,IDataParameter[]parameters)
  {
   Connection.Open();
   SqlCommandcommand;
   command=BuildQueryCommand(storedProcName,parameters);
   command.ExecuteNonQuery();
   Connection.Close();
  }

  ///<summary>
  /// 执行存储过程，返回执行操作影响的行数目
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  /// <paramname="rowsAffected"></param>
  ///<returns></returns>
  public int RunProcedure(stringstoredProcName,IDataParameter[] parameters,out introwsAffected)
  {
   intresult;
   Connection.Open();
   SqlCommandcommand = BuildCommand(storedProcName,parameters);
   rowsAffected= command.ExecuteNonQuery();
   result =(int)command.Parameters["ReturnValue"].Value;
   Connection.Close();

   returnresult;
  }

  ///<summary>
  ///重载RunProcedure把执行存储过程的结果放在SqlDataReader中
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  ///<returns></returns>
  public SqlDataReaderRunProcedure(string storedProcName,IDataParameter[]parameters)
  {
   SqlDataReaderreturnReader;
   Connection.Open();
   SqlCommandcommand = BuildQueryCommand(storedProcName,parameters);
   command.CommandType= CommandType.StoredProcedure;
   returnReader= command.ExecuteReader(CommandBehavior.CloseConnection);
   returnreturnReader;
  }

  ///<summary>
  ///重载RunProcedure把执行存储过程的结果存储在DataSet中和表tableName为可选参数
  ///</summary>
  /// <paramname="storedProcName"></param>
  /// <paramname="parameters"></param>
  /// <paramname="tableName"></param>
  ///<returns></returns>
  public DataSetRunProcedure(string storedProcName,IDataParameter[]parameters,params string[] tableName)
  {
   DataSetdataSet = new DataSet();
   Connection.Open();
   SqlDataAdaptersqlDA = new SqlDataAdapter();
   sqlDA.SelectCommand= BuildQueryCommand(storedProcName,parameters);
   stringflag;
   flag ="";
   for(inti=0;i<tableName.Length;i++)
    flag= tableName[i];
   if(flag!="")
    sqlDA.Fill(dataSet,tableName[0]);
   else
    sqlDA.Fill(dataSet);
   Connection.Close();
   returndataSet;
  }

  ///<summary>
  /// 执行SQL语句，返回数据到DataSet中
  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public DataSetReturnDataSet(string sql)
  {
   DataSetdataSet=new DataSet();
   Connection.Open();
   SqlDataAdaptersqlDA=new SqlDataAdapter(sql,Connection);
   sqlDA.Fill(dataSet,"objDataSet");
   Connection.Close();
   returndataSet;
  }

  ///<summary>
  /// 执行SQL语句，返回 DataReader
  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public SqlDataReaderReturnDataReader(String sql)
  {
   Connection.Open();
   SqlCommandcommand = new SqlCommand(sql,Connection);
   SqlDataReaderdataReader = command.ExecuteReader();

returndataReader;
}

  ///<summary>
  /// 执行SQL语句，返回记录数
  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public intReturnRecordCount(string sql)
  {
   intrecordCount = 0;

   Connection.Open();
   SqlCommandcommand = new SqlCommand(sql,Connection);
   SqlDataReaderdataReader = command.ExecuteReader();

   while(dataReader.Read())
   {
    recordCount++;
   }
   dataReader.Close();
   Connection.Close();

returnrecordCount;
}

  ///<summary>
  /// 执行SQL语句
  ///</summary>
  /// <paramname="sql"></param>
  ///<returns></returns>
  public bool EditDatabase(stringsql)
  {
   boolsuccessState = false;

   Connection.Open();
   SqlTransactionmyTrans = Connection.BeginTransaction();
   SqlCommandcommand = new SqlCommand(sql,Connection,myTrans);
   try
   {
    command.ExecuteNonQuery();
    myTrans.Commit();
    successState= true;
   }
   catch
   {
    myTrans.Rollback();
   }
   finally
   {
    Connection.Close();
   }

returnsuccessState;
}

  ///<summary>
  /// 关闭数据库联接
  ///</summary>
  public void Close()
  {
   Connection.Close();
  }

}//end class
}//end namespace

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。