This is a data access class written by a friend of the Internet, encapsulating most of the underlying database operation, such as returning DataSet, DataTable
Execute SQL statements, stored procedures, etc. It is also easy to support SQL Server, it is easy to rewrite other databases such as Oracle, with OLEDB
The control can be.
MSDN is really good, to write universal portable data access interface, refer to MSDN's articles;
Http://www.microsoft.com/china/msdn/library/data/dataaccess/wriportap2.mspx
I hope everyone can write high quality procedures, make high quality software, which is our common goal.
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: "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) {Try {OpenConnection (); for (int i = 0; i
/// /// Perform 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 return the first record of the first line, return value to the Object to use it to remove the operation -> unbox /// /// incoming Sql statement /// object return value public static object ExecuteScalar (string sqlstr) {object obj = new object (); try {openConnection (); comm.CommandType = CommandType TR Ext; comm.commandtext = SQLSTR; OBJ = Comm.executescalar ();} catch (exception e) {throw new exception (E.MESSAGE);} finally {closeconnection ();} returnobj;}
/// /// 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;}}}
