C # Simple example of operating MySQL database this example demonstrates how to operate MySQL using C #. It provides three Reusable Classes: MySqlDBUtil, MySqlPageUtil, and Page.
This example is modified by a simple example of C # operating the Access database.
1. first download the. NET driver of the MySQL database
Http://dev.mysql.com/get/Downloads/Connector-Net/mysql-connector-net-5.0.8.1-noinstall.zip/from/pick#mirrors
Copy the MySql. Data. dll file in the bin directory to the project directory and add the dll reference to the project. in this way, you can operate the MySQL database by using related classes.
Using MySql. Data. MySqlClient;
Using System. Data;
Connection String: String connectionString = "server = 127.0.0.1; user id = root; password = as; database = 3tvs; pooling = false; charset = utf8 ";
The meaning of each item in the connection string is clear. Note that charset should be set to be consistent with the charset of the database table. Otherwise, Chinese characters may be garbled.
Then replace OleDb in the entire project with MySql, which basically works. The difference is that SQL statements with parameters,
Access is
String SQL = "insert into product (name, quantity, price, sale_date, checked_flag) values (?,?,?,?,?) ";
Parameters [0] = new MySqlParameter ("@ name", MySqlDbType. VarChar, 100 );
Change to MySql
String SQL = "insert into product (name, quantity, price, sale_date, checked_flag) values (? Name ,? Quantity ,? Price ,? Sale_date ,? Checked_flag )";
Parameters [0] = new MySqlParameter ("? Name ", MySqlDbType. VarChar, 100 );
2. obtain the id value of the MySQL auto-incrementing id field after data is inserted.
Cmd. CommandText = @ "select @ identity ";
Int value = Int32.Parse (cmd. ExecuteScalar (). ToString ());
Return value;
Use cmd. CommandText = @ "select LAST_INSERT_ID ()"; the results are the same. the differences between the two are not studied.
3. paging query
Use the Limit clause to process paging queries. in this way, the code is greatly simplified and the efficiency is also improved compared with Access paging queries.
Code Download mysqldbutildemo.rar (198.41 KB, download: 4166 times)
This routine is a c # winform program, but the data pipeline class can be used in the Web environment.
Create a table using the SQL statement file in bin/Release and modify the connection string in MySqlDBUtil before running.
This routine demonstrates:
1. INSERT, UPDATE, modify, and query the MySQL database;
2. use of SQL statements with parameters instead of spelling SQL statements;
3. paging query using the Limit clause;
4. execute multiple SQL statements simultaneously using transactions;
5. return the latest ID value while inserting data;
6. integer, real, string, date, and Boolean data type operations;
7. use regular expressions to verify integers and real numbers;
8. some basic usage of listview for displaying data.
This example does not include:
1. the complete paging encapsulation only provides simple paging packaging.
2. nested transaction processing, which allows you to execute multiple SQL statements at the same time, but does not support nested transactions.
3. the use of listview only uses the winform control to demonstrate data access. Therefore, it cannot be used as a good example of winform programming. for example, the interface is not well updated when adding data.
Postscript:
Because the APIs are the same, it is easy to change the class used to operate the Access database to the class used to operate the MySQL database. using these two examples, you can learn the basic database operations, it can also be used for some simple applications. There are mature open-source projects available in actual projects, such as iBATIS. NET and nhib.pdf. these projects are built on these basic APIs. learning these two examples helps you understand these projects.
A little easier on http://www.3tvs.info
