News system-SQLHelper assistant optimization, news system sqlhelper

News system-SQLHelper assistant optimization, news system sqlhelper

We used SQLHelper when restructuring our data center, but at that time we only applied it. We knew that using SQLHelper could help us connect to the database, but we still don't know how to write it clearly, today, we will unveil this layer of mystery to truly experience how to compile the SQLHelper assistant.

SQLHelper accesses the database and executes SQL statements or stored procedures. After multiple adjustments, the assistant can execute both SQL query statements and stored procedures.

First, analyze SQLHelper:

1. Variable initialization:

Local variables must be initialized; otherwise, compilation errors may occur.

     private SqlConnection conn = null;     private SqlCommand cmd = null;     private SqlDataReader sdr = null;

2. Connect to the database

Create a public database connection through Constructors

public SQLHelper()        {          string connStr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;          conn = new SqlConnection(connStr);        }

3. Open the connection

Determine whether the database connection is enabled. When the database connection is closed, the connection is enabled.

private SqlConnection GetConn()        {            if (conn.State == ConnectionState.Closed)            {                conn.Open();            }            return conn;        }

4. Execute SQL statements or stored procedures

The red is the input parameter, and the try... catch statement is used to simplify SQLHelper writing.

<Span style = "color: # 362e2b; ">/// <summary> /// Execute SQL statements or stored procedures without adding, deleting, and modifying parameters /// </summary> /// <param name =" </span> <span style = "color: # ff0000; "> plain text </span> <span style =" color: # 362e2b; ">"> </span> <span style = "color: # ff0000; "> add, delete, modify, and delete SQL statements or stored procedures </span> <span style =" color: # 362e2b; "> </param> // <param name =" </span> <span style = "color: # ff0000; "> ct </span> <span style =" color: # 362e2b; ">"> </span> <span style = "color: # ff0000; "> command type </span> <span style =" color: # 362e2b; "> </param> // <returns> </returns> public int ExecuteNonQuery (string plain text, CommandType ct) {int res; try {cmd = new SqlCommand (plain text, getConn (); res = cmd. executeNonQuery ();} catch (Exception ex) {throw ex; 'throw an error} finally {if (conn. state = ConnectionState. open) 'determine whether the connection is closed {conn. close () ;}} return res ;}</span>

Summary

After the transformation, SQLHelper has been greatly streamlined with the previous SQLHelper Assistant, which not only greatly reduces the code repetition rate, but also provides clearer thinking. After summing up, it is easier to master SQLHelper. It is called "One life" and "two times", and I will sum up more.