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