SqlHelper of "Beef Brisket Press Release System" step-by-step evolution

Personal reconstruction, although the use of sqlhelper, but do not know its past life, all the total feeling is very abrupt. When we look at the brisket, it dawned on me. SQLHelper can know from the name, it is related to the database, and can help the database. Such a thought, when understood again, "SQLHelper is used to simplify the way you repeatedly write those database connections (SqlConnection), Sqlcommand,sqldatareader, and so on. After the SqlHelper package is usually only required to pass some parameters such as database connection string, SQL parameters, etc., you can access the database. "We have a clear understanding of his role.

Today we just take a look at a class to see how the SqlHelper has different database operation changes.

Let's take a look at one of the most primitive queries.

Using system.data;//Add Reference using system.data.sqlclient;//Add Reference namespace  dal{public    class SQLHelper    {        public  int Query ()        {            //① defines the database connection string            [email protected] "SERVER=MADAN\SQL;DATABASE=MEWSSYSTEM; uid=sa;pwd=123456 ";           ② the connection string to the incoming database            SqlConnection conn=new SqlConnection (connstr);//③ open  conn.open            //④ Database statement Change statement            string sql= "INSERT into category (name) value (' Accelerated ')";            ⑤ command execution object requires SQL statement and Conn command fixed statement            SqlCommand cmd=new SqlCommand (sql,conn);            ⑥ execution returns an int  fixed statement            int res=cmd. ExecuteNonQuery ();            ⑦ closes the connection fixed statement            Conn. Close ();            return res;        }    

No matter how many query classes we write, we will find that the steps above are fixed. And there will be changes in only two places, one is connected to the database login user and password will change. Second, the different query content causes the specific query statement to be different. So in order to encapsulate the changes, we take the parts of the change out. The public part was also refined.

Looking at the code below we know how the invariant parts are refined, and how the changing parts are encapsulated . Look directly at the code

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >using system.data.sqlclient;//Add Reference using system.configuration;//Add Reference namespace dal{public class SQLHelper {//Proposed frequently         with private SqlConnection conn = null;        Private SqlCommand cmd = null;        Private SqlDataReader SDR = null; Public SQLHelper ()//Initialize constructor {string connstr = configurationmanager.connectionstrings["ConnStr"].        Connectionstring;//②web.config has been set in CONNSTR for the connection statement this sentence is outgoing connection Statement conn = new SqlConnection (CONNSTR); }//Open the connection method private SqlConnection Getconn () {if (conn. state = = connectionstate.closed) {Conn.            Open ();        } return conn;            } public int ExecuteNonQuery (String sql) {int res; try {cmd = new SqlCommand (SQL, Getconn ());//③⑤ calls the method above to open the connection and pass in the SQL statement res = cmd.     ExecuteNonQuery ();//⑥ execution}       catch (Exception ex) {throw ex; } finally {if (conn. state = = ConnectionState.Open) {Conn.        Close ();//⑦ closes the connection}} return res; }</span>

In addition to the above, you can use try catch to ensure that the connection is finally closed. There is also an easier way to use the using using to define a range, where the object is disposed at the end of the range.

Where do you see the change in the section below?

The first is to define the connection string for the database, which we have applied to when we use the abstract Factory + reflection. b/S is the same. The real practice is to add the configuration statement in Web. config and change the text directly if there are any changes.

Second, each query statement is written directly in the calling class.

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" > String sql = "INSERT into category (name) VALUES (@caName)";            Sqlparameter[] Paras = new sqlparameter[]{                        new  SqlParameter ("@caName", CAName)};</span>

When we look at the design pattern, it is always where the code is reused, where there is a bad taste.

Universal, through this demonstration of the sqlhelper changes, I would like to summarize such a thought, that is commonly used to extract public methods, and the changes involved in the things only write the class name, not specific.

So consider only three questions:

One: Where to repeat

Two: How to abstract the public part (global variable?) Public method? or the other)

Three: Where is the part of the change written?

Summarize

A while ago wrote his personal reconstruction summary, and today looked at others to write. Feel the harvest they mentioned, I also have. But I should summarize not in place, also always put the problem enlarged, only in the end of the computer room, very frustrated feeling. Learn from the last lesson, from now on things do not fear difficult, more summary. Contact Old knowledge. In addition, before really do not understand to hang up, like SqlHelper. In the future many will have a sense of epiphany, every stage of learning, you can pay attention to swallowed and elephant, waiting for no results so do not tangle. Stride, must be steady ahead.

SqlHelper of "Beef Brisket Press Release System" step-by-step evolution