这篇文章主要讲解了“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的方法是什么这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是***,小编将为大家推送更多相关知识点的文章,欢迎关注!