Using system.data; using system.data.sqlclient;
Summary description of Namespace SysclassLibrary {/// /// dataaccess. /// Data processing base class, call mode: dataAccess.dataset (String) SQLSTR); or DataAccess.DataSet (String) SQLSTR, REF DATASET DS); /// public class DataAccess {#region property protected static SqlConnection conn = new SqlConnection (); protected static SqlCommand comm = new SqlCommand (); #endregion public DataAccess () {// init ();} #region internal static method does not function Execute a DataAccess () constructor
/// /// Open database connection /// private static void OpenConnection () {if (conn.State == ConnectionsTate.closed) {//sysconfig.connectionstring is connected to the system configuration class. Strings, such as "Server = localhost; database = DatabaseName; UID = SA; PWD =;"
Conn.connectionstring = sysconfig.connectionstring; comm.connection = conn; try {conn.open ();} catch (exception e) {throw new exception (E.MESSAGE);}}} /// ///// Close Current Database Connection /// Private Static Void CloseConnection () {IF (Conn.State == ConnectionsTate.open) Conn.close (); conn.dispose (); comm.dispose ();} #ENDREGION /// /// Execute SQL query statement /// /// incoming SQL statement public static void executesql (string sqlstr) {TRY {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; comm.ExecuteNonQuery ();} catch (Exception e) {throw new Exception (e.Message);} finally {closeConnection ();}}
/// /// Execute stored procedure /// /// stored procedure name /// SQLParameters collection public static void executeporcedure (String procname, sqlparameter [] coll) {{openconnection (); for (int i = 0; i // / Execute a stored procedure and return data set /// /// Stored Procedure Name //// SQLParameter Collection /// dataset public static void executeporcedure (String Procname, Sqlparameter [] COLL, REF DATASET DS) {TRY { Sqldataadapter Da = new SqldataAdapter (); OpenConnection (); for (int i = 0; i
/// /// execute SQL query statement and returns the first record of the first line, the return value needs to be removed when the value is Object ,-> unbox /// /// Inferred SQL statement /// Object Return Value public static object executescalar (string sqlstr) {Object Obj = new object (); try {openconnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; obj = comm.ExecuteScalar ();} catch (Exception e) {throw new Exception (e.Message);} finally {closeConnection ();} return obj;} /// /// execute SQL query statement, while transaction /// /// Inferred SQL statement public static void ExecuteSQLWITHTRANSACTION string sqlstr) {SqlTransaction trans; trans = conn.BeginTransaction (); comm.Transaction = trans; try {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; comm.ExecuteNonQuery (); trans.Commit ();} Catch {trans. Rollback ();
/// /// Returns the SQLDATAREADER of the specified SQL statement, please note that the object is turned off after use, and will automatically call CloseConnection () to close the database connection // method to turn off the database connection /// /// Intrinscent SQL statement /// SqlDataReader object public static sqldataareader DataReader (SQLDATAREADER DR = null; try { openConnection (); comm.CommandText = sqlstr; comm.CommandType = CommandType.Text; dr = comm.ExecuteReader (CommandBehavior.CloseConnection);} catch {try {dr.Close (); closeConnection ();} catch {}} return DR;} /// /// Returns the SQLDataReader of the specified SQL statement, please note that the object is turned off after use, and will automatically call closeConnection () to turn off the database connection // method to turn off the database connection //// / /// Inferred SQL statement /// Incoming Ref DataReader object public static void DataReader String Sqlstr, Ref SqldataReader DR) {Try {OpenConnection (); comm.commandtext = SQLSTR; comm.commandtype = CommandType.Text; DR = Comm.ExecuteReader (Commandbehavior.CloseConnecti) ON);} catch {try {if (DR! = null&&! Dr.isclosed) Dr.close ();} catch {} finally {closeConnection ();}}}
/// /// Returns a DataSet /// /// incoming SQL statement /// Dataset < / returns> public static DataSet dataSet (string sqlstr) {DataSet ds = new DataSet (); SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; da. SELECTCOMMAND = Comm; DA.FILL (DS);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();} return
/// /// Returns DataSet /// ///// /// incoming SQL statement DataSet object reference passed public static void dataSet (string sqlstr, ref DataSet ds) {SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); comm.CommandType = CommandType.Text; comm. Commandtext = SQLSTR; da.selectcommand = communication; da.fill (ds);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection ();}} /// // / Return DataTable /// /// incoming SQL statement /// DataTable public static datatable dataatable string sqlstr) {SqlDataAdapter da = new SqlDataAdapter (); DataTable datatable = new DataTable (); try {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = sqlstr; da.SelectCommand = comm; da.Fill ( DataTable);} catch (exception e) {throw new exception (E.MESSAGE); CloseConnection ();} Return DataTable;
/// /// Execute the specified SQL statement, and assign a value to the incoming DataTable /// /// Inferred SQL statement // / ref DataTable dt public static void dataTable (string sqlstr, ref DataTable dt) {SqlDataAdapter da = new SqlDataAdapter (); try {openConnection (); comm.CommandType = CommandType.Text Comm.commandtext = SQLSTR; da.selectcommand = communication; da.fill (dt);} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeconnection ();}} /// // / Perform a parameter stored procedure and return data set /// /// stored procedure name /// SQLParameterCollection input parameters /// public static DataTable dataTable (string procName, SqlParameterCollection parameters) {SqlDataAdapter da = new SqlDataAdapter (); DataTable datatable = new DataTable (); try {openConnection (); Comm.Parameters.clear (); comm.commandtype = commandType.StoredProcedure; comm.command Text = procname; foreach (SqlParameter Para in parameters) {SQLParameter P = (SQLParameter) Para; Comm.Parameters.Add (p);} da.selectcommand = communication; da.fill (DataTable);} catch (Exception E) { THROW New Exception (E.MESSAGE);} finally {closeConnection ();
public static DataView dataView (string sqlstr) {SqlDataAdapter da = new SqlDataAdapter (); DataView dv = new DataView (); DataSet ds = new DataSet (); try {openConnection (); comm.CommandType = CommandType.Text; comm.CommandText = SQLSTR; DA.SELECTCOMMAND = COMM; DA.FILL (DS); DV = DS.TABLES [0] .defaultview;} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeConnection (); } Return DV;}}}
