Ado. NET Operation MySQL Database

Preface

Ado. NET includes 5 large objects, namely connection, Command, DataReader, DataSet, DataAdapter, using ADO to access the database has two steps: to establish a database connection, read or update data. There are two main ways of reading data: Command and DataReader, DataAdapter and DataSet.

Connection to MySQL database preparation 1. Download the free-install. zip link on the MySQL website as follows: Http://dev.mysql.com/downloads/connector/net/5.0.html,platform SELECT. Net      & Mono. 2. Unzip after download, unzip the file open V4.0 (the example in this blog is used in this version) 3. You can see some DLL files when you open them and add references to them in your project 4. The following example is the operation of the table Comment table in database test in MySQL. Step one: Establish a database connection 1. Define Global variables [CSharp]View PlainCopy

1. To create a Command object
2. private Mysqlcommand cmd = null;
3. Create a Connection Connection object
4. private Mysqlconnection conn = null;

2. Establishing a database connection [CSharp]View PlainCopy

1. Database connection string
2. String connstr = "Server=localhost;database =test;uid=root;pwd=1;charset=utf8";
3. Establishing a database connection
4. conn = new Mysqlconnection (CONNSTR);

Step two: Read Data 1.command and DataReader object read Data Description: The DataReader object provides a read-only forward pointer to the database retrieval data. DataReader is an abstract class that cannot be instantiated directly, so you need to create a command object before using it, and then create the DataReader object, which is read-only, so you cannot modify it. Process of reading data: ① Create connection ② Open connection ③ create command object ④ Execute command ExecuteReader () method ⑤ bind DataReader to Data Control ⑥ close Datareader⑦ Close The connection code shows: [CSharp]View PlainCopy

2. Mysqldatareader reader = null;
4. Try
6. {
8. Conn.    Open (); //② Opening a database connection
10. cmd = new Mysqlcommand ("select * from comment", conn); //③ Creating a SqlCommand object using the specified SQL command and connection object
12. reader = cmd. ExecuteReader (); //④ ExecuteReader () method to execute command
14. //⑤ binding DataReader to a Data control
16. DataTable dt = new DataTable ();
18. Dt. Load (reader);
20. Datagridview1.datasource = DT;
22. }catch (Exception) {
24. throw;
26. }finally{
28. //⑥ Close DataReader
30. Reader. Close ();
32. //⑦ Closing the connection
34. Conn. Close ();
36. }

[CSharp]View PlainCopy

2.DataSet and DataAdapter Object read Data Description: DataAdapter is the media between the dataset and the database, DataAdapter open a connection and execute the corresponding MySQL statement, the dataset is equivalent to a small database, You can store many tables, the dataset is a collection object, we can read the data in the dataset, and we can update the data in it. The process of reading data: ① Create a connection ② create a DataAdapter object ③ create a DataSet object ④ execute the DataAdapter object's fill () method ⑤ bind the table in the dataset to the code presentation in the Data control: [CSharp]View PlainCopy

2. Try
4. {
6. string mysqltext = "select * from comment;";
8. //Create a SqlDataAdapter object with the specified SQL command and connection object
10. Mysqldataadapter MYSDA = new Mysqldataadapter (MYSQLTEXT, conn);
12. DataSet ds = new DataSet (); //Create a DataSet object
16. //Use the SqlDataAdapter Fill method to populate the DataSet Mysda.  Fill (ds, "comment");
18. //bind a table in a dataset to a data control
20. BindingSource BS;
22. BS = new BindingSource ();
24. Bs. DataSource = ds.   tables["comment"];
26. Datagridview1.datasource = BS;
28. }
30. catch (Exception) {
32. throw;
34. }

[CSharp]View PlainCopy

Step three: Update data 1.command Object Update data Description: A transaction is a set of units of related tasks that either succeed or fail altogether. If they fail, all are rolled back, and the four properties (ACID) of the transaction are atomicity (atomicity), consistency (consistency), isolation (isolation), persistence (durability), respectively. Detailed information no longer repeat, do not know can Baidu a bit Oh! Code Show: [CSharp]View PlainCopy

2. Database connection string
4. String connstr = "Server=localhost;database =test;uid=root;pwd=1;charset=utf8";
8. Establishing a database connection
10. using (conn = new Mysqlconnection (CONNSTR))
12. {
14. Conn. Open ();
16. //Start a transaction
18. using (mysqltransaction transaction = conn. BeginTransaction ())
20. {
24. using (cmd=conn. CreateCommand ())
26. {
28. Try
30. {
32. Cmd.  Transaction = Transaction; //Specify a transaction for the command
34. Cmd.commandtext = "insert into comment (quantity,comment) value (' 11 ', ' very good ');";
36. Cmd. ExecuteNonQuery ();
38. Cmd.commandtext = "insert into comment (quantity,comment) value (' 12 ', ' very good ');";
40. Cmd. ExecuteNonQuery ();
42. Transaction.commit (); //Transaction Submission
46. }
48. catch (Exception)
50. {
54. Transaction. Rollback (); //Transaction rollback
58. }
60. }
62. }
64. }

2.DataAdapter Object Update Data [CSharp]View PlainCopy

2. <span style="FONT-SIZE:18PX;" >//Database connection string
4. String connstr = "Server=localhost;database =test;uid=root;pwd=1;charset=utf8";
6. //Establish database connection
8. conn = new Mysqlconnection (CONNSTR);
12. Try
14. {
16. //comment is the table name, quantity is the field name
18. string mysqltext = "select * from comment;";
20. Mysqldataadapter MYSDA = new Mysqldataadapter (MYSQLTEXT, conn);
22. DataSet ds = new DataSet ();
24. Mysda.  Fill (ds, "comment");
26. //Bind Mysqldataadapter object, automatically generate command to update MySQL from DataSet
28. Mysqlcommandbuilder cb = new Mysqlcommandbuilder (MYSDA);
30. //Update data in DS
32. Ds. tables["comment"].  rows[0]["Quantity"] = 111;
34. //Update data in the database
36. Mysda.  Update (ds, "comment");
40. }
42. catch (Exception)
44. {
46. throw;
48. }</span>

Summary 1.DataReader object read data in the database can only read, and is read-only, can not be modified; The data read by the dataset and the DataAdapter object is readable and can be modified, and many tables can be stored in the dataset. 2.DataReader read the data before the need to manually write code to connect the database, that is Conn.Open (), after the completion of the query need to manually write code to close the database connection; the DataSet and the DataAdapter object do not need to manually write code to connect to the database before reading the data, it will automatically identify If the database connection is not open, it is turned on and automatically shuts down if it is not turned off.

Ado. NET Operation MySQL Database