C # Writing the SqlHelper class using a detailed description

This article mainly introduces the use of C # to write SqlHelper class, small series feel very good, and now share to everyone, but also for everyone to do a reference. Let's take a look at it with a little knitting.

Boring weekend, learning, coding weak. Want to find something to do but do not know what to do, suddenly found himself learned sqlhelper quickly forget. So the tiger is a shock thought how can be so degenerate, immediately open the computer, hands touch the keyboard. Write down this article as a refresher in the learning process and share the knowledge (by the way, pass the time-^.^-).

Start text below

Here is a console program for the case. First we need to configure the connection string first, we need to add the following node in the App. Config file:

<connectionStrings>   <add name= "Sql" connectionstring= "server= database address; uid= user name; pwd= password; database= database name"/ >  </connectionStrings>

1. Next, you need to create a class named Sqlhepler, and then create a method to get the connection string that is configured in the App. config file.

public static string getsqlconnectionstring () {   return ConfigurationManager.     connectionstrings["SQL"]. ConnectionString; }

2. The following encapsulates the first Sqlhepler method, encapsulating the number of rows affected by an executed SQL.

public static int ExecuteNonQuery (string sqltext,params sqlparameter[] parameters) {   using (SqlConnection conn = new S Qlconnection (Getsqlconnectionstring ()))   {     using (SqlCommand cmd=conn. CreateCommand ())     {       Conn.　　Open ();　　Open database       cmd.commandtext = SQLText; The CommandText is assigned a value of       cmd.　　Parameters.addrange (Parameters); Assign a value of return CMD to the database using Parameters       . ExecuteNonQuery ();}}}   

Parameter description: sqltext: SQL Script to execute, parameters: required set of parameters

This method is primarily used to perform, delete, update, and insert operations, returning the number of rows affected.

3. Continue to encapsulate a query operation that returns the value of the first column in the first row of the query results

public static Object ExecuteScalar (string sqltext, params sqlparameter[] parameters) {  using (SqlConnection conn=new SqlConnection (Getsqlconnectionstring ()))  {   using (SqlCommand cmd=conn. CreateCommand ())   {     Conn. Open ();     Cmd.commandtext = SQLText;     Cmd. Parameters.addrange (Parameters);     return CMD. ExecuteScalar ();}}}  

Parameter description: As above.

The method returns the value of the object, so the class can be used when we query the data without knowing what type it is.

4. In the encapsulation of a common query method, return a DataTable

public static DataTable executedatatable (string sqltext, params sqlparameter[] parameters)  {  using ( SqlDataAdapter adapter =new SqlDataAdapter (Sqltext,getsqlconnectionstring ()))  {    DataTable dt = new DataTable ();    Adapter. SelectCommand.Parameters.AddRange (Parameters);    Adapter. Fill (DT);    return dt;   }}

Parameter description: As above.

This method is mainly used for some query data, DT will be populated with the queried data, and then return the data.

5. Finally in the write wrapper a query method, the method returned is a SqlDataReader type

public static SqlDataReader ExecuteReader (string sqltext, params sqlparameter[] parameters) {   //SqlDataReader required, When it reads the data, it has its own SqlConnection object, and SqlConnection must be open   SqlConnection conn = new SqlConnection ( Getsqlconnectionstring ());//Do not release the connection because the connection is also required to open   SqlCommand cmd = conn. CreateCommand ();   Conn. Open ();   Cmd.commandtext = SQLText;   Cmd. Parameters.addrange (Parameters);   CommandBehavior.CloseConnection when SqlDataReader is released, the SqlConnection object is also released as   return CMD. ExecuteReader (commandbehavior.closeconnection); }

Parameter description: Still as above.

The SqlDataReader type object returned by this method needs to always use the SqlConnection object, so it cannot be freed. The type of read data is a row of reads. Read uses the Read () method of the class, and the return value is bool to determine whether the data is empty (that is, whether to read to the last line), and the method will automatically read down to the next record.

As a beginner, this is just a brief introduction, and review the Sqlhepler class.

Enclose all the code:

Using system.configuration;using system.data;using System.data.sqlclient;namespace userinfomgr{class SqlHelper {// <summary>///Get connection string///</summary>//<returns> connection string </returns> public static Strin G Getsqlconnectionstring () {return configurationmanager.connectionstrings["SQL"].    ConnectionString; }///<summary>//package an executed SQL returns the number of rows affected///</summary>//<param name= "sqltext" > Executed SQL Script &lt ;/param>//<param name= "parameters" > Parameter collection </param>//<returns> affected rows </returns> publi c Static int ExecuteNonQuery (string sqltext,params sqlparameter[] parameters) {using (SqlConnection conn = new Sq Lconnection (Getsqlconnectionstring ())) {using (SqlCommand cmd=conn. CreateCommand ()) {Conn.          Open ();          Cmd.commandtext = SQLText; Cmd.          Parameters.addrange (Parameters); return CMD.        ExecuteNonQuery (); }}}///&LT;summary>///Execute SQL, return the value of the first column in the first row of the query results///</summary>//<param name= "sqltext" > Executed SQL Script </param&    Gt <param name= "Parameters" > Parameters collection </param>//<returns> values of first column in first row of query results </returns> public Stati C Object ExecuteScalar (String sqltext, params sqlparameter[] parameters) {using (SqlConnection conn=new Sqlconnec tion (getsqlconnectionstring ())) {using (SqlCommand cmd=conn. CreateCommand ()) {Conn.          Open ();          Cmd.commandtext = SQLText; Cmd.          Parameters.addrange (Parameters); return CMD.        ExecuteScalar (); }}}///<summary> Execute SQL return a DataTable///</summary>//<param name= "SQLText" ; executed SQL script </param>///<param name= "parameters" > Parameter collection </param>//<returns> returns a datatable</  returns> public static DataTable executedatatable (string sqltext, params sqlparameter[] parameters) {using (SqldataadapteR Adapter =new SqlDataAdapter (Sqltext,getsqlconnectionstring ())) {DataTable dt = new DataTable (); Adapter.        SelectCommand.Parameters.AddRange (Parameters); Adapter.        Fill (DT);      return DT; }}///<summary>//Execute SQL Script///</summary>//<param name= "sqltext" > Executed SQL Script </param >//<param name= "parameters" > Parameter collection </param>//<returns> returns a sqldatareader</returns> p Ublic static SqlDataReader ExecuteReader (string sqltext, params sqlparameter[] parameters) {//SqlDataReader required, it reads When data is available, it is exclusive to its SqlConnection object, and SqlConnection must be open SqlConnection conn = new SqlConnection (getsqlconnectionstring ( ), or//Do not release the connection because the connection is also required to open SqlCommand cmd = conn.      CreateCommand (); Conn.      Open ();      Cmd.commandtext = SQLText; Cmd.      Parameters.addrange (Parameters); CommandBehavior.CloseConnection when SqlDataReader is released, the SqlConnection object is also released as return CMD. ExecuteReader (Commandbehavior.closEconnection); }  }}