一文带你了解C#操作MySql的方法

代码介绍

功能包含：

创建数据库
创建数据表
批量添加数据
MySql事务执行
清表
分页、模糊查询

代码实现

创建数据库

 
public void CreateDatabase(string sqlStr)
{
    string str = $"Server=localhost;User=root;Password=123456;CharSet=UTF8;";
    using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnection))
    {
        mySqlConnection.Open();
        try 
        {
            MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
            cmd.ExecuteNonQuery();
        } catch(Exception e) 
        {
            Debug.Log(e.Message.ToString());
        }
        finally
        {
            mySqlConnection.Close();
        }
    }
}

创建数据表

 
private static void CteateDataTable(string sqlStr)
{
    using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
    {
        mySqlConnection.Open();
        MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
        try
        {
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            mySqlConnection.Close();
        }
    }
}

查询数据

 
private static DataTable SelectTable(string sqlStr)
{
    DataTable dt = new DataTable();
    using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
    {
        mySqlConnection.Open();
        try
        {
            MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, mySqlConnection);
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            mySqlConnection.Close();
        }
    }
}

事务

 
private static bool ExecuteSqlTransaction(string sqlStr)
{
    using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
    {
        mySqlConnection.Open();
        MySqlCommand cmd = mySqlConnection.CreateCommand();
        cmd.Connection = mySqlConnection;
        MySqlTransaction sqlTransaction = mySqlConnection.BeginTransaction();
        try
        {
            cmd.CommandText = sqlStr;
            cmd.ExecuteNonQuery();
            sqlTransaction.Commit();
            sqlTransaction = mySqlConnection.BeginTransaction();
            return true;
        }
        catch (Exception ex)
        {
            sqlTransaction.Rollback();
            return false;
        }
        finally
        {
            mySqlConnection.Close();
        }
    };
}

代码示例

 
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using NPinyin;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
 
namespace ConsoleApp1
{
    internal class Program
    {
        private static string mySqlConnectStr = ConfigurationManager.AppSettings["connectionStr"];
 
        static void Main(string[] args)
        {
            CreateDatabase("CREATE DATABASE DataBaseName;");
 
            CreateTable();
 
            SQLCMD();
 
            DeleteTableDataAll();
 
            var drugData = SelectTable(@"SELECT * FROM `t_drugs` WHERE t_drugs.drug_name_py LIKE ""%PT%"" LIMIT 1,10;");
 
            List<Drug> drugs = new List<Drug>();
            foreach (DataRow item in drugData.Rows)
            {
                drugs.Add(new Drug
                {
                    hospital_no = item["hospital_no"].ToString(),
                    hospital_name = item["hospital_name"].ToString(),
                    drug_id = item["drug_id"].ToString(),
                    drug_name = item["drug_name"].ToString(),
                    drug_type = item["drug_type"].ToString(),
                    drug_short = item["drug_short"].ToString(),
                    sizes = item["sizes"].ToString(),
                    unit = item["unit"].ToString(),
                    price = item["price"].ToString(),
                    money_type = item["money_type"].ToString(),
                    producer = item["producer"].ToString(),
                    dose = item["dose"].ToString(),
                    usage = item["usage"].ToString(),
                    summary = item["summary"].ToString(),
                    ext = item["ext"].ToString(),
                });
            }
 
            DataTable projectData = SelectTable(@"SELECT * FROM `t_project` WHERE t_project.item_name_py LIKE ""%PT%"" LIMIT 1,10;");
 
            List<Project> project = new List<Project>();
            foreach (DataRow item in projectData.Rows)
            {
                project.Add(new Project
                {
                    hospital_no = item["hospital_no"].ToString(),
                    hospital_name= item["hospital_name"].ToString(),
                    item_id = item["item_id"].ToString(),
                    item_name = item["item_name"].ToString(),
                    item_type = item["item_type"].ToString(),
                    item_short = item["item_short"].ToString(),
                    sizes = item["sizes"].ToString(),
                    unit = item["unit"].ToString(),
                    price = item["price"].ToString(),
                    money_type = item["money_type"].ToString(),
                    ext = item["ext"].ToString(),
                });
            }
 
            Console.ReadKey();
        }
 
