C # Fourth job: MySQL database and C # operate MySQL database,

C # Fourth job: MySQL database and C # operate MySQL database,

1. Basic MySQL database operations include the following two tasks:

(1) Insert data from two files into the MySQL database

(Note: when creating a database, select gb2312 -- GB2312 Simplified Chinese. Otherwise, it cannot be imported because it cannot be recognized)

Import list_class1.txt to mysql:

1. Add attributes to the data in the txt document

2. TXT file before the default encoding is ANSI, save as encoded as UTF-8 text file, so that the import of mysql will not garbled

3. Start Import

4. Import successful

Import list_class1_submit.xls to mysql:

The process is similar to importing a. txt file into mysql.

(2) Use the SQL command to find all the student lists that appear in "List_Class1" but not "List_Class1_Submit", and save or output ().

1. Set the encoding so that the database and cmd code are consistent. Otherwise, Chinese characters are garbled:
Set character_set_client = gbk;
Set character_set_results = gbk;
2. Select a database
Use cshape;
3. Enter the query statement:
Select list_class1. name from list_class1 where list_class1. Name not in (select name from list_class1_submit );

4. query results

2. C # operate databases

(1) connect to the related table in the database created in Job 1 and display it in the DataGridView control.

Note: Reference MySql. Data. dll in C #

Code:

[Csharp]View plaincopy

1. Using System;
2. Using System. Collections. Generic;
3. Using System. ComponentModel;
4. Using System. Data;
5. Using System. Drawing;
6. Using System. Linq;
7. Using System. Text;
8. Using System. Windows. Forms;
9. Using MySql. Data. MySqlClient;
10. Using System. Data. SqlClient;
12. Namespace mysqltest1
13. {
14. Public partial class Form1: Form
15. {
16. Public Form1 ()
17. {
18. InitializeComponent ();
19. }
20. Private void button#click (object sender, EventArgs e)
21. {
22. String strConn = "server = localhost; User Id = root; password =; Database = cshape; Charset = utf8 ";
23. MySqlConnection conn = new MySqlConnection (strConn );
24. Conn. Open (); // link to the database
25. DataSet ds = new DataSet ();
26. String SQL = "select * from list_class1_submit ";
27. MySqlDataAdapter da = new MySqlDataAdapter (SQL, conn );
28. Da. Fill (ds, "table ");
29. DataGridView1.DataSource = ds. Tables ["table"];
31. }
32. }
33. }

Running result:
Press the button key to connect to the list_class1_submit table in the mysql database cshape written in the code, which is displayed in the dataGridView

(2) C # programming language inserts, deletes, and modifies one or more records of a database.

Code:

[Csharp]View plaincopy

1. Using System;
2. Using System. Collections;
3. Using System. Configuration;
4. Using MySql. Data;
5. Using MySql. Data. MySqlClient;
6. Using System. Data;
8. Namespace mysqlconsletest
9. {
10. Class Program
11. {
12. Static void Main (string [] args)
13. {
14. String constr = "server = localhost; User Id = root; password =; Database = cshape; Charset = utf8 ";
15. MySqlConnection mycon = new MySqlConnection (constr );
16. Mycon. Open ();
18. MySqlCommand mycmd1 = new MySqlCommand ("insert into list_class1_submit (student ID, name, group, job URL) values ('20140901', 'liang Jiahui ', '1', 'HTTP: // blog.csdn.net/u010580422') ", mycon );
19. MySqlCommand mycmd2 = new MySqlCommand ("update list_class1_submit set student ID = '000000' where student ID = '000000'", mycon );
20. MySqlCommand mycmd3 = new MySqlCommand ("delete from list_class1_submit where student ID = '20140901'", mycon );
21. If (mydomain1.executenonquery ()> 0)
22. {
23. Console. WriteLine ("data inserted successfully! ");
24. }
25. If (mydomain2.executenonquery ()> 0)
26. {
27. Console. WriteLine ("data modified successfully! ");
28. }
29. If (mydomain3.executenonquery ()> 0)
30. {
31. Console. WriteLine ("data deleted successfully! ");
32. }
34. Console. ReadLine ();
35. Mycon. Close ();
37. }
38. }
39. }

Running result:

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.