// Database Components // Powered by Sai, Daxiong //QQ: 2231068//e-mail:2231068@qq.com
Using system; using system.data; using system.data.oledb; using system.data.sqlclient;
Namespace database.sqlserver {public class sqlbase {// static variable, save the database connection string, the string reads protected static string m_strconn = configurationSettings.appsettings in the web.config configuration file from the web.config configuration file; "Strconn"];
// Constructor PUBLIC SQLBASE () {} // Static method, set database connection string public static void setConnectionstring (String strconn) {m_strconn = strconn;} // static method, acquire database connection string public static string getConnectionstring () {return m_strConn;} // static methods that do not return data set Sql statement public static int ExecuteSql (string strSql) {SqlConnection dbConn = new SqlConnection (m_strConn); // myConnection.Open (); // SqlCommand myCommand = myConnection .CreateCommand (); SQLCommand dbcmd = new SQLCOMMAND (STRSQL); try {dbconn (); dbcmd.executenonQuery (); return 0; // Return 0 Representation query success} catch (sqlexception e) {throw new Exception (e) .Message);} finally {dbCmd.Dispose (); dbConn.Close ();}} // execute SQL statements, it is determined whether the data is returned public static int ExecuteSqlEx (string strSql) {SqlConnection dbConn = new SqlConnection (m_strConn); SqlCommand Dbcmd = new sqlcommand (strsql); try {dbconn.open (); sqldataareader myreader = dbcmd.executeReader (); if (myReader.Read ()) Return 0; Else Throw New Exception ("Data that does not query in the database");} catch (sqlexception e) {throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close ();}
} // execute SQL statement and return data sets public static DataSet ExecuteSqlDs (string strSql) {SqlConnection dbConn = new SqlConnection (m_strConn); DataSet ds = new DataSet ( "ds"); try {dbConn.Open (); SqlDataAdapter adapter = new SqlDataAdapter (); adapter.SelectCommand = new SqlCommand (strSql, dbConn); adapter.Fill (ds); adapter.Dispose (); return ds;} catch (SqlException e) {throw new Exception (e.Message);} finally {dbConn.Close ();}} // returns only perform a numeric value the result of an SQL statement public static int ExecuteSqlValue (string strSql) {SqlConnection dbConn = new SqlConnection (m_strConn); SqlCommand dbCmd = new SqlCommand (strSql); try {Dbconn.open (); object r = dbcmd.executescalar (); if (Object.Equals (r, null) throw new exception ("no value"); else return (int)),} catch (SQLException E) {Throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close ();}} // Execute only SQL statements that return only one data, return data can be any type public static Object Execu teSqlValueEx (string strSql) {SqlConnection dbConn = new SqlConnection (m_strConn); SqlCommand dbCmd = new SqlCommand (strSql); try {dbConn.Open (); object r = dbCmd.ExecuteScalar (); if (object.Equals (r, null )) Throw new exception; else return r;} catch (sqlexception e) {throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close ();} / / executes a set of SQL statements, the data set does not return public static int ExecuteSqls (string [] strSqls) {SqlConnection dbConn = new SqlConnection (m_strConn); SqlCommand dbCmd = new SqlCommand (); // SqlTransaction class that represents the database to the SQL Server TRANSACT-SQL transaction processing. This class cannot be inherited.
// Perform all the back operations associated with the transaction (for example, submit or abort the transaction) for the SQLTransaction object. // The application creates a SQLTransaction object by calling BeGintransaction on a SQLConnection object. Sqltransaction myTrans = dbconn.begintransaction (); int j = strsqls.length; // array length try {dbconn.open ();} catch (sqlexception e) {throw new exception (E.MESSAGE);} dbcmd.connection = dbconn DBCMD.TRANSACTION = MyTrans; try {foreach (string str in strsqls) {dbcmd.commandtext = Str; dbcmd.executenonQuery () // Commit method is equivalent to the Transact-SQL Commit Transaction statement. // Submit a transaction myTrans.commit (); return 0;} Catch (SQLEXCEPTION E) {// rollback () method: Rolling the transaction from the suspend state Roll Transaction MyTrans.Rollback (); throw new exception (E.MESSAGE); Finally {//sqltransaction.dispose (); dbcmd.dispose (); dbconn.close ();}}}}
namespace DataBase.OleDB {public class OleBase {protected static string m_strConn = ConfigurationSettings.AppSettings [ "strConn"]; // static methods, database connections string public static void SetConnectionString (string strConn) {m_strConn = strConn;} // static Method, obtain database connection string public static string getConnectionstring () {return m_strconn;}
// static methods, do not return the data set Sql statement public static int ExecuteSql (string strSql) {OleDbConnection dbConn = new OleDbConnection (m_strConn); OleDbCommand dbCmd = new OleDbCommand (strSql); try {dbConn.Open (); dbCmd. EXECUTENONQUERY (); return 0; // Return 0 Represents Query Success} Catch (OLEDBEXCEPTION E) {Throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close ();}} // SQL statement, determines if data is returned public static int ExecuteSqlEx (string strSql) {OleDbConnection dbConn = new OleDbConnection (m_strConn); OleDbCommand dbCmd = new OleDbCommand (strSql); try {dbConn.Open (); OleDbDataReader myReader = dbCmd.ExecuteReader () ; If (MyReader.Read ()) Return 0; Else Throw new exception ("Data to query in the database");} catch (oledbexception e) {throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); Dbconn.close ();
} // execute SQL statement and return data sets public static DataSet ExecuteSqlDs (string strSql) {OleDbConnection dbConn = new OleDbConnection (m_strConn); DataSet ds = new DataSet ( "ds"); try {dbConn.Open (); OleDbDataAdapter adapter = new OleDbDataAdapter (); adapter.SelectCommand = new OleDbCommand (strSql, dbConn); adapter.Fill (ds); adapter.Dispose (); return ds;} catch (OleDbException e) {throw new Exception (e.Message);} finally {dbConn.Close ();}} // returns only perform a numeric value the result of an SQL statement public static int ExecuteSqlValue (string strSql) {OleDbConnection dbConn = new OleDbConnection (m_strConn); OleDbCommand dbCmd = new OleDbCommand (strSql); try {Dbconn.open (); object r = dbcmd.executescalar (); if (Object.Equals (r, null) throw new exception; Else Return (int));} catch (OLEDBEXCEPTION E) {Throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close ();}} // Execute only SQL statements that only return a data, return data can be any type of PUBL ic static object ExecuteSqlValueEx (string strSql) {OleDbConnection dbConn = new OleDbConnection (m_strConn); OleDbCommand dbCmd = new OleDbCommand (strSql); try {dbConn.Open (); object r = dbCmd.ExecuteScalar (); if (object.Equals ( R, NULL)) Throw new Exception ("No object"); Else Return R;} catch (OLEDBEXCEPTION E) {throw new exception (E.MESSAGE);} finally {dbcmd.dispose (); dbconn.close (); }} // Execute a set of SQL statements, do not return data set public static int executesqls (string [] strsqls) {oledbconnection dbconn = new oledbConnection (m_strconn); OLEDBCommand dbcmd = new OLEDBCommand ();
// SQLTransaction class, indicating the Transact-SQL transaction to be processed in the SQL Server database. This class cannot be inherited. // Perform all the back operations associated with the transaction (for example, submit or abort the transaction) for the SQLTransaction object. // The application creates a SQLTransaction object by calling BeGintransaction on a SQLConnection object. OleDbTransaction myTrans = dbConn.BeginTransaction (); int j = strSqls.Length; // array length try {dbConn.Open ();} catch (OleDbException e) {throw new Exception (e.Message);} dbCmd.Connection = dbConn DBCMD.TRANSACTION = MyTrans; try {foreach (string str in strsqls) {dbcmd.commandtext = Str; dbcmd.executenonQuery () // Commit method is equivalent to the Transact-SQL Commit Transaction statement. // Submit transaction myTrans.commit (); return 0;} Catch (OLEDBEXCEPTION E) {// rollback () method: Rolling the transaction from hanging status MYTRANS. ROLLBACK (); throw new exception (E.MESSAGE); Finally {//sqltransaction.dispose (); dbcmd.dispose (); dbconn.close ();}}}}
