Press release system, SqlHelper reconstruction

    In the clear grasp of the beef Brisket news release system requirements, as well as the database of the system has done the corresponding design, the next few days is to write back code.
    In the video, the use of the classic three-layer framework, which has been experienced in the machine room reconfiguration, knocking code is still very easy to get started.    I believe we will not forget a good assistant in the machine room reconfiguration, that is sqlhelper.    in the Machine room reconstruction time, read a lot of blogs, we all use, also feel good, I also directly learn from. In their own step-by-step debugging, in their own program when the bug, really found the benefits of SqlHelper a lot. But I have not seriously studied how such a class is written.     and now learn the beef Brisket video, the teacher gave us a clear explanation of how sqlhelper is a step by step to improve. This process, the feeling really benefited.    first, it is clear that the operation of the database, the abstract, that is, five steps: Connect-open-execute-close--return. In the    object-oriented C # language programming, constructors are the tools used for initialization. So the code to connect to the database is:

Public SQLHelper ()        {            string connstr = "SERVER=HZT-PC;DATABASE=NEWSSYSTEM;UID=SA; pwd=123456; ";        }

    The following is a determination of the connection status, and if it is off, open it:

Private SqlConnection Getconn ()        {            if (conn. state = = connectionstate.closed)            {                Conn. Open ();            }            return conn;        }

    Once the database has done the first two tasks, you can begin to perform other actions that the user wants to perform. Take an operation that executes a parameterless query with the following code:

Public DataTable ExecuteQuery (String sql)        {            DataTable dt = new DataTable ();            Connect            string connstr = "SERVER=HZT-PC;DATABASE=NEWSSYSTEM;UID=SA; pwd=123456 ";            SqlConnection conn = new SqlConnection (CONNSTR);            Open            Conn. Open ();            Execute            SqlCommand cmd = new SqlCommand (SQL, conn);            SqlDataReader SDR = cmd. ExecuteReader ();            Dt. Load (SDR);            Close the            SDR. Close ();            Conn. Close ();            return to            DT;        }

       As you can see from the code above, you'll need to add a connection-open-close three-step code for each specific operation, and then we can encapsulate it and start refactoring the SqlHelper class.     first, from the connection, the code written in the D layer specifies which database to connect to, which server to connect to, but if we want to replace a database, then we must go to the D-level to modify the code before the modification, which is not in line with the open and close principle.     so to solve this problem, we add the configuration file, so that the long string of code to connect with a string variable, if the database is replaced, only need to add the appropriate code to the configuration file on the line.     code in the configuration file:

<connectionStrings>        <add name= "connstr" connectionstring= "server=; database=newssystem;uid=sa;pwd= 123456 "/>    </connectionStrings>    Database Connection code: Public      SQLHelper ()        {            string connstr = configurationmanager.connectionstrings["ConnStr"]. ConnectionString;            conn = new SqlConnection (CONNSTR);        }

    Second, the database status is still judged, and there are no changes.
    Third, the database for various types of operations, and still take a non-parametric query operation as an example, code as follows:

Public DataTable ExecuteQuery (string cmdtext, CommandType ct)        {            DataTable dt = new DataTable ();            Execute            cmd = new SqlCommand (Cmdtext, Getconn ());            Cmd.commandtype = ct;            Close            using (SDR = cmd. ExecuteReader (commandbehavior.closeconnection))            {                dt. Load (SDR);            Close            return dt;        }  

    From the above code, this is much simpler than the first sqlhelper, and other types of operations are done as well, and a sqlhelper is written.
    Learning experience:    this time, for the SqlHelper class of the re-construction of learning, so that their helper class and close to a lot. In the process of thinking about how this class is derived, some basic operations of the previously learned database have been reviewed and the feeling is much simpler. This time the code is according to the source of the video written out, perhaps, the next time, according to the summary of the five steps, step by step to write, you can write out a complete sqlhelper.

Press release system, SqlHelper reconstruction