[MrYoung Tutorial: easy to learn] addition, repair, deletion, and query of 3ADO. NET basic data

I. Previous Review
In the previous article, we introduced the basic usage of SQLDATAADAPTER and DATASET as a data display form. It is a set of cache data in our memory, this section describes how to bind data to our form control and how to obtain the value of a specified cell in the dview.

Ii. Overview
In this article, we will focus on how to add, repair, delete, and query data by concatenating strings, and introduce the basic principles of SQL injection.

Iii. Main Content
3.1 Data addition, repair, and deletion

3.2 LIKE operator and record Query

3.3 SQL Injection principles

4. Data addition, repair, and deletion
First, drag the following control in our frmMain form and rename it:

4.1 Add record: We first add data, but after you enter the correct name, password, age, and other information in the text box above, click the Add button, we store the data in the database. Obtain the values of various text boxes and splice them into correct SQL statements, such as insert into tb_user values (name, password, age .......), then call the SQL statement for execution. The specific code is as follows:

View sourceprint? 01 private void btn Add _ Click (object sender, EventArgs e)

02 {

03 // concatenate the SQL string into the final insert statement

04 string SQL = "insert into tb_user (username, userpassword, userage, userphone, useraddress )"

05 + "values (" + txtUserName. text + "," + txtPWD. text + "," + txtAge. text + "," + txtPhone. text + "," + txtAddress. text + ")";

06 // call the database operation method execution statement

07 BaseClass. BaseOperate. getcom (SQL );

08 // a dialog box is displayed, prompting you that the operation is successful.

09 MessageBox. Show ("User Added successfully! ");

10 // re-execute the query and bind the data form to display the new content, which is equivalent to refreshing the Data Form

11 ds = BaseClass. BaseOperate. getds ("select id as number, username as name, userpassword as password, userage as age, userphone number, useraddress as address from tb_user ");

12 dataGridView1.DataSource = ds. Tables [0];

13 // The value of each input box

14 reset ();

15 // place the cursor in the first ID input box

16 txtID. Focus ();

17}

18 void reset ()

19 {

20 txtAddress. Text = "";

21 txtAge. Text = "";

22 txtID. Text = "";

23 txtPhone. Text = "";

24 txtPWD. Text = "";

25 txtUserName. Text = "";

26}

　　

The getcom method in BaseOperate is used to perform insert, modify, and delete operations:

 

View sourceprint? 01 /// <summary>

02 // execute the SQLCOMMAND

03 // </summary>

04 // <param name = "str_ SQL"> SQL statement to be executed </param>

05 public static void getcom (string str_ SQL)

06 {

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

08 {

09 using (SqlCommand sqlcom = new SqlCommand (str_ SQL, sqlcon ))

10 {

11 try

12 {

13

14 sqlcon. Open ();

15 sqlcom. ExecuteNonQuery ();

16}

17 catch (Exception e1)

18 {

19 sqlcon. Close ();

20 throw new Exception (e1.Message );

21}

22

23}

24}

25}

　　

4.2 modification and deletion methods are similar. They are used to obtain the ID information of the row selected by the user in dataGridView1. The ID is used because the ID is the unique identifier of each record in the database) perform operations on Specific Row data in the database.

 

View sourceprint? 01 private void btn modify _ Click (object sender, EventArgs e)

02 {

03 // obtain the User Name of the row selected

04 string strname = Convert. ToString (maid [1, maid. RowIndex]. Value );

05 // the pop-up dialog box prompts you whether to modify the information. If you click OK, perform the modification.

06 if (MessageBox. Show ("are you sure you want to modify" + strname + "User? "," Prompt ", MessageBoxButtons. OKCancel, MessageBoxIcon. Information) = DialogResult. OK)

07 {

08 string SQL = "update tb_user set username =" + txtUserName. Text + ", userpassword =" + txtPWD. Text

09 + ", userage =" + txtAge. Text + ", userphone =" + txtPhone. Text + ", useraddress =" + txtAddress. Text + "where id ="

10 + Convert. ToString (dataGridView1 [0, dataGridView1.CurrentCell. RowIndex]. Value );

11 BaseClass. BaseOperate. getcom (SQL );

12

13 MessageBox. Show ("user modified successfully! ");

14

15 ds = BaseClass. BaseOperate. getds ("select id as number, username as name, userpassword as password, userage as age, userphone number, useraddress as address from tb_user ");

16 dataGridView1.DataSource = ds. Tables [0];

17 reset ();

18 txtID. Focus ();

19}

20}

 

View sourceprint? 01 private void btn Delete _ Click (object sender, EventArgs e)

02 {