using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.Common;
|
using System.Data.SqlClient;
|
using System.Data.SqlTypes;
|
using CSFramework.DB.Common;
|
using CSFramework.DB.Models;
|
using MySql.Data.MySqlClient;
|
using MySql.Data.Types;
|
using Oracle.ManagedDataAccess.Client;
|
using Oracle.ManagedDataAccess.Types;
|
|
namespace CSFramework.DB
|
{
|
/// <summary>
|
/// 通用多数据库底层驱动（支持SqlServer,MySQL,Oracle)
|
/// 1.依赖app.config文件,需要配置数据库提供者.
|
/// 2.将app.config文件内容复制到应用程序的配置文件，因程序内使用AppSettings以及加载Oracle,MySql底层驱动。
|
/// </summary>
|
public class DatabaseMDB : IDatabase
|
{
|
private string _ConnectionString;
|
private DatabaseType _DatabaseType;
|
|
public DatabaseMDB(DatabaseType providerType, string connectionString)
|
{
|
_DatabaseType = providerType;
|
_ConnectionString = connectionString;
|
}
|
|
public DatabaseType DatabaseType => _DatabaseType;
|
|
public CommandHelper CreateSqlProc(string spName)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.CreateSqlProc(spName);
|
}
|
|
public CommandHelper CreateCommand(string commandText)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.CreateCommand(commandText);
|
}
|
|
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;
|
}
|
}
|
|
public string ConnectionString
|
{
|
get => _ConnectionString;
|
set => _ConnectionString = value;
|
}
|
|
public DateTime GetServerTime()
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.GetServerTime();
|
}
|
|
|
public string BuildConnectionString(string server, int port,
|
string dbName, string uid, string pwd, int timeout = 15)
|
{
|
if (_DatabaseType == DatabaseType.SqlServer)
|
return new DatabaseMSSQL("").BuildConnectionString(server, port,
|
dbName, uid, pwd, timeout);
|
|
if (_DatabaseType == DatabaseType.MySQL)
|
return new DatabaseMySQL("").BuildConnectionString(server, port,
|
dbName, uid, pwd, timeout);
|
|
if (_DatabaseType == DatabaseType.Oracle)
|
return new DatabaseOracle("").BuildConnectionString(server,
|
port, dbName, uid, pwd, timeout);
|
|
throw new Exception("数据库类型不支持！");
|
}
|
|
public int DefaultPort
|
{
|
get
|
{
|
if (_DatabaseType == DatabaseType.SqlServer) return 1433;
|
if (_DatabaseType == DatabaseType.MySQL) return 3306;
|
if (_DatabaseType == DatabaseType.Oracle) return 1521;
|
throw new Exception("数据库类型不支持！");
|
}
|
}
|
|
public DbParameter CreateTimestampParameter(string parameterName,
|
string fieldName)
|
{
|
DbParameter p;
|
|
if (DatabaseType.SqlServer == _DatabaseType)
|
{
|
p = new SqlParameter();
|
(p as SqlParameter).SqlDbType = SqlDbType.Timestamp;
|
}
|
else if (DatabaseType.MySQL == _DatabaseType)
|
{
|
p = new MySqlParameter();
|
(p as MySqlParameter).MySqlDbType = MySqlDbType.Timestamp;
|
}
|
else if (DatabaseType.Oracle == _DatabaseType)
|
{
|
p = new OracleParameter();
|
(p as OracleParameter).OracleDbType = OracleDbType.TimeStamp;
|
}
|
else
|
{
|
throw new Exception("请扩展" + GetType().Name +
|
".CreateTimestampParameter方法！");
|
}
|
|
p.SourceColumn = fieldName;
|
|
return p;
|
}
|
|
public Type DateTimeType
|
{
|
get
|
{
|
var T = typeof(SqlDateTime);
|
switch (_DatabaseType)
|
{
|
case DatabaseType.SqlServer:
|
T = typeof(SqlDateTime);
|
break;
|
case DatabaseType.MySQL:
|
T = typeof(MySqlDateTime);
|
break;
|
case DatabaseType.Oracle:
|
T = typeof(OracleDate);
|
break;
|
}
|
|
return T;
|
}
|
}
|
|
public string ParamSymboName { get; set; }
|
|
public DbCommand CreateCommand(string commandText,
|
CommandType commandType)
|
{
|
var cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(commandText);
|
cmd.CommandType = commandType;
|
cmd.CommandTimeout = CommandTimeout;
|
return cmd;
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
object parameterValue)
|
{
|
var p = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateParameter();
|
p.ParameterName = parameterName;
|
p.Value = parameterValue;
|
return p;
|
}
|
|
public DbParameter CreateParameter(string parameterName,
|
DbType parameterType, int size, string sourceColumn,
|
object parameterValue)
|
{
|
var p = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateParameter();
|
p.ParameterName = parameterName;
|
p.Value = parameterValue;
|
p.DbType = parameterType;
|
p.Size = size;
|
p.SourceColumn = sourceColumn;
|
return p;
|
}
|
|
public DbCommandBuilder CreateCommandBuilder()
|
{
|
return DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommandBuilder();
|
}
|
|
public DbDataAdapter CreateDataAdapter()
|
{
|
return DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateDataAdapter();
|
}
|
|
public DbDataAdapter CreateDataAdapter(
|
AdapterRowUpdatingEvent eventHandler)
|
{
|
return DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateDataAdapter();
|
}
|
|
public DbConnection CreateConnection()
|
{
|
return CreateConnection(_DatabaseType, _ConnectionString);
|
}
|
|
public DbConnection CreateConnection(string connectionString)
|
{
|
return CreateConnection(_DatabaseType, connectionString);
|
}
|
|
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 DataSet GetDataSet(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
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)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = type;
|
cmd.Connection = connection;
|
|
if (paramlist != null)
|
cmd.Parameters.AddRange(paramlist);
|
|
var ds = new DataSet();
|
adapter = DataProviderFactory.GetFactory(_DatabaseType)
|
.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 DataSet GetDataSet(DbCommand cmd)
|
{
|
DbDataAdapter adapter = null;
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
var ds = new DataSet();
|
adapter = DataProviderFactory.GetFactory(_DatabaseType)
|
.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 = "")
|
{
|
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 db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.GetTop(top, tableName, fields, where, orderBy);
|
}
|
|
public DataTable meta_GetTableNames(string dbName = "")
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetTableNames(dbName);
|
}
|
|
public DataTable meta_GetTableStru(string tableName)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetTableStru(tableName);
|
}
|
|
public DataTable meta_GetDatabase()
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetDatabase();
|
}
|
|
public List<MetaDBNames> meta_GetDatabaseList()
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetDatabaseList();
|
}
|
|
public List<MetaTableNames> meta_GetTableNamesList(string dbName = "")
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetTableNamesList(dbName);
|
}
|
|
public List<MetaTableStructure> meta_GetTableStruList(string tableName)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetTableStruList(tableName);
|
}
|
|
public List<MetaStoreProcedure> meta_GetStoreProcList(
|
string dbName = "")
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.meta_GetStoreProcList(dbName);
|
}
|
|
public DataTable GetTable(DbCommand cmd, string tableName = "")
|
{
|
var dt = GetDataSet(cmd).Tables[0];
|
if (tableName != "") dt.TableName = tableName;
|
return dt;
|
}
|
|
public DataRow GetDataRow(string SQL)
|
{
|
var dt = GetDataSet(SQL).Tables[0];
|
return dt.Rows.Count > 0 ? dt.Rows[0] : null;
|
}
|
|
public int ExecuteSQL(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
return ExecuteSQL(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public int ExecuteCommand(DbCommand cmd)
|
{
|
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 object ExecuteScalar(string SQL)
|
{
|
DbConnection connection = null;
|
DbCommand cmd = null;
|
try
|
{
|
connection = CreateConnection();
|
cmd = CreateCommand(connection, SQL);
|
return cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
Close(connection);
|
}
|
}
|
|
public object ExecuteScalar(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null)
|
cmd.Connection = connection = CreateConnection();
|
|
return cmd.ExecuteScalar();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public DbDataReader ExecuteReader(string SQL)
|
{
|
DbConnection connection = null;
|
try
|
{
|
connection = CreateConnection();
|
return ExecuteReader(SQL, connection);
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public DbDataReader ExecuteReader(DbCommand cmd)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null) cmd.Connection = CreateConnection();
|
return cmd.ExecuteReader();
|
}
|
finally
|
{
|
Close(connection);
|
}
|
}
|
|
public T ExecuteReader<T>(DbCommand cmd) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null) cmd.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()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command = CreateCommand(SQL, CommandType.Text))
|
{
|
command.Connection = connection;
|
var obj = default(T);
|
using (dataReader = command.ExecuteReader())
|
{
|
if (dataReader.Read())
|
obj = DbTools.Convert2Object<T>(dataReader);
|
}
|
|
return obj;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReader<T>(string SQL,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
DbConnection connection = null;
|
DbCommand command = null;
|
DbDataReader dataReader = null;
|
|
try
|
{
|
using (connection = CreateConnection())
|
{
|
using (command =
|
CreateCommand(connection, SQL, CommandType.Text))
|
{
|
var list = new List<T>();
|
using (dataReader = command.ExecuteReader())
|
{
|
while (dataReader.Read())
|
list.Add(action.Invoke(dataReader));
|
}
|
|
return list;
|
}
|
}
|
}
|
finally
|
{
|
if (connection != null) connection.Dispose();
|
if (command != null) command.Dispose();
|
if (dataReader != null) dataReader.Dispose();
|
}
|
}
|
|
public List<T> ExecuteReader<T>(DbCommand cmd,
|
Func<DbDataReader, T> action) where T : new()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null) cmd.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<T> ExecuteReaderList<T>(string SQL) where T : new()
|
{
|
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()
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null) cmd.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<string> GetStringList(string 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)
|
{
|
DbConnection connection = null;
|
try
|
{
|
if (cmd.Connection == null) cmd.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 void Close(DbConnection connection)
|
{
|
if (connection != null)
|
{
|
connection.Close();
|
connection.Dispose();
|
}
|
}
|
|
/// <summary>
|
/// 在事务内提交数据
|
/// </summary>
|
/// <param name="trans">事务</param>
|
/// <param name="SQL">SQL语句</param>
|
/// <returns></returns>
|
public int ExecuteTrans(DbTransaction trans, string SQL)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
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)
|
{
|
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 DbType ToDbType(string sourceType)
|
{
|
if (_DatabaseType == DatabaseType.SqlServer)
|
return new DatabaseMSSQL("").ToDbType(sourceType);
|
|
if (_DatabaseType == DatabaseType.MySQL)
|
return new DatabaseMySQL("").ToDbType(sourceType);
|
|
if (_DatabaseType == DatabaseType.Oracle)
|
return new DatabaseOracle("").ToDbType(sourceType);
|
|
throw new Exception("数据库类型不支持！");
|
}
|
|
public Type ToNetType(string sourceType)
|
{
|
if (_DatabaseType == DatabaseType.SqlServer)
|
return new DatabaseMSSQL("").ToNetType(sourceType);
|
|
if (_DatabaseType == DatabaseType.MySQL)
|
return new DatabaseMySQL("").ToNetType(sourceType);
|
|
if (_DatabaseType == DatabaseType.Oracle)
|
return new DatabaseOracle("").ToNetType(sourceType);
|
|
throw new Exception("数据库类型不支持！");
|
}
|
|
/// <summary>
|
/// 返回包含参数符号的参数名称,比如：@Code, :Code, ?p_Code
|
/// </summary>
|
/// <param name="paramName"></param>
|
/// <returns></returns>
|
public string ParseParamName(string paramName)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.ParseParamName(paramName);
|
}
|
|
|
/// <summary>
|
/// 获取当前表最新(最大)的自增字段值
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <returns></returns>
|
public int GetMaxID(string tableName)
|
{
|
var db =
|
DatabaseFactory.CreateDatabase(_DatabaseType,
|
_ConnectionString);
|
return db.GetMaxID(tableName);
|
}
|
|
|
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 DbConnection CreateConnection(DatabaseType providerType,
|
string connectionString)
|
{
|
var DBConn = DataProviderFactory.GetFactory(providerType)
|
.CreateConnection();
|
DBConn.ConnectionString = connectionString;
|
DBConn.Open(); //打开连接
|
return DBConn;
|
}
|
|
public DbCommand CreateCommand(DbConnection conn, string SQL)
|
{
|
var cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
cmd.CommandTimeout = CommandTimeout;
|
return cmd;
|
}
|
|
public DbCommand CreateCommand(DbConnection conn, string SQL,
|
CommandType type)
|
{
|
var cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = type;
|
cmd.Connection = conn;
|
cmd.CommandTimeout = CommandTimeout;
|
return cmd;
|
}
|
|
public DataSet GetDataSet(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
DbDataAdapter adapter = null;
|
try
|
{
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
|
var ds = new DataSet();
|
adapter = DataProviderFactory.GetFactory(_DatabaseType)
|
.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();
|
}
|
}
|
|
public DataTable GetTable(string SQL, DbConnection conn)
|
{
|
return GetDataSet(SQL, conn).Tables[0];
|
}
|
|
public int ExecuteSQL(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
var i = -1;
|
try
|
{
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
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 object ExecuteScalar(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
object o = null;
|
try
|
{
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
cmd.CommandText = CodeSafeHelper.GetSafeSQL(SQL);
|
cmd.CommandType = CommandType.Text;
|
cmd.Connection = conn;
|
o = cmd.ExecuteScalar();
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
finally
|
{
|
if (cmd != null) cmd.Dispose();
|
}
|
|
return o;
|
}
|
|
public DbDataReader ExecuteReader(string SQL, DbConnection conn)
|
{
|
DbCommand cmd = null;
|
DbDataReader o = null;
|
try
|
{
|
cmd = DataProviderFactory.GetFactory(_DatabaseType)
|
.CreateCommand();
|
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();
|
}
|
|
return o;
|
}
|
}
|
}
|
