SQL database generic category

Source: Internet
Author: User

Using system;
Using system. configuration;
Using system. Data;
Using system. Data. sqlclient;
Using system. collections;

Namespace cfchina. Tools. Database
{
/// <Summary>
/// General database
/// </Summary>
Public class database
{

Private string connstr = NULL;

/// <Summary>
/// Framework function (overload, with connection string)
/// </Summary>
/// <Returns> </returns>
Public Database ()
{
// Connstr = configurationsettings. receivettings ["connstr"];
Connstr = configurationmanager. connectionstrings ["connstr"]. tostring ();
}

/// <Summary>
/// Framework function (overload, with connection string)
/// </Summary>
/// <Returns> </returns>
Public Database (string connstr)
{
Try
{
This. connstr = connstr;

}
Catch (exception ex)
{
Throw ex;
}
}

/// <Summary>
/// Return the connection object
/// </Summary>
/// <Returns> </returns>
Public sqlconnection returnconn ()
{
Sqlconnection conn = new sqlconnection (connstr );
Conn. open ();
Return conn;
}

/// <Summary>
/// Release resources and recycle garbage
/// </Summary>
/// <Returns> </returns>
Public void dispose (sqlconnection conn)
{
If (Conn! = NULL)
{
Conn. Close ();
Conn. Dispose ();
}
GC. Collect ();
}

/// <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>
/// Return 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>
/// Check whether data exists
/// </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>
/// Return the first column of the first row of the SQL statement execution result
/// </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>
/// Return 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>
/// Generate the sqlcommand used by the stored procedure.
/// </Summary>
/// <Param name = "procname"> name of the stored procedure. </param>
/// <Param name = "prams"> the stored procedure is added to the parameter group. </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>
/// Generate a sqlcommand object for the stored procedure
/// </Summary>
/// <Param name = "procname"> stored procedure name </param>
/// <Param name = "prams"> stored procedure parameters </param>
/// <Returns> sqlcommand object </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 SQL statement
/// </Summary>
/// <Param name = "SQL"> </param>
Public void runsql (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 the SQL statement to return datareader
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Returns> sqldatareader object. </returns>
Public sqldatareader runproc (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>
/// Run the SQL statement and return the 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 the SQL statement and return the 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 the SQL statement and return the 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>
/// 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 sqldatareader runproc (string procname, sqlparameter [] prams, sqldatareader Dr)
{

Sqlcommand cmd = createcmd (procname, prams, Dr );
Dr = cmd. executereader (system. Data. commandbehavior. closeconnection );
Return Dr;
}

/// <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 and return dataset.
/// </Summary>
/// <Param name = "procname"> name of the stored procedure. </param>
/// <Param name = "prams"> the stored procedure is added to the parameter group. </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;
}

}
}

 

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.