using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Text;
|
using CSFramework.DB.Common;
|
using CSFramework.DB.Models;
|
using MySql.Data.MySqlClient;
|
using MySql.Data.Types;
|
|
namespace CSFramework.DB
|
{
|
public class DatabaseMySQL : IDatabase
|
{
|
private string _ConnectionString;
|
|
//存储过程、函数参数前缀
|
private string ParamSymboName1 = "p_";
|
|
public DatabaseMySQL(string connectionString)
|
{
|
_ConnectionString = connectionString;
|
}
|
|
public DatabaseType DatabaseType => DatabaseType.MySQL;
|
|
public CommandHelper CreateSqlProc(string spName)
|
{
|
return new CommandHelper(this, spName, CommandType.StoredProcedure);
|
}
|
|
public CommandHelper CreateCommand(string commandText)
|
{
|
return new CommandHelper(this, commandText, CommandType.Text);
|
}
|
|
public string BuildConnectionString(string server, int port,
|
string dbName, string uid, string pwd, int timeout = 15)
|
{
|
//Convert Zero Datetime=True;
|
//Allow Zero Datetime=True; //不建议添加此属性！经测试，若加上此属性给日期类型赋值会报错： 所需类型是 MySqlDateTime，值类型与列类型不匹配。
|
const string DEF_SQL_CONNECTION1 =
|
"Server={0};Database={1};User ID={2};Password={3};Connection TimeOut={4};Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";
|
const string DEF_SQL_CONNECTION2 =
|
"Server={0};Port={1};Database={2};User ID={3};Password={4};Connection TimeOut={5};Charset=utf8;Pooling=true;Min Pool Size=0;Max Pool Size=500;Connection Lifetime=0;Convert Zero Datetime=True;";
|
|
var connstr = "";
|
if (port == DefaultPort)
|
connstr = string.Format(DEF_SQL_CONNECTION1, server, dbName,
|
uid, pwd, timeout);
|
else
|
connstr = string.Format(DEF_SQL_CONNECTION2, server,
|
port.ToString(), dbName, uid, pwd, timeout);
|
return connstr;
|
}
|
|
public int DefaultPort => 3306;
|
|
|
public int CommandTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 30;
|
return DatabaseFactory.CommandTimeOut > 0
|
? DatabaseFactory.CommandTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
public int ConnectionTimeout
|
{
|
get
|
{
|
var defaultTimeOut = 15;
|
return DatabaseFactory.ConnectionTimeOut > 0
|
? DatabaseFactory.ConnectionTimeOut
|
: defaultTimeOut;
|
}
|
}
|
|
/// <summary>
|
/// 服务器时间
|
/// </summary>
|
public DateTime GetServerTime()
|
{
|
var o = ExecuteScalar("SELECT NOW();");
|
return Convert.ToDateTime(o);
|
}
|
|
public Type DateTimeType => typeof(MySqlDateTime);
|
|
/// <summary>
|
/// 参数符号.如: WHERE Code=?Code, MySQL默认不带参数符号.
|
///
|
/// Insert(X)VALUES(?X)
|
///
|
/// 注意：MySQL数据库，存储过程、函数的参数前面统一加了"p_"
|
///
|
/// </summary>
|
public string ParamSymboName { get; set; } = "?p_";
|
|
/// <summary>
|
/// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
public string ParseParamName(string paramName)
|
{
|
if (string.IsNullOrEmpty(ParamSymboName) ||
|
string.IsNullOrEmpty(paramName))
|
{
|
return paramName;
|
}
|
else
|
{
|
if (paramName.IndexOf(ParamSymboName) == 0) //首字母包含默认参数符号 ?p_
|
{
|
return paramName;
|
}
|
else
|
{
|
if (paramName.IndexOf(ParamSymboName1) ==
|
0) //参数前缀是p_开头, 如：p_Name，改为:?p_Name
|
return "?" + paramName;
|
else
|
return ParamSymboName + paramName; //添加参数符号
|
}
|
}
|
}
|
|
/// <summary>
|
/// 创建时间戳参数
|
/// </summary>
|
/// <param name="parameterName">参数名</param>
|
/// <param name="fieldName">字段名</param>
|
/// <returns></returns>
|
public DbParameter CreateTimestampParameter(string parameterName,
|
string fieldName)
|
{
|
var p = new MySqlParameter();
|
p.ParameterName = parameterName;
|
p.MySqlDbType = MySqlDbType.Timestamp;
|
p.Size = 8;
|
p.SourceColumn = fieldName;
|
return p;
|
}
|
|
public string ConnectionString
|
{
|
get => _ConnectionString;
|
set => _ConnectionString = value;
|
}
|
|
public void Close(DbConnection connection)
|
{
|
if (connection != null)
|
{
|
connection.Close();
|
connection.Dispose();
|
}
|
}
|
|
public DbCommand CreateCommand(string commandText,
|
CommandType commandType)
|
{
|
if (CommandType.Text == commandType)
|
commandText = ParseSqlEnd(commandText); //SQL结尾添加;
|
|
return new MySqlCommand(commandText)
|
{
|
CommandText = commandText, CommandType = commandType,
|
CommandTimeout = CommandTimeout
|
};
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new MySqlParameter
|
{
|
ParameterName = parameterName, Value = parameterValue,
|
IsNullable = true
|
};
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
DbType parameterType, int size, string sourceColumn,
|
object parameterValue)
|
{
|
parameterName = ParseParamName(parameterName);
|
return new MySqlParameter
|
{
|
ParameterName = parameterName, DbType = parameterType,
|
Size = size, SourceColumn = sourceColumn,
|
Value = parameterValue, IsNullable = true
|
};
|
}
|
|
|
public DbCommandBuilder CreateCommandBuilder()
|
{
|
return new MySqlCommandBuilder();
|
}
|
|
public DbDataAdapter CreateDataAdapter()
|
{
|
return new MySqlDataAdapter();
|
}
|
|
public DbConnection CreateConnection()
|
{
|
return CreateConnection(_ConnectionString);
|
}
|
|
public DbConnection CreateConnection(string connectionString)
|
{
|
DbConnection DBConn = new MySqlConnection(connectionString);
|
DBConn.Open(); //打开连接
|
return DBConn;
|
}
|
|
public DbTransaction TransBegin()
|
{
|
return CreateConnection().BeginTransaction();
|
}
|
|
public void TransCommit(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Commit();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public void TransRollback(DbTransaction trans,
|
bool closeConnection = false)
|
{
|
trans.Rollback();
|
if (closeConnection) Close(trans.Connection);
|
}
|
|
public int ExecuteCommand(DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null && cmd.Transaction == null)
|
cmd.Connection =
|
connection = CreateConnection(_ConnectionString);
|
return cmd.ExecuteNonQuery();
|
}
|
finally
|
{
|
if (cmd.Transaction == null) Close(connection);
|
}
|
}
|
|
public DbDataReader ExecuteReader(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbCommand cmd = null;
|
DbDataReader o = null;
|
DbConnection conn = null;
|
try
|
{
|
conn = CreateConnection(_ConnectionString);
|
cmd = new MySqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
o = cmd.ExecuteReader();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
Close(conn);
|
}
|
|
return o;
|
}
|
|
public DbDataReader ExecuteReader(DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
connection = CreateConnection();
|
cmd.Connection = connection;
|
}
|
|
return cmd.ExecuteReader();
|
}
|
finally
|
{
|
if (connection != null) Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(DbCommand cmd) where T : new()
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var obj = default(T);
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(string SQL) where T : new()
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
using (var connection = CreateConnection())
|
{
|
using (var command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var obj = default(T);
|
using (var dataReader = command.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
}
|
}
|
|
public List<T> ExecuteReaderList<T>(string SQL) where T : new()
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<T>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReaderList<T>(DbCommand cmd) where T : new()
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(DbTools.Convert2Object<T>(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public List<T> ExecuteReader<T>(string SQL,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
using (var connection = CreateConnection())
|
{
|
using (var command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<T>();
|
using (var dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
|
public List<T> ExecuteReader<T>(DbCommand cmd,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<T>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public List<string> GetStringList(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var list = new List<string>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0)
|
.ToString()); //取第1列
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<string> GetStringList(DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var list = new List<string>();
|
using (var dataReader = cmd.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(dataReader.GetValue(0).ToString()); //取第1列
|
}
|
|
return list;
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbConnection connection = null;
|
DbCommand cmd = null;
|
try
|
{
|
cmd = CreateCommand(SQL, CommandType.Text);
|
cmd.Connection = connection = CreateConnection();
|
return cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
return cmd.ExecuteScalar();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public int ExecuteSQL(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
return ExecuteSQL(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
/// <summary>
|
/// 在事务内提交数据
|
/// </summary>
|
/// <param name="trans">事务</param>
|
/// <param name="SQL">SQL语句</param>
|
/// <returns></returns>
|
public int ExecuteTrans(DbTransaction trans, string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new MySqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
|
public int ExecuteTrans(DbTransaction trans, DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
var i = -1;
|
try
|
{
|
cmd.Connection = trans.Connection;
|
cmd.Transaction = trans;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataSet GetDataSet(DbCommand cmd)
|
{
|
if (CommandType.Text == cmd.CommandType)
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
{
|
connection = CreateConnection(_ConnectionString);
|
cmd.Connection = connection;
|
}
|
|
var ds = new DataSet();
|
adapter = new MySqlDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public DataSet GetDataSet(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection(_ConnectionString);
|
return GetDataSet(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
/// <summary>
|
/// 执行带参数的 Sql 语句或存储过程，并返回 DataSet 对象。
|
/// </summary>
|
/// <param name="SQL">要执行的 Sql 语句或存储过程名等。</param>
|
/// <param name="type">CommandType 参数类型，即该命令是 sql 语句，还是存储过程名等。</param>
|
/// <param name="paramlist">参数集合。</param>
|
/// <returns>DataSet 对象。</returns>
|
public virtual DataSet GetDataSet(string SQL, CommandType type,
|
IDataParameter[] paramlist)
|
{
|
if (CommandType.Text == type)
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
|
cmd = CreateCommand(SQL, type);
|
cmd.Connection = connection;
|
|
if (paramlist != null)
|
cmd.Parameters.AddRange(paramlist);
|
|
var ds = new DataSet();
|
adapter = CreateDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public DataTable GetTable(string SQL, string tableName = "")
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
var dt = GetDataSet(SQL).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
return dt;
|
}
|
|
|
public DataTable GetTop(int top, string tableName, string fields = "",
|
List<DbParameter> where = null, string orderBy = "")
|
{
|
var cmd = CreateCommand("");
|
|
fields = string.IsNullOrWhiteSpace(fields) ? "*" : fields;
|
orderBy = string.IsNullOrEmpty(orderBy)
|
? ""
|
: " ORDER BY " + orderBy;
|
|
var whereSQL = "";
|
if (where != null && where.Count > 0)
|
{
|
string pName;
|
foreach (var p in where)
|
{
|
pName = p.ParameterName;
|
if (pName.IndexOf(ParamSymboName) >= 0) //参数名称带有符号,如：@Code
|
whereSQL = whereSQL +
|
$" AND {pName.Replace(ParamSymboName, "")}={p.ParameterName}";
|
else
|
whereSQL = whereSQL +
|
$" AND {p.ParameterName}={ParamSymboName + p.ParameterName}";
|
|
cmd.Command.Parameters.Add(p);
|
}
|
}
|
|
whereSQL = string.IsNullOrEmpty(whereSQL)
|
? ""
|
: " WHERE 1=1 " + whereSQL;
|
|
var SQL =
|
$"SELECT {fields} FROM {tableName} {whereSQL} {orderBy} LIMIT {top};";
|
|
cmd.Command.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
return GetTable(cmd.Command, tableName);
|
}
|
|
public DataTable GetTable(DbCommand cmd, string tableName = "")
|
{
|
cmd.CommandText =
|
CodeSafeHelper.GetSafeSQL(
|
ParseSqlEnd(cmd.CommandText)); //SQL结尾添加;
|
|
var dt = GetDataSet(cmd).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
return dt;
|
}
|
|
public DataRow GetDataRow(string SQL)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
var dt = GetDataSet(SQL).Tables[0];
|
return dt.Rows.Count > 0 ? dt.Rows[0] : null;
|
}
|
|
public DbType ToDbType(string sourceType)
|
{
|
return DBDataTypes.GetDbTypeByMySqlType(sourceType);
|
|
////若转换有误。
|
//MySqlDbType type = (MySqlDbType)Enum.Parse(typeof(MySqlDbType), sourceType, true);
|
//MySqlParameter p = new MySqlParameter("", null);
|
//p.MySqlDbType = type;
|
//p.ResetDbType();
|
//return p.DbType;
|
}
|
|
public Type ToNetType(string sourceType)
|
{
|
return DBDataTypes.GetNetTypeByMySql(sourceType);
|
}
|
|
public DataTable meta_GetTableNames(string dbName = "")
|
{
|
var cmd = CreateCommand("");
|
cmd.AddParam("P1", "BASE TABLE");
|
cmd.AddParam("P2", "VIEW");
|
var sql = "";
|
if (string.IsNullOrWhiteSpace(dbName))
|
{
|
sql =
|
$"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where TABLE_SCHEMA=(select database()) AND TABLE_TYPE IN ({ParamSymboName}P1,{ParamSymboName}P2)";
|
}
|
else
|
{
|
sql =
|
$"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where TABLE_SCHEMA={ParamSymboName}DBName AND TABLE_TYPE IN ({ParamSymboName}P1,{ParamSymboName}P2)";
|
cmd.AddParam("DBName", dbName);
|
}
|
|
cmd.Command.CommandText = sql;
|
return GetTable(cmd.Command);
|
}
|
|
public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
|
{
|
var sql = "";
|
if (string.IsNullOrWhiteSpace(dbName))
|
sql =
|
"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where TABLE_SCHEMA=(select database()) AND TABLE_TYPE IN ('BASE TABLE','VIEW')";
|
else
|
sql =
|
"select 0 as id,TABLE_TYPE as type,TABLE_NAME as name from information_schema.tables where TABLE_SCHEMA='" +
|
dbName + "' AND TABLE_TYPE IN ('BASE TABLE','VIEW')";
|
|
return ExecuteReaderList<MetaTableNames>(sql);
|
}
|
|
public DataTable meta_GetDatabase()
|
{
|
var sql =
|
"select SCHEMA_NAME AS DBName from information_schema.schemata;";
|
return GetTable(sql, "schemata");
|
}
|
|
public List<MetaDBNames> meta_GetDatabaseList()
|
{
|
var sql =
|
"select SCHEMA_NAME AS DBName from information_schema.schemata;";
|
return ExecuteReaderList<MetaDBNames>(sql);
|
}
|
|
public DataTable meta_GetTableStru(string tableName)
|
{
|
var b = CodeSafeHelper.IsCheckSQM;
|
try
|
{
|
CodeSafeHelper.IsCheckSQM = false;
|
var sql = meta_GetTableStruSQL(tableName);
|
return GetTable(sql, tableName);
|
}
|
finally
|
{
|
CodeSafeHelper.IsCheckSQM = b;
|
}
|
}
|
|
public List<MetaTableStructure> meta_GetTableStruList(string tableName)
|
{
|
var b = CodeSafeHelper.IsCheckSQM;
|
try
|
{
|
CodeSafeHelper.IsCheckSQM = false;
|
var sql = meta_GetTableStruSQL(tableName);
|
return ExecuteReaderList<MetaTableStructure>(sql);
|
}
|
finally
|
{
|
CodeSafeHelper.IsCheckSQM = b;
|
}
|
}
|
|
|
public List<MetaStoreProcedure> meta_GetStoreProcList(
|
string dbName = "")
|
{
|
var sql =
|
$"select type,name from mysql.proc where db = '{dbName}' and `type` IN ('PROCEDURE','FUNCTION');";
|
|
CodeSafeHelper.IsCheckSQM = false;
|
var result = new List<MetaStoreProcedure>();
|
var sp = CreateCommand(sql);
|
var dt = GetTable(sp.Command);
|
foreach (DataRow R in dt.Rows)
|
{
|
if (R["type"].ToString().Trim().ToUpper() == "PROCEDURE")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.StoreProcedure,
|
Name = R["name"].ToString()
|
});
|
if (R["type"].ToString().Trim().ToUpper() == "FUNCTION")
|
result.Add(new MetaStoreProcedure
|
{
|
Type = MetaStoreProcedureType.Function,
|
Name = R["name"].ToString()
|
});
|
}
|
|
return result;
|
}
|
|
/// <summary>
|
/// 获取当前表最新(最大)的自增字段值
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
public int GetMaxID(string tableName)
|
{
|
var sql = $"SELECT LAST_INSERT_ID();";
|
var o = ExecuteScalar(sql);
|
return o == DBNull.Value || o == null ? 0 : Convert.ToInt32(o);
|
}
|
|
public bool UpdateDataSet(DataTable ds, string tableName, string KEY)
|
{
|
////先删除原表数据
|
//var sql = "";
|
//var ErrorMsg = "";
|
//foreach (DataRow dr in ds.Rows)
|
//{
|
// sql = string.Format(@"delete from {0} where {1} in ('{2}')", tableName, KEY, dr[KEY]);
|
// break;
|
//}
|
|
//var a = ExecuteSQL(sql);
|
//var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
//for (var i = 0; i < ds.Columns.Count; i++)
|
//{
|
// colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
|
//}
|
|
//DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
|
return true;
|
}
|
|
|
public bool InstDataSet(DataTable ds, string tableName)
|
{
|
//先删除原表数据
|
//var ErrorMsg = "";
|
//var colMapping = new SqlBulkCopyColumnMapping[ds.Columns.Count];
|
//for (var i = 0; i < ds.Columns.Count; i++)
|
//{
|
// colMapping[i] = new SqlBulkCopyColumnMapping(ds.Columns[i].ColumnName, ds.Columns[i].ColumnName);
|
//}
|
|
//DataTableToSQLServer(ds, _ConnectionString, tableName, colMapping, ref ErrorMsg);
|
return true;
|
}
|
|
public int ExecuteSQL(string SQL, DbConnection conn)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = new MySqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
i = cmd.ExecuteNonQuery();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return i;
|
}
|
|
public DataSet GetDataSet(string SQL, DbConnection conn)
|
{
|
SQL = ParseSqlEnd(SQL); //SQL结尾添加;
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
try
|
{
|
cmd = new MySqlCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
|
var ds = new DataSet();
|
adapter = new MySqlDataAdapter();
|
adapter.SelectCommand = cmd;
|
adapter.Fill(ds);
|
return ds;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (adapter != null) adapter.Dispose();
|
if (cmd != null) cmd.Dispose();
|
}
|
}
|
|
/// <summary>
|
/// 给字符串结尾添加SQL语句结尾符号;
|
/// </summary>
|
/// <param name="sql"></param>
|
/// <returns></returns>
|
private string ParseSqlEnd(string sql)
|
{
|
if (string.IsNullOrWhiteSpace(sql)) return sql;
|
|
var symbol = ";"; //SQL语句结尾符号
|
if (sql.Substring(sql.Length - 1, 1) != symbol) sql = sql + symbol;
|
return sql;
|
}
|
|
private string meta_GetTableStruSQL(string tableName)
|
{
|
var sql = new StringBuilder();
|
|
sql.AppendLine("SELECT ");
|
sql.AppendLine(" A.TABLE_NAME AS TableName,");
|
sql.AppendLine(" A.TABLE_COMMENT AS TableDesc,");
|
sql.AppendLine(" B.ORDINAL_POSITION AS FieldOrder,");
|
sql.AppendLine(" B.COLUMN_NAME AS FieldName,");
|
sql.AppendLine(
|
" CASE WHEN EXTRA='auto_increment' THEN 'Y' ELSE 'N' END AS IsIdentity,");
|
sql.AppendLine(
|
" CASE WHEN COLUMN_KEY = 'PRI' THEN 'Y' ELSE 'N' END AS PK,");
|
sql.AppendLine(
|
" CASE WHEN EXISTS(SELECT * FROM information_schema.`TABLE_CONSTRAINTS` WHERE TABLE_SCHEMA=A.TABLE_SCHEMA AND TABLE_NAME = A.TABLE_NAME AND CONSTRAINT_TYPE = 'FOREIGN KEY') THEN 'Y' ELSE 'N' END AS FK,");
|
sql.AppendLine(
|
" CASE WHEN EXISTS(SELECT * FROM information_schema.`STATISTICS` WHERE TABLE_SCHEMA=A.TABLE_SCHEMA AND TABLE_NAME = A.TABLE_NAME AND INDEX_NAME = B.COLUMN_NAME) THEN 'Y' ELSE 'N' END AS IDX, ");
|
sql.AppendLine(" DATA_TYPE AS FieldType,");
|
sql.AppendLine(
|
" CASE WHEN REPLACE(REPLACE(REPLACE(COLUMN_TYPE,DATA_TYPE,''),'(',''),')','')='' THEN 0 ELSE REPLACE(REPLACE(REPLACE(COLUMN_TYPE,DATA_TYPE,''),'(',''),')','') END AS FieldLength,");
|
sql.AppendLine(" B.NUMERIC_PRECISION AS Prec,");
|
sql.AppendLine(" IFNULL(B.NUMERIC_SCALE,0) AS Scale,");
|
sql.AppendLine(
|
" CASE WHEN IS_NULLABLE = 'YES' THEN 'Y' ELSE 'N' END AS AllowNull,");
|
sql.AppendLine(" IFNULL(COLUMN_DEFAULT,'') AS DefaultValue,");
|
sql.AppendLine(
|
" CASE WHEN IFNULL(COLUMN_COMMENT,'')='' THEN B.COLUMN_NAME ELSE COLUMN_COMMENT END AS FieldCaption ");
|
sql.AppendLine("FROM information_schema.`TABLES` A");
|
sql.AppendLine(
|
"INNER JOIN information_schema.`COLUMNS` B ON A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_NAME = B.TABLE_NAME ");
|
sql.AppendLine(
|
"WHERE A.TABLE_SCHEMA=DATABASE() AND A.TABLE_NAME = '" +
|
tableName + "' AND B.TABLE_SCHEMA=DATABASE();");
|
|
return sql.ToString();
|
}
|
|
|
#region
|
|
protected AdapterRowUpdatingEvent _MyRowUpdatingEvent = null;
|
|
public DbDataAdapter CreateDataAdapter(
|
AdapterRowUpdatingEvent eventHandler)
|
{
|
_MyRowUpdatingEvent = eventHandler;
|
|
var adp = new MySqlDataAdapter();
|
adp.RowUpdating += Adp_RowUpdating;
|
|
return adp;
|
}
|
|
private void Adp_RowUpdating(object sender, MySqlRowUpdatingEventArgs e)
|
{
|
if (_MyRowUpdatingEvent != null) _MyRowUpdatingEvent(sender, e);
|
}
|
|
#endregion
|
}
|
}
|
