一文带你了解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
}
作者:黑夜中的潜行者原文地址:https://blog.csdn.net/qq_43562262/article/details/129653556
0
分享 2023-03-20

%s 个评论

要回复文章请先登录或注册

关于我们 • 赞助与捐赠 • 站点地图 • 联系我们 • 网站合作 • 免责声明
友情链接： 新禾文化 • 开发者 • MySQL Workbench • 爱码帮 • 交换链接
本站为个人网站，部分内容来自于互联网，版权归原作者所有。如本站内容有侵权，敬请来信联系我们，我们将及时删除清理。
© 2024 爱码帮™分享编程知识和开发经验