forked from zhangqi-ulua/XlsxToLua
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMySQLOperateHelper.cs
More file actions
145 lines (132 loc) · 6.03 KB
/
MySQLOperateHelper.cs
File metadata and controls
145 lines (132 loc) · 6.03 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
public class MySQLOperateHelper
{
// MySQL支持的用于定义Schema名的参数名
private static string[] _DEFINE_SCHEMA_NAME_PARAM = { "Database", "Initial Catalog" };
private const string _SELECT_ALL_DATA_SQL = "SELECT * FROM {0}";
private const string _SELECT_COLUMN_INFO_SQL = "SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'";
private const string _SELECT_TABLE_COMMENT_SQL = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'";
private static MySqlConnection _conn = null;
private static string _schemaName = null;
// 数据库中存在的数据表名
public static List<string> ExistTableNames { get; private set; }
public static bool ConnectToDatabase(out string errorString)
{
if (AppValues.ConfigData.ContainsKey(AppValues.APP_CONFIG_KEY_MYSQL_CONNECT_STRING))
{
// 提取MySQL连接字符串中的Schema名
string connectString = AppValues.ConfigData[AppValues.APP_CONFIG_KEY_MYSQL_CONNECT_STRING];
foreach (string legalSchemaNameParam in _DEFINE_SCHEMA_NAME_PARAM)
{
int defineStartIndex = connectString.IndexOf(legalSchemaNameParam, StringComparison.CurrentCultureIgnoreCase);
if (defineStartIndex != -1)
{
// 查找后面的等号
int equalSignIndex = -1;
for (int i = defineStartIndex + legalSchemaNameParam.Length; i < connectString.Length; ++i)
{
if (connectString[i] == '=')
{
equalSignIndex = i;
break;
}
}
if (equalSignIndex == -1 || equalSignIndex + 1 == connectString.Length)
{
errorString = string.Format("MySQL数据库连接字符串（\"{0}\"）中\"{1}\"后需要跟\"=\"进行Schema名声明", connectString, legalSchemaNameParam);
return false;
}
else
{
// 查找定义的Schema名，在参数声明的=后面截止到下一个分号或字符串结束
int semicolonIndex = -1;
for (int i = equalSignIndex + 1; i < connectString.Length; ++i)
{
if (connectString[i] == ';')
{
semicolonIndex = i;
break;
}
}
if (semicolonIndex == -1)
_schemaName = connectString.Substring(equalSignIndex + 1).Trim();
else
_schemaName = connectString.Substring(equalSignIndex + 1, semicolonIndex - equalSignIndex - 1).Trim();
}
break;
}
}
if (_schemaName == null)
{
errorString = string.Format("MySQL数据库连接字符串（\"{0}\"）中不包含Schema名的声明，请在{1}中任选一个参数名进行声明", connectString, Utils.CombineString(_DEFINE_SCHEMA_NAME_PARAM, ","));
return false;
}
try
{
_conn = new MySqlConnection(connectString);
_conn.Open();
if (_conn.State == System.Data.ConnectionState.Open)
{
// 获取已存在的数据表名
ExistTableNames = new List<string>();
DataTable schemaInfo = _conn.GetSchema(System.Data.SqlClient.SqlClientMetaDataCollectionNames.Tables);
foreach (DataRow info in schemaInfo.Rows)
ExistTableNames.Add(info.ItemArray[2].ToString());
errorString = null;
return true;
}
else
{
errorString = "未知错误";
return true;
}
}
catch (MySqlException exception)
{
errorString = exception.Message;
return false;
}
}
else
{
errorString = string.Format("未在config配置文件中以名为\"{0}\"的key声明连接MySQL的字符串", AppValues.APP_CONFIG_KEY_MYSQL_CONNECT_STRING);
return false;
}
}
public static DataTable ReadDatabaseTable(string tableName)
{
MySqlCommand cmd = new MySqlCommand(string.Format(_SELECT_ALL_DATA_SQL, _CombineDatabaseTableFullName(tableName)), _conn);
return _ExecuteSqlCommand(cmd);
}
public static DataTable GetColumnInfo(string tableName)
{
MySqlCommand cmd = new MySqlCommand(string.Format(_SELECT_COLUMN_INFO_SQL, _schemaName, tableName), _conn);
return _ExecuteSqlCommand(cmd);
}
/// <summary>
/// 获取数据库某张表格设置的Comment
/// </summary>
public static string GetDatabaseTableComment(string tableName)
{
MySqlCommand cmd = new MySqlCommand(string.Format(_SELECT_TABLE_COMMENT_SQL, _schemaName, tableName), _conn);
DataTable dt = _ExecuteSqlCommand(cmd);
return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : string.Empty;
}
private static DataTable _ExecuteSqlCommand(MySqlCommand cmd)
{
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
/// <summary>
/// 将数据库的表名连同Schema名组成形如'SchemaName'.'tableName'的字符串
/// </summary>
private static string _CombineDatabaseTableFullName(string tableName)
{
return string.Format("`{0}`.`{1}`", _schemaName, tableName);
}
}