ALinq makes Mysql so simple _ MySQL

ALinq makes Mysql so simple. when you are used to using. net to operate SQL Server, how many people have tried to use. net to operate Mysql databases! In the halo of. net, Mysql is so insignificant! However, the open source of Mysql is so tempting.

1. connect to the Mysql database using ADO. NET

After searching for information on the Internet, there are three methods to connect to the mysql database on. net:

Method 1:

Use the MYSQL access component released by CoreLab to reference the namespace CoreLab. MySql

Method 2:
To access the MYSQL database through ODBC, you must first download two components: the ODBC driver of odbc.net and MYSQL. you can use ODBC to access the Mysql database.

Method 3:
MYSQL Connector/Net is an ADO. NET driver for MySQL. this component is named ADO.. NET to access the MYSQL database.. NET access component, using MySql. data. mySqlClient

For details about the above three methods, refer to the article:

Http://blog.csdn.net/lxh0959/archive/2008/08/05/2773424.aspx

This article provides a detailed description of this article.

The third method is described here. this access method fully follows the mode in which we access SQL Server using Ado.net, so it is more handy to use.

ADO. NET driver for MySQL:

Http://www.mysql.com/downloads/

2. basic database operations

Using ADO.net for database operations, fixed mode, five core objects

Connection: The Connection object is mainly used to enable the Connection between the program and the database. You cannot obtain data from the database if you do not use the linked object to open the database.

Command: The Command object can be used to send commands to the database, such as sending query, addition, modification, and deletion commands to the database, as well as calling the stored programs in the database.

DataAdapter: the DataSetCommand object transfers data between data sources and DataSet. it can issue commands through the Command object and put the obtained data into the DataSet object.

DataSet: The DataSet object can be regarded as a Cache. it can retain the data queried from the database and even display the whole database. DataSet can not only store multiple tables, but also obtain data Table structures such as primary keys through the DataSetCommand object and record the association between data tables. DataSet object can be said to be a heavyweight object in ADO. NET. this object architecture is on the DataSetCommand object and cannot communicate with the data source itself.

DataReader: the DataReader object can be used to read data in sequence instead of other operations. The DataReader object only reads data from the data source in a descending order, and the data is read-only and does not allow other operations. Because DataReader only reads one row at a time and can only be read-only, it not only saves resources but also improves efficiency.

Using System;
Using System. Collections. Generic;
Using System. Linq;
Using System. Text;
Using MySql. Data. MySqlClient;

Namespace CimDataImport
{
Public class TestCharacter
{
Public const string mysqlConnection = "User Id = root; Host = localhost; Database = cim; password = root ";

///
/// Add data
///
///
Public int Add (UserBase entity)
{
String SQL = "INSERT INTO cimuser (userid, userNickName) VALUE (@ userid, @ userNickName )";
Using (MySqlConnection mycn = new MySqlConnection (mysqlConnection ))
{
Mycn. Open ();
MySqlCommand command = new MySqlCommand (SQL, mycn );
Command. Parameters. AddWithValue ("@ userid", entity. UserId );
Command. Parameters. AddWithValue ("@ userNickName", entity. UserNickName );
Return command. ExecuteNonQuery ();
}
}

///
/// Modify data
///
///
///
Public int Update (UserBase entity)
{
String SQL = "UPDATE cimuser SET userNickName = @ userNickName WHERE userid = @ userid ";
Using (MySqlConnection mycn = new MySqlConnection (mysqlConnection ))
{
Mycn. Open ();
MySqlCommand command = new MySqlCommand (SQL, mycn );
Command. Parameters. AddWithValue ("@ userid", entity. UserId );
Command. Parameters. AddWithValue ("@ userNickName", entity. UserNickName );
Return command. ExecuteNonQuery ();
}
}

///
/// Delete data
///
///
///
Public int Delete (int primaryKey)
{
String SQL = "DELETE FROM cimuser WHERE userid = @ userid ";
Using (MySqlConnection mycn = new MySqlConnection (mysqlConnection ))
{
Mycn. Open ();
MySqlCommand command = new MySqlCommand (SQL, mycn );
Command. Parameters. AddWithValue ("@ userid", primaryKey );
Return command. ExecuteNonQuery ();
}
}

///
/// Query by primary key
///
///
///
Public UserBase Get (int primaryKey)
{
String SQL = "SELECT userid, userNickName FROM cimuser where userid = @ userid ";
Using (MySqlConnection mycn = new MySqlConnection (mysqlConnection ))
{
Mycn. Open ();
MySqlCommand command = new MySqlCommand (SQL, mycn );
Command. Parameters. AddWithValue ("@ userid", primaryKey );
MySqlDataReader reader = command. ExecuteReader ();
UserBase userBase = null;
If (reader. Read ())
{
UserBase = new UserBase ();
UserBase. UserId = Convert. ToInt32 (reader ["userid"]);
UserBase. UserNickName = reader ["userNickName"] = DBNull. Value? Null: reader ["userNickName"]. ToString ();
}
Return userBase;
}

}

///
/// Query the set
///
///
Public IList GetList ()
{
String SQL = "SELECT userid, userNickName FROM cimuser limit 1, 10 ";
Using (MySqlConnection mycn = new MySqlConnection (mysqlConnection ))
{
Mycn. Open ();
MySqlCommand command = new MySqlCommand (SQL, mycn );
MySqlDataReader reader = command. ExecuteReader ();
IList List = new List ();
UserBase userBase = null;
While (reader. Read ())
{
UserBase = new UserBase ();
UserBase. UserId = Convert. ToInt32 (reader ["userid"]);
UserBase. UserNickName = reader ["userNickName"] = DBNull. Value? Null: reader ["userNickName"]. ToString ();
List. Add (userBase );
}
Return list;
}
}

}

}

