1. Ado.net way to connect MySQL database
After searching for information on the web, there are three ways to connect MySQL databases on. NET:
Method One:
Use the MYSQL access component introduced by Corelab to reference namespaces Corelab.mysql
Method Two:
Access to the MySQL database through ODBC, before the first download two components: odbc.net and MySQL ODBC driver, you can use ODBC access to the MySQL database
Method Three:
Using MySQL to launch the MySQL connector/net is a ado.net driver for MySQL, this component for MySQL ado.net access to the MySQL database design of the. Net Access Components, so With MySql.Data.MySqlClient
The above three ways of description can be specific reference to the article:
Http://blog.csdn.net/lxh0959/archive/2008/08/05/2773424.aspx
This article gives a detailed description of this
Here's a third way to do this, which completely follows the pattern of our ado.net access to SQL Server, so it's more handy to use.
Ado.net Driver for MySQL download address:
Official website: http://www.mysql.com/downloads/
2. Basic Database Operations
Use Ado.net to manipulate the database, fixed the invariant pattern, five core objects
The Connection:connection object is primarily a link between the opening program and the database. Data cannot be obtained from the database without using the linked object to open the database.
Command:command objects can be used to send some instructions to the database, such as the database can be issued query, add, modify, delete data and other instructions, and call the existence of the database stored procedures.
The Dataadapter:datasetcommand object is primarily the work of data transfer between the source and the dataset, which can be placed through the command object and put the obtained data into the DataSet object.
Dataset:dataset This object can be regarded as a registers (Cache), which can keep the data queried from the database and even display the whole database. The ability of a dataset is not just to store multiple tables, but also to get some data table structures, such as primary keys, through the DataSetCommand object, and to record the associations between the data tables. The DataSet object can be said to be a heavyweight object in the Ado.net, which is not capable of communicating with the data source on the DataSetCommand object.
DataReader: We can use the DataReader object when we only need sequential reading of the data and no other action is required. The DataReader object simply reads data from the data source one at a time, and the data is read-only and does not allow other operations. Because DataReader restricts reading only one pen at a time while reading data, it is not only efficient but also cost-effective to use.
Copy Code code as follows:
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 ";
<summary>
Add data
</summary>
<returns></returns>
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 ();
}
}
<summary>
modifying data
</summary>
<param name= "entity" ></param>
<returns></returns>
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 ();
}
}
<summary>
Delete data
</summary>
<param name= "PrimaryKey" ></param>
<returns></returns>
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 ();
}
}
<summary>
Query based on PRIMARY key
</summary>
<param name= "PrimaryKey" ></param>
<returns></returns>
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;
}
}
<summary>
Query Collection
</summary>
<returns></returns>
Public ilist<userbase> 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<userbase> list = new list<userbase> ();
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 LINQ was real , Ado.net 's database operations made us feel the pain of programming, repeating the same database operation code every day, and then all sorts of code generators freed us from it, though some code generators were really good, But the heart always feel that the code is missing something!
After the introduction of LINQ to SQL, we felt the sweetness of the. NET syntax, the sweetness to the heart, the feeling of being completely released from the code! However , LINQ to SQL only supports Microsoft's SQL Server database.
Alinq is so perfect solves the problem of the multi type database operation, in the operationSQL ServerThe data is no lessLINQ to SQLInMysql，Oracle,db2And so on database operation is even more a chip!
First: new projects
Use the console program here
Second: Add New Item
Third: New database Connection
Fourth: Add Mysql database connection
Fifth: Choose Mysql data source
Sixth: Change the data source
Seventh: Enter the Mysql service name and account number and password
Eighth: Test the connection
Nineth: Successful Connection
The above operation is necessary to install the ado.net driver for MySQL component in order to operate normally, otherwise, you will not see the MySQL database option when you choose to change the data source in step sixth .
LINQ to SQL is already familiar to everyone, and the previous articles in Alinq's operation have been explained in detail. The operation here does not make the detailed explanation!
4. What do you want me to say about you ?
Mysql Database Open Source, more importantly, it can run in the Linux system! In the work of the SQL Server database to import data into MySQL , the unexpected things happen, Chinese in MySQL has become garbled. and MySQL is installed on the Linux system.
MySQL in Linux solution garbled problem Solution and operation steps:
1. Find the. CNF configuration file under mysql
Install the MySQL program in the Windows System , where the configuration file containing the saved data is named My.ini
This file exists in the MySQL installation directory, using Notepad to open the file changes are as follows:
Change Default-character-set=utf8 ( two places) to DEFAULT-CHARACTER-SET=GBK and Add nodes: CHARACTER_SET=GBK
The configuration file commands to find MySQL first in Linux are as follows:
The following results are obtained:
2. Copy a cnf file in Mysql to the etc file directory and save as my.cnf
Look for my.cnf files in the ETC directory , if this file does not exist, you need to my-large.cnf from the files found in 1.
MY-SMALL.CNF,MY-MEDIUM.CNF Copy One of the three files to the etc directory and save the my.cnf file.
To use the command:
cp/usr/share/mysql/my-large.cnf/etc/
MV Etc/my-large.cnf ETC/MY.CNF
The file is copied to a specific directory and renamed.
3. Modify my.cnf configuration file
CD etc/ into the ETC directory
VI my.cnf use VI command to open my.cnf file
If this node exists in the file, the encoding is modified to GBK
4. Restart Service
/etc/init.d/mysql Restart Restart the MySQL service
5. Import data again successfully
