/// <summary>
/// Sql查询返回DataTable，参数化查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="parameters">参数(可选)</param>
/// <returns></returns>
public DataTable GetDataTableWithSql(string sql, List <DbParameter> parameters = null)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conStr;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Count > 0)
{
parameters.ForEach(item => cmd.Parameters.Add(item));
}
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet table = new DataSet();
adapter.Fill(table);
cmd.Parameters.Clear();
return(table.Tables[0]);
}
}
}
/// <summary>
/// 执行无返回值的Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="parameters">查询参数（可选）</param>
/// <returns>返回受影响的行数</returns>
public int ExecuteSql(string sql, List <DbParameter> parameters = null)
{
int count = 0;
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conStr;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Count > 0)
{
parameters.ForEach(param => cmd.Parameters.Add(param));
}
count = cmd.ExecuteNonQuery();
}
}
return(count);
}
/// <summary>
/// 获取数据库中的所有表
/// </summary>
/// <param name="schemaName">模式（架构）</param>
/// <returns></returns>
public override List <DbTableInfo> GetDbAllTables(string schemaName = null)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
string dbName = string.Empty;
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conStr;
dbName = conn.Database;
}
string sql = @"SELECT TABLE_NAME as TableName,table_comment as Description
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @dbName";
return(GetListBySql <DbTableInfo>(sql, new List <DbParameter> {
new MySqlParameter("@dbName", dbName)
}));
}