/// <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="spList">查询参数</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>
/// <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语句
/// </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);
}
/// <summary>
/// 获取指定的数据库帮助类
/// </summary>
/// <param name="dbType">数据库类型字符串</param>
/// <param name="conStr">连接字符串</param>
/// <returns></returns>
public static DbHelper GetDbHelper(string dbTypeStr, string conStr)
{
DatabaseType dbType = DbProviderFactoryHelper.DbTypeStrToDbType(dbTypeStr);
return(GetDbHelper(dbType, conStr));
}