queries in SQL Server

This blog post provides a brief introduction to several types of queries commonly used in SQL Server and how to use them.

First, ExecuteScalar method gets a single value

The ExecuteScalar method is one of the methods of the SqlCommand class, executes the query, and returns the first column in the first row of the result set returned by the query.

    Class Program {static void Main (string[] args) {string strcon = "Data source=192.168. 24.193;initial Catalog=charge_sys; User Id=sa;            password=123456 "; using (SqlConnection con = new SqlConnection (Strcon))//Use connection pooling to automatically close the connection {using (SqlCommand cmd = Con.                    CreateCommand ()) {String sql = "SELECT COUNT (*) from user_info";//define SQL statement to query the number of rows for the entire table                    Cmd.commandtext = SQL; Con.                    Open (); int count = Int32.Parse (cmd). ExecuteScalar ().                                                                                                                ToString ());                    Returns the first header column of the entire result set, which is an object type Console.WriteLine (count); Cmd.commandtext = "SELECT * from User_info";//define SQL statement string s = cmd. ExecuteScalar ().                                                                                            ToString ();                                Returns the first row of the entire User_info table and assigns the string s Console.WriteLine (s);//Prints the result out                Console.ReadLine (); }            }        }

Execution results such as:

Second, the ExecuteNonQuery method to perform additions and deletions to change operations

The ExecuteNonQuery method is also one of the SqlCommand methods that executes the T-SQL statement on the connection and returns the number of rows affected. Example:

static void Main (string[] args) {string strcon = "Data source=192.168.24.193;initial catalog=charge_ Sys User Id=sa;            password=123456 "; using (SqlConnection con = new SqlConnection (Strcon))//Use connection pooling to automatically close the connection {using (SqlCommand cmd = Con.  CreateCommand ()) {///Data manipulation language, additions and deletions change to string ins = "INSERT INTO User_info                                     (UserID) VALUES (' 123 ') "; Con.                    Open ();//Connection database cmd.commandtext = INS; int res = cmd. ExecuteNonQuery (); The Execute SQL statement returns the number of rows affected if (Res > 0) {Console.WriteLine ("Insert Successfully"                    + res + "record");                    } else {Console.WriteLine ("Insert Failed");                } console.readline (); }            }        }

Execution results such as:

Three, Beginexecutereader () and Endexecutereader () querying large result sets asynchronously

The so-called asynchronous query large result set, that is, if the amount of data is very large, then start to execute the query and end the query process may take a while, during this time we can also let our program to do something else, there are two ways. Example:

        static void Main (string[] args) {string strcon = "Data source=192.168.24.193;initial Catalog =charge_sys; User Id=sa;            password=123456 "; using (SqlConnection con = new SqlConnection (Strcon))//Use connection pooling to automatically close the connection {using (SqlCommand cmd = Con. CreateCommand ()) {//asynchronously queries large result set CMD.  CommandText = "waitfor delay ' 00:00:05 '; select * from User_info"; Delay 5 Seconds to connect to database con.                    Open (); IAsyncResult Iar=cmd.                      Beginexecutereader (); The Beignexecutereader method determines whether the asynchronous query is complete//can be written here to other database operations SqlDataReader sdr= cmd.                     Endexecutereader (IAR); The Endexecutereader method ends the connection and stores the data in the data set while (SDR.                    Read ()) {Console.WriteLine (sdr[0]+ "" +sdr[1]);       } console.readline ();         }            }        } 

Due to a delay of 5 seconds, the output will appear after 5 seconds as follows:

Iv. Perform bulk query operations

In general, when querying with SQL statements, we only query a single record, so how to insert or update multiple records in a single table is a multi-day T-SQL statement. Example:

    Class Program {static void Main (string[] args) {string strcon = "Data source=192.168. 24.193;initial Catalog=charge_sys; User Id=sa;            password=123456 "; using (SqlConnection con = new SqlConnection (Strcon))//Use connection pooling to automatically close the connection {using (SqlCommand cmd = Con.                                        CreateCommand ()) {//Execute a batch operation query while executing multiple SQL statements String sql = "Update user_info Set UserName = ' 888 ' WHERE UserID = ' 2 '" + "update user_                    Info Set UserName = ' 999 ' WHERE UserID = ' 3 ' "; Connect two T-SQL statements to con.                    Open ();//Opening database cmd.commandtext = SQL; int res = cmd. ExecuteNonQuery (); The Execute SQL statement returns the number of rows affected if (Res > 0) {Console.WriteLine ("Insert Successfully"                    + res + "record");                 } else {       Console.WriteLine ("Insert Failed");                } console.readline (); }            }        }

The results of the implementation are as follows:

Five, parameterized query

We are just beginning to learn to write SQL statements in the application, the general use of splicing strings, single quotation marks, double quotes, everything, writing is very inconvenient, and easy to be SQL injection to destroy sensitive data, neither security, so we introduced a parameterized query, as the name implies, is to query the content with parameters instead. Look at the following example:

      

<span style= "Font-family:times New Roman;" >static void Main (string[] args) {string constr = "Data source=192.168.24.193;initial catalog=charge _sys; User Id=sa;            password=123456 "; using (SqlConnection con = new SqlConnection (constr)) {using (SqlCommand cmd = c Mnl CreateCommand ()) {cmd.                    CommandText = "Select * from User_info where [email protected]";                    The first method//sqlparameter param = new SqlParameter (); Param.                    ParameterName = "username"; Param.                    SqlDbType = SqlDbType.Char; Param.                    Size = 10; Param.                    Value = "admin"; Con.                    Open (); The second method directly uses the AddWithValue method to assign a value to the parameter cmd directly.                    Parameters.addwithvalue ("@userid", ' 1 '); Con.                    Open (); SqlDataReader SDR = cmd.              ExecuteReader ();      while (SDR.                    Read ()) {Console.WriteLine (sdr[0]+ "" +sdr[1]);                } console.readline (); }}}</span>

Vi. Muhipleactiveresultset Method executes a multi-activity result set

Generally a connection object supports only one active operation, if we need to add another one or the other or, after the SQL connection statement, add muhipleactiveresultset=true. This is not the demo, I hope this blog can bring you some help.