        public void CreateDatabase(string sqlStr)
        {
            string str = $"Server=localhost;User=root;Password=123456;CharSet=UTF8;";
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnection))
            {
                mySqlConnection.Open();
                try 
                {
                    MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
                    cmd.ExecuteNonQuery();
                } catch(Exception e) 
                {
                    Debug.Log(e.Message.ToString());
                }
                finally
                {
                    mySqlConnection.Close();
                }
            }
	    } 
 
        private static DataTable SelectTable(string sqlStr)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
            {
                mySqlConnection.Open();
                try
                {
                    MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, mySqlConnection);
                    da.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    mySqlConnection.Close();
                }
            }
        }
 
        /// <summary>
        /// 执行 插入药品、项目数据
        /// </summary>
        private static void SQLCMD()
        {
            #region 药品
 
            var drugjsonPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"drug.json");
            var drugJsonStr = File.ReadAllText(drugjsonPath);
            Rootobject<List<Drug>> drugs = JsonConvert.DeserializeObject<Rootobject<List<Drug>>>(drugJsonStr);
            string drugSql = @"INSERT INTO T_drugs (drug_id,drug_name,drug_type,sizes,unit,price,money_type,producer) VALUE ";
            foreach (var drug in drugs.data)
            {
                drugSql += $"(\"{drug.drug_id}\",\"{drug.drug_name}\",\"{drug.drug_type}\",\"{drug.sizes}\",\"{drug.unit}\",\"{drug.price}\",\"{drug.money_type}\",\"{drug.producer}\"),";
            }
            drugSql = $"{drugSql.Remove(drugSql.Length - 1, 1)};";
            if (ExecuteSqlTransaction(drugSql))
            {
                Console.WriteLine("执行成功！");
            }
            else
            {
                Console.WriteLine("执行失败！");
            }
 
            #endregion
 
            #region 项目
 
            var projectjsonPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"project.json");
            var projectJsonStr = File.ReadAllText(projectjsonPath);
            Rootobject<List<Project>> projects = JsonConvert.DeserializeObject<Rootobject<List<Project>>>(projectJsonStr);
            string projectSql = @"INSERT INTO T_project (item_id,item_name,unit,price) VALUE ";
            foreach (var project in projects.data)
            {
                projectSql += $"(\"{project.item_id}\",\"{project.item_name}\",\"{project.unit}\",\"{project.price}\"),";
            }
            projectSql = $"{projectSql.Remove(projectSql.Length - 1, 1)};";
            if (ExecuteSqlTransaction(projectSql))
            {
                Console.WriteLine("执行成功！");
            }
            else
            {
                Console.WriteLine("执行失败！");
            }
            #endregion
        }
 
        /// <summary>
        /// 创建 药品、项目表
        /// </summary>
        private static void CreateTable()
        {
            string t_drugSql = @"USE xzd;
                                CREATE TABLE IF NOT EXISTS T_drugs
                                (
	                                `hospital_no` VARCHAR(20),
	                                `hospital_name` VARCHAR(50),
	                                `drug_id` VARCHAR(50),
	                                `drug_name` VARCHAR(50),
	                                `drug_name_py` VARCHAR(50),
	                                `drug_type` VARCHAR(10),
	                                `drug_short` VARCHAR(10),
	                                `sizes` VARCHAR(10),
	                                `unit` VARCHAR(10),
	                                `price` VARCHAR(10),
	                                `money_type` VARCHAR(50),
	                                `producer` VARCHAR(100),
	                                `dose` VARCHAR(10),
	                                `usage` VARCHAR(10),
	                                `summary` VARCHAR(50),
	                                `ext` VARCHAR(50)
                                )ENGINE=INNODB DEFAULT CHARSET=utf8;";
 
            string t_project = @"USE xzd;
                                CREATE TABLE IF NOT EXISTS T_project
                                (
                                 `hospital_no` VARCHAR(20),
                                 `hospital_name` VARCHAR(50),
                                 `item_id` VARCHAR(50),
                                 `item_name` VARCHAR(50),
                                 `item_name_py` VARCHAR(50),
                                 `item_type` VARCHAR(10),
                                 `item_short` VARCHAR(10),
                                 `sizes` VARCHAR(10),
                                 `unit` VARCHAR(30),
                                 `price` VARCHAR(10),
                                 `money_type` VARCHAR(50),
                                 `ext` VARCHAR(50)
                                )ENGINE=INNODB DEFAULT CHARSET=utf8;";
 
 
            CteateDataTable(t_drugSql);
            CteateDataTable(t_project);
        }
 
        /// <summary>
        /// 执行创建表sql
        /// </summary>
        /// <param name="sqlStr"></param>
        private static void CteateDataTable(string sqlStr)
        {
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
            {
                mySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    mySqlConnection.Close();
                }
            }
        }
 
        /// <summary>
        /// mysql事务
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <exception cref="Exception"></exception>
        private static bool ExecuteSqlTransaction(string sqlStr)
        {
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
            {
                mySqlConnection.Open();
                MySqlCommand cmd = mySqlConnection.CreateCommand();
                cmd.Connection = mySqlConnection;
 
                MySqlTransaction sqlTransaction = mySqlConnection.BeginTransaction();
                try
                {
                    cmd.CommandText = sqlStr;
                    cmd.ExecuteNonQuery();
                    sqlTransaction.Commit();
                    sqlTransaction = mySqlConnection.BeginTransaction();
                    return true;
                }
                catch (Exception ex)
                {
                    sqlTransaction.Rollback();
                    return false;
                }
                finally
                {
                    mySqlConnection.Close();
                }
            };
 
        }
 
        /// <summary>
        /// 删除表所有数据
        /// </summary>
        /// <returns></returns>
        private static bool DeleteTableDataAll()
        {
            string sqlStr = @"DELETE FROM T_drugs; DELETE FROM T_project;";
            using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
            {
                mySqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
                try
                {
                    cmd.ExecuteNonQuery();                   
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                    throw new Exception(ex.Message);
                }
                finally
                {
                    mySqlConnection.Close();
                }
            }
        }
    }
 
    #region 实体
 
    /// <summary>
    /// 包装类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Rootobject<T>
    {
        public string code { get; set; }
        public T data { get; set; }
    }
 
    /// <summary>
    /// 药品
    /// </summary>
    public class Drug
    {
        public string hospital_no { get; set; }
        public string hospital_name { get; set; }
        public string drug_id { get; set; }
        public string drug_name { get; set; }
        public string drug_type { get; set; }
        public string drug_short { get; set; }
        public string sizes { get; set; }
        public string unit { get; set; }
        public string price { get; set; }
        public string money_type { get; set; }
        public string producer { get; set; }
        public string dose { get; set; }
        public string usage { get; set; }
        public string summary { get; set; }
        public string ext { get; set; }
    }
 
    /// <summary>
    /// 项目
    /// </summary>
    public class Project
    {
        public string hospital_no { get; set; }
        public string hospital_name { get; set; }
        public string item_id { get; set; }
        public string item_name { get; set; }
        public string item_type { get; set; }
        public string item_short { get; set; }
        public string sizes { get; set; }
        public string unit { get; set; }
        public string price { get; set; }
        public string money_type { get; set; }
        public string ext { get; set; }
    }
 
    #endregion
}

	public void CreateDatabase(string sqlStr)
	{
	string str = $"Server=localhost;User=root;Password=123456;CharSet=UTF8;";
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnection))
	{
	mySqlConnection.Open();
	try
	{
	MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
	cmd.ExecuteNonQuery();
	} catch(Exception e)
	{
	Debug.Log(e.Message.ToString());
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	private static void CteateDataTable(string sqlStr)
	{
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
	try
	{
	cmd.ExecuteNonQuery();
	}
	catch (Exception ex)
	{
	throw new Exception(ex.Message);
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	private static DataTable SelectTable(string sqlStr)
	{
	DataTable dt = new DataTable();
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	try
	{
	MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, mySqlConnection);
	da.Fill(dt);
	return dt;
	}
	catch (Exception ex)
	{
	throw new Exception(ex.Message);
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	private static bool ExecuteSqlTransaction(string sqlStr)
	{
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	MySqlCommand cmd = mySqlConnection.CreateCommand();
	cmd.Connection = mySqlConnection;
	MySqlTransaction sqlTransaction = mySqlConnection.BeginTransaction();
	try
	{
	cmd.CommandText = sqlStr;
	cmd.ExecuteNonQuery();
	sqlTransaction.Commit();
	sqlTransaction = mySqlConnection.BeginTransaction();
	return true;
	}
	catch (Exception ex)
	{
	sqlTransaction.Rollback();
	return false;
	}
	finally
	{
	mySqlConnection.Close();
	}
	};
	}

	using MySql.Data.MySqlClient;
	using Newtonsoft.Json;
	using NPinyin;
	using System;
	using System.Collections.Generic;
	using System.Configuration;
	using System.Data;
	using System.IO;
	using System.Text;

	namespace ConsoleApp1
	{
	internal class Program
	{
	private static string mySqlConnectStr = ConfigurationManager.AppSettings["connectionStr"];

	static void Main(string[] args)
	{
	CreateDatabase("CREATE DATABASE DataBaseName;");

	CreateTable();

	SQLCMD();

	DeleteTableDataAll();

	var drugData = SelectTable(@"SELECT * FROM `t_drugs` WHERE t_drugs.drug_name_py LIKE ""%PT%"" LIMIT 1,10;");

	List<Drug> drugs = new List<Drug>();
	foreach (DataRow item in drugData.Rows)
	{
	drugs.Add(new Drug
	{
	hospital_no = item["hospital_no"].ToString(),
	hospital_name = item["hospital_name"].ToString(),
	drug_id = item["drug_id"].ToString(),
	drug_name = item["drug_name"].ToString(),
	drug_type = item["drug_type"].ToString(),
	drug_short = item["drug_short"].ToString(),
	sizes = item["sizes"].ToString(),
	unit = item["unit"].ToString(),
	price = item["price"].ToString(),
	money_type = item["money_type"].ToString(),
	producer = item["producer"].ToString(),
	dose = item["dose"].ToString(),
	usage = item["usage"].ToString(),
	summary = item["summary"].ToString(),
	ext = item["ext"].ToString(),
	});
	}

	DataTable projectData = SelectTable(@"SELECT * FROM `t_project` WHERE t_project.item_name_py LIKE ""%PT%"" LIMIT 1,10;");

	List<Project> project = new List<Project>();
	foreach (DataRow item in projectData.Rows)
	{
	project.Add(new Project
	{
	hospital_no = item["hospital_no"].ToString(),
	hospital_name= item["hospital_name"].ToString(),
	item_id = item["item_id"].ToString(),
	item_name = item["item_name"].ToString(),
	item_type = item["item_type"].ToString(),
	item_short = item["item_short"].ToString(),
	sizes = item["sizes"].ToString(),
	unit = item["unit"].ToString(),
	price = item["price"].ToString(),
	money_type = item["money_type"].ToString(),
	ext = item["ext"].ToString(),
	});
	}

	Console.ReadKey();
	}

	public void CreateDatabase(string sqlStr)
	{
	string str = $"Server=localhost;User=root;Password=123456;CharSet=UTF8;";
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnection))
	{
	mySqlConnection.Open();
	try
	{
	MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
	cmd.ExecuteNonQuery();
	} catch(Exception e)
	{
	Debug.Log(e.Message.ToString());
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	private static DataTable SelectTable(string sqlStr)
	{
	DataTable dt = new DataTable();
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	try
	{
	MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, mySqlConnection);
	da.Fill(dt);
	return dt;
	}
	catch (Exception ex)
	{
	throw new Exception(ex.Message);
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	/// <summary>
	/// 执行插入药品、项目数据
	/// </summary>
	private static void SQLCMD()
	{
	#region 药品

	var drugjsonPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"drug.json");
	var drugJsonStr = File.ReadAllText(drugjsonPath);
	Rootobject<List<Drug>> drugs = JsonConvert.DeserializeObject<Rootobject<List<Drug>>>(drugJsonStr);
	string drugSql = @"INSERT INTO T_drugs (drug_id,drug_name,drug_type,sizes,unit,price,money_type,producer) VALUE ";
	foreach (var drug in drugs.data)
	{
	drugSql += $"(\"{drug.drug_id}\",\"{drug.drug_name}\",\"{drug.drug_type}\",\"{drug.sizes}\",\"{drug.unit}\",\"{drug.price}\",\"{drug.money_type}\",\"{drug.producer}\"),";
	}
	drugSql = $"{drugSql.Remove(drugSql.Length - 1, 1)};";
	if (ExecuteSqlTransaction(drugSql))
	{
	Console.WriteLine("执行成功！");
	}
	else
	{
	Console.WriteLine("执行失败！");
	}

	#endregion

	#region 项目

	var projectjsonPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"project.json");
	var projectJsonStr = File.ReadAllText(projectjsonPath);
	Rootobject<List<Project>> projects = JsonConvert.DeserializeObject<Rootobject<List<Project>>>(projectJsonStr);
	string projectSql = @"INSERT INTO T_project (item_id,item_name,unit,price) VALUE ";
	foreach (var project in projects.data)
	{
	projectSql += $"(\"{project.item_id}\",\"{project.item_name}\",\"{project.unit}\",\"{project.price}\"),";
	}
	projectSql = $"{projectSql.Remove(projectSql.Length - 1, 1)};";
	if (ExecuteSqlTransaction(projectSql))
	{
	Console.WriteLine("执行成功！");
	}
	else
	{
	Console.WriteLine("执行失败！");
	}
	#endregion
	}

	/// <summary>
	/// 创建药品、项目表
	/// </summary>
	private static void CreateTable()
	{
	string t_drugSql = @"USE xzd;
	CREATE TABLE IF NOT EXISTS T_drugs
	(
	`hospital_no` VARCHAR(20),
	`hospital_name` VARCHAR(50),
	`drug_id` VARCHAR(50),
	`drug_name` VARCHAR(50),
	`drug_name_py` VARCHAR(50),
	`drug_type` VARCHAR(10),
	`drug_short` VARCHAR(10),
	`sizes` VARCHAR(10),
	`unit` VARCHAR(10),
	`price` VARCHAR(10),
	`money_type` VARCHAR(50),
	`producer` VARCHAR(100),
	`dose` VARCHAR(10),
	`usage` VARCHAR(10),
	`summary` VARCHAR(50),
	`ext` VARCHAR(50)
	)ENGINE=INNODB DEFAULT CHARSET=utf8;";

	string t_project = @"USE xzd;
	CREATE TABLE IF NOT EXISTS T_project
	(
	`hospital_no` VARCHAR(20),
	`hospital_name` VARCHAR(50),
	`item_id` VARCHAR(50),
	`item_name` VARCHAR(50),
	`item_name_py` VARCHAR(50),
	`item_type` VARCHAR(10),
	`item_short` VARCHAR(10),
	`sizes` VARCHAR(10),
	`unit` VARCHAR(30),
	`price` VARCHAR(10),
	`money_type` VARCHAR(50),
	`ext` VARCHAR(50)
	)ENGINE=INNODB DEFAULT CHARSET=utf8;";


	CteateDataTable(t_drugSql);
	CteateDataTable(t_project);
	}

	/// <summary>
	/// 执行创建表sql
	/// </summary>
	/// <param name="sqlStr"></param>
	private static void CteateDataTable(string sqlStr)
	{
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
	try
	{
	cmd.ExecuteNonQuery();
	}
	catch (Exception ex)
	{
	throw new Exception(ex.Message);
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}

	/// <summary>
	/// mysql事务
	/// </summary>
	/// <param name="sqlStr"></param>
	/// <exception cref="Exception"></exception>
	private static bool ExecuteSqlTransaction(string sqlStr)
	{
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	MySqlCommand cmd = mySqlConnection.CreateCommand();
	cmd.Connection = mySqlConnection;

	MySqlTransaction sqlTransaction = mySqlConnection.BeginTransaction();
	try
	{
	cmd.CommandText = sqlStr;
	cmd.ExecuteNonQuery();
	sqlTransaction.Commit();
	sqlTransaction = mySqlConnection.BeginTransaction();
	return true;
	}
	catch (Exception ex)
	{
	sqlTransaction.Rollback();
	return false;
	}
	finally
	{
	mySqlConnection.Close();
	}
	};

	}

	/// <summary>
	/// 删除表所有数据
	/// </summary>
	/// <returns></returns>
	private static bool DeleteTableDataAll()
	{
	string sqlStr = @"DELETE FROM T_drugs; DELETE FROM T_project;";
	using (MySqlConnection mySqlConnection = new MySqlConnection(mySqlConnectStr))
	{
	mySqlConnection.Open();
	MySqlCommand cmd = new MySqlCommand(sqlStr, mySqlConnection);
	try
	{
	cmd.ExecuteNonQuery();
	return true;
	}
	catch (Exception ex)
	{
	return false;
	throw new Exception(ex.Message);
	}
	finally
	{
	mySqlConnection.Close();
	}
	}
	}
	}

	#region 实体

	/// <summary>
	/// 包装类
	/// </summary>
	/// <typeparam name="T"></typeparam>
	public class Rootobject<T>
	{
	public string code { get; set; }
	public T data { get; set; }
	}

	/// <summary>
	/// 药品
	/// </summary>
	public class Drug
	{
	public string hospital_no { get; set; }
	public string hospital_name { get; set; }
	public string drug_id { get; set; }
	public string drug_name { get; set; }
	public string drug_type { get; set; }
	public string drug_short { get; set; }
	public string sizes { get; set; }
	public string unit { get; set; }
	public string price { get; set; }
	public string money_type { get; set; }
	public string producer { get; set; }
	public string dose { get; set; }
	public string usage { get; set; }
	public string summary { get; set; }
	public string ext { get; set; }
	}

	/// <summary>
	/// 项目
	/// </summary>
	public class Project
	{
	public string hospital_no { get; set; }
	public string hospital_name { get; set; }
	public string item_id { get; set; }
	public string item_name { get; set; }
	public string item_type { get; set; }
	public string item_short { get; set; }
	public string sizes { get; set; }
	public string unit { get; set; }
	public string price { get; set; }
	public string money_type { get; set; }
	public string ext { get; set; }
	}

	#endregion
	}

一文带你了解C#操作MySql的方法

目录

代码介绍

代码实现

创建数据库

创建数据表

查询数据

事务

代码示例