The simple database operation of ado.net (three)-the practical application of simple addition, deletion, modification and check

The simple database operation of ado.net (three)-the practical application of simple addition, deletion, modification and check

  Sure enough, you should write a blog when you are sleepy, and blogging will not be sleepy, haha!

  In the last article, I recorded the development process of my own SqlHelper. Today, I will record how to use this sqlhelper to write a small example with simple additions, deletions, and changes.

  Example description: Create a new TbClass table in the database, read the contents of the table to a DataGridView, and add, delete, and modify the contents of the table on the winform panel. 1. the design of winform is given:

Let’s start writing the steps below, haha:

The first step: build the winform framework:

Make some modifications to its properties as shown in the figure. After the construction is completed, it is:

Step 2 : Write the code:

1. Data sheet introduction

First look at what my database looks like:

tClassId is self-incremented as the primary key and cannot be empty. tClassName is nvarchar and not empty. tClassDesc is nVarchar and can be empty.

Then determine the query statement, as: select * from TbClass;

2. Add SqlHelper, write data loading method

1. create the LoadData() method, and then call the LoadData method in the Form1_Load() method;

The LoadData method is written as follows:

 1 public void LoadData()
 2 {
 3//Declare a list collection
 4 List<TbClass> list = new List<TbClass>();
 5//Write sql statement
 6 string sql = "select * from TbClass";
 7//Adjust SqlHelper to implement query
 8 SqlDataReader reader = SqlHelper.ExecuteReader(sql);
 9 if (reader.HasRows)
10 {
11 while (reader.Read())
12 {
13//Create database object tbClass
14 TbClass tbClass = new TbClass();
15 tbClass.tClassId = reader.GetInt32(0);
16 tbClass.tClassName = reader.GetString(1);
17 tbClass.tClassDesc = reader.IsDBNull(2)? Null: reader.GetString(2);//The field in the data table is allowed to be empty, so a judgment is made here. If it is empty, the value will be null,
18 list.Add(tbClass);
19}
20}
21//Bind the list data to the dataGridView
22 this.dataGridView1.DataSource = list;
23 }

  Before using SqlHelper, you need to add configuration code in App.config and add references to the project . This is something you need to pay attention to, because there is a detailed explanation of how to add it in the previous blog, so I won't talk about it here.

Let's take a look at the result after reading the data:

3. Complete the function of adding a class

The added function is mainly to insert a record into the database, first write out the SQL statement:

sql = "insert into TbClass (tClassName,tClassDesc) Values('High 5 Class','Liberal Arts Class')";

Because the SQL statement passing parameters is needed here, the above statement is written as:

sql = "insert into TbClass (tClassName,tClassDesc) Values(@classname,@classdesc)"

Then we double-click the "Add" button on winform to enter the relevant method body boundary method:

Look at the code:

 1 private void button1_Click(object sender, EventArgs e)
 2 {
 3//1. Perform insert operation
 4 bool row = InsertClass();
 5 if (row)
 6 {
 7//2. Insert successfully, reload data
 8 MessageBox.Show("Added" + 1 + "Class");
 9 LoadData();
10}
11 else
12 {
13//Insert failure
14}
15           
16}
17 
18///<summary>
19///Perform the operation of inserting the added class into the database
20///</summary>
21///<returns> returns a boolean result, returns true if successful, otherwise false</returns>
22 private Boolean InsertClass()
23 {
24//1. Get the input value
25 string className = txtClassName.Text.Trim();
26 string classDesc = txtClassDesc.Text.Trim();
27//2.sql statement
28 string sql = "insert into TbClass (tClassName,tClassDesc) values ​​(@classname,@classdesc)";
29//3. Define the parameter array
30 SqlParameter[] parameters = new SqlParameter[] {
31 new SqlParameter("@classname",System.Data.SqlDbType.NVarChar,50){Value = className },
32 new SqlParameter("@classdesc",System.Data.SqlDbType.NVarChar,250){Value = classDesc}
33 };
34//4. Call SqlHelper and return the result
35 return SqlHelper.ExecuteNonQurey(sql,parameters)> 0;
36 
37}                    

