Access classes for SQL databases encapsulated in asp.net

Source: Internet
Author: User
Tags foreach table name first row tostring
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;
}

}
}


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.