. NET + MySQL combined development (2) Data Access

1. create databases, tables, and add data
Here, we use the graphical SQL Manager 2005 Lite for MySQL to create data. Its operation interface is very similar to office software. It is easy to use and easy to use. Below we start to create databases and tables.
Click "creat new database": Create Database

Enter the password:


Select the client code as gb2312 to prevent Garbled text. You can also right-click the new database and choose "database registration info" to change the encoding:

New table: name of the input table:

New Field

Click the plus sign to manually add data, click the right sign, and submit the data:

Select the DDL option to directly view the script you just operated. Alternatively, you can directly write an SQL script to create data without using the above operations:

Ii. Ado. NET data operations
MySQL connector net 5.0.3 is recommended
Add a connection string in Web. config:<Add name="Mysqlserver"Connectionstring="Data Source = 127.0.0.1; user id = root; Password = 123; database = book; charset = gb2312"/>

For convenience, data access is encapsulated into a class: 1 // Execute SQL
2 Public   Int Executesql ( String Strsql, mysqlparameter [] mypar)
3 {
4 Try
5 {
6 Myconnection. open ();
7 Mysqlcommand cmd =   New Mysqlcommand (strsql, myconnection );
8 If (Mypar ! =   Null )
9 {
10 Foreach (Mysqlparameter spar In Mypar)
11 {
12Cmd. Parameters. Add (SPAR );
13}
14 }
15 Int Result = Cmd. executenonquery ();
16 Myconnection. Close ();
17 Return Result;
18 }
19 Catch
20 {
21Return 0;
22}
23 }

Get data: 1   Public Dataset getdataset ( String Strsql)
2 {
3 Try
4 {
5 Mysqldataadapter da =   New Mysqldataadapter (strsql, myconnection );
6 Dataset DS =   New Dataset ();
7 Da. Fill (DS );
8 Return DS;
9 }
10 Catch
11 {
12Return Null;
13}
14 }

On the page, we use a gridview to read, write, edit, and delete data:

Data Binding: 1   // Databind
2 Protected   Void Bindgrid ()
3 {
4 Dataset DS = OBJ. getdataset ( " Select * from book order by bid " );
5 Gridview1.datasource = DS;
6 Gridview1.databind ();
7 DS. Dispose ();
8 }

Add data: 1   String Strsql =   " Insert into book (bname, author, publish) values (? Bname ,? Author ,? Publish) " ;
2 Mysqlparameter [] mysp =
3 {
4 New Mysqlparameter ( " ? Bname " , Mysqldbtype. varchar ),
5 New Mysqlparameter ( " ? Author " , Mysqldbtype. varchar ),
6 New Mysqlparameter ( " ? Publish " , Mysqldbtype. varchar)
7 } ;
8 Mysp [ 0 ]. Value = Txtname. Text. Trim ();
9 Mysp [ 1 ]. Value = Txtauthor. Text. Trim ();
10 Mysp [ 2 ]. Value = Txtpublish. Text. Trim ();
11 If (Obj. executesql (strsql, mysp) =   1 )
12 {
13 Response. Write ( " <SCRIPT> alert ('submitted successfully'); </SCRIPT> " );
14 Bindgrid ();
15 Txtname. Text = Txtauthor. Text = Txtpublish. Text =   "" ;
16 }

note that the parameter symbol is "? "Instead "@", this is different from SQL server
other operations such as editing Code . Please download the file
for details code File/files/chy710/mysql_adonet.rar
next article: garbled characters in MySQL development