SQL database generic category

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;
}

}
}