Asp.Net Core使用MySql.Data连接MySql示例

asp.net core使用mysql可使用开源组件MySql.Data

首先，加入依赖，在project.json中加入

"SapientGuardian.MySql.Data": "6.9.813",

代码：

using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Text;


/* 建表sql

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birthday`  datetime NULL DEFAULT NULL,
PRIMARY KEY(`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET = utf8 COLLATE=utf8_general_ci
  AUTO_INCREMENT = 2
ROW_FORMAT=COMPACT
;

 */

namespace MySql.Data_Demo
{
    public class Program
    {
        public static void Main(string[] args)
        {
            Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

            MySqlConnection connection = new MySqlConnection
            {
                ConnectionString = "server=127.0.0.1;database=test;uid=root;pwd=123456;charset='gbk'"
            };

            //增加一条数据
            //Insert(connection, "张三",DateTime.Now);

            //增加多条数据
            //InsertCollection(connection);

            //修改数据
            //Update(connection, "李四", "张三");

            //删除数据
            //Delete(connection, "李四");

            //查找数据
            //Search(connection);

            //简单事务
            //Transaction(connection);

            //执行存储过程
            //StoredProcedure(connection);

            Console.WriteLine("finish");
            System.Console.ReadKey();
        }

        /// <summary>
        /// 插入一条数据
        /// </summary>
        private static int Insert(MySqlConnection connection, string name, DateTime time)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand($"insert into person values (null,'{name}','{time}');", connection);
            int count = command.ExecuteNonQuery();
            Console.WriteLine(count);
            connection.Close();
            return count;
        }

        /// <summary>
        /// 插入多条数据
        /// </summary>
        private static void InsertCollection(MySqlConnection connection)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand();
            command.Connection = connection;

            command.CommandText = "INSERT INTO person VALUES ( null,?name, ?birthday)";
            command.Parameters.Add("?name", MySqlDbType.VarChar);
            command.Parameters.Add("?birthday", MySqlDbType.DateTime);

            for (int x = 0; x < 30; x++)
            {
                command.Parameters[0].Value = "name" + x;
                command.Parameters[1].Value = DateTime.Now;
                command.ExecuteNonQuery();
            }

            command.ExecuteNonQuery();
            connection.Close();
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        private static int Update(MySqlConnection connection, string newName, string oldName)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand($"update person set name = '{newName}' where name = '{oldName}';", connection);
            int count = command.ExecuteNonQuery();
            connection.Close();
            return count;
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        private static int Delete(MySqlConnection connection, string name)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand($"delete from person where name = '{name}';", connection);
            int count = command.ExecuteNonQuery();
            Console.WriteLine(count);
            connection.Close();
            return count;
        }

        /// <summary>
        /// 查找数据
        /// </summary>
        private static void Search(MySqlConnection connection)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand("select * from person;", connection);

            using (MySqlDataReader reader = command.ExecuteReader())
            {
                System.Console.WriteLine("id\t\tname");
                while (reader.Read())
                {
                    string row = $"{reader["id"]}\t\t{reader["name"]}";
                    System.Console.WriteLine(row);
                }
            }
        }

        /// <summary>
        /// 简单事务
        /// </summary>
        private static void Transaction(MySqlConnection connection)
        {
            connection.Open();
            MySqlTransaction transaction = connection.BeginTransaction();
            try
            {
                MySqlCommand cmd = new MySqlCommand("delete from person;", connection, transaction);
                cmd.ExecuteNonQuery();
                transaction.Rollback();
                transaction = connection.BeginTransaction();
                cmd = new MySqlCommand($"insert into person values (null,'王五','{DateTime.Now}');", connection, transaction);
                cmd.ExecuteNonQuery();
                transaction.Commit();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
            }
            connection.Clone();
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        private static void StoredProcedure(MySqlConnection connection)
        {
            connection.Open();
            MySqlCommand command = new MySqlCommand("存储过程名字", connection);
            command.CommandType = CommandType.StoredProcedure;
            IAsyncResult asyncResult = command.BeginExecuteNonQuery();
            connection.Close();
        }

    }
}

demo地址：
https://github.com/BinGithub2015/aspdotnetcore/tree/master/MySql.Data/MySql.Data_Demo

MySql.Data源码地址：
https://github.com/mysql/mysql-connector-net