[MrYoung Tutorial: easy to learn] parameter query based on 4ADONET

I. Previous Review
In the previous section, we explained the database addition, repair, deletion, and query operations, and introduced the notorious SQL Injection in an example.

Ii. Overview
In this lesson, we will discuss how to use parameterized queries to solve the problem of SQL injection and rewrite all our methods.

Iii. Main Content
3.1 advantages of parameterized Query.

3.2 SqlCommand attributes.
3.3 SqlDataAdapter.

3.4 Conclusion.

Iv. Advantages
Prevent SQL injection. by assigning values to parameters, you can compare the entire parameter as a value. ADO. NET will escape some special characters instead of concatenating strings.

V. SqlCommand attributes
5.1 SqlCommand. Connection attribute: gets or sets the Connection used by this instance of SqlCommand.
 
1 SqlConnection sqlcon = new SqlConnection (connectionString); 2 SqlCommand sqlcom = new SqlCommand (); 3 sqlcom. Connection = sqlcon;
5.2 SqlCommand. CommandText attribute: gets or sets the SQL statement to be executed on the data source.
 
View sourceprint? 1 sqlcom. CommandText = "select * from tb_user where UserName = @ u and <A href = mailto: UserPassWord = @ p";> UserPassWord = @ p ";

2 </A>

 
 
5.3 SqlCommand. Parameters attributes: get the list of set Parameters. Indicates the set of associated parameters.
 
View sourceprint? 1 sqlcom. Parameters. Add ("username", SqlDbType. VarChar, 50). Value = "MrYoung ";

 
The preceding example adds a parameter for PARAMETERS of SQLCOM. The parameter name is username, and SqlDbType. VarChar is the database Field Type of the parameter. The length is 50. VALUE is assigned to MrYoung.

Next we will rewrite our add, repair, and delete methods respectively as follows:

 
View sourceprint? 001 // <summary>

002

003 // insert data

004

005 /// </summary>

006

007 // <param name = "username"> </param>

008

009 // <param name = "userpassword"> </param>

010

011 /// <param name = "userage"> </param>

012

013 /// <param name = "userphone"> </param>

014

015 /// <param name = "useraddress"> </param>

016

017 public static void InsertData (string username, string userpassword, int userage, string userphone, string useraddress)

018

019 {

020

021 // instantiate the connection object

022

023 using (SqlConnection sqlcon = new SqlConnection (connectionString ))

024

025 {

026

027 // instantiate SQLCOMMAND. Note that it is the default null constructor.

028

029 using (SqlCommand sqlcom = new SqlCommand ())

030

031 {

032

033 // specify the SQLCONNECTION

034

035 sqlcom. Connection = sqlcon;

036

037 // set the query statement

038

039 sqlcom. CommandText = "insert into tb_user values (@ username, @ userpassword, @ userage, @ userphone, @ useraddress )";

040

041 // Add Parameters to Parameters and copy

042

043 sqlcom. Parameters. Add ("username", SqlDbType. VarChar, 50). Value = username;

044

045 sqlcom. Parameters. Add ("userpassword", SqlDbType. VarChar, 50). Value = userpassword;

046

047 sqlcom. Parameters. Add ("userage", SqlDbType. Int, 4). Value = userage;

048

049 sqlcom. Parameters. Add ("userphone", SqlDbType. VarChar, 50). Value = userphone;

050

051 sqlcom. Parameters. Add ("useraddress", SqlDbType. VarChar, 50). Value = useraddress;

052

053 try

054

055 {

056

057

058

059 sqlcon. Open ();

060

061 sqlcom. ExecuteNonQuery ();

062

063}

064

065 catch (Exception e1)

066

067 {

068

069 sqlcon. Close ();

070

071 throw new Exception (e1.Message );

072

073}

074

075

076

077}

078

079}

080

081}

082

083

084

085

086

087 // <summary>

088

089 // modify data

090

091 /// </summary>

092

093 // <param name = "username"> </param>

094