/// <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 = _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)
{
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语句查询返回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 = _conStr;
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]);
}
}
}
/// <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>
/// 获取数据库中的所有表
/// </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)
}));
}
/// <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 = _conStr;
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);
}