/// <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>
/// 查询 返回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>
/// <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);
}