3. ALinq makes me so simple

Before the advent of Linq, the database operations of Ado.net made us feel the pain of programming. we repeat the same database operation code every day. then, the strange code generator freed us from this, although some code generators are really good, they always feel that the code is missing!

After the release of Linq to SQL, we felt the sweetness of the. net syntax, the sweetness in our hearts, and we felt like we were completely releasing the code! However, Linq To SQL only supports Microsoft SQL Server databases.

Alinq is so perfect to solve the problem of multi-type database operations. it is no inferior to Linq to SQL in operating SQL Server data, and even better in Mysql, Oracle, Db2 and other database operations!

1. create a project

Use the console program

2. add a new item

Third: create a database connection

4. add a Mysql database connection

Fifth, select the Mysql data source

6. change the data source

7. enter the Mysql service name, account, and password.

Eighth: Test the connection

Ninth: connection successful

The preceding operations can be performed only when the ADO. NET driver for MySQL component is installed. Otherwise, the MySql database option is not displayed when you select to change the data source in step 6.

You are already familiar with the use of Linq to SQL. the previous articles on ALinq operations have also been described in detail. The operation here is not described in detail!

4. what do you want me to say about you?

Mysql database is open-source. More importantly, it can run in Linux! When I imported data from the SQL server database to Mysql at work, unexpected events occurred, and Chinese characters became garbled characters in Mysql. Mysql is installed on Linux.

Solution and Operation steps for Mysql to solve garbled characters in Linux:

1. find the. cnf configuration file in Mysql.

Install the mysql program in windows. the configuration file for saving the data is my. ini.

This file exists in the mysql installation directory. Use Notepad to open this file and modify the content as follows:

Change default-character-set = utf8 to default-character-set = GBK and add the node character_set = gbk.

In Linux, run the following command to find the mysql configuration file:

Find-name *. cnf-print

The following result is displayed:

2. copy a cnf file in Mysql to The etc file directory and save it as my. cnf

Find the my. cnf file in the etc Directory, if this file does not exist, you need to find the my-large.cnf from the file in 1,

My-small.cnf, copy one of the three my-medium.cnf files under the etc directory and save the my. cnf file.

Run the following command:

Cp/usr/share/mysql/ my-large.cnf/etc/

Mv etc./my-large.cnf etc/my. cnf

Copy the file to a specific directory and rename it.

3. modify the my. cnf configuration file

Cd etc/go to The etc directory

Vi my. cnf use the vi command to open the my. cnf file

If the node exists in the file, change the encoding method to gbk.

4. restart the service

/Etc/init. d/mysql restart mysql service

5. data is imported again.