public void test() { OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM BARANG", conn.c); OracleCommandBuilder cb = new OracleCommandBuilder(da); Console.WriteLine(); Console.WriteLine(cb.GetInsertCommand().CommandText); Console.WriteLine(); Console.WriteLine(cb.GetUpdateCommand().CommandText); Console.WriteLine(); Console.WriteLine(); Console.WriteLine(cb.GetDeleteCommand().CommandText); Console.WriteLine(); //OracleCommand cmd = cb.GetInsertCommand(); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add(1); //cmd.Parameters.Add("aku"); //cmd.Parameters.Add(2); //cmd.Parameters.Add(1); //cmd.ExecuteNonQuery(); }
private void button2_Click(object sender, EventArgs e) { if (conn.State == ConnectionState.Open) { conn.Close(); } try { conn.Open(); string sql2 = "SELECT id_transaksi FROM h_transaksi WHERE id_kamar = '" + comboBox1.Text + "' ORDER BY tgl_checkin DESC"; OracleCommand cmd2 = new OracleCommand(sql2, conn); string kode = cmd2.ExecuteScalar().ToString(); OracleCommand cmd = new OracleCommand("select * from d_transaksi where id_transaksi = '" + kode + "'", conn); OracleDataAdapter od = new OracleDataAdapter(cmd); builder = new OracleCommandBuilder(od); od.InsertCommand = builder.GetInsertCommand(); od.Update(datatablessource); updatedata(kode); conn.Close(); } catch (Exception ex) { conn.Close(); MessageBox.Show(ex.Message); } }
public static void Main() { OracleConnection thisConnection = new OracleConnection( @"data source=topcredu;User ID=scott;Password=tiger"); thisConnection.Open(); OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * from emp", thisConnection); OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter); Console.WriteLine("SQL SELECT Command is:\n{0}\n", thisAdapter.SelectCommand.CommandText); OracleCommand updateCommand = thisBuilder.GetUpdateCommand(); Console.WriteLine("SQL UPDATE Command is:\n{0}\n", updateCommand.CommandText); OracleCommand insertCommand = thisBuilder.GetInsertCommand(); Console.WriteLine("SQL INSERT Command is:\n{0}\n", insertCommand.CommandText); OracleCommand deleteCommand = thisBuilder.GetDeleteCommand(); Console.WriteLine("SQL DELETE Command is:\n{0}", deleteCommand.CommandText); thisConnection.Close(); }
public DbDataAdapter CreateDataAdapter(string selectCommandText, IDbConnection connection) { var oracleConnection = (OracleConnection)connection; var dataAdapter = new OracleDataAdapter(selectCommandText, oracleConnection); var commandBuilder = new OracleCommandBuilder(dataAdapter); try { dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); } catch { } try { dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); } catch { } try { dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); } catch { } return(dataAdapter); }
public static void Main() { string str = @"Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.27)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = topcredu) ) ) ; User Id = scott; Password = tiger"; OracleConnection thisConnection = new OracleConnection(str); thisConnection.Open(); OracleDataAdapter thisAdapter = new OracleDataAdapter("SELECT * from emp", thisConnection); OracleCommandBuilder thisBuilder = new OracleCommandBuilder(thisAdapter); Console.WriteLine("SQL SELECT Command is:\n{0}\n", thisAdapter.SelectCommand.CommandText); OracleCommand updateCommand = thisBuilder.GetUpdateCommand(); Console.WriteLine("SQL UPDATE Command is:\n{0}\n", updateCommand.CommandText); OracleCommand insertCommand = thisBuilder.GetInsertCommand(); Console.WriteLine("SQL INSERT Command is:\n{0}\n", insertCommand.CommandText); OracleCommand deleteCommand = thisBuilder.GetDeleteCommand(); Console.WriteLine("SQL DELETE Command is:\n{0}", deleteCommand.CommandText); thisConnection.Close(); }
void comboBox_CurrentChanged(string curItem) { //string curItem = (string)sender; string sql = @"select * from tracker_tb_vl where sgroup = '%group%' and prj = 'BPGOM' order by 1,2".Replace("%group%", curItem); try { //ds = New DataSet("sGroup") da_sGroup = new OracleDataAdapter(sql, cnn); da_sGroup.AcceptChangesDuringUpdate = true; OracleCommandBuilder cb = new OracleCommandBuilder(da_sGroup); //set_upd_cmd() da_sGroup.UpdateCommand = cb.GetUpdateCommand(); da_sGroup.InsertCommand = cb.GetInsertCommand(); da_sGroup.DeleteCommand = cb.GetDeleteCommand(); DataTable dt = MyDb.Oracle.sql2DT(sql, (System.Data.Common.DbConnection)cnn); dt.TableName = "sGroup"; ds = new DataSet("sGroup"); ds.Tables.Add(dt); //Class_Db_Oracle.get_crud(ref canInsert, ref canSelect, ref canUpdate, ref canDelete, wbs, // Class_Common.CurrentUserDisc(xMainWindow), cnn);//.ParentForm), cnn); xDataGrid.DataSource = ds.Tables["sGroup"].DefaultView; //ultraGrid1.DataMember = "sGroup"; } catch (Exception ex) { Console.WriteLine(ex.Message); } }
/// <summary> /// 단일 실행형(일부 컬럼만 업데이트 할때) /// </summary> /// <param name="dt">업데이트할 테이블</param> /// <param name="strColNm">수정할 컬럼이름</param> /// <returns></returns> public int updateTable(DataTable dt, string strTableName, string strColNm) { int affectRow = 0; OracleCommandBuilder cb; this.oDataAdapter = new OracleDataAdapter("SELECT " + strColNm + " FROM " + strTableName, this.oConn); cb = new OracleCommandBuilder(this.oDataAdapter); this.oDataAdapter.DeleteCommand = cb.GetDeleteCommand(); this.oDataAdapter.InsertCommand = cb.GetInsertCommand(); this.oDataAdapter.UpdateCommand = cb.GetUpdateCommand(); try { this.oConn.Open(); this.oTr = this.oConn.BeginTransaction(); this.oDataAdapter.DeleteCommand.Transaction = this.oTr; this.oDataAdapter.InsertCommand.Transaction = this.oTr; this.oDataAdapter.UpdateCommand.Transaction = this.oTr; affectRow = this.oDataAdapter.Update(dt); this.oTr.Commit(); return(affectRow); } catch (Exception ex) { this.oTr.Rollback(); this.oConn.Close(); throw new Exception("DB 업로드 오류", ex); } }
public void InsertRow(string tableName, List <DbColumnNamesModel> l) { if (tableName == null || tableName == "") { return; } using (var cmd = _databaseConnection.CreateCommand()) { cmd.CommandText = "select * from " + tableName; DataTable table = new DataTable(); using (var adapter = new OracleDataAdapter(cmd)) { adapter.Fill(table); DataRow dataRow = table.NewRow(); for (int i = 0; i < l.Count; i++) { dataRow[l[i].Name] = l[i].Value; } table.Rows.Add(dataRow); OracleCommandBuilder commandBuilder = new OracleCommandBuilder(adapter); //adapter.UpdateCommand = commandBuilder.GetUpdateCommand(); adapter.InsertCommand = commandBuilder.GetInsertCommand(); adapter.Update(table); } } }
/// <summary> /// 批量插入数据。 /// </summary> /// <param name="dataTable">数据表</param> /// <param name="tableName">要插入的数据表名称</param> /// <param name="connKey">连接配置名称</param> public override void BatchInsert(DataTable dataTable, string tableName, string connKey = null) { var sql = string.Empty; try { this.Open(connKey); this.Command.CommandType = CommandType.Text; this.Command.CommandText = "SELECT * FROM " + tableName; var dt = dataTable.Copy(); foreach (DataRow dr in dt.Rows) { if (dr.RowState == DataRowState.Unchanged) { dr.SetAdded(); } } this.Command.Transaction = this.Connection.BeginTransaction(); var da = new OracleDataAdapter((OracleCommand)this.Command); var cb = new OracleCommandBuilder(da); da.InsertCommand = cb.GetInsertCommand(); da.Update(dt); this.Command.Transaction.Commit(); } catch (Exception ex) { throw new DataObjectException("批量插入数据时出现错误:" + ex.Message + "\r\n" + sql, ex); } }
/// <summary> /// 先存入DataTable中,再插入,实际不快,为什么 /// </summary> /// <param name="comm"></param> private void BatchInsert(OracleCommand comm) { Msg += "初始化前:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; DataTable dt = new DataTable(); int count = GetTimes(); dt.TableName = "AAATest"; dt.Columns.Add("EID", typeof(String)); dt.Columns.Add("FROMID", typeof(String)); dt.Columns.Add("TOID", typeof(String)); dt.Columns.Add("LL", typeof(Int32)); dt.Columns.Add("YW", typeof(Int32)); dt.Columns.Add("LXSJ", typeof(Int32)); Msg += "初始化中:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; OracleDataAdapter da = new OracleDataAdapter(comm); OracleCommandBuilder ocb = new OracleCommandBuilder(da); da.SelectCommand.CommandText = "SELECT * FROM AAA"; da.InsertCommand = ocb.GetInsertCommand(); Msg += "初始化后:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; for (int i = 0; i < count; i++) { dt.Rows.Add(new Object[] { DateTime.Now.ToString("HH:mm:ss fff"), "1", "1", 1, 1, 1 }); } Msg += "提交前:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; da.Update(dt); Msg += "提交后:" + DateTime.Now.ToString("HH:mm:ss fff") + "\n"; }
private void Booking_Flight_Load(object sender, EventArgs e) { transAdapter = new OracleDataAdapter($"select customer_name as Nama,customer_email as Email,customer_alamat as Alamat,customer_phone as Telefon,CUSTOMER_PASSPORT as Passport from customer where 1 = 2", conn); OracleCommandBuilder builder = new OracleCommandBuilder(transAdapter); transAdapter.InsertCommand = builder.GetInsertCommand(); transTable = new DataTable(); transAdapter.Fill(transTable); dgv_pesanan.DataSource = transTable; }
/// <summary> /// 批量插入数据 /// </summary> /// <param name="strSQL">插入数据表查询SQL</param> /// <param name="dt">数据集</param> /// <param name="connectionString">数据库连接字符串,默认值为null 即表示使用默认连接字符串</param> public static void MultiInsert(string strSQL, DataTable dt, string connectionString = null) { if (string.IsNullOrEmpty(connectionString)) { connectionString = ConnectionScope.GetDefaultConnectionString(); if (string.IsNullOrEmpty(connectionString)) { throw new System.Exception("请先调用Citms.Data.Extensions.Initializer.UnSafeInit(connectionString)设置默认连接字符串"); } } if (string.IsNullOrEmpty(strSQL)) { throw new ArgumentNullException(); } OracleConnection connection = null; OracleCommand command = null; OracleTransaction _transcation = null; try { using (connection = new OracleConnection(connectionString)) { connection.Open(); _transcation = connection.BeginTransaction(); using (command = connection.CreateCommand()) { command.CommandText = strSQL; command.Transaction = _transcation; OracleDataAdapter da = new OracleDataAdapter(command); OracleCommandBuilder cb = new OracleCommandBuilder(da); da.InsertCommand = cb.GetInsertCommand(true); da.Update(dt); _transcation.Commit(); } } } catch (System.Exception ex) { if (_transcation != null) { _transcation.Rollback(); } throw ex; } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
//////////////////////////////////////////////////// public override IDbCommand GetInsertCommand(DataTable table, IDbDataAdapter adapter, bool bDisableIdsAuto) { DataColumn[] cols = table.PrimaryKey; string strChampId = null; if (cols != null && cols.Length == 1) { if (cols[0].AutoIncrement) { strChampId = cols[0].ColumnName; } } OracleCommandBuilder builder = new OracleCommandBuilder((OracleDataAdapter)adapter); bool bAvecTriggerMajAuto = true; COracleDatabaseConnexion conOracle = m_connexion as COracleDatabaseConnexion; if (conOracle != null) { if (strChampId != null) { conOracle.GetNomSequenceColAuto(table.TableName, strChampId, ref bAvecTriggerMajAuto); } } if (strChampId != null && !bAvecTriggerMajAuto) { table.Columns[strChampId].AutoIncrement = false; } OracleCommand cmdInsert = builder.GetInsertCommand(); if (strChampId != null && !bAvecTriggerMajAuto) { table.Columns[strChampId].AutoIncrement = true; } if (m_connexion.IsInTrans()) { cmdInsert.Transaction = (OracleTransaction)m_connexion.Transaction; } if (strChampId != null) { C2iOracleDataAdapter oracleAdapter = C2iOracleDataAdapter.GetOracleDataAdapter(adapter); if (oracleAdapter != null && !bDisableIdsAuto) { oracleAdapter.PreparerInsertionLigneAvecAutoID(table.TableName, strChampId); } cmdInsert.UpdatedRowSource = UpdateRowSource.Both; } return(cmdInsert); }
public static int ImportTableInto(DataTable impTb, string intoTb, string select, int clear) { int count = 0; DataTable target = null; //导入前是否先清空 if (clear == 1) { DBAccess.RunSQL("delete from " + intoTb); } try { target = DBAccess.RunSQLReturnTable(select); } catch (Exception ex) //select查询出错,可能是缺少列 { throw new Exception("源表格式有误,请核对!" + ex.Message + " :" + select); } object conn = DBAccess.GetAppCenterDBConn; SqlDataAdapter sqlada = null; OracleDataAdapter oraada = null; DBType dbt = DBAccess.AppCenterDBType; if (dbt == DBType.MSSQL) { sqlada = new SqlDataAdapter(select, (SqlConnection)DBAccess.GetAppCenterDBConn); SqlCommandBuilder bl = new SqlCommandBuilder(sqlada); sqlada.InsertCommand = bl.GetInsertCommand(); count = ImportTable(impTb, target, sqlada); } else if (dbt == DBType.Oracle) { oraada = new OracleDataAdapter(select, (OracleConnection)DBAccess.GetAppCenterDBConn); OracleCommandBuilder bl = new OracleCommandBuilder(oraada); oraada.InsertCommand = bl.GetInsertCommand(); count = ImportTable(impTb, target, oraada); } else { throw new Exception("未获取数据库连接! "); } target.Dispose(); return(count); }
public int Insert(Hashtable hashTable, string SourceTable) { OracleConnection oracleConn = dbConectorObj.GetConnection; OpenAppConnection(); try { OracleCommand oracleComd = new OracleCommand("SELECT * FROM " + SourceTable + " WHERE NOT(1=1)", oracleConn); OracleDataAdapter ODP = new OracleDataAdapter(oracleComd); DataSet DS = new DataSet(); ODP.Fill(DS, SourceTable); DataRow drAddrow = DS.Tables[0].NewRow(); foreach (object OBJ in hashTable.Keys) { string colName = Convert.ToString(OBJ); drAddrow[colName] = hashTable[OBJ]; } DS.Tables[0].Rows.Add(drAddrow); OracleCommandBuilder ocmd = new OracleCommandBuilder(ODP); ODP.InsertCommand = ocmd.GetInsertCommand(); return(ODP.Update(DS, SourceTable)); } catch (OracleException ex) { if (Trans != null) { Trans.Rollback(); Trans = null; } throw ex; } catch (Exception Ex) { if (Trans != null) { Trans.Rollback(); Trans = null; } throw Ex; } finally { if (Trans == null) { CloseAppConnection(); } } }
public void UpdateTableContent(string tableName, DataTable table) { using (OracleCommand command = _connection.CreateCommand( )) { command.CommandText = string.Format("select * from {0}", tableName); using (OracleDataAdapter adapter = new OracleDataAdapter(command)) { using (OracleCommandBuilder builder = new OracleCommandBuilder(adapter)) { adapter.UpdateCommand = builder.GetUpdateCommand( ); adapter.InsertCommand = builder.GetInsertCommand( ); adapter.DeleteCommand = builder.GetDeleteCommand( ); adapter.Update(table); } } } }
/// <summary> /// Update Multi Table /// </summary> /// <param name="ds">Multi Update Data</param> /// <param name="sqlList">Multi Select sql</param> /// <returns></returns> public ResultData UpdateMultiTable(List <DataTable> ds, List <string> sqlList) { var connection = CreateConnection(); OracleConnection oracleConn = (OracleConnection)connection; OracleTransaction tran = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted); ResultData resultData = new ResultData(); resultData.Status = ResultStatus.Fail; try { foreach (DataTable inputDt in ds) { OracleCommand command = new OracleCommand(sqlList[ds.IndexOf(inputDt)], oracleConn); OracleDataAdapter dataAdapter = new OracleDataAdapter(); dataAdapter.SelectCommand = command; OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); command.Transaction = tran; int rows = dataAdapter.Update(inputDt); if (rows >= 1) { resultData.Status = ResultStatus.Success; } else { tran.Rollback(); return(resultData); } } tran.Commit(); } catch (Exception ex) { tran.Rollback(); throw ex; } return(resultData); }
public bool Save(DataTable dt, out int i) { try { OracleDataAdapter oda = new OracleDataAdapter(); StringBuilder selectOracle = new StringBuilder(256); selectOracle.AppendFormat(" Select * From {0} Where 1!=1 ", dt.TableName); OracleCommand selectCommand = new OracleCommand(selectOracle.ToString(), Connection); selectCommand.Transaction = Tran; oda.SelectCommand = selectCommand; OracleCommandBuilder ocb = new OracleCommandBuilder(oda); oda.InsertCommand = ocb.GetInsertCommand(); oda.UpdateCommand = ocb.GetUpdateCommand(); i = oda.Update(dt); return(true); } catch { throw; } }
//연속실행형 public void updateDataSet(DataSet ds) { //DataTable dt; int i = 0; OracleCommandBuilder cb; OracleDataAdapter[] oDA = new OracleDataAdapter[ds.Tables.Count]; foreach (DataTable dt in ds.Tables) { oDA[i] = new OracleDataAdapter("SELECT * FROM " + dt.TableName, this.oConn); cb = new OracleCommandBuilder(oDA[i]); oDA[i].DeleteCommand = cb.GetDeleteCommand(); oDA[i].InsertCommand = cb.GetInsertCommand(); oDA[i].UpdateCommand = cb.GetUpdateCommand(); i++; } try { i = 0; this.oConn.Open(); this.oTr = this.oConn.BeginTransaction(); foreach (DataTable updateTable in ds.Tables) { oDA[i].DeleteCommand.Transaction = this.oTr; oDA[i].InsertCommand.Transaction = this.oTr; oDA[i].UpdateCommand.Transaction = this.oTr; oDA[i].Update(updateTable); i++; } this.oTr.Commit(); } catch (Exception oErr) { this.oTr.Rollback(); this.oConn.Close(); throw oErr; } }
/// <summary> /// use oraclecommand update DB by sql string and update column /// </summary> /// <param name="inputDT"></param> /// <param name="sql"></param> /// <returns></returns> public ResultData UpdateOracleDB(DataTable inputDT, string sql) { var connection = CreateConnection(); OracleConnection oracleConn = (OracleConnection)connection; OracleCommand command = new OracleCommand(sql, oracleConn); OracleTransaction tran = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted); ResultData resultData = new ResultData(); resultData.Status = ResultStatus.Fail; command.Transaction = tran; try { OracleDataAdapter dataAdapter = new OracleDataAdapter(); dataAdapter.SelectCommand = command; OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter); dataAdapter.InsertCommand = commandBuilder.GetInsertCommand(); dataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand(); dataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand(); int rows = dataAdapter.Update(inputDT); if (rows >= 1) { tran.Commit(); resultData.Status = ResultStatus.Success; return(resultData); } else { tran.Rollback(); return(resultData); } } catch (Exception ex) { tran.Rollback(); throw ex; } }
/// <summary> /// 表批量写入 /// 根据行数据 RowState 状态新增、修改 /// </summary> /// <param name="dt">数据表</param> /// <param name="sqlEmpty">查询空表脚本,默认*,可选列,会影响数据更新的列</param> /// <param name="dataAdapter">执行前修改(命令行脚本、超时等信息)</param> /// <param name="openTransaction">开启事务,默认开启</param> /// <returns></returns> public int BulkBatchOracle(DataTable dt, string sqlEmpty = null, Action <OracleDataAdapter> dataAdapter = null, bool openTransaction = true) { return(SafeConn(() => { var connection = (OracleConnection)Connection; OracleTransaction transaction = openTransaction ? (OracleTransaction)(Transaction = connection.BeginTransaction()) : null; var cb = new OracleCommandBuilder(); if (string.IsNullOrWhiteSpace(sqlEmpty)) { var sntn = SqlSNTN(dt.TableName, dt.Namespace, SharedEnum.TypeDB.Oracle); sqlEmpty = SqlEmpty(sntn); } cb.DataAdapter = new OracleDataAdapter { SelectCommand = new OracleCommand(sqlEmpty, connection) }; cb.ConflictOption = ConflictOption.OverwriteChanges; var da = new OracleDataAdapter { InsertCommand = cb.GetInsertCommand(true), UpdateCommand = cb.GetUpdateCommand(true) }; da.InsertCommand.CommandTimeout = 300; da.UpdateCommand.CommandTimeout = 300; //执行前修改 dataAdapter?.Invoke(da); var num = da.Update(dt); transaction?.Commit(); return num; })); }
public OracleDataAdapter GetDataAdapterForOracle(string Sql, List <ReportFilter> Filter = null) { OracleConnection conn = null; OracleDataAdapter adp = null; OracleCommandBuilder cmdb = null; try { ConnectionDriversConfig driversConfig = DMEEditor.Utilfunction.LinkConnection2Drivers(Dataconnection.ConnectionProp); //string adtype = Dataconnection.DataSourceDriver.AdapterType; //string cmdtype = Dataconnection.DataSourceDriver.CommandBuilderType; //string cmdbuildername = driversConfig.CommandBuilderType; //Type adcbuilderType = Type.GetType("OracleCommandBuilder"); //List<ConstructorInfo> lsc = DMEEditor.assemblyHandler.GetInstance(adtype).GetType().GetConstructors().ToList(); ; //List<ConstructorInfo> lsc2 = DMEEditor.assemblyHandler.GetInstance(cmdbuildername).GetType().GetConstructors().ToList(); ; //ConstructorInfo ctor = lsc[GetCtorForAdapter(lsc)]; //ConstructorInfo BuilderConstructer = lsc2[GetCtorForCommandBuilder(adcbuilderType.GetConstructors().ToList())]; //ObjectActivator<Oracle.ManagedDataAccess.Client.OracleDataAdapter> adpActivator = GetActivator<Oracle.ManagedDataAccess.Client.OracleDataAdapter>(ctor); //ObjectActivator<Oracle.ManagedDataAccess.Client.OracleCommandBuilder> cmdbuilderActivator = GetActivator<Oracle.ManagedDataAccess.Client.OracleCommandBuilder>(BuilderConstructer); //create an instance: // adp = OracleDataAdapter( RDBMSConnection.DbConn); conn = (OracleConnection)RDBMSConnection.DbConn; adp = new OracleDataAdapter(Sql, conn); cmdb = new OracleCommandBuilder(adp); try { //Oracle.ManagedDataAccess.Client.OracleCommand cmdBuilder = cmdbuilderActivator(adp); if (Filter != null) { if (Filter.Count > 0) { if (Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue) && !string.IsNullOrEmpty(p.Operator) && !string.IsNullOrWhiteSpace(p.Operator)).Any()) { foreach (ReportFilter item in Filter.Where(p => !string.IsNullOrEmpty(p.FilterValue) && !string.IsNullOrWhiteSpace(p.FilterValue))) { OracleParameter parameter = adp.SelectCommand.CreateParameter(); string dr = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue; parameter.ParameterName = "p_" + item.FieldName; if (item.valueType == "System.DateTime") { parameter.DbType = DbType.DateTime; parameter.Value = DateTime.Parse(dr).ToShortDateString(); } else { parameter.Value = dr; } if (item.Operator.ToLower() == "between") { OracleParameter parameter1 = adp.SelectCommand.CreateParameter(); parameter1.ParameterName = "p_" + item.FieldName + "1"; parameter1.DbType = DbType.DateTime; string dr1 = Filter.Where(i => i.FieldName == item.FieldName).FirstOrDefault().FilterValue1; parameter1.Value = DateTime.Parse(dr1).ToShortDateString(); adp.SelectCommand.Parameters.Add(parameter1); } // parameter.DbType = TypeToDbType(tb.Columns[item.fieldname].DataType); adp.SelectCommand.Parameters.Add(parameter); } } } } // adp.ReturnProviderSpecificTypes = true; adp.SuppressGetDecimalInvalidCastException = true; adp.InsertCommand = cmdb.GetInsertCommand(true); adp.UpdateCommand = cmdb.GetUpdateCommand(true); adp.DeleteCommand = cmdb.GetDeleteCommand(true); } catch (Exception ex) { // DMEEditor.AddLogMessage("Fail", $"Error in Creating builder commands {ex.Message}", DateTime.Now, -1, ex.Message, Errors.Failed); } adp.MissingSchemaAction = MissingSchemaAction.AddWithKey; adp.MissingMappingAction = MissingMappingAction.Passthrough; ErrorObject.Flag = Errors.Ok; } catch (Exception ex) { DMEEditor.AddLogMessage("Fail", $"Error in Creating Adapter {ex.Message}", DateTime.Now, -1, ex.Message, Errors.Failed); adp = null; } return(adp); }
//oracle连接 public void UpdateData() { System.Data.OracleClient.OracleTransaction sqltran; //将事务绑定到连接对像 //init.DBConnect(); try { //writeLog.Write("" + dtSave.Count, "log"); using (OracleConnection connection = new OracleConnection(OracleHelper.GetConnectionstring())) { sqltran = connection.BeginTransaction();// ConnectDB.con.BeginTransaction(); for (int i = 0; i < dtSave.Count; i++) { OracleDataAdapter adapter = new OracleDataAdapter(selectStr[i], connection); OracleCommandBuilder cmdBuilder = new OracleCommandBuilder(adapter); try { adapter.SelectCommand = new OracleCommand(selectStr[i], ConnectDB.con, sqltran); adapter.InsertCommand = cmdBuilder.GetInsertCommand(); adapter.UpdateCommand = cmdBuilder.GetUpdateCommand(); if (dtSave[i].Select(null, null, DataViewRowState.Deleted).Length > 0) { adapter.Update(dtSave[i].Select(null, null, DataViewRowState.Deleted)); } if (dtSave[i].Select(null, null, DataViewRowState.ModifiedCurrent).Length > 0) { adapter.Update(dtSave[i].Select(null, null, DataViewRowState.ModifiedCurrent)); } if (dtSave[i].Select(null, null, DataViewRowState.Added).Length > 0) { adapter.Update(dtSave[i].Select(null, null, DataViewRowState.Added)); } } catch (Exception ex) { writeLog.Write("UpdateData处理:" + ex.Message, "log"); } } try { sqltran.Commit(); } catch (Exception ex1) { sqltran.Rollback(); writeLog.Write("事务提交失败:" + ex1.Message, "log"); } finally { connection.Close(); connection.Dispose(); } } for (int i = 0; i < strGraph.Count; i++) { //writeLog.Write(strGraph[i].ToString(), "log"); System.IO.FileStream fs = new System.IO.FileStream(strGraph[i].ToString(), System.IO.FileMode.OpenOrCreate); byte[] blob = new byte[fs.Length]; fs.Read(blob, 0, blob.Length); fs.Close(); strUpdateSQL = "update test_graph_result set GRAPH_RESULT = :Photo where GRAPH_RESULT_ID = '" + strGraphID[i] + "'"; OracleCommand oraCmd = new OracleCommand(strUpdateSQL, ConnectDB.con); OracleParameter op = new OracleParameter("Photo", OracleType.Blob); op.Value = blob; oraCmd.Parameters.Add(op); try { oraCmd.ExecuteNonQuery(); } catch (Exception ex) { writeLog.Write("UpdateDataIO处理:" + ex.Message, "log"); } } } catch (System.Data.OracleClient.OracleException sqlex) { // sqltran.Rollback(); writeLog.Write("UpdateData_sqlex:" + sqlex.Message, "log"); //return sqlex.Message; } catch (Exception ex) { // sqltran.Rollback(); writeLog.Write("UpdateData_catch:" + ex.Message, "log"); //return ex.Message; } finally { sqltran = null; } }
/// <summary> /// ����selectedCommandText�����OracleDataAdapter�����Զ����� /// InsertCommand��DeleteCommand��UpdateCommand /// </summary> /// <param name="selectedCommandText">selectedCommandText</param> /// <returns>The SqlDataAdapter</returns> public override IDbDataAdapter GetDataAdapter(string selectCommandText, IDbConnection connection) { OracleDataAdapter adapter = new OracleDataAdapter(selectCommandText,(OracleConnection)connection); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; using(OracleCommandBuilder builder = new OracleCommandBuilder(adapter)) { adapter.InsertCommand = (OracleCommand)((ICloneable)builder.GetInsertCommand()).Clone(); adapter.DeleteCommand = (OracleCommand)((ICloneable)builder.GetDeleteCommand()).Clone(); adapter.UpdateCommand = (OracleCommand)((ICloneable)builder.GetUpdateCommand()).Clone(); } return adapter; }
private OperationReturn SaveDomainInfo(SessionInfo session, List <string> listParams) { OperationReturn optReturn = new OperationReturn(); optReturn.Result = true; optReturn.Code = 0; string Mes = string.Empty; try { //ListParam //0 操作编号:0:删除;1:添加;2:修改 //1 被操作的对象DomainInfo类系列化的string if (listParams == null || listParams.Count < 2) { optReturn.Result = false; optReturn.Code = Defines.RET_PARAM_INVALID; optReturn.Message = string.Format("Request param is null or count invalid"); return(optReturn); } string OperationCode = listParams[0]; optReturn = XMLHelper.DeserializeObject <BasicDomainInfo>(listParams[1]); BasicDomainInfo DI = optReturn.Data as BasicDomainInfo; string domainName = EncryptToDB002(DI.DomainName); string rentToken = session.RentInfo.Token; string strSql = string.Empty; DataSet objDataSet; if (OperationCode == "1") { switch (session.DBType) { //MSSQL case 2: using (SqlConnection connection = new SqlConnection(session.DBConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); strSql = string.Format("SELECT * FROM T_00_012 WHERE C002 = '{0}' AND C009=0 ORDER BY C004", rentToken); SqlDataAdapter sqlDA = new SqlDataAdapter(strSql, connection); sqlDA.Fill(dataSet); //设置主键 //dataSet.Tables[0].PrimaryKey = new DataColumn[] { dataSet.Tables[0].Columns[0] }; SqlCommandBuilder sqlCB = new SqlCommandBuilder(sqlDA); sqlDA.InsertCommand = sqlCB.GetInsertCommand(); int drCurrent = dataSet.Tables[0].Select(string.Format("C003 = '{0}'", domainName)).Count() > 0 ? 1 : 0; if (drCurrent == 0) { optReturn = SaveNewDomainInfo(dataSet, session, DI); Mes += "SaveNewDomainInfo ok/"; if (optReturn.Result) { dataSet = optReturn.Data as DataSet; sqlDA.Update(dataSet); dataSet.AcceptChanges(); } } else { sqlDA.Dispose(); connection.Close(); //如果重名,返回报错提醒 optReturn.Result = false; optReturn.Code = 99; return(optReturn); } sqlDA.Dispose(); connection.Close(); } break; //ORCL case 3: using (OracleConnection connection = new OracleConnection(session.DBConnectionString)) { DataSet dataSet = new DataSet(); connection.Open(); strSql = string.Format("SELECT * FROM T_00_012 WHERE C002 = '{0}' AND C009=0 ORDER BY C004", rentToken); OracleDataAdapter oracleDA = new OracleDataAdapter(strSql, connection); oracleDA.Fill(dataSet); //设置主键 //dataSet.Tables[0].PrimaryKey = new DataColumn[] { dataSet.Tables[0].Columns[0] }; OracleCommandBuilder oracleCB = new OracleCommandBuilder(oracleDA); oracleDA.InsertCommand = oracleCB.GetInsertCommand(); int drCurrent = dataSet.Tables[0].Select(string.Format("C003='{0}'", domainName)).Count() > 0 ? 1 : 0; //dataSet.Tables[0].Rows.Find(newRole.RoleID.ToString()); if (drCurrent == 0) { optReturn = SaveNewDomainInfo(dataSet, session, DI); if (optReturn.Result) { dataSet = optReturn.Data as DataSet; oracleDA.Update(dataSet); dataSet.AcceptChanges(); } } else { oracleDA.Update(dataSet); dataSet.AcceptChanges(); //如果重名,返回报错提醒 optReturn.Result = false; optReturn.Code = 99; return(optReturn); } oracleDA.Dispose(); connection.Close(); } break; default: break; } } else { switch (OperationCode) { case "0": //删除 strSql = string.Format("UPDATE T_00_012 SET C009=1 WHERE C001={1} AND C002='{0}'", rentToken, DI.DomainID); break; case "2": //修改 strSql = string.Format("UPDATE T_00_012 SET C003='{2}',C005='{3}',C006='{4}',C010='{5}',C999='{6}' WHERE C001={1} AND C002='{0}'" , rentToken, DI.DomainID , EncryptToDB002(DI.DomainName.Trim()).Trim() , EncryptToDB002(DI.DomainUserName.Trim()).Trim() , EncryptToDB003(string.Format("{0}@{1}", DI.DomainID.ToString().Trim(), DI.DomainUserPassWord.Trim())).Trim() , DI.IsActiveLogin ? 1 : 0, DI.Description); break; } switch (session.DBType) { case 2: optReturn = MssqlOperation.GetDataSet(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } objDataSet = optReturn.Data as DataSet; break; case 3: optReturn = OracleOperation.GetDataSet(session.DBConnectionString, strSql); if (!optReturn.Result) { return(optReturn); } objDataSet = optReturn.Data as DataSet; break; default: optReturn.Result = false; optReturn.Code = Defines.RET_PARAM_INVALID; optReturn.Message = string.Format("Database type not surpport.\t{0}", session.DBType); return(optReturn); } if (objDataSet == null) { optReturn.Result = false; optReturn.Code = Defines.RET_OBJECT_NULL; optReturn.Message = string.Format("DataSet is null"); return(optReturn); } } } catch (Exception ex) { optReturn.Result = false; optReturn.Code = Defines.RET_FAIL; optReturn.Message = ex.Message; return(optReturn); } return(optReturn); }