Abstract SqlHelper to connect to a database

In this big data today, we are no longer working on the data, even a simple small system, there will be 10 tables around, let alone if we face a large project, hundreds of tables is often the case, if we do a simple operation on each table, Then we mean to connect hundreds of databases.

So if we were to connect to the database for the first time, I think everyone would write that.

Imports System.Data.SqlClientPublic Class UserDB ' database connection statement Dim str as String = "Data source=localhost;initial Catalog=cha Rge Persist Security info=true; User Id=sa;  password=123456 "Dim Conn as sqlclient.sqlconnection public Sub New () ' constructor to establish a connection, open database conn = new Sqlclient.sqlconnection Conn. ConnectionString = str Conn.         Open () End Sub ' queries whether a user in the database has public Function query_user (ByVal tstuser as Model.user) as Boolean ' database query statement Dim sqlstr as String sqlstr = "SELECT * from User_info Where user_id= '" & tstuser.user_id & "' and User_pwd = ' "& Tstuser.user_pwd &" ' "' Query Dim sqlcmd as Sqlclient.sqlcommand = New Sqlclient.sql Command (SQLSTR, conn) Dim myreader as SqlDataReader ' defines a reader for reading data myreader = sqlcmd.            ExecuteReader () ' runs your query, results to myreader if Myreader.read () then ' if the data is found to Return True Else Return False End If End FunctionEnd Class

So if we want to connect the number of times is hundreds, then the code in addition to the SQL statement, 90 is the same, if we go to re-write it every time it feels wasted effort, if we are copied every time, will increase the probability of the bug, so we have to think of ways to let us do as little as possible each time. All have a few days of sqlhelper this class, he is on the top of the code found every time a connection to the database after the same point summary.

/* * Created by: Menghai * Created: November 13, 2014 16:42:40 * Description: Database helper class */using system;using system.collections.generic;using system.linq;us ing system.text;using system.threading.tasks;using system.data.sqlclient;using system.data;using        System.configuration;namespace dal{public class SQLHelper {private SqlConnection conn = null;        Private SqlCommand cmd = null;        Private SqlDataReader SDR = null; Public SQLHelper () {String connstr = configurationmanager.connectionstrings["ConnStr"].            ConnectionString;        conn = new SqlConnection (CONNSTR); Private SqlConnection Getconn () {if (conn. state==connectionstate.closed) {Conn.            Open ();        } return conn; }///<summary>///Do not take parameters to delete or change SQL statements or stored procedures///</summary>//<param name= "Cmdtex T "></param>//<param name=" CT "></param>//<returns></returns>            public int ExecuteNonQuery (string cmdtext, CommandType ct) {int res;                try {cmd = new SqlCommand (Cmdtext, Getconn ());                Cmd.commandtype = ct; res = cmd.            ExecuteNonQuery ();            } catch (Exception ex) {throw ex; } finally {if (conn. state = = ConnectionState.Open) {Conn.                Close ();            }}//string sql= "INSERT into category values (' Nsdf ')";        return res; }///<summary>///execute with parameters or delete or change SQL statements or stored procedures///</summary>//<param name= "Cmdtext ">sql statement or stored procedure </param>//<param name=" ct "> Command type </param>///<returns></returns           > public int ExecuteNonQuery (string cmdtext,sqlparameter[] paras, CommandType ct) {int res; using (Cmd=new SqlCommand (Cmdtext,getconn ())) {cmd.commandtype=ct; Cmd.                Parameters.addrange (paras);                Cmd.commandtype = ct; res = cmd.            ExecuteNonQuery ();        } return res; }///<summary>///EXECUTE query SQL statements or stored procedures with parameters///</summary>//<param name= " Cmdtext ">sql or stored procedure </param>///<param Name=" Paras "> Parameters </param>///<param name=" CT " ; type </param>///<returns></returns> Public DataTable ExecuteQuery (string cmdtext,sqlparame            ter[] 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;}///<summary>///The method executes a query with parameters for SQL or stored procedures///</summary>//<param name= "Cmdtext" &G T;sql statement or </param>//<param Name= "Paras" > Parameters </param>//<param name= "ct" > Type </param&       Gt            <returns></returns> Public DataTable ExecuteQuery (String Cmdtext,commandtype ct) {            DataTable dt = new DataTable ();            cmd = new SqlCommand (Cmdtext, Getconn ());            Cmd.commandtype = ct; using (SDR = cmd. ExecuteReader (commandbehavior.closeconnection)) {dt.            Load (SDR);        } return DT; }    }}

By the way, the reason here with three///this to comment, because we write code, if the mouse on a method, the top will come out corresponding explanation, in fact, this is the role of the three slash.Using (sdr=cmd. ExecuteReader (commandbehavior.closeconnection)) This sentence is equivalent to determine whether the connection, if connected, after the completion of the automatic shutdown, thus eliminating the process of manual judgment.

in the computer room charge system when used, but did not understand, directly from the online when a copy, but this is followed by a good study of the video, also hit the general, the biggest sigh of the process is abstract everywhere, will abstract, work is efficient, just simple.

Abstract SqlHelper to connect to a database