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(); }