Reconstruction of SQLHelper-[niuyun news publishing system] And niuyun News Publishing System

Reconstruction of SQLHelper-[niuyun news publishing system] And niuyun News Publishing System

When I first learned about the data center charging system, I did not have a deep understanding of the abstraction and encapsulation of Database Assistants. I saw the reconstruction of SQLHelper in the niugu news publishing system, and I felt a lot impressed with the abstract process of SQLHelper.

The following is a brief introduction to ADO (ActiveX Data Object). It first establishes a Connection with the server through Connection, then runs the Command through Command, and finally operates and views the query result through the Recordset Object. Parameters is the parameter information of the Command object. Filed is used through Recordset, which provides the corresponding field information. Error information is provided during Connection.

The following is the SQLHelper source code and some comments I have added after the query. I hope this will be helpful to you.

　　

<Span style = "font-size: 18px;">/** created by: Wang Mei * Creation Time: 10:42:16, January 1, January 21, 2015 * Description: Database Assistant class * All Versions: wang Mei */using System; using System. collections. generic; using System. linq; using System. text; using System. configuration; // System. the Configuration namespace contains the types of programming models used to process Configuration data. // Test the database connection using System. Data; // provides access to the ADO structure class using System. Data. SqlClient; // The namespace is the SQL server. NET Framework Data provider. Namespace DAL {public class SQLHelper {private SqlConnection conn = null; // The join is empty private SqlCommand cmd = null; // The command is empty private SqlDataReader sdr = null; // indicates a set of data commands used to fill the DataSet and update the SQL Server database and connect to a database. // SQLHelper constructor public SQLHelper () {// provides access to the client configuration file application string connStr = ConfigurationManager. connectionStrings ["connStr"]. connectionString; conn = new SqlConnection (connStr); // instantiated connection} // connection opened by the sqlhelper database private SqlConnection GetConn () {if (conn. state = ConnectionState. closed) // if the current data source status is disabled {conn. open (); // Open the connection} return conn ;} /// <summary> /// execute the SQL statement or stored procedure without parameters. /// </summary> /// <param Name = "plain text"> add, delete, modify, and delete SQL statements or stored procedures </param> /// <param name = "ct"> command type </param> /// <returns> </returns> public int ExecuteNonQuery (string plain text, commandType ct) {// define the database definition string // initialize a new instance of SQlConnection and establish a connection to open the database. Int res; try {cmd = new SqlCommand (plain text, GetConn (); // instantiate command cmd. commandType = ct; // define the command type: stored procedure, text, table res = cmd. executeNonQuery (); // The T-SQL statement executed on the couplet and returns the affected number of rows} catch (Exception ex) {throw ex;} finally {if (conn. state = ConnectionState. open) // if the connection status is closed, Open {conn. close () ;}} return res ;} /// <summary> /// execute the SQL statement or stored procedure with parameters. /// </summary> /// <param name = "plain text"> add, delete, and modify SQL statement or stored procedure </param> /// <param name = "ct"> command type </param> /// <returns> </returns> public int ExecuteNonQuery (string plain text, sqlParameter [] paras, CommandType ct) {int res; using (cmd = new SqlCommand (plain text, GetConn () // instantiate command, which contains two parameter SQL statements, join {cmd. commandType = ct; cmd. parameters. addRange (paras); // Add the parameter array res = cmd to the command. executeNonQuery ();} return res ;} /// <summary> /// execute the SQL statement or stored procedure with parameters. /// </summary> /// <param name = "plain text"> add, delete, and modify SQL statement or stored procedure </param> /// <param name = "ct"> command type </param> /// <returns> </returns> public DataTable ExecuteQuery (string plain text, commandType ct) {DataTable dt = new DataTable (); // instantiate datatable cmd = new SqlCommand (plain text, GetConn (); // instantiate command cmd. commandType = ct; // input command type using (sdr = cmd. executeReader (CommandBehavior. closeConnection) // If the associated datareader is closed, the corresponding connection is closed {dt. load (sdr); // Load SQLDataReader} return dt ;} /// <summary> /// execute the SQL statement or stored procedure with parameters. /// </summary> /// <param name = "plain text"> query the SQL statement or stored procedure </param> /// <param name = "paras"> parameter set </param> /// <param name = "ct"> command type </param >/// <returns> </returns> public DataTable ExecuteQuery (string plain text, sqlParameter [] paras, CommandType ct) {DataTable dt = new DataTable (); cmd = new SqlCommand (cmdText, GetConn (); cmd. commandType = ct; cmd. parameters. addRange (paras); using (sdr = cmd. executeReader (CommandBehavior. closeConnection) {dt. load (sdr) ;}return dt ;}}</span>

Conclusion: The foundation is the principle of abstraction and encapsulation only when the amount of code is available.