Using system;
Using system.configuration;
Using system.data;
Using system.data.sqlclient;
Using system.collections;
Namespace mycorporation.Department.Database
{
///
/// General Database Class
///
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;
}
}
///
/// Return Connection object
///
///
Public SqlConnection ReturnConn ()
{
SqlConnection conn = new SqlConnection (connStr);
Cn.open ();
Return conn;
}
Public Void Dispose (SqlConnection CONN)
{
IF (CONN! = NULL)
{
CONN.CLOSE ();
Conn.dispose ();
}
Gc.collect ();
}
///
/// run the SQL statement
///
///
Public void Runproc (String SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SQLCommand CMD;
CMD = CREATECMD (SQL, CONN);
Try
{
Cmd.executenonquery ();
}
Catch
{
Throw new Exception (SQL);
}
Dispose (CONN);
Return;
}
///
/// Run the SQL statement to return DataReader
///
///
///
Public SqlDataReader RunprocgetReader (String SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SQLCommand CMD;
CMD = CREATECMD (SQL, CONN);
SqlDataReader DR;
Try
{
DR = cmd.executeReader (commandbehavior.default);
}
Catch
{
Throw new Exception (SQL);
}
// Dispose (conn);
Return DR;
}
///
/// Generate a Command object ///
///
///
///
Public SQLCommand CreateCMD (String SQL, SQLCONNECTION CONN)
{
SQLCommand CMD;
CMD = New SQLCOMMAND (SQL, CONN);
Return CMD;
}
///
/// Generate a Command object
///
///
///
Public SqlCommand CreateCMD (String SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SQLCommand CMD;
CMD = New SQLCOMMAND (SQL, CONN);
Return CMD;
}
///
/// Return Adapter object
///
///
///
///
Public SqlDataAdapter Createda (String SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SqlDataAdapter Da;
Da = New SqlDataAdapter (SQL, CONN);
Return DA;
}
///
/// Run the SQL statement, return DataSet objects
///
/// SQL statement
/// DataSet object
Public DataSet Runproc (String SQL, Dataset DS)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.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;
}
///
/// Run the SQL statement, return DataSet objects
///
/// SQL statement
/// DataSet object /// table name
Public DataSet Runproc (String SQL, Dataset DS, String TableName)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SqlDataAdapter Da;
DA = Createda (SQL);
Try
{
Da.fill (DS, TABLENAME);
}
Catch (Exception EX)
{
Throw EX;
}
Dispose (CONN);
Return DS;
}
///
/// Run the SQL statement, return DataSet objects
///
/// SQL statement
/// DataSet object
/// Table name
Public DataSet Runproc (String SQL, DataSet DS, Int StartIndex, Int PageSize, String TableName)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SqlDataAdapter Da;
DA = Createda (SQL);
Try
{
Da.fill (DS, StartIndex, PageSize, TableName);
}
Catch (Exception EX)
{
Throw EX;
}
Dispose (CONN);
Return DS;
}
///
/// Test if data exists
///
///
Public Bool EXISTDATE (STRING SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.open ();
SqlDataReader DR;
DR = CREATECMD (SQL, CONN) .executeReader ();
IF (Dr.Read ())
{
Dispose (CONN);
Return True;
}
Else
{
Dispose (CONN);
Return False;
}
}
///
/// Return to the first line of the first line of the SQL statement execution
///
///
Public String ReturnValue (String SQL)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.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;
}
///
/// Return the first column of the SQL statement, the columni column,
///
///
Public String ReturnValue (String SQL, INT Columni)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.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;
}
///
/// Generate a SQLCommand used in a stored procedure.
///
/// Stored procedure name.
/// Stored procedures into the parameter group.
///
Public SQLCommand CreateCMD (String Procname, Sqlparameter [] PRAMS)
{
SqlConnection conn;
CONN = New SqlConnection (Conntr);
Cn.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;
}
///
/// Generate a SQLCommand object for the stored procedure
///
/// Store process name
/// Stored procedure parameters
///
Private Sqlcommand CreateCMD (String Procname, Sqlparameter [] Prams, SqlDataReader DR)
{
SqlConnection conn;
CONN = New SqlConnection (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;
}
///
/// Run the stored procedure, return.
///
/// Store process name
/// Stored procedure parameters
/// SqlDataReader object
Public void Runproc (String Procname, Sqlparameter [] Prams, SqlDataReader DR)
{
Sqlcommand cmd = createcmd (Procname, PRAMS, DR);
DR = cmd.executeReader (System.Data.commandbehavior.closeConnection);
Return;
}
///
/// Run the stored procedure, return.
///
/// Store process name
/// Stored procedure parameters
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
{
""; "
}
}
///
/// Run the stored procedure and return to DataSet.
///
/// Stored procedure name.
/// Stored procedures into the parameter group.
///
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;
}
}
}
