SQL Server based data-tier base class C # source

server|sqlserver| data

/*****************************************************
* FileName: DBObject.cs
* Functional Description: Defines the data-tier base class.
* Create person: Xiachuntao xchuntao@163.com qq:23106676
* Date Created: 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>
 ///Data-tier base class, providing basic operations on underlying data
 ///</ Summary>
 public class DBObject
 {  
  private SqlConnection connection;
&NBSP;&NBSP
   #region Constructors
  ///<summary>
  ///constructors, initializing data connection objects
  ///</summary>
  public dbobject ()
  {
   string connectionString = ConfigurationSettings.AppSettings.Get ("connectionString");//connection string
obtained from web.config     connection = new SqlConnection (connectionString);
  }

  ///<summary><table style= "font-size:12px"
  ///<tr><td> <b> function Description </b>: constructor, initializes the data connection object </td></tr>
  ///<TR><TD based on the specified data connection string ><b> founder </b>: Xiachuntao </td></tr>
  ///<tr><td><b> creation Time </b >:2005-05-28 </td></tr>
  ///</table></summary>
  ///< param name= "newconnectionstring" > Data connection string </param>
  public dbobject (string newconnectionstring)
  {
   string connectionString = newconnectionstring;
   connection = new SqlConnection (connectionString);
  }
   #endregion

  ///<summary>
  ///Data Connection object (read-only)
  ///</summary>  
  public SqlConnection Connection
  {
   get
   {
    return Connection;
   }
   set
   {
    connection = value;
&NBSP;&NBSP;&NBSP}
  }  

  //-----------------------------------------------------------------------------------------
  //The following is the copy (Xiachuntao)------------------------------------
  //from the ASP.net Web site Advanced Programming--------- --------------------------------------------------------------------------------
  ///<summary
  ///creates a SqlCommand object that gets the return value of the stored procedure
  ///</summary>
  ///< param name= "storedprocname" > Stored procedure name </param>
  ///<param name= "Parameters" > The Parameter object List (array) of the stored procedure </param>
  ///<returns>sqlcommand object </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>
Creates a SqlCommand object that is used to generate SqlDataReader
</summary>
<param name= "storedprocname" > Stored procedure name </param>
<param name= "Parameters" > stored procedure Parameter object list (array) </param>
<returns>sqlcommand Objects </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>
  ///Run the stored procedure, get the number of effects, return the results of the stored procedure run
  ///</summary>
  ///<param name= "storedprocname" > Stored procedure name </param>
  ///<param name= " Parameters "> Parameter object List (array) </param> of stored procedures;
  ///<param name=" rowsaffected "> Outbound parameters: The number of record rows affected by the execution of the stored procedure </param>
  ///<returns> stored procedures Run results </returns>
   Public object runprocedure (String storedprocname, idataparameter[] parameters, out int rowsaffected)
 & nbsp {
   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>
Run the stored procedure to return the resulting SqlDataReader object
</summary>
<param name= "storedprocname" > Stored procedure name </param>
<param name= "Parameters" > stored procedure Parameter object list (array) </param>
<returns>sqldatareader Objects </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>
  ///Run the stored procedure to create a DataSet object,
  /// Returns the results of the run to the specified DataTable, returning the DataSet object
  ///</summary>
  ///<param name= " Storedprocname > Stored Procedure name </param>
  ///<param name= "parameters" > stored procedure Parameter Objects list (array) </ Param>
  ///<param name= "tablename" > Data table name </param>
  ///<returns> DataSet Object </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>
Runs the stored procedure, storing the results in the specified table of the existing DataSet object, without return value
</summary>
<param name= "storedprocname" > Stored procedure name </param>
<param name= "Parameters" > stored procedure Parameter object list (array) </param>
<param name= "DataSet" >dataset object </param>
<param name= "tablename" > Datasheet name </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 ();
}

//-----------------------------------------------------------------------------------------
The following is a self-built (Xiachuntao)-------------------------------------------------------------------
//-----------------------------------------------------------------------------------------
<summary>
Runs the SQL statement associated with the write database and returns the number of rows affected **********************************************
</summary>
<param name= "SqlString" >sql statement </param>
<returns> affect rows </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>
Run SQL statement, return SqlDataReader object
</summary>
<param name= "SqlString" >sql statement </param>
<returns>sqldatareader Objects </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>
Run SQL statement, return DataSet object
</summary>
<param name= "string" >sql statement </param>
<param name= "tablename" > Datasheet name </param>
<returns>dataset Objects </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>
Runs the SQL statement, stores the results in the specified table of the existing DataSet object, no return value
</summary>
<param name= "SqlString" >sql statement </param>
<param name= "DataSet" >dataset object </param>
<param name= "tablename" > Datasheet name </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>
Runs the SQL statement, returns the first column of the first row of the query result, ignoring other rows or columns
</summary>
<param name= "SqlString" >sql statement </param>
<returns> affect rows </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 ();
}

}
}