public static DBAct InitDBAct(DBTypeEnum.DBType dbtype, string connectstring)
{
DBAct dbact = new DBAct();
dbact.ConnectionStr = connectstring;
dbact.DBType = Enum.GetName(dbtype.GetType(), dbtype);
dbact.DBTypeValue = (int)dbtype;
dbact.DBABase = new DBActBase(connectstring);
return(dbact);
}
/// <summary>
/// 初始化配置
/// </summary>
/// <param name="Type">数据库类型</param>
public static void InitSetting(DBTypeEnum.DBType Type)
{
try
{
ConnectConfig = ConfigurationManager.ConnectionStrings;
AppSetingConfig = ConfigurationManager.AppSettings;
LogPath = ConfigurationManager.AppSettings["LogPath"];//日志文件路径不含文件名
DBType = Enum.GetName(Type.GetType(), Type);
ConnectString = ConfigurationManager.ConnectionStrings[DBType].ToString();
KEY = ConfigurationManager.AppSettings["Key"];
}
catch (System.Exception ex)
{
Log.LogInBatchExceptWrite(ex, "文件读取配置部分错误！");
}
}
/// <summary>
/// 查询 返回dataset 注意不同数据库的sql不太一样
/// </summary>
/// <param name="SQL">查询sql</param>
/// <returns>dataset</returns>
public DataSet DBSelectDS(string SQL)
{
var dataSet = new DataSet();
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
adapt.SelectCommand = cmd;
adapt.Fill(dataSet);
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(dataSet);
}
/// <summary>
/// 执行多条sql语句或特殊语句
/// </summary>
/// <param name="SQL">sql脚本</param>
/// <param name="IsTransAction">是否开启事务 默认true</param>
/// <returns>受影响条数</returns>
public int DBOther(string SQL, bool IsTransAction = true)
{
var num = 0;
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
if (IsTransAction)
{
using (OracleTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
if (IsTransAction)
{
using (OleDbTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
if (IsTransAction)
{
using (SqlTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
if (IsTransAction)
{
using (SQLiteTransaction orclTrans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
num = cmd.ExecuteNonQuery();
orclTrans.Commit();
}
catch (Exception ex)
{
orclTrans.Rollback();
throw ex;
}
}
}
else
{
num = cmd.ExecuteNonQuery();
}
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(num);
}
/// <summary>
/// 更新 返回受影响行数 注意不同数据库的sql不太一样
/// </summary>
/// <param name="SQL">更新SQL语句</param>
/// <returns>更新条数</returns>
public int DBUpdata(string SQL)
{
var num = 0;
if (!string.IsNullOrEmpty(SQL) && DBABase != null)
{
DBTypeEnum.DBType dbtype = (DBTypeEnum.DBType)DBTypeValue;
switch (dbtype)
{
case DBTypeEnum.DBType.Oracle:
var tupOracle = DBABase.GetOracleConnnect(SQL);
using (var connection = tupOracle.Item1)
{
try
{
var cmd = tupOracle.Item2;
var adapt = tupOracle.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Access:
var tupAccess = DBABase.GetAccessConnnect(SQL);
using (var connection = tupAccess.Item1)
{
try
{
var cmd = tupAccess.Item2;
var adapt = tupAccess.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.SqlServer:
var tupSqlServer = DBABase.GetSqlServerConnnect(SQL);
using (var connection = tupSqlServer.Item1)
{
try
{
var cmd = tupSqlServer.Item2;
var adapt = tupSqlServer.Item3;
num = cmd.ExecuteNonQuery();
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
case DBTypeEnum.DBType.Sqlite:
var tupSqlite = DBABase.GetSqliteConnnect(SQL);
using (var connection = tupSqlite.Item1)
{
try
{
var cmd = tupSqlite.Item2;
var adapt = tupSqlite.Item3;
num = cmd.ExecuteNonQuery(); //如果有返回条数，但数据库数据为变化，需要将SQLite.Interop.066.DLL复制到输出目录
connection.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
break;
}
}
return(num);
}
public DBActBase(DBTypeEnum.DBType dbtype, string connectstr)
{
this.ConnectionStr = connectstr;
this.DBType = Enum.GetName(dbtype.GetType(), dbtype);
this.DBTypeValue = (int)dbtype;
}
