这篇文章主要讲解了“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的方法是什么这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。