Then look at the inserted result:

4. Modify and delete classes

(1) To select a row, the information of the row appears in the edit box below

To perform operations on a row, you must first select a row. Therefore, first change the SelectionMode in the properties of the dataGridView to FullRowSelect, as shown in the figure:

Then, when the mouse selects a row, get the tClassId of that row. Next, let’s take a look at how to get the id:

Find an event named RowEnter in the property page of dataGridView, double-click to enter the method, and write the function:

Then we look at the code:

 1 private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
 2 {
 3//Get the currently selected row object
 4 DataGridViewRow currentRow = this.dataGridView1.Rows[e.RowIndex];
 5 
 6//Get the value in the selected row object and store the value in TbClass
 7 TbClass model = currentRow.DataBoundItem as TbClass;
 8 
 9 if (model != null)
10 {
11 txtClassId.Text = model.tClassId.ToString();
12 txtEditClassName.Text = model.tClassName.ToString();
13 txtEditClassDesc.Text = model.tClassDesc.ToString();
14}
15             
16}

(3) Realize the modification operation

After obtaining the in-line information of a row, it becomes simple to modify and delete the row. It is also a few database operations. Below we double-click to save the modification and enter the modification button method body to write the code:

code show as below:

 1///<summary>
 2///Store the modified content in the database and reload the data
 3///</summary>
 4///<param name="sender"></param>
 5///<param name="e"></param>
 6 private void button2_Click(object sender, EventArgs e)
 7 {
 8//Get the modified data
 9 TbClass model = new TbClass();
10 int classId = Convert.ToInt32(txtClassId.Text);
11 string className = txtEditClassName.Text.Trim();
12 string classDesc = txtEditClassDesc.Text.Trim();
13//Determine and write sql statement
14 string sql = "update TbClass set tClassName = @classname, tClassDesc = @classdesc where tClassId = @clsssid";
15//Define the parameter array and assign values
16 SqlParameter[] parameters = new SqlParameter[] {
17 new SqlParameter("@clsssid",System.Data.SqlDbType.Int){ Value = classId},
18 new SqlParameter("@classname",System.Data.SqlDbType.NVarChar,50){Value = className },
19 new SqlParameter("@classdesc ",System.Data.SqlDbType.NVarChar,250){Value = classDesc}
20 };
21 int row = SqlHelper.ExecuteNonQurey(sql,parameters);
22 if (row> 0)
23 {
24 MessageBox.Show("Updated" +row + "row");
25 LoadData();
26}
27 else
28 {
29                     
30}
31 
32}

Demonstration results:

(4) Delete operation

The same statement is for operating the database, let's look at the code, haha:

 1 private void button3_Click(object sender, EventArgs e)
 2 {
 3//Get the tClassId of the current row
 4 int classId = Convert.ToInt32(txtClassId.Text);
 5//sql statement
 6 string sql = "delete from TbClass where tClassId = @classid";
 7//Parameter array
 8 SqlParameter[] parameters = new SqlParameter[] {
 9 new SqlParameter("@classid",System.Data.SqlDbType.Int){ Value = classId}
10 };
11//Execute sql statement
12 int row = SqlHelper.ExecuteNonQurey(sql,parameters);
13 if (row> 0)
14 {
15 MessageBox.Show("Delete" + row + "record");
16 LoadData();
17}
18 
19}

Result graph:

Well, some basic operations about ado.net are recorded here, I will learn mvc later, and I hope to make progress with other rookies.

My QQ: 3074596466

Reference: https://cloud.tencent.com/developer/article/1503490 ado.net's simple database operations (3)-the practical application of simple additions, deletions, and changes-Cloud + Community-Tencent Cloud