/// <summary>
/// 通过连接字符串和表名获取数据库表的信息
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public override List <TableInfo> GetDbTableInfo(string tableName)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
string dbName = string.Empty;
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
dbName = conn.Database;
}
string sql = @"select DISTINCT
a.COLUMN_NAME as Name,
a.DATA_TYPE as Type,
(a.COLUMN_KEY = 'PRI') as IsKey,
(a.IS_NULLABLE = 'YES') as IsNullable,
a.COLUMN_COMMENT as Description,
a.ORDINAL_POSITION
from information_schema.columns a
where table_name=@tableName and table_schema=@dbName
ORDER BY a.ORDINAL_POSITION";
return(GetListBySql <TableInfo>(sql, new List <DbParameter> {
new MySqlParameter("@tableName", tableName), new MySqlParameter("@dbName", dbName)
}));
}
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable,参数化查询
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public DataTable GetDataTableWithSql(string sql, List <DbParameter> parameters)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null && parameters.Count > 0)
{
foreach (var item in parameters)
{
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="paramters"></param>
public int ExecuteSql(string sql, List <DbParameter> paramters)
{
int count = 0;
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = dbProviderFactory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
if (paramters != null && paramters.Count > 0)
{
foreach (var item in paramters)
{
cmd.Parameters.Add(item);
}
}
count = cmd.ExecuteNonQuery();
return(count);
}
}
}
/// <summary>
/// 通过数据库连接字符串和Sql语句查询返回DataTable
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns></returns>
public DataTable GetDataTableWithSql(string sql)
{
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = conn.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet table = new DataSet();
adapter.Fill(table);
return(table.Tables[0]);
}
}
}
public override DataTable GetDataTableWithSql(string sql, List <DbParameter> parameters)
{
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;
cmd.CommandTimeout = 5 * 60;
if (parameters != null && parameters?.Count > 0)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
DbDataAdapter adapter = new MySqlDataAdapter();
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="paramters">查询参数</param>
public int ExecuteSql(string sql, List <DbParameter> paramters)
{
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 = dbProviderFactory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandTimeout = 5 * 60;
if (paramters != null && paramters?.Count > 0)
{
cmd.Parameters.AddRange(paramters.ToArray());
}
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 = _conString;
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)
}));
}
/// <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;
OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder(_conStr);
dbName = builder.UserID;
if (schemaName.IsNullOrEmpty())
{
schemaName = dbName;
}
string sql = @"select
TABLE_NAME as ""TableName"",
COMMENTS as ""Description""
from all_tab_comments
where owner =:schemaName";
return(GetListBySql <DbTableInfo>(sql, new List <DbParameter> {
new OracleParameter("schemaName", schemaName)
}));
}
/// <summary>
/// 执行无返回值的Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
public int ExecuteSql(string sql)
{
int count = 0;
DbProviderFactory dbProviderFactory = DbProviderFactoryHelper.GetDbProviderFactory(_dbType);
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = _conString;
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (DbCommand cmd = dbProviderFactory.CreateCommand())
{
cmd.Connection = conn;
cmd.CommandText = sql;
count = cmd.ExecuteNonQuery();
return(count);
}
}
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbType">数据库类型</param>
/// <param name="conStr">连接名或连接字符串</param>
public DbHelper(DatabaseType dbType, string conStr)
{
_dbType = dbType;
_conStr = DbProviderFactoryHelper.GetConStr(conStr);
}