asp.net| Access | encapsulation | data | database using System;
Using System.Configuration;
Using System.Data;
Using System.Data.SqlClient;
Using System.Collections;
Namespace MyCorporation.DepartMent.DataBase
{
<summary>
Common database Classes
</summary>
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;
}
}
<summary>
Returns the Connection object
</summary>
<returns></returns>
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 ();
}
<summary>
Running SQL statements
</summary>
<param name= "SQL" ></param>
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
}
<summary>
Run SQL statement return DataReader
</summary>
<param name= "SQL" ></param>
<returns>sqldatareader Object .</returns>
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;
}
<summary>
Generate Command Object
</summary>
<param name= "SQL" ></param>
<param name= "Conn" ></param>
<returns></returns>
Public SqlCommand Createcmd (string SQL, SqlConnection Conn)
{
SqlCommand CMD;
CMD = new SqlCommand (SQL, Conn);
return CMD;
}
<summary>
Generate Command Object
</summary>
<param name= "SQL" ></param>
<returns></returns>
Public SqlCommand Createcmd (string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection (CONNSTR);
Conn.Open ();
SqlCommand CMD;
CMD = new SqlCommand (SQL, Conn);
return CMD;
}
<summary>
Returns the adapter object
</summary>
<param name= "SQL" ></param>
<param name= "Conn" ></param>
<returns></returns>
Public SqlDataAdapter Createda (string SQL)
{
SqlConnection Conn;
Conn = new SqlConnection (CONNSTR);
Conn.Open ();
SqlDataAdapter Da;
Da = New SqlDataAdapter (SQL, Conn);
return Da;
}
<summary>
Run SQL statement, return DataSet object
</summary>
<param name= "procname" >sql statement </param>
<param name= "Prams" >dataset object </param>
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;
}
<summary>
Run SQL statement, return DataSet object
</summary>
<param name= "procname" >sql statement </param>
<param name= "Prams" >dataset object </param>
<param name= "DataReader" > table name </param>
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;
}
<summary>
Run SQL statement, return DataSet object
</summary>
<param name= "procname" >sql statement </param>
<param name= "Prams" >dataset object </param>
<param name= "DataReader" > table name </param>
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;
}
<summary>
Verify that there is data
</summary>
<returns></returns>
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;
}
}
<summary>
Returns the first row of the results of the SQL statement execution first column
</summary>
<returns> string </returns>
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;
}
<summary>
Returns the first column of the SQL statement, column Columni,
</summary>
<returns> string </returns>
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;
}
<summary>
Generates a SqlCommand used by a stored procedure.
</summary>
<param name= "procname" > Stored procedure name .</param>
<param name= "Prams" > Stored procedures into parameter groups .</param>
<returns>sqlcommand Object .</returns>
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;
}
<summary>
To generate a SqlCommand object for a stored procedure
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure parameters </param>
<returns>sqlcommand Objects </returns>
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;
}
<summary>
Run the stored procedure and return.
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure parameters </param>
<param name= "DataReader" >sqldatareader object </param>
public void Runproc (string procname, sqlparameter[] prams, SqlDataReader Dr)
{
SqlCommand CMD = Createcmd (procname, prams, Dr);
Dr = Cmd.executereader (System.Data.CommandBehavior.CloseConnection);
Return
}
<summary>
Run the stored procedure and return.
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= "Prams" > Stored procedure parameters </param>
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 "";
}
}
<summary>
Run the stored procedure to return the dataset.
</summary>
<param name= "procname" > Stored procedure name .</param>
<param name= "Prams" > Stored procedures into parameter groups .</param>
<returns>dataset Object .</returns>
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;
}
}
}
