Using system; using system.data.sqlclient; Namespace SysclassLibrary {/// /// DataAccess's summary description.
/// 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 dataAccess () constructor /// /// Open database connection /// private static void openconnection () {if (conn.state == connectionState.closed) {//sysconfig.connectionstring The connection string in the system configuration class, such as "Server = localhost; database = database = 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 () {ix (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 executedure (string procname, sqlparameter [] coll) {{openconnection (); for (int i = 0; i /// Execute the 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 return the first line A record, the return value is used to use the Object to use it to unpack operation -> unbox /// /// Inferred SQL statement /// return value object 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 /// < / summary> /// Sql statement incoming 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} cat.rollback ();} finally {closeConnection ();}} / // /// Returns the SqlDataReader of the specified SQL statement, note that please turn off this object after use, and automatically call CloseConnection () to turn off the database connection // method to turn off the database connection /// /// Inferred SQL statement /// SqlDataReader object public static sqldataareader DataReader (SQLDATAREADER DR = NULL; try {OpenConnection (); Comm.commandtext = SQLSTR; Comm.commandType = CommandType.Text; DR = CO Mm.executeReader (Commandbehavior.CloseConnection);} catch {try {dr.close (); closeconnection ();} catch {}} Return DR;} /// /// Returns SQLDataReader for specified SQL statement, please Note that after use, turn off this object, and will automatically call CloseConnection () to close the database connection /// method to turn off the database connection /// /// Incoming SQL Statement /// Intrinscent 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.CloseConnection);} catch {try {if (dr = null && dr.IsClosed!!) dr.Close ();} catch {} finally {closeConnection ( );}}} /// /// Returns DataSet /// ///// /// incoming SQL statement ////// DataSet 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 ds;} /// /// Returns a DataSet /// /// incoming SQL statement incoming SQL statement /// public static void dataset (string sqlstr, ref digd ds) {sqldataadapter da = new SQLD ATAADAPTER (); 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 ();}} /// /// returns Sql statement DataTable /// /// Sql statement incoming /// DataTable public static dataable data (string sqlstr) {sqldataadapter da = new SqlDataAdapter (); dataable dataatable =
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);} finally {closeConnection ();} Return DataTable;} /// // / execute the specified SQL statement, and assign a value to the incoming DataTable /// /// Inferred SQL statement /// Ref dataable dt public static void data (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 ();}} /// // / Execute a parameter stored procedure and return data set /// /// stored procedure name /// SQLParameterCollection Enter parameter /// 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.CommandText = procName; foreach (SqlParameter para in parameters) {SqlParameter p = (SqlParameter) para; comm.Parameters.Add (p);} da.SelectCommand = comm; da.Fill (datatable);} catch ( Exception E) {throw new exception (E.MESSAGE);
} Finally {closeConnection ();} return datatable;} 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 = communication; da.fill (DS); DV = DS.TABLES [0] .defaultview;} catch (Exception E) {throw new Exception E.MESSAGE);} finally {closeconnection ();} Return DV;}}
9CBS users (2004-06-16) still use this kind of thing?
Seeko0 (2004-06-16) Requires MS SQLHELPER for friends, please click here to download http://25h.bigwww.com/download/dataaccessv2.rar
Seeko0 (2004-06-11) Hi yongjie37 leaves an email I send you a copy, or send me an email: scwuchen@263.net
Yongjie37 (2004-06-10) Why don't I do MS SQLHELPER? How to get this class
Seeko0 (2004-06-09) Previous test, if the connection is not proactive, the number of connections in Master.Processes will increase. Responding to the network access database resource valuable call, use a process after processing the data, immediately disconnects the database connection, it may feel very comfortable, watching MS SQLHELPER, I feel that it is not at a level.
