protected override ExeResEdm ExecuteNonQueryFromSqlContianer(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
string curSQL = "";
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction oraOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = oraOprate;
foreach (SqlContianer objOraSqlCon in ltSqls)
{
cmd.CommandText = objOraSqlCon.strSqlTxt;
curSQL = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
int intRes = cmd.ExecuteNonQuery();
dBResEdm.ExeNum += intRes;
if (objOraSqlCon.intExpectNums >= 0)
{
if (intRes != objOraSqlCon.intExpectNums)
{
throw new Exception("Update records not match the expect nums");
}
}
else if (objOraSqlCon.intExpectNums != Int16.MinValue)
{
if (intRes != 0 && intRes != objOraSqlCon.intExpectNums * -1)
{
throw new Exception("Update records not match the expect nums");
}
}
}
oraOprate.Commit();
}
catch (Exception ex)
{
oraOprate.Rollback();
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
return(dBResEdm);
}
public void InsertCLOB(string SQLStatement, string str)
{
if (SQLStatement.Length > 0)
{
if (connOracle.State.ToString().Equals("Open"))
{
byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str);
sqlCommandOracle =
new System.Data.OracleClient.OracleCommand(SQLStatement, connOracle);
rstOracle = sqlCommandOracle.ExecuteReader();
rstOracle.Read();
txn = connOracle.BeginTransaction();
clob = rstOracle.GetOracleLob(0);
clob.Write(newvalue, 0, newvalue.Length);
txn.Commit();
}
}
}
protected override ExeResEdm GetDataSets(List <SqlContianer> ltSqls)
{
ExeResEdm dBResEdm = new ExeResEdm();
DataSet ds = new DataSet();
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction tsOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
List <string> tbNames = new List <string>();
foreach (var objOraSqlCon in ltSqls)
{
DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(objOraSqlCon.tableName))
{
dt.TableName = objOraSqlCon.tableName;
}
else
{
string tb = GetTableNameFromSelectSql(objOraSqlCon.strSqlTxt);
if (!string.IsNullOrEmpty(tb))
{
dt.TableName = tb;
}
}
if (tbNames.Contains(dt.TableName))
{
dt.TableName = dt.TableName + "_" + (tbNames.Count() + 1);
}
tbNames.Add(dt.TableName);
cmd.CommandText = objOraSqlCon.strSqlTxt;
cmd.Parameters.Clear();
if (objOraSqlCon.ltOraParams != null && objOraSqlCon.ltOraParams.Count > 0)
{
cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray());
}
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
adapter.Fill(dt);
ds.Tables.Add(dt);
}
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "GetDataSets方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeModel = ds;
return(dBResEdm);
}
protected override ExeResEdm UpdateDsToDB(DataSet dsTables, Dictionary <string, string> dicDtMainFields = null)
{
ExeResEdm dBResEdm = new ExeResEdm();
int n = 0;
try
{
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr))
{
conn.Open();
System.Data.OracleClient.OracleTransaction tsOprate = conn.BeginTransaction();
try
{
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = tsOprate;
foreach (DataTable dtTemp in dsTables.Tables)
{
string strComFields = "*";
if (dicDtMainFields != null && dicDtMainFields.Count > 0 && dicDtMainFields.ContainsKey(dtTemp.TableName))
{
strComFields = !string.IsNullOrEmpty(dicDtMainFields[dtTemp.TableName]) ? dicDtMainFields[dtTemp.TableName]: strComFields;
}
cmd.CommandText = GetColumnsNameSql(dtTemp.TableName, strComFields);
System.Data.OracleClient.OracleDataAdapter adapter = new System.Data.OracleClient.OracleDataAdapter(cmd);
var dtChanges = dtTemp.GetChanges();
adapter.FillSchema(dtChanges, SchemaType.Mapped); //new added
if (dtChanges != null) //是添加或更新
{
adapter.UpdateCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetUpdateCommand();
n += adapter.Update(dtChanges);
dtTemp.AcceptChanges();
}
else //是删除
{
adapter.DeleteCommand = new System.Data.OracleClient.OracleCommandBuilder(adapter).GetDeleteCommand();
for (int i = dtTemp.Rows.Count - 1; i >= 0; i--)
{
dtTemp.Rows[i].Delete();
}
n += adapter.Update(dtTemp);
}
}
dsTables.AcceptChanges();
tsOprate.Commit();
}
catch (Exception ex)
{
tsOprate.Rollback();
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
}
}
catch (Exception ex)
{
dBResEdm.Module = "UpdateDsToDB方法";
dBResEdm.ExBody = ex;
dBResEdm.ErrCode = 1;
return(dBResEdm);
}
dBResEdm.ExeNum = n;
return(dBResEdm);
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTns()
{
_tns.Commit();
}
public void Commit()
{
_trans.Commit();
}