一文带你了解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内容请搜索编程宝库以前的文章,希望以后支持编程宝库

 1.调用Win32 API优先级最高,全局监听, 支持最小化失焦等情况那么,假如我要在一个WPF程序监听CTRL+5按键,首先在主窗口程序添加以下代码: /// <summary> ...