最近项目用到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);