Query in SQL Server

This blog briefly introduces several types of queries commonly used in SQL Server and their usage methods.

1. Obtain a single value using the ExecuteScalar Method

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

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 the connection pool to automatically close the connection {using (SqlCommand cmd = con. createCommand () {string SQL = "select count (*) from User_Info"; // define an SQL statement to query the number of rows in the entire table cmd. commandText = SQL; con. open (); int count = Int32.Parse (cmd. executeScalar (). toString (); // returns the first row and first column of the entire result set, which is an Object-type Console. writeLine (count); cmd. commandText = "select * from User_Info"; // defines the SQL statement string s = cmd. executeScalar (). toString (); // returns the first row of the entire User_Info table and assigns it to the string s Console. writeLine (s); // print the result to the Console. readLine ();}}}

The execution result is as follows:

2. ExecuteNonQuery

The ExecuteNonQuery method is also one of SqlCommand methods, which executes T-SQL statements on the connection and returns the affected number of rows. 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 the connection pool to automatically close the connection {using (SqlCommand cmd = con. createCommand () {// data operation language, add, delete, modify, and query string ins = "insert into User_Info (userID) values ('20140901')"; con. open (); // connect to the database cmd. commandText = ins; int res = cmd. executeNonQuery (); // execute the SQL statement to return the affected rows if (res> 0) {Console. writeLine ("successfully inserted" + res + "record");} else {Console. writeLine ("insertion failed");} Console. readLine ();}}}

The execution result is as follows:

3. inexecutereader () and EndExecuteReader () asynchronously query large result sets

The so-called asynchronous query of large result sets means that if the data volume is very large, it may take some time to start the query and end the query, during this period, we can also let our program do something else, so we can use the above two methods. 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 the connection pool to automatically close the connection {using (SqlCommand cmd = con. createCommand () {// asynchronously query the large result set cmd. commandText = "waitfor delay '00: 00: 05 '; select * from User_Info"; // connect to the database con with a latency of 5 seconds. open (); IAsyncResult iar = cmd. beginExecuteReader (); // The BeignExecuteReader method is used to determine whether the asynchronous query is complete. // SqlDataReader sdr = cmd can be written to other databases. 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 ();}}}

Because the delay is 5 seconds, the output result is as follows after 5 seconds:

4. Perform Batch Query

Generally we use SQL statement query only to query a record, so how to insert or update multiple records in a table at the same time, this uses the 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 the connection pool to automatically close the connection {using (SqlCommand cmd = con. createCommand () {// execute batch operation query, execute Multiple SQL statements at the same time string SQL = "update User_Info set UserName = '000000' where userID = '2'" + "update User_Info set UserName = '000000' where userID = '3 '"; // connect two T-SQL statements to con. open (); // Open the database cmd. commandText = SQL; int res = cmd. executeNonQuery (); // execute the SQL statement to return the affected rows if (res> 0) {Console. writeLine ("successfully inserted" + res + "record");} else {Console. writeLine ("insertion failed");} Console. readLine ();}}}

The execution result is as follows:

5. parameterized Query

When we first learned how to write SQL statements in an application, we usually use concatenated strings with single quotation marks and double quotation marks. this is inconvenient to write and can be easily exploited by SQL injection to damage sensitive data, it is neither safe nor safe, so we introduce parameterized queries. As the name suggests, the content to be queried is replaced by parameters. See 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 = con. createCommand () {cmd. commandText = "select * from User_Info where userID = @ userid"; // method 1 // SqlParameter param = new SqlParameter (); // param. parameterName = "username"; // param. sqlDbType = SqlDbType. char; // param. size = 10; // param. value = "admin"; // con. open (); // method 2. directly assign the cmd value to the parameter using the AddWithValue method. parameters. addWithValue ("@ userid", '1'); con. open (); SqlDataReader sdr = cmd. executeReader (); while (sdr. read () {Console. writeLine (sdr [0] + "" + sdr [1]);} Console. readLine () ;}}</span>

6. The MuHipleActiveResultSet method executes the multi-activity result set.

Generally, a Connection object only supports one active operation. If we need to add another or, add MuHipleActiveResultSet = True to the SQL Connection statement. I hope this blog post will help you.