C#操作MySql的方法是什么

这篇文章主要讲解了“C#操作MySql的方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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}

感谢各位的阅读，以上就是“C#操作MySql的方法是什么”的内容了，经过本文的学习后，相信大家对C#操作MySql的方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是编程网，小编将为大家推送更多相关知识点的文章，欢迎关注！