C# 连接数据库

1、安装驱动

下载地址：http://dev.mysql.com/downloads/connector/net/

默认是安装在C盘：C:Program FilesMySQLMySQL Connector Net 6.9.9

其中，C:Program FilesMySQLMySQL Connector Net 6.9.9Assemblies下有v4.0和v4.5。

(VS2010使用V4.0下的dll文件
VS2012/2013/2015使用v4.5下的dll文件)

其中有一个帮助手册十分有用：

Documentation文件夹下的ConnectorNET.chm中包含了连接mysql数据库的API。

安装完后我们选择使用v4.0。然后在应用工程中引用组件MySql.Data.dll。

 

2、把MySql.Data.dll引进去

写上using MySql.Data.MySqlClient;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {

            //VerifyUSser("root", "123456");
           
            //InsertSQL();
            DeleteSQL();
           // UpdateSQL();
            ReadSQL();
            Console.ReadKey();
        }

        /// <summary>
        /// 读取数据库
        /// </summary>
        static void ReadSQL()
        {
            string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=123456;SslMode=none;charset=utf8";
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {
                conn.Open();    //建立连接，打开数据库
                Console.WriteLine("打开数据库成功");
                string sqlstr = "select * from student";
                MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
                MySqlDataReader reader = cmd.ExecuteReader();
                //reader.Read();  //读取下一页数据 ，读取成功，返回true，下一页没有数据则返回false表示到了最后一页
                while (reader.Read())   //遍历表中数据
                {
                    int sno = reader.GetInt32(reader.GetOrdinal("sno"));
                    string name = reader.GetString(reader.GetOrdinal("name"));
                    string sex = reader.GetString(reader.GetOrdinal("sex"));
                    int age = reader.GetInt32(reader.GetOrdinal("age"));
                    Console.WriteLine(sno + " " + name + " " + sex + " " + age);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();   //关闭连接
            }
        }

        static bool VerifyUSser()
        {
            string username = "";
            string password = "";
            string server = "";
            string port = "";
            string database = "";
            string connectStr = "server=" + server + ";"
                                + "port=" + port + ";"
                                + "database=" + database + ";"
                                + "username=" + username + ";"
                                + "password=" + password + ";"
                                + "SslMode=none" //防止报sll connecttion 错误
                                + "charset=utf8"; //可以插入中文
            MySqlConnection conn = new MySqlConnection(connectStr);   //建立数据库连接
            try
            {
                conn.Open(); //建立连接，打开数据库
                string sqlstr = "select * from student";
                MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
                //通过设置参数的形式给SQL 语句串值
                cmd.Parameters.AddWithValue("para1", username);
                cmd.Parameters.AddWithValue("para2", password);

                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();   //关闭连接              
            }
            return false;
        }


        /// <summary>
        /// 插入数据到数据库中
        /// </summary>
        static void InsertSQL()
        {
            string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=123456;SslMode=none;charset=utf8";
            //并没有建立数据库连接
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {   //建立连接，打开数据库
                conn.Open();
                //注意一条SQL语句不要一条语句执行两次，会因为已存在而报错
                string sqlstr = "insert into student(sno,name,sex,age) values(1105,"sdfd","男",321)";   //SQL语句
                MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
                //int result = cmd.ExecuteNonQuery();   //返回值为执行后数据库中受影响的数据行数
                //Console.WriteLine("执行成功，影响了{0}行数据", result);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();//关闭连接
            }
        }

        /// <summary>
        /// 修改数据库中数据
        /// </summary>
        static void UpdateSQL()
        {
            string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=123456;SslMode=none;charset=utf8";
            //并没有建立数据库连接
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {   //建立连接，打开数据库
                conn.Open();
                string sqlstr = "update student set age=28,name='sdsffcc' where sno=1105";   //SQL语句
                MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
                int result = cmd.ExecuteNonQuery();   //返回值为执行后数据库中受影响的数据行数
                Console.WriteLine("执行成功，影响了{0}行数据", result);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();   //关闭连接
            }
        }

        /// <summary>
        /// 删除数据库中的数据
        /// </summary>
        static void DeleteSQL()
        {
            string connectStr = "server=127.0.0.1;port=3306;database=test;user=root;password=123456;SslMode=none;charset=utf8";
            //并没有建立数据库连接
            MySqlConnection conn = new MySqlConnection(connectStr);
            try
            {   //建立连接，打开数据库
                conn.Open();
                //注意一条SQL语句不要一条语句执行两次，会因为已存在而报错
                string sqlstr = "Delete from student where sno = 1105";   //SQL语句
                MySqlCommand cmd = new MySqlCommand(sqlstr, conn);
                int result = cmd.ExecuteNonQuery();   //返回值为执行后数据库中受影响的数据行数
                Console.WriteLine("执行成功，影响了{0}行数据", result);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                conn.Close();   //关闭连接
            }
        }
    }
}