最近项目用到C# 以及MariaDB相关知识，在此小计一下。
关于MariaDB的安装使用参照以下连接文档：
MariaDB安装与使用_HeidiSQL工具_IT_wind007的博客-CSDN博客
自带的HeidiSQL工具觉得界面不太友好，于是自己下载了一个Navicat进行操作，相关操作参照以下连接文档：
Navicat使用快速入门教程_苜苜的烂笔头的博客-CSDN博客_navicat使用教程详解
以上步骤完成以后，打开C#工程，C#工程中的“引用”——添加——扩展——勾选MySql.Data，确认即可使用
在需要使用数据库操作的文件头添加 using MySql.Data.MySqlClient;
即可调用相关操作。
1、数据库连接
1 public bool ConnectToSql()
2 {
3
4 // 对象转字符串方法
5 MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
6 builder.Database = "plc_control"; // 数据库名
7 builder.Server = "localhost"; // IP
8 builder.Port = 3306; // 端口号
9 builder.UserID = "root"; // 用户名
10 builder.Password = "root"; // 密码
11 builder.CharacterSet = "utf8mb4"; // 编码格式
12
13 if (null == conn)
14 {
15 conn = new MySqlConnection(builder.ToString());
16 }
17
18 try
19 {
20 conn.Open();
21
22 //在open之后调用就可以避免creatTable报错
23 MySqlCommand setcmd = new MySqlCommand("SET character_set_results=utf8mb4", conn);
24 int n = setcmd.ExecuteNonQuery();
25 setcmd.Dispose();
26
27 Console.WriteLine("连接成功!!!");
28
29 return true;
30 }
31 catch (Exception e1)
32 {
33 Console.WriteLine("打印抛出：" + e1.ToString() + builder.ToString());
34 return false;
35 }
36 }
2、数据库关闭
1 public void CloseConn()
2 {
3 if (null != conn)
4 {
5 if (conn.State == ConnectionState.Open)
6 {
7 conn.Close();
8 Console.WriteLine("正常关闭!!!");
9 }
10 }
11 }
3、创建（create table）
1 private void CreatActionLogTable()
2 {
3 string strCreat = string.Format(@"CREATE TABLE IF NOT EXISTS actionlog(
4 `time` datetime DEFAULT NULL,
5 `user` varchar(24) DEFAULT NULL,
6 `log` varchar(200) DEFAULT NULL
7 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;");
8
9 ExecuteQuery(strCreat);
10 }
11
12
13 private void ExecuteQuery(string strCreat)
14 {
15 try
16 {
17 MySqlCommand myCmd = new MySqlCommand(strCreat, conn);
18 myCmd.ExecuteNonQuery();
19 }
20 catch (MySqlException ex) {
21 Console.WriteLine("err:"+ex.Message);
22 }
23 }
4、增（insert）数据的批处理操作
1 //插入指令的批处理操作
2 public void ExecuteSqlTranssaction(List<string> sqlStrList)
3 {
4 //创建执行命令的command对象
5 MySqlCommand command = new MySqlCommand();
6 command.Connection = conn;
7
8 //开启mysql事务
9 MySqlTransaction transaction = conn.BeginTransaction();
10 command.Transaction = transaction;
11
12 try
13 {
14
15 if (sqlStrList.Count > 1)
16 {
17 for (int i = 0; i < sqlStrList.Count; i++)
18 {
19 string sqlStr = sqlStrList[i].ToString();
20 if (sqlStr.Trim().Length > 1)
21 {
22 command.CommandText = sqlStr;
23 command.ExecuteNonQuery();
24 }
25
26 if (i > 0 && (i % 500 == 0
27 || i == sqlStrList.Count - 1))
28 {
29 transaction.Commit();
30
31 if (i != sqlStrList.Count - 1)
32 {
33 transaction = conn.BeginTransaction();
34 }
35 }
36 }
37 }
38 else
39 {
40 for (int i = 0; i < sqlStrList.Count; i++)
41 {
42 string sqlStr = sqlStrList[i].ToString();
43 if (sqlStr.Trim().Length > 1)
44 {
45 command.CommandText = sqlStr;
46 command.ExecuteNonQuery();
47
48 transaction.Commit();
49
50 }
51 }
52 }
53 }
54 catch (MySqlException err)
55 {
56
57 Console.WriteLine("ExecuteSqlTranssaction error!!!" + err.ToString());
58
59 transaction.Rollback();
60 throw new Exception(err.Message);
61
62 }
63 }
64
65 private void Insert2actionlog(C_ActionLog tmpActionLog)
66 {
67 string sqlStr = string.Format("insert into actionlog (time,user,log) " +
68 "values('{0}','{1}','{2}');",
69 tmpActionLog.dateTime,
70 tmpActionLog.user,
71 tmpActionLog.logInfo);
72
73 Console.WriteLine(sqlStr);
74
75 actSqlStrList.Add(sqlStr);
76 }
5、删（delete） 整理碎片
1 //多条删除
2 public int DeleteLog(DateTime sTime, DateTime endTime) {
3
4 string startTimeStr = sTime.ToString();
5 string endTimeStr = endTime.ToString();
6
7 //需要挑选日期
8 string sqlStr = string.Format("DELETE FROM actionlog WHERE time BETWEEN '{0}' AND '{1}' LIMIT 10000;", startTimeStr, endTimeStr);
9
10 ExecuteQuery(sqlStr);
11
12 //刪除数据以后，整理碎片 大量数据才需要用到
13 string alterStr = string.Format("alter table actionlog engine = innodb,ALGORITHM=INPLACE, LOCK=NONE;");
14 ExecuteQuery(alterStr);
15
16 return 0;
17 }
6、查（select）
1 //获取日志
2 public void SelectLogFromSql(short timeFlag, DateTime sTime, DateTime eTime, out List<C_ActionLog> logList)
3 {
4
5 logList = new List<C_ActionLog>();
6
7 string sqlStr = "SELECT * FROM actionlog ";
8 string timeStr = "";
9 string endStr = ";";
10 string startTimeStr = sTime.ToString();
11 string endTimeStr = eTime.ToString();
12 if (1 == timeFlag)
13 {//需要挑选日期
14 timeStr = string.Format("WHERE time BETWEEN '{0}' AND '{1}' ", startTimeStr, endTimeStr);
15
16 }
17
18 sqlStr += timeStr + endStr;
19
20 Console.WriteLine(sqlStr);
21
22 MySqlCommand command = new MySqlCommand(sqlStr, conn);
23 MySqlDataReader reader = command.ExecuteReader();
24
25 while (reader.Read())
26 {
27 C_ActionLog ectionLog = new C_ActionLog();
28
29 ectionLog.dateTime = reader.GetDateTime("time");
30 ectionLog.user = reader.GetString("user");
31 ectionLog.logInfo = reader.GetString("log");
32
33 logList.Add(ectionLog);
34 }
35
36 reader.Close();
37 }
7、改（Update）
1 updateStr = string.Format("UPDATE equipcraftconf SET id={0},craftName='{1}' WHERE staNo={2} AND craftNo={3};",
2 equipCraftConf.id,
3 equipCraftConf.craftInfo.craftName,
4 equipCraftConf.craftInfo.staNo,
5 equipCraftConf.craftInfo.craftNo
6 );
7
8 ExecuteQuery(updateStr);