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