VS2010与mysql

Connecting to the Database from C#

What will allow us to work with the data in any MySQL database from C# is a reference to the MySql.Data assembly, which is registered into the Global Assembly Cache after the MySQL Connector/NET installation. First, create a new Console Application project in Visual C#. You may call it MySQLDBConnection or whatever name you decide. Now in the Solution Explorer within the Visual C# IDE, right click on the References folder and choose Add Reference... as shown below:

In the Add Reference dialog box that appears, select the MySQL.Data item from the list:

Now, after doing that, we must add the using MySql.Data.MySqlClient statement to our code in the Visual C# IDE:

Below you will find the basic lines of code needed to perform the connection to any MySQL database from C#:

Collapse | Copy Code

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;namespace MySQLDBConnection
{class Program{static void Main(string[] args){MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();connBuilder.Add("Database", "shop");connBuilder.Add("Data Source", "localhost");connBuilder.Add("User Id", "root");connBuilder.Add("Password", "masterkey");MySqlConnection connection = new MySqlConnection(connBuilder.ConnectionString);MySqlCommand cmd = connection.CreateCommand();connection.Open();// Here goes the code needed to perform operations on the
            // database such as querying or inserting rows into a table
connection.Close();}}
}

Notice that I've decided to use the MySqlConnectionStringBuilder class instead of putting all the connection items into a single string. This promotes better readability and maintainability of your code.

First, we start by adding a reference to the MySql.Data.MySqlClient namespace in our code. Then we create a MySqlConnectionStringBuilder instance and we add pairs of name/value items for the database name, data source, user ID and password. After that, we create an instance of the MySqlConnection class and we pass the ConnectionString from our MySqlConnectionStringBuilder instance as a parameter.

Let's create the following two methods within the Program class. One is for reading the contents of the table we are working with and the other is for appending new data into it.

Collapse | Copy Code

public static void QueryCommand(MySqlCommand cmd)
{cmd.CommandText = "SELECT * FROM article";cmd.CommandType = CommandType.Text;MySqlDataReader reader = cmd.ExecuteReader();while (reader.Read()){Console.WriteLine(String.Format("{0}, {1}, {2}",reader.GetInt32(0), reader.GetString(1), reader.GetDouble(2)));}reader.Close();
}public static void InsertCommand(MySqlCommand cmd, string name, double price)
{cmd.CommandText = "append_data";cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add(new MySqlParameter("param_name", name));cmd.Parameters.Add(new MySqlParameter("param_price", price));cmd.ExecuteNonQuery();
}

Now let's add some code between the connection.Open() and connection.Close() statements to perform some basic operations on the database.

Collapse | Copy Code

InsertCommand(cmd, "MQ95 Flat Monitor", 399.00);
QueryCommand(cmd);