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 returnn () {sqlConnection conn = new SqlConnection (conn.open); Return conn; public void dispose (SqlConnection conn) {if (conn! = null) {conn.close (); conn.dispose ();} gc.collect ();} /// /// run SQL statement /// /// public void runproc (string sql) {sqlConnection conn; conn = new SqlConnection (conn.open (); sqlcommand CMD; cmd = cretecmd (sql, conn); try {cmd.executenonQuery ();} catch {throw new exception (SQL);} (CONN); RETURN;} /// /// run SQL statement Returns DataReader /// /// SqlDataReader object. public sqldatareader RunprocgetReader (SQLCONNECTION CONN; conn = New SqlConnection (Conn.open (); SQLCommand CMD; cmd = createcmd (SQL, CONN); SqlDataRead Er DR; try {dr = cmd.executeReader (Commandbehavior.default);} catch {throw new exception (sql);} // dispose (conn); Return DR;}
/// /// Generate Command object /// /// /// / // public sqlcommand createcmd (SQLCOMMAND CMD; cmd = new sqlcommand (sql, conn); return cmd;} /// /// Generate Command object / // /// /// public sqlcommand createcmd (string sql) {sqlConnection conn; conn = new SqlConnection (connStr); Conn (); sqlcommand cmd; cmd = new sqlcommand (sql, conn); return cmd;} /// /// Return Adapter object /// /// /// /// public sqldataadapter createda (string sql) {sqlConnection conn; conn = new SqlConnection (ConnTR) Conn.open (); SqlDataAdapter Da; DA = New SqlDataAdapter (SQL, Conn); Return Da;} /// /// runs SQL statement, return to DataSet object /// ///// SQL statement /// DataSet object public DataSet RunProc (SQLCONNECTION CONN; conn = new sqlconnection); conn.open (); sqlDataAdapter Da; / / Da = Createda (SQL, CONN); DA = New SqldataAdapter (SQL, Conn); Try {DA.FILL (DS);} catch (exception err) {throw Err;} Dispose (conn); returnif; // // / Run SQL statement, return to DataSet object /// /// SQL statement /// DataSet object /// table name public DataSet RunProc (String SQL, DataSet DS, String Tablename) {SQLCONNECTION CONN; conn = new sqlconnection (conn.open (); sqldataadapter da; DA = CREATEDA (SQL); try {da.fill (DS, TABLENAME);} catch (Exception EX) ) {Throw ex;} DISPOSE (CONN); RETURN DS;} /// /// run SQL statement, return to DataSet object /// /// sql Statement /// DataSet object /// table name 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;} /// /// Test Whether the data /// ///// Public Bool ExistDate (STRING SQL) {SqlConnection Conn; conn = new sqlconnection (conn.open (); SqlDataReader DR; DR = CreateCMD (SQL, Conn) .executeReader (); if (); Dr.read ()) {Dispose (conn); return true;} else {dispose;}} /// /// Returns the first line of the SQL statement execution results / // /// string public string returnValue (string sql) {sqlconnection conn; conn = new sqlConnection (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);}; return result;} /// /// Returns the first column of SQL statement, columni column, / // /// PUBLIC STRING RETURNVALUE (SQLCONNECTION CONN; conn = new sqlconnection); conn.open (); string result; SqlDataReader DR; try {dr = cretecmd (sql, conn) .executeReader ();} catch {throw new exception (sql);}}} {results = dr [columni] .tostring ();} else {Result = "";} Dr.close (); Dispose (conn); return result;}
/// /// Generates a SQLCommand. /// /// stored procedure name. /// stored procedure into the parameter group. /// SQLCommand object. 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);}}}}}}}}}}}}}}}} /// /// For stored procedures Generate a SQLCommand object /// /// Storage Procedure Name /// Stored procedure parameter /// SQLCommand object private sqlcommand createcmd (String Procname, Sqlparameter [] Prams, SqlDataReader DR) {SQLCONNECTION CONN; conn = new sqlconnection (conn.open (); sqlcommand cmd = new sqlcommand (procname, conn); cm d.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 stored procedure, return. /// /// Stored procedure name /// stored procedure parameter /// SqlDataReader Object public void RunProc (string procName, SqlParameter [] prams, SqlDataReader Dr) {SqlCommand Cmd = CreateCmd (procName, prams, DR); DR = cmd.executeReader (system.data.commandbehavior.CloseConnection); returnne;} /// /// run stored procedure, return. /// /// stored procedure name /// stored procedure parameter public string runproc (String procname, sqlparameter [] prams) {SqlDataReader DR; sqlcommand cmd = cretecmd Procname, PRAMS; DR = cmd.executeReader (system.data.commandbehavior.closeConnection); if (Dr.Read ()) {Return Dr.GetValue (0) .tostring ();} else {return ";}} /// /// run the stored procedure, return DataSet. /// /// Stored process name. /// >>>>> into the parameter group. /// DataSet object. PU blic 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;
}
