基于SQLServer的数据层基类C#源码

2024-07-21 02:27:33

字体：大中小

来源：转载

供稿：网友

/*****************************************************
* 文件名：dbobject.cs
* 功能描述：定义数据层基类。
* 创建人：夏春涛 [email protected] qq:23106676
* 创建时间：2004-08-11 11：05
*****************************************************/

using system;
using system.data;
using system.data.sqlclient;
using system.configuration;

namespace xd.xlb.jbc.jbinfo.webmodules.data
{
/// <summary>
/// 数据层基类，提供对底层数据的基本操作
/// </summary>
public class dbobject
{
  private sqlconnection connection;

  #region 构造函数
  /// <summary>
  /// 构造函数，初始化数据连接对象
  /// </summary>
  public dbobject()
  {
   string connectionstring = configurationsettings.appsettings.get("connectionstring");//从web.config中取得的连接字符串
   connection = new sqlconnection(connectionstring);
  }

  /// <summary><table >
  /// <tr><td><b>功能描述</b>：构造函数，根据指定的数据连接字符串，初始化数据连接对象</td></tr>
  /// <tr><td><b>创建人</b>：夏春涛 </td></tr>
  /// <tr><td><b>创建时间</b>：2005-05-28 </td></tr>
  /// </table></summary>
  /// <param name="newconnectionstring">数据连接字符串</param>
  public dbobject( string newconnectionstring )
  {
   string connectionstring = newconnectionstring;
   connection = new sqlconnection( connectionstring );
  }
  #endregion

  /// <summary>
  /// 数据连接对象（只读）
  /// </summary>
  public sqlconnection connection
  {
   get
   {
    return connection;
   }
   set
   {
    connection = value;
   }
  }

  //-----------------------------------------------------------------------------------------
  //以下是从《asp.net web站点高级编程》中copy的（夏春涛）------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 创建一个sqlcommand对象，用于获取存储过程的返回值
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <returns>sqlcommand对象</returns>
  private sqlcommand buildintcommand(string storedprocname, idataparameter[] parameters)
  {
   sqlcommand command = buildquerycommand( storedprocname, parameters );

   command.parameters.add( new sqlparameter ( "returnvalue",
    sqldbtype.int,
    4, /* size */
    parameterdirection.returnvalue,
    false, /* is nullable */
    0, /* byte precision */
    0, /* byte scale */
    string.empty,
    datarowversion.default,
    null ));

return command;
}

  /// <summary>
  /// 创建一个sqlcommand对象，用于生成sqldatareader
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <returns>sqlcommand对象</returns>
  private sqlcommand buildquerycommand(string storedprocname, idataparameter[] parameters)
  {
   sqlcommand command = new sqlcommand( storedprocname, connection );
   command.commandtype = commandtype.storedprocedure;

   foreach (sqlparameter parameter in parameters)
   {
    command.parameters.add( parameter );
   }

return command;

}

  /// <summary>
  /// 运行存储过程，获取影响数，返回存储过程运行结果
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <param name="rowsaffected">出参：执行存储过程所影响的记录行数</param>
  /// <returns>存储过程的运行结果</returns>
  public object runprocedure(string storedprocname, idataparameter[] parameters, out int rowsaffected )
  {
   object result;

   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = buildintcommand( storedprocname, parameters );
   rowsaffected = command.executenonquery();
   result = command.parameters["returnvalue"].value;
   connection.close();
   return result;
  }

  /// <summary>
  /// 运行存储过程，返回产生的sqldatareader对象
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <returns>sqldatareader对象</returns>
  public sqldatareader runprocedure(string storedprocname, idataparameter[] parameters )
  {
   sqldatareader returnreader;

   connection.open();
   sqlcommand command = buildquerycommand( storedprocname, parameters );
   command.commandtype = commandtype.storedprocedure;

   returnreader = command.executereader();
   //connection.close();
   return returnreader;
  }

  /// <summary>
  /// 运行存储过程，创建一个dataset对象，
  /// 将运行结果存入指定的datatable中，返回dataset对象
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <param name="tablename">数据表名称</param>
  /// <returns>dataset对象</returns>
  public dataset runprocedure(string storedprocname, idataparameter[] parameters, string tablename )
  {
   dataset dataset = new dataset();
   connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = buildquerycommand( storedprocname, parameters );
   sqlda.fill( dataset, tablename );
   connection.close();

return dataset;
}

  /// <summary>
  /// 运行存储过程，将运行结果存入已有dataset对象的指定表中，无返回值
  /// </summary>
  /// <param name="storedprocname">存储过程名称</param>
  /// <param name="parameters">存储过程的参数对象列表（数组）</param>
  /// <param name="dataset">dataset对象</param>
  /// <param name="tablename">数据表名称</param>
  public void runprocedure(string storedprocname, idataparameter[] parameters, dataset dataset, string tablename )
  {
   connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = buildintcommand( storedprocname, parameters );
   sqlda.fill( dataset, tablename );
   connection.close();
  }

  //-----------------------------------------------------------------------------------------
  //以下是自建的（夏春涛）-------------------------------------------------------------------
  //-----------------------------------------------------------------------------------------
  /// <summary>
  /// 运行与写数据库相关的sql语句，返回影响行数**********************************************
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <returns>影响行数</returns>
  public int exenonquery(string sqlstring)
  {
   int rowaffected;
   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   rowaffected = command.executenonquery();
   //connection.close();

   return rowaffected;

}

  /// <summary>
  /// 运行sql语句，返回sqldatareader对象
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <returns>sqldatareader对象</returns>
  public sqldatareader exesqlstring(string sqlstring)
  {
   sqldatareader returnreader;

   if(connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   returnreader = command.executereader();
   //connection.close();

return returnreader;
}

  /// <summary>
  /// 运行sql语句，返回dataset对象
  /// </summary>
  /// <param name="string">sql语句</param>
  /// <param name="tablename">数据表名称</param>
  /// <returns>dataset对象</returns>
  public dataset exesqlstring(string sqlstring, string tablename )
  {
   dataset dataset = new dataset();
   if (connection.state.tostring() == "closed")
                connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = new sqlcommand( sqlstring, connection );
   sqlda.fill( dataset, tablename );
   connection.close();

return dataset;
}

  /// <summary>
  /// 运行sql语句，将运行结果存入已有dataset对象的指定表中，无返回值
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <param name="dataset">dataset对象</param>
  /// <param name="tablename">数据表名称</param>
  public void exesqlstring(string sqlstring, dataset dataset, string tablename )
  {
   if (connection.state.tostring() == "closed")
    connection.open();
   sqldataadapter sqlda = new sqldataadapter();
   sqlda.selectcommand = new sqlcommand( sqlstring, connection );
   sqlda.fill( dataset, tablename );
   connection.close();
  }

  /// <summary>
  /// 运行sql语句，返回查询结果的第一行的第一列，忽略其它行或列
  /// </summary>
  /// <param name="sqlstring">sql语句</param>
  /// <returns>影响行数</returns>
  public object exescalar(string sqlstring)
  {
   object returnscalar;
   if (connection.state.tostring() == "closed")
    connection.open();
   sqlcommand command = new sqlcommand( sqlstring, connection );
   returnscalar = command.executescalar();
   //connection.close();

   return returnscalar;
  }

  ~dbobject()
  {
   if(connection.state.tostring() == "open")
                connection.close();
   connection.dispose();
  }