/**
Permite eliminar un cliente
*/
public string deleteCustomer(string id)
{
string deleteCustomer = "DELETE FROM CUSTOMERS.CUSTOMER WHERE Id = :Id";
OracleTransaction transaction;
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
try
{
connection.Open();
transaction = connection.BeginTransaction();
deleteCustomerAddress(id, connection, transaction);
using (OracleCommand command = new OracleCommand(deleteCustomer, connection))
{
command.Parameters.Add(new OracleParameter("Id", id));
command.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception e)
{
throw new PlatformException(e.Message);
}
}
return "OK";
}
public string addAddress(string customer_id, Address address)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO CUSTOMERS.ADDRESS (Id, street, state, zip, country, address_type, city) VALUES");
sql.Append("(CUSTOMERS.SEQ_ADDRESS.NEXTVAL, :street, :state, :zip, :country, :address_type, :city)");
StringBuilder sqlCustomerAddress = new StringBuilder();
sqlCustomerAddress.Append("INSERT INTO CUSTOMERS.CUSTOMER_ADDRESS (address_id, customer_id) VALUES");
sqlCustomerAddress.Append("(CUSTOMERS.SEQ_ADDRESS.CURRVAL, :customer_id)");
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
OracleTransaction transaction;
try
{
connection.Open();
transaction = connection.BeginTransaction();
using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
{
command.Parameters.Add(new OracleParameter("street", address.street));
command.Parameters.Add(new OracleParameter("state", address.state));
command.Parameters.Add(new OracleParameter("zip", address.zip));
command.Parameters.Add(new OracleParameter("country", address.country));
command.Parameters.Add(new OracleParameter("address_type", address.address_type));
command.Parameters.Add(new OracleParameter("city", address.city));
command.ExecuteNonQuery();
using (OracleCommand secondCommand = new OracleCommand(sqlCustomerAddress.ToString(), connection))
{
secondCommand.CommandText = sqlCustomerAddress.ToString();
secondCommand.Parameters.Add(new OracleParameter("customer_id", customer_id));
secondCommand.ExecuteNonQuery();
}
transaction.Commit();
}
return "OK";
}
catch (Exception ex)
{
throw new PlatformException(ex.Message);
}
}
}
public string deleteAddress(string id, string customer_id)
{
StringBuilder deleteCustomerAddress = new StringBuilder();
deleteCustomerAddress.Append("DELETE FROM CUSTOMERS.CUSTOMER_ADDRESS ");
deleteCustomerAddress.Append("WHERE customer_id = :customer_id AND address_id = :Id");
StringBuilder deleteAddress = new StringBuilder();
deleteAddress.Append("DELETE FROM CUSTOMERS.ADDRESS ");
deleteAddress.Append("WHERE Id = :Id");
OracleTransaction transaction;
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
connection.Open();
transaction = connection.BeginTransaction();
using (OracleCommand command = new OracleCommand(deleteCustomerAddress.ToString(), connection))
{
command.Parameters.Add(new OracleParameter("Id", id));
try
{
command.ExecuteNonQuery();
using(OracleCommand secondCommand = new OracleCommand(deleteAddress.ToString(), connection))
{
secondCommand.CommandText = deleteAddress.ToString();
secondCommand.Parameters.Add(new OracleParameter("customer_id", customer_id));
secondCommand.ExecuteNonQuery();
}
transaction.Commit();
return "OK";
}
catch (Exception e)
{
throw new PlatformException(e.Message);
}
}
}
}
/**
Crea un nuevo cliente
*/
public string createCustomer(Customer customer)
{
StringBuilder sql = new StringBuilder();
sql.Append("INSERT INTO CUSTOMERS.CUSTOMER (Id, first_name, last_name, phone_number, ");
sql.Append("email, creditcard_number, creditcard_type, status ) VALUES ( :Id, ");
sql.Append(":first_name, :last_name, :phone_number, :email, :creditcard_number, ");
sql.Append(":creditcard_type, :status)");
using (OracleConnection connection = new OracleConnection())
{
connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;
OracleTransaction transaction;
try
{
connection.Open();
transaction = connection.BeginTransaction();
using (OracleCommand command = new OracleCommand(sql.ToString(), connection))
{
command.Parameters.Add(new OracleParameter("Id", customer.Id));
command.Parameters.Add(new OracleParameter("first_name", customer.first_name));
command.Parameters.Add(new OracleParameter("last_name", customer.last_name));
command.Parameters.Add(new OracleParameter("phone_number", customer.phone_number));
command.Parameters.Add(new OracleParameter("email", customer.email));
command.Parameters.Add(new OracleParameter("creditcard_number", customer.creditcard_number));
command.Parameters.Add(new OracleParameter("creditcard_type", customer.creditcard_type));
command.Parameters.Add(new OracleParameter("status", customer.status));
command.ExecuteNonQuery();
createCustomerAddress(customer.Id, customer.address, connection, transaction);
transaction.Commit();
}
}
catch (Exception ex)
{
throw new PlatformException(ex.Message);
}
}
return "OK";
}
/**Funcion de insercion a la DB*/
private int oracleInsert(ArrayList q)
{
/**Almacenamos data en BD*/
int count = 0;
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString)/*"Data Source=localhost;Persist Security Info=True;User ID=webadmin;Password=webadmin;Unicode=True")*/;
conn.Open();
OracleTransaction t = conn.BeginTransaction();
try
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.Transaction = t;
cmd.CommandType = CommandType.Text;
//Ejecutamos cada insert en la lista
foreach (String s in q)
{
cmd.CommandText = s;
count += cmd.ExecuteNonQuery();
}
//Si fue exitoso, Commit
t.Commit();
}
catch (Exception ex)
{
//Sino rollback
Response.Write(ex);
t.Rollback();
}
finally
{
//Cerramos conexion
t.Dispose();
conn.Dispose();
conn.Close();
}
return count;
}
private void finishBtn_Click(object sender, EventArgs e)
{
string new_id = getIdTrans();
if (entryLb.Items.Count > 0)
{
int total_biaya = 0;
foreach (object i in entries)
{
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
total_biaya += harga;
}
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
try {
if (beliRb.Checked)
{
string club_asal = clubTargetCb.SelectedValue.ToString();
string club_baru = clubCb.SelectedValue.ToString();
string htrans = "INSERT INTO h_transaksi VALUES ('" + new_id + "', '" + club_asal + "', '" + club_baru + "', " +
total_biaya + ", TO_DATE(SYSDATE, 'DD-MM-YYYY'))";
OracleCommand cmd = new OracleCommand(htrans, conn);
cmd.ExecuteNonQuery();
foreach (object i in entries)
{
string id_player = i.GetType().GetProperty("value").GetValue(i, null).ToString();
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
string dtrans = "INSERT INTO d_transaksi VALUES ('" + new_id + "', '" + id_player + "', " + harga + ")";
cmd = new OracleCommand(dtrans, conn);
cmd.ExecuteNonQuery();
string player = "UPDATE player " +
"SET id_club = '" + club_baru + "' " +
"WHERE id_player = '" + id_player + "'";
cmd = new OracleCommand(player, conn);
cmd.ExecuteNonQuery();
}
showNews("beli", clubCb.Text);
}
else
{
string club_asal = clubCb.SelectedValue.ToString();
string club_baru = clubTargetCb.SelectedValue.ToString();
string htrans = "INSERT INTO h_transaksi VALUES ('" + new_id + "', '" + club_asal + "', '" + club_baru + "', " +
total_biaya + ", TO_DATE(SYSDATE, 'DD-MM-YYYY'))";
OracleCommand cmd = new OracleCommand(htrans, conn);
cmd.ExecuteNonQuery();
foreach (object i in entries)
{
string id_player = i.GetType().GetProperty("value").GetValue(i, null).ToString();
int harga = int.Parse(i.GetType().GetProperty("harga").GetValue(i, null).ToString());
string dtrans = "INSERT INTO d_transaksi VALUES ('" + new_id + "', '" + id_player + "', " + harga + ")";
cmd = new OracleCommand(dtrans, conn);
cmd.ExecuteNonQuery();
string player = "UPDATE player " +
"SET id_club = '" + club_baru + "' " +
"WHERE id_player = '" + id_player + "'";
cmd = new OracleCommand(player, conn);
cmd.ExecuteNonQuery();
}
showNews("jual", clubCb.Text);
}
trans.Commit();
entries.Clear();
entryLb.DataSource = null;
} catch (Exception ex) {
MessageBox.Show(ex.Message);
trans.Rollback();
}
conn.Close();
}
if (clubCb.SelectedValue != null)
{
showGrid(clubCb.SelectedValue);
}
}
/// <summary>
/// 批次匯入大量 Table 資料
/// </summary>
/// <param name="dataTable">表格資料</param>
static void ImportToOra(DataTable dataTable)
{
const int BATCH_SIZE = 1024;
var json = JsonConvert.SerializeObject(dataTable, Formatting.Indented);
object data = ToCollections(JArray.Parse(json));
var tableName = dataTable.TableName;
var propNames = dataTable.Columns.Cast <DataColumn>().Select(c => c.ColumnName);
var oraParams = dataTable.Columns.Cast <DataColumn>().ToDictionary(o => o.ColumnName, o => {
var p = new OracleParameter()
{
ParameterName = o.ColumnName
};
switch (o.DataType.Name)
{
case "String":
p.DbType = DbType.String;
break;
case "DateTime":
p.DbType = DbType.DateTime;
break;
case "Int32":
p.DbType = DbType.Int32;
break;
default:
throw new NotImplementedException(o.DataType.ToString());
}
return(p);
});
string insertSql =
$"INSERT INTO {tableName} ({string.Join(",", propNames)}) VALUES ({string.Join(",", propNames.Select(o => $":{o}").ToArray())})";
using (var cn = new OracleConnection(connectionString))
{
cn.Open();
using (var trans = cn.BeginTransaction())
{
try
{
var cmd = cn.CreateCommand();
cmd.BindByName = true;
cmd.CommandText = insertSql;
foreach (var batchData in SplitBatch(data as IEnumerable <object>, BATCH_SIZE))
{
InsertWithArrayBinding(cmd, oraParams, batchData);
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
throw;
}
}
}
}
public string SyncOrder(string cOrderNumber, string cEasNewOrder, string cGuid, int iCount)
{
var dtMoveIssue = GetImportDataTable(cGuid);
if (dtMoveIssue.Rows.Count < 1)
{
return("无内容");
}
var cUserName = dtMoveIssue.Rows[0]["cUser"].ToString();
using (var ocon = new OracleConnection(Properties.Settings.Default.EasCon))
{
ocon.Open();
{
using (var otran = ocon.BeginTransaction())
{
OracleCommand ocmd;
using (ocmd = new OracleCommand())
{
ocmd.Connection = ocon;
try
{
ocmd.CommandText = "select FNUMBER from T_IM_MoveIssueBill where FNUMBER=:FNUMBER";
ocmd.Parameters.Add(":FNUMBER", cEasNewOrder);
if (ocmd.ExecuteReader().Read())
{
return("OK");
}
ocmd.Parameters.Clear();
//执行主表写入
ocmd.CommandText = BillCmdStr;
FillBill(cOrderNumber, cEasNewOrder, cUserName);
GenBillPara(ocmd);
ocmd.ExecuteNonQuery();
//执行子表写入，先填充，再写入
ocmd.CommandText = BillEntryCmdStr;
for (var i = 0; i < dtMoveIssue.Rows.Count; i++)
{
ocmd.Parameters.Clear();
var cInvCode = dtMoveIssue.Rows[i]["cInvCode"].ToString();
var cInvName = dtMoveIssue.Rows[i]["cInvName"].ToString();
var iQuantity = dtMoveIssue.Rows[i]["iQuantity"].ToString();
var cLotNo = dtMoveIssue.Rows[i]["cLotNo"].ToString();
FillBillEntry(cOrderNumber, cInvCode, iQuantity, cInvName, cLotNo, i + 1);
GenBillEntryPara(ocmd);
ocmd.ExecuteNonQuery();
}
ocmd.CommandText = RelationCmdstr;
ocmd.Parameters.Clear();
GenRelationPara(ocmd);
ocmd.ExecuteNonQuery();
otran.Commit();
return("OK");
}
catch (OracleException ex)
{
otran.Rollback();
return(ex.Message);
}
}
}
}
}
}
internal static async Task UpdateModelAsync(OracleConnection connection, string currentSchema, bool suppressException, CancellationToken cancellationToken, params IModelDataProvider[] updaters)
{
using (var command = connection.CreateCommand())
{
command.BindByName = true;
OracleTransaction transaction = null;
try
{
foreach (var updater in updaters)
{
command.ResetParametersToAvoidOdacBug();
command.CommandText = String.Empty;
command.CommandType = CommandType.Text;
updater.InitializeCommand(command);
try
{
if (updater.IsValid)
{
if (connection.State == ConnectionState.Closed)
{
await connection.OpenAsynchronous(cancellationToken);
connection.ModuleName = "SQLPad backround";
connection.ActionName = "Model data provider";
if (!String.IsNullOrEmpty(currentSchema))
{
using (var setSchemaCommand = connection.CreateCommand())
{
await setSchemaCommand.SetCurrentSchema(currentSchema, cancellationToken);
}
}
transaction = connection.BeginTransaction();
}
if (updater.HasScalarResult)
{
var result = await command.ExecuteScalarAsynchronous(cancellationToken);
updater.MapScalarValue(result);
}
else
{
using (var reader = await command.ExecuteReaderAsynchronous(CommandBehavior.Default, cancellationToken))
{
await updater.MapReaderData(reader, cancellationToken);
}
}
}
}
catch (OracleException exception)
{
if (exception.Number == (int)OracleErrorCode.UserInvokedCancellation)
{
break;
}
throw;
}
}
}
catch (Exception e)
{
TraceLog.WriteLine($"Update model failed: {e}");
if (!suppressException)
{
throw;
}
}
finally
{
if (transaction != null)
{
await transaction.RollbackAsynchronous();
transaction.Dispose();
}
}
}
}
private void SafeExecuteCommand(Action<OracleCommand> action)
{
using (var connection = new OracleConnection(_connectionString))
{
OracleTransaction transaction = null;
OracleCommand com;
try
{
connection.Open();
OracleSqlUtility.SetSqlUserInfo(connection, _userInfo);
transaction = connection.BeginTransaction();
com = connection.CreateCommand();
com.CommandTimeout = SqlUtility.SqlCommandTimeout;
com.Transaction = transaction;
}
catch (OracleException ex)
{
if (transaction != null)
transaction.Rollback();
var csb = new OracleConnectionStringBuilder(_connectionString);
string msg = string.Format(CultureInfo.InvariantCulture, "Could not connect to data source '{0}', userID '{1}'.", csb.DataSource, csb.UserID);
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
try
{
var setNationalLanguage = OracleSqlUtility.SetNationalLanguageQuery();
if (!string.IsNullOrEmpty(setNationalLanguage))
{
_logger.Trace("Setting national language: {0}", SqlUtility.NationalLanguage);
com.CommandText = setNationalLanguage;
com.ExecuteNonQuery();
}
action(com);
transaction.Commit();
}
catch (OracleException ex)
{
if (com != null && !string.IsNullOrWhiteSpace(com.CommandText))
_logger.Error("Unable to execute SQL query:\r\n" + com.CommandText);
string msg = "OracleException has occurred:\r\n" + ReportSqlErrors(ex);
if (ex.Number == 911)
msg += "\r\nCheck that you are not using ';' at the end of the command's SQL query.";
_logger.Error(msg);
_logger.Error(ex.ToString());
throw new FrameworkException(msg, ex);
}
finally
{
TryRollback(transaction);
}
}
}
/// <summary>
/// ִ�ж���SQL��䣬ʵ�����ݿ�����
/// </summary>
/// <param name="SQLStringList">����SQL���</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
logger.Debug(strsql);
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}
//删除数据
private void barBtnDel_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
this.gvVIN_INFO.PostEditor();
var dataSource = (DataView)this.gvVIN_INFO.DataSource;
if (dataSource == null)
{
MessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
var dtSelected = dataSource.Table.Copy();
dtSelected.Clear();
if (dataSource != null && dataSource.Table.Rows.Count > 0)
{
for (int i = 0; i < dataSource.Table.Rows.Count; i++)
{
bool result = false;
bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result);
if (result)
{
dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray);
}
}
}
if (dtSelected.Rows.Count == 0)
{
MessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (dtSelected.Select("MERGER_STATUS=1").Count() > 0)
{
MessageBox.Show("您选择要操作的记录中包含已合并数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (MessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
using (OracleConnection conn = new OracleConnection(FuelDataSysClient.Tool.OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
foreach (DataRow dr in dtSelected.Rows)
{
try
{
FuelDataSysClient.Tool.OracleHelper.ExecuteNonQuery(FuelDataSysClient.Tool.OracleHelper.conn, string.Format("Delete from VIN_INFO where ID='{0}'", dr["ID"]), null);
}
catch (Exception ex)
{
trans.Rollback();
MessageBox.Show(String.Format("数据库操作出现异常，删除失败：{0}！", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
this.refrashCurrentPage();
}
/// <summary>
/// 执行SQL语句，返回受影响的行数(带事物)
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="sqlParams">参数数组</param>
/// <param name="timeOut">等待命令执行的时间（以秒为单位）,默认值为 30 秒。</param>
/// <param name="errorMsg">返回的异常信息</param>
/// <returns>受影响的行数</returns>
public int ExecuteTran(string sql, OracleParameter[] sqlParams, int timeOut, out string errorMsg)
{
int intResult = 0;
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction sqlTran = null;
errorMsg = string.Empty;
try
{
//取得数据库连接
conn = GetConnection();
cmd.Connection = conn;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
if (sqlParams != null)
{
foreach (OracleParameter sp in sqlParams)
{
cmd.Parameters.Add(sp);
}
}
//等待命令执行的时间（以秒为单位）,默认值为 30 秒。
if (timeOut <= 30)
{
cmd.CommandTimeout = 30;
}
else
{
cmd.CommandTimeout = timeOut;
}
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
try
{
sqlTran.Rollback();
}
catch (Exception e)
{
Log4NetUtil.Error(this, "OracleBatchExec->回滚异常 SQL:" + sql + "|*|" + e.ToString());
}
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "SQL:" + sql + "|*|ErrorMsg:" + errorMsg);
}
return(intResult);
}
/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="tableName">表名称 </param>
/// <param name="columnRowData">键-值存储的批量数据：键是列名称，值是对应的数据集合 </param>
/// <param name="len">每次批处理数据的大小 </param>
/// <returns></returns>
public static int BatchInsert(string tableName, Dictionary<string, object> columnRowData, int len)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentException("必须指定批量插入的表名称", "tableName");
}
if (columnRowData == null || columnRowData.Count < 1)
{
throw new ArgumentException("必须指定批量插入的字段名称", "columnRowData");
}
int iResult = 0;
string[] dbColumns = columnRowData.Keys.ToArray();
StringBuilder sbCmdText = new StringBuilder();
if (columnRowData.Count > 0)
{
//准备插入的SQL
sbCmdText.AppendFormat("INSERT INTO {0}(", tableName);
sbCmdText.Append(string.Join(",", dbColumns));
sbCmdText.Append(") VALUES (");
sbCmdText.Append(":" + string.Join(",:", dbColumns));
sbCmdText.Append(")");
using (OracleConnection conn = new OracleConnection(connectionString))
{
using (OracleCommand cmd = conn.CreateCommand())
{
//绑定批处理的行数
cmd.ArrayBindCount = len;
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sbCmdText.ToString();
cmd.CommandTimeout = 600;//10分钟
//创建参数
OracleParameter oraParam;
List<IDbDataParameter> cacher = new List<IDbDataParameter>();
OracleDbType dbType = OracleDbType.Varchar2;
foreach (string colName in dbColumns)
{
dbType = GetOracleDbType(columnRowData[colName]);
oraParam = new OracleParameter(colName, dbType);
oraParam.Direction = ParameterDirection.Input;
oraParam.OracleDbTypeEx = dbType;
oraParam.Value = columnRowData[colName];
cmd.Parameters.Add(oraParam);
}
//打开连接
conn.Open();
/*执行批处理*/
var trans = conn.BeginTransaction();
try
{
cmd.Transaction = trans;
iResult = cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (conn != null) conn.Close();
}
}
}
}
return iResult;
}
public static string addNewHardware(string imeModel, string performance, string relYear, string slikaPateka, string desc, string typeId, string companyId)
{
OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConnection;
cmd.CommandText = "INSERT INTO HardverProizvod(hardid,imeModel,performansi,godnaproizvod,slikaPateka,opis,brposetenost) values(SEQ_HARDVERPROIZVOD.nextval,:ime,:performansi,to_date(:godnaproizvod,'DD.MM.YYYY'),:slikapateka,:opis,:brposetenost)";
cmd.Parameters.Add(new OracleParameter("ime", imeModel));
cmd.Parameters.Add(new OracleParameter("performansi", performance));
cmd.Parameters.Add(new OracleParameter("godnaproizvod", relYear));
cmd.Parameters.Add(new OracleParameter("slikapateka", slikaPateka));
cmd.Parameters.Add(new OracleParameter("opis", desc));
cmd.Parameters.Add(new OracleParameter("brposetenost", "0"));
OracleTransaction transaction =null;
try
{
oracleConnection.Open();
transaction = oracleConnection.BeginTransaction();
int success = cmd.ExecuteNonQuery();
if (success != 0)
{
OracleCommand addCompanyCmd = new OracleCommand();
addCompanyCmd.Connection = oracleConnection;
addCompanyCmd.CommandText = "INSERT INTO IzdavaHardver(koid,hardid) values (:koid,SEQ_HARDVERPROIZVOD.currval)";
addCompanyCmd.Parameters.Add(new OracleParameter("koid", companyId));
int companySuccess = addCompanyCmd.ExecuteNonQuery();
OracleCommand addGameGenreCmd = new OracleCommand();
addGameGenreCmd.Connection = oracleConnection;
addGameGenreCmd.CommandText = "INSERT INTO TipHardver(hardid,katid) values(SEQ_HARDVERPROIZVOD.currval,:katid)";
addGameGenreCmd.Parameters.Add(new OracleParameter("katid", typeId));
int genreSuccess = addGameGenreCmd.ExecuteNonQuery();
}
transaction.Commit();
return "Додавањето е успешно";
}
catch (Exception e)
{
transaction.Rollback();
return e.Message;
}
finally
{
oracleConnection.Close();
}
return null;
}
public static string addNewGame(string name, string relYear, string imgPath, string desc, string company, string minPerf, string PrepPerf, string zhanr, string expansionOf)
{
OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConnection;
cmd.CommandText = "INSERT INTO Igra(igraid,ime,godizdavanje,slikapateka,opis,minperform,prepperform,brposetenost) values(seq_igra.nextval,:ime,to_date(:godizdavanje,'DD.MM.YYYY'),:slikapateka,:opis,:minperform,:prepperform,:brposetenost)";
cmd.Parameters.Add(new OracleParameter("ime", name));
cmd.Parameters.Add(new OracleParameter("godizdavanje", relYear));
cmd.Parameters.Add(new OracleParameter("slikapateka", imgPath));
cmd.Parameters.Add(new OracleParameter("opis", desc));
cmd.Parameters.Add(new OracleParameter("minperform", minPerf));
cmd.Parameters.Add(new OracleParameter("prepperform", PrepPerf));
cmd.Parameters.Add(new OracleParameter("brposetenost", "0"));
OracleTransaction transaction = null;
try
{
oracleConnection.Open();
transaction = oracleConnection.BeginTransaction();
int success = cmd.ExecuteNonQuery();
if (success != 0)
{
string igraid = getLatestAddedGameId(oracleConnection);
OracleCommand addCompanyCmd = new OracleCommand();
addCompanyCmd.Connection = oracleConnection;
addCompanyCmd.CommandText = "INSERT INTO IzdavaIgra(koid,igraid) values (:koid,:igraid)";
addCompanyCmd.Parameters.Add(new OracleParameter("koid", company));
addCompanyCmd.Parameters.Add(new OracleParameter("igraid", igraid));
int companySuccess = addCompanyCmd.ExecuteNonQuery();
OracleCommand addGameGenreCmd = new OracleCommand();
addGameGenreCmd.Connection = oracleConnection;
addGameGenreCmd.CommandText = "INSERT INTO ZHANRIGRA(igraid,zhanrid) values(:igraid,:zhanrid)";
addGameGenreCmd.Parameters.Add(new OracleParameter("igraid", igraid));
addGameGenreCmd.Parameters.Add(new OracleParameter("zhanrid", zhanr));
int genreSuccess = addGameGenreCmd.ExecuteNonQuery();
if (expansionOf != null)
{
OracleCommand addGameExpansionCmd = new OracleCommand();
addGameExpansionCmd.Connection = oracleConnection;
addGameExpansionCmd.CommandText = "INSERT INTO IMAVERZIJA (prvaigraid,prodolzenieid) values (:prvaigraid, :prodolzenieid)";
addGameExpansionCmd.Parameters.Add(new OracleParameter("prvaigraid", expansionOf));
addGameExpansionCmd.Parameters.Add(new OracleParameter("prodolzenieid", igraid));
int expansionSuccess = addGameExpansionCmd.ExecuteNonQuery();
}
}
transaction.Commit();
return "Додавањето е успешно";
}
catch (Exception e)
{
transaction.Rollback();
return e.Message;
}
finally
{
oracleConnection.Close();
}
return null;
}
public static void CLOBTest (OracleConnection connection)
{
Console.WriteLine (" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction ();
Console.WriteLine (" Drop table CLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand ();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE CLOBTEST";
cmd2.ExecuteNonQuery ();
}
catch (OracleException oe1) {
// ignore if table already exists
}
Console.WriteLine (" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand ();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
create.ExecuteNonQuery ();
Console.WriteLine (" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand ();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
insert.ExecuteNonQuery ();
OracleCommand select = connection.CreateCommand ();
select.Transaction = transaction;
select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
Console.WriteLine (" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
OracleDataReader reader = select.ExecuteReader ();
if (!reader.Read ())
Console.WriteLine ("ERROR: RECORD NOT FOUND");
Console.WriteLine (" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob (0);
Console.WriteLine (" LENGTH: {0}", lob.Length);
Console.WriteLine (" CHUNK SIZE: {0}", lob.ChunkSize);
UnicodeEncoding encoding = new UnicodeEncoding ();
try {
// read file "cs-parser.cs" into the oracle clob
using (StreamReader sr = new StreamReader(infilename)) {
string sbuff = sr.ReadToEnd ();
byte[] evalue = encoding.GetBytes (sbuff);
lob.Write (evalue, 0, evalue.Length);
}
}
catch (Exception e) {
Console.WriteLine("The file could not be read:");
Console.WriteLine(e.Message);
}
lob.Close ();
Console.WriteLine (" CLOSING READER...");
reader.Close ();
transaction.Commit ();
}
public void DoWork()
{
using (OracleConnection conn = new OracleConnection(GlobalUtils.TopSecret.OracleCS))
using (OracleCommand command = new OracleCommand())
{
try
{
conn.Open();
//conn.StatisticsEnabled = true;
command.Connection = conn;
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
return;
}
try
{
using (OracleTransaction sqlTran = conn.BeginTransaction())
{
command.Transaction = sqlTran;
OracleDataReader reader;
List<string> commands = GetCommands(com);
foreach (string c in commands)
{
command.CommandText = c;
using (reader = command.ExecuteReader())
{
ShowResultSet(reader, command);
while (reader.NextResult())
ShowResultSet(reader, command);
}
}
//string out_string;
//int status = 0;
//command.CommandText = "BEGIN DBMS_OUTPUT.GET_LINE (:out_string, :status); END;";
//command.CommandType = CommandType.Text;
//command.Parameters.Clear();
//command.Parameters.Add("out_string", OracleDbType.Varchar2, 32000);
//command.Parameters.Add("status", OracleDbType.Double);
//command.Parameters[0].Direction = System.Data.ParameterDirection.Output;
//command.Parameters[1].Direction = System.Data.ParameterDirection.Output;
//command.ExecuteNonQuery();
//out_string = command.Parameters[0].Value.ToString();
//status = int.Parse(command.Parameters[1].Value.ToString());
//if (!string.IsNullOrEmpty(out_string))
//{
// Console.WriteLine(System.Web.HttpUtility.HtmlEncode(out_string));
//}
//var stats = conn.RetrieveStatistics();
//using (TextWriter tw = new StreamWriter(path + ".stats"))
//{
// tw.WriteLine("Execution time: {0} sec, rows selected: {1}, rows affected: {2}",
// Math.Round((double)(long)stats["ExecutionTime"] / 1000, 2),
// stats["SelectRows"],
// stats["IduRows"]);
//}
}
}
catch (Exception e)
{
Console.Error.WriteLine(e.Message);
if (command != null)
command.Cancel();
}
}
}
public Usuario crearUsuario(Usuario usuario)
{
OracleConnection conn = null;
try
{
conn = new OracleConnection(this.strCadenaConexion);
conn.Open();
}
catch
{
throw new Exception("Error de conexión a la Base de Datos");
}
using (OracleCommand cmd = conn.CreateCommand())
{
using (OracleTransaction trx = conn.BeginTransaction())
{
cmd.Transaction = trx;
try
{
usuario = UsuariosBD.CrearUsuario(cmd, usuario);
usuario.Contrasena = Encriptacion.MD5(usuario.Contrasena);
int contrasenaResul = UsuariosBD.ActualizarContrasena(cmd, usuario.Id, usuario.Contrasena, usuario.User);
if (usuario.Id < 0 && contrasenaResul < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
if (usuario.Permisos != null)
{
for (int i = 0; i < usuario.Permisos.Count; i++)
{
int resultPermXUsr = UsuariosBD.MantenimientoUsuarioXPermiso(cmd, usuario.Id, usuario.Permisos[i].Id, usuario.User, usuario.Permisos[i].Estado);
if (resultPermXUsr < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
if (usuario.Perfiles != null)
{
for (int i = 0; i < usuario.Perfiles.Count; i++)
{
int resultUserXPerf = UsuariosBD.MantenimientoUsuarioXPerfil(cmd, usuario.Id, usuario.Perfiles[i].Id, usuario.User, usuario.Perfiles[i].Estado);
if (resultUserXPerf < 0)
{
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
trx.Commit();
}
catch
{
trx.Rollback();
throw new Exception("Ocurrió un error al guardar el usuario");
}
}
}
CerrarConexion(conn);
return(usuario);
}
public void BeginTransaction()
{
oracleConnection = GetConnect();
transaction = oracleConnection.BeginTransaction();;
}
/// <summary>
/// 批量删除（Delete）
/// </summary>
/// <param name="tableName">目标表名称</param>
/// <param name="dic_ColumnParaArry">列参数数组</param>
/// <param name="batchSize">批次中的数量</param>
/// <param name="timeOut">等待命令执行的时间</param>
/// <param name="errorMsg">返回的异常信息</param>
public int OracleBatchDel(string tableName, Dictionary <string, OrclDataStru> dic_ColumnParaArry, int batchSize, int timeOut, out string errorMsg)
{
errorMsg = string.Empty;
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction sqlTran = null;
StringBuilder sbCmdText = new StringBuilder();
//影响的行数（由于触发器的存在，不一定准确）
int intResult = 0;
//列数组
string[] arr_Columns = null;
//参数校验
if (string.IsNullOrEmpty(tableName))
{
errorMsg = "tableName 为空";
return(intResult);
}
if (dic_ColumnParaArry == null || dic_ColumnParaArry.Count < 1)
{
errorMsg = "dic_ColumnParaArry 为空";
return(intResult);
}
//取得列数组
arr_Columns = dic_ColumnParaArry.Keys.ToArray();
//准备Delete语句
sbCmdText.Append("DELETE FROM ");
sbCmdText.Append(tableName);
sbCmdText.Append(" WHERE ");
if (arr_Columns.Length == 1)
{
sbCmdText.Append(arr_Columns[0]);
sbCmdText.Append("=:");
sbCmdText.Append(arr_Columns[0]);
}
else
{
sbCmdText.Append("1=1");
foreach (string col in arr_Columns)
{
sbCmdText.Append(" AND ");
sbCmdText.Append(col);
sbCmdText.Append("=:");
sbCmdText.Append(col);
}
}
try
{
//取得数据库连接
conn = GetConnection();
//OracleCommand
cmd.Connection = conn;
//批次中的行数
if (batchSize > 0)
{
cmd.ArrayBindCount = batchSize;
}
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sbCmdText.ToString();
//等待命令执行的时间（以秒为单位）,默认值为 300 秒。
if (timeOut <= 300)
{
cmd.CommandTimeout = 300;
}
else
{
cmd.CommandTimeout = timeOut;
}
//创建参数
foreach (string colName in arr_Columns)
{
OracleDbType dbType = dic_ColumnParaArry[colName].dbType;
OracleParameter oraParam = new OracleParameter(colName, dbType);
oraParam.Direction = ParameterDirection.Input;
if (dic_ColumnParaArry[colName].arrParam != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arrParam;
}
else if (dic_ColumnParaArry[colName].arryList != null)
{
oraParam.Value = dic_ColumnParaArry[colName].arryList.ToArray();
}
else
{
errorMsg = "ParaArry 为空";
return(intResult);
}
cmd.Parameters.Add(oraParam);
}
conn.Open();
sqlTran = conn.BeginTransaction();
cmd.Transaction = sqlTran;
intResult = cmd.ExecuteNonQuery();
sqlTran.Commit();
}
catch (Exception ex)
{
try
{
sqlTran.Rollback();
}
catch (Exception e)
{
Log4NetUtil.Error(this, "OracleBatchDel->回滚异常 SQL:" + sbCmdText.ToString() + "|*|" + e.ToString());
}
errorMsg = ex.ToString();
}
finally
{
if (sqlTran != null)
{
sqlTran.Dispose();
}
if (conn != null)
{
conn.Close();
}
cmd.Dispose();
}
//记录日志
if (!string.IsNullOrEmpty(errorMsg))
{
Log4NetUtil.Error(this, "OracleBatchDel->SQL:" + sbCmdText.ToString() + "|*|" + errorMsg);
}
return(intResult);
}
protected void cmdConfirm_Click(object sender, EventArgs e)//----------------------------------Records Confirm Click Event----------------------------------------//
{
//double dr_total = 0;
if (page_result.Visible == true)
{
page_result.Visible = false;
}
get_LiveRecords_Total();
if (int.Parse(Session["TotalRecords_WhenPageLoad"].ToString()) == LiveRecord_Count)
{
//Check Totals-------------
Double TempTot = 0;
DataTable dtTemp = (DataTable)Session["TransList"];
if (dtTemp == null)
{
return;
}
foreach (DataRow item in dtTemp.Rows)
{
TempTot = TempTot + Convert.ToDouble(item["Amount"].ToString());
}
//*****************************************-----New Fn-----**********************************//
double real_total = double.Parse(txtTotal.Text);
double calculation_differance = TempTot - real_total;
if ((Math.Abs(calculation_differance) <= 0.1) || (calculation_differance == 0))
{
if (calculation_differance > 0)
{
calculation_differance = (-1) * (calculation_differance);
}
else
{
calculation_differance = (1) * (calculation_differance);
}
}
else
{
call_error_msg(false);
lblResult.Text = "Calculation Differance Is Greater than 0.1 ";
return;
}
//********************************************************************************************//
CommonCLS.CommonFunctions clsCom = new CommonCLS.CommonFunctions();
DataTable Dt = (DataTable)Session["TransList"];
Int16 SubNo = 1;
CommonCLS.IBTBatches IBTBatch = new CommonCLS.IBTBatches();
String BatchNo = IBTBatch.GetBatchNo("BATCH_NO");
using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ORAWF"].ToString()))
{
conn.Open();
OracleCommand command = conn.CreateCommand();
OracleTransaction transaction;
transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
// Assign transaction object for a pending local transaction
command.Transaction = transaction;
try
{
Int16 Count = 1;
foreach (DataRow item in Dt.Rows)
{
if (item["Status"].ToString().Trim() != "New")
{
continue;
}
OracleCommand cmd = new OracleCommand();//conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_fas_ibt_BulkReceipt";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("vID", OracleType.Int32).Value = DtlID;
cmd.Parameters.AddWithValue("vSubNo", OracleType.VarChar).Value = SubNo;
cmd.Parameters.AddWithValue("vRefNumber", OracleType.VarChar).Value = item["Ref_no"].ToString();
//cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString());
cmd.Parameters.AddWithValue("vAmount", OracleType.Number).Value = double.Parse(item["Amount"].ToString()) + (calculation_differance);
calculation_differance = 0;
cmd.Parameters.AddWithValue("vCreatedBy", OracleType.Number).Value = usrName;
cmd.Parameters.AddWithValue("vCount", OracleType.Number).Value = Count;
cmd.Parameters.AddWithValue("vBatchNo", OracleType.Number).Value = BatchNo;
SubNo = Convert.ToInt16(SubNo + 1);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
Count++;
}
arrRemoveListRV = (ArrayList)Session["arrRemoveListRV"];
if (arrRemoveListRV != null)
{
foreach (Object obj in arrRemoveListRV)
{
OracleCommand cmd = new OracleCommand();//conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_fas_ibt_bulk_rv_Remove";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("BulkRV_ID", OracleType.Int32).Value = Convert.ToInt32(obj);
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, "----Bulk Receipt Confirmed!---- By: " + usrName + " Base Record ID :" + DtlID, Server.MapPath("~/IBTLogFiles/Log.txt"));
conn.Close();
}
catch (Exception ex)
{
CommonCLS.CommonFunctions.Logger(System.Web.VirtualPathUtility.GetFileName(System.Web.HttpContext.Current.Request.Url.AbsolutePath), System.Reflection.MethodBase.GetCurrentMethod().Name, ex.InnerException.ToString(), Server.MapPath("~/IBTLogFiles/Log.txt"));
call_error_msg(false);
lblResult.Text = ex.InnerException.ToString();
}
}
ClearControls();
running_count = 0;
if (running_count == 0)
{
get_TotalRecords_WhenPageLoad();
running_count = running_count + 1;//New
}
call_error_msg(true);
lblResult.Text = "Records Confirmed For Receipt...!";
}
else
{
running_count = 0;
call_error_msg(false);
lblResult.Text = "Record Locked By Another User...! Please Refresh And Try Again!";
}
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="sqlList">多条SQL语句</param>
public static bool ExecuteSqlTran(ArrayList sqlList)
{
bool re = false;
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
connection.Open();
OracleCommand cmd = new OracleCommand {Connection = connection};
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (object sql in sqlList)
{
string strsql = sql.ToString();
if (strsql.Trim().Length <= 1) continue;
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
tx.Commit();
re = true;
}
catch (Exception e)
{
re = false;
tx.Rollback();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
return re;
}
public static void BLOBTest (OracleConnection connection)
{
Console.WriteLine (" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction ();
Console.WriteLine (" Drop table BLOBTEST ...");
try {
OracleCommand cmd2 = connection.CreateCommand ();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE BLOBTEST";
cmd2.ExecuteNonQuery ();
}
catch (OracleException) {
// ignore if table already exists
}
Console.WriteLine (" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand ();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
create.ExecuteNonQuery ();
Console.WriteLine (" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand ();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
insert.ExecuteNonQuery ();
OracleCommand select = connection.CreateCommand ();
select.Transaction = transaction;
select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
Console.WriteLine (" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");
OracleDataReader reader = select.ExecuteReader ();
if (!reader.Read ())
Console.WriteLine ("ERROR: RECORD NOT FOUND");
Console.WriteLine (" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob (0);
Console.WriteLine (" LENGTH: {0}", lob.Length);
Console.WriteLine (" CHUNK SIZE: {0}", lob.ChunkSize);
//try {
if (File.Exists(infilename) == false) {
Console.WriteLine("Filename does not exist: " + infilename);
return;
}
FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] bytes = null;
int bufferLen = 8192;
bytes = r.ReadBytes (bufferLen);
while(bytes.Length > 0) {
Console.WriteLine("byte count: " + bytes.Length.ToString());
lob.Write (bytes, 0, bytes.Length);
bytes1 = ByteArrayCombine (bytes1, bytes);
if (bytes.Length < bufferLen)
break;
bytes = r.ReadBytes (bufferLen);
}
r.Close();
fs.Close ();
//}
//catch (Exception e) {
// Console.WriteLine("The file could not be read:");
// Console.WriteLine(e.Message);
//}
lob.Close ();
Console.WriteLine (" CLOSING READER...");
reader.Close ();
transaction.Commit ();
transaction = null;
lob = null;
reader.Dispose();
reader = null;
create = null;
insert = null;
select = null;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="sqlList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
public static void ExecuteSqlTran(Hashtable sqlList)
{
using (OracleConnection conn = new OracleConnection(ConnectionString)) {
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction()) {
OracleCommand cmd = new OracleCommand();
try {
//循环
foreach (DictionaryEntry sql in sqlList) {
string cmdText = sql.Key.ToString();
OracleParameter[] cmdParms = (OracleParameter[])sql.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch {
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// Supprime un utilisateur en base
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public bool Delete(OracleConnection connection)
{
bool bResult = false;
OracleTransaction transaction = null /*=connection.BeginTransaction()*/;
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "", strRoleList = "", sqlDelete = "";
OracleDataReader sqlReader = null;
OracleCommand commandDelete = new OracleCommand();
commandDelete.Connection = connection;
try
{
//desaffecter les roles de tous les profiles affectés a l'utilisateur
//lock de la table affectation
sql = "LOCK TABLE MOU01.affectation IN EXCLUSIVE MODE";
command.CommandText = sql;
command.ExecuteNonQuery();
try
{
transaction = connection.BeginTransaction();
//Recherche des rôles à REVOKER
sql = "SELECT role.role nom FROM MOU01.role, MOU01.right, MOU01.profile, MOU01.affectation " +
"WHERE Role.id_role = Right.id_role " +
" AND right.id_profile = profile.id_profile " +
" AND affectation.id_profile = profile.id_profile " +
" AND affectation.id_user_=" + this.m_dIdUser.ToString("0") +
" GROUP BY role.role ";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
if (strRoleList != "")
{
strRoleList += ", ";
}
strRoleList = strRoleList + sqlReader.GetString(0);
}
//revoke les roles du profile à l'utilisateur
if (strRoleList != "")
{
sql = "REVOKE " + strRoleList + " FROM " + this.UserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//Recherche des rôles et profiles à desaffecter
sql = "SELECT affectation.id_profile FROM MOU01.affectation " +
"WHERE affectation.id_user_=" + this.m_dIdUser.ToString("0");
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
while (sqlReader.Read())
{
//mise à jour de la table affectation
sqlDelete = "DELETE FROM MOU01.affectation WHERE id_user_=" + this.m_dIdUser.ToString("0") + " AND id_profile=" + sqlReader.GetInt64(0).ToString();
commandDelete.CommandText = sqlDelete;
commandDelete.ExecuteNonQuery();
}
//Suppression du compte Oracle
sql = "DROP USER " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
//Mise à jour de la table temporary_tablespace
sql = "UPDATE MOU01.temporary_tablespace SET user_count=(user_count-1) WHERE id_temporary_tablespace=" + this.IdTemporaryTableSpace.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//Suppression dand la table user_
sql = "DELETE FROM MOU01.user_ WHERE user_oracle='" + this.m_strUserOracle + "'";
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
sql = "COMMIT";
command.CommandText = sql;
command.ExecuteNonQuery();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors de la suppression de l'utilisateur avec l'erreur : " + error.Message);
}
}
catch (Exception error)
{
sql = "ROLLBACK";
command.CommandText = sql;
command.ExecuteNonQuery();
MessageBox.Show("Problème lors du lock de la table Affectation avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
//Affiche FAMILLE JDE ajax (table)
public OracleDataReader GetJDE(string matr)
{
DBConnectJDE c = new DBConnectJDE();
OracleConnection conn = c.GetConnectionJDE();
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
nb = CompterAgentTemp(matr);
if (nb == 0)
{
OracleCommand cmd = new OracleCommand
{
CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'",
Connection = conn,
CommandType = CommandType.Text
};
try
{
// Exécution de la requête
dr = cmd.ExecuteReader();
// On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée
trans.Commit();
}
catch (Exception ex)
{
// Une erreur est survenue: on ne valide pas la requête
trans.Rollback();
Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>");
}
finally
{ // Libération des ressources
cmd.Dispose();
}
}
else if (nb == 1)
{
OracleCommand cmd = new OracleCommand
{
CommandText = "SELECT YAAN8, YAOEMP , YAALPH , YADSC1 , YASEX , YAALPH1, CASE YATRDJ WHEN 0 THEN '-' ELSE to_char(to_date(to_char(1900 + floor(YATRDJ/ 1000)),'YYYY') + mod(YATRDJ,1000) - 1, 'DD-MM-YYYY') END FROM F55EMPME WHERE YADSC1 <> YAALPH AND YAAN8 = '" + matr + "' OR YAOEMP= '" + matr + "'",
Connection = conn,
CommandType = CommandType.Text
};
try
{
// Exécution de la requête
dr = cmd.ExecuteReader();
// On soumet la requête au serveur: tout s'est bien déroulé , la requête est exécutée
trans.Commit();
}
catch (Exception ex)
{
// Une erreur est survenue: on ne valide pas la requête
trans.Rollback();
Console.WriteLine("<body><script >alert('Requête non effectuée !!\nErreur: '" + ex.Message + "'');</script></body>");
}
finally
{ // Libération des ressources
cmd.Dispose();
}
}
return(dr);
}
public void run()
{
Exception exp = null;
MonoTests.System.Data.Utils.DataBaseServer dbServer = ConnectedDataProvider.GetDbType(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleTransaction txn = null;
try
{
BeginCase("IsolationLevel = ReadCommitted");
con.Open();
txn = con.BeginTransaction();
Compare(txn.IsolationLevel, IsolationLevel.ReadCommitted);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
//not supported in Oracle
if (dbServer != MonoTests.System.Data.Utils.DataBaseServer.Oracle)
{
try
{
BeginCase("IsolationLevel = ReadUncommitted");
con.Open();
txn = con.BeginTransaction(IsolationLevel.ReadUncommitted);
Compare(txn.IsolationLevel, IsolationLevel.ReadUncommitted);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
//not supported in Oracle
if (dbServer != MonoTests.System.Data.Utils.DataBaseServer.Oracle)
{
try
{
BeginCase("IsolationLevel = RepeatableRead");
con.Open();
txn = con.BeginTransaction(IsolationLevel.RepeatableRead);
Compare(txn.IsolationLevel, IsolationLevel.RepeatableRead);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
try
{
BeginCase("IsolationLevel = Serializable");
con.Open();
txn = con.BeginTransaction(IsolationLevel.Serializable);
Compare(txn.IsolationLevel, IsolationLevel.Serializable);
txn.Rollback();
txn = con.BeginTransaction();
txn.Rollback();
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
// not supported in DB2,MSSQL,Oracle,sybase and guess what... Postgres.
if (dbServer != MonoTests.System.Data.Utils.DataBaseServer.DB2 &&
dbServer != MonoTests.System.Data.Utils.DataBaseServer.SQLServer &&
dbServer != MonoTests.System.Data.Utils.DataBaseServer.Oracle &&
dbServer != DataBaseServer.PostgreSQL &&
dbServer != MonoTests.System.Data.Utils.DataBaseServer.Sybase)
{
try
{
BeginCase("IsolationLevel = Unspecified");
con.Open();
txn = con.BeginTransaction(IsolationLevel.Unspecified);
Compare(txn.IsolationLevel, IsolationLevel.Unspecified);
}
catch (Exception ex)
{
exp = ex;
}
finally
{
EndCase(exp);
exp = null;
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
}
/// <summary>
/// Méthodes de la classe Application
/// </summary>
#region
/// <summary>
/// Ajout d'une application en base
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public bool Add(OracleConnection connection)
{
bool bResult = false;
// Start a local transaction
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
OracleDataReader sqlReader = null;
string sql = "";
try
{
//crée un nouvel Id pour le projet
sql = "SELECT MAX(id_application) + 1 FROM MOU01.application";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
if (sqlReader.Read())
{
if (!sqlReader.IsDBNull(0))
{
this.m_dIdApplication = long.Parse(sqlReader.GetValue(0).ToString());
}
else
{
this.m_dIdApplication = 1;
}
}
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateCreation = "TO_DATE ('" + m_dtCreation.Day.ToString("00") + "/" + m_dtCreation.Month.ToString("00") + "/" + m_dtCreation.Year.ToString("0000") + " " + m_dtCreation.Hour.ToString("00") + ":" + m_dtCreation.Minute.ToString("00") + ":" + m_dtCreation.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateCreation = "TO_DATE ('" + this.m_dtCreation.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
sql = "INSERT INTO MOU01.application VALUES (" + this.m_dIdApplication.ToString("0") + ",'" + this.m_strApplication.Replace("'", "''") + "'," + this.m_dIdProject.ToString("0") + ",'" + this.m_strDefaultTableSpace.Replace("'", "''") + "'," + strDateCreation + ",NULL,'" + this.m_strCommentary.Replace("'", "''") + "'," + dActivation.ToString("0") + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
transaction.Commit();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la création d'une application avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
public int Insert(BookModel record)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "INSERT INTO books VALUES (:isbn, :title, :author, TO_DATE(:publication_date, 'YYYY'), :price, :discount_price, :instock)"
};
#region Parameters
OracleParameter isbnParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":isbn",
Direction = System.Data.ParameterDirection.Input,
Value = record.Isbn
};
command.Parameters.Add(isbnParameter);
OracleParameter titleParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":title",
Direction = System.Data.ParameterDirection.Input,
Value = record.Title
};
command.Parameters.Add(titleParameter);
OracleParameter authorParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":author",
Direction = System.Data.ParameterDirection.Input,
Value = record.Author
};
command.Parameters.Add(authorParameter);
OracleParameter publicationDateParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":publication_date",
Direction = System.Data.ParameterDirection.Input,
Value = record.PublicationDate
};
command.Parameters.Add(publicationDateParameter);
OracleParameter priceParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":price",
Direction = System.Data.ParameterDirection.Input,
Value = record.Price
};
command.Parameters.Add(priceParameter);
OracleParameter priceDiscountParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":discount_price",
Direction = System.Data.ParameterDirection.Input,
Value = record.DiscountPrice
};
command.Parameters.Add(priceDiscountParameter);
OracleParameter instockParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":instock",
Direction = System.Data.ParameterDirection.Input,
Value = record.Instock
};
command.Parameters.Add(instockParameter);
#endregion
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
try
{
affectedRows = command.ExecuteNonQuery();
ot.Commit();
}
catch (OracleException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (Exception ex)
{
ot.Rollback();
throw new FaultException(new FaultReason(ex.Message));
}
return(affectedRows);
}
private void btnOk_Click(object sender, EventArgs e)
{
///////执行创建库体
OracleConnectionStringBuilder Connectstrbuilder = new OracleConnectionStringBuilder();
Connectstrbuilder.DataSource = this.m_Server;
Connectstrbuilder.UserID = this.m_User;
Connectstrbuilder.Password = this.m_Password;
Connectstrbuilder.Unicode = true;
Connectstrbuilder.PersistSecurityInfo = true;
////////获取表名和建库的SQL语句进行建库
OracleConnection Con = new OracleConnection(Connectstrbuilder.ConnectionString);
OracleCommand Com = null;
OracleTransaction Tra = null;
FrmProcessBar ProcBar = new FrmProcessBar(this.list_Table.Items.Count);
ProcBar.SetFrmProcessBarText("正在创建表");
Application.DoEvents();
//////开始创建///////
try
{
Con.Open(); ////////////////////打开连接
Tra = Con.BeginTransaction(); ////事务对象
}
catch (Exception eError)
{
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "打开数据库失败！\n原因：" + eError.Message);
// throw new Exception("打开数据库失败！\n原因：" + eError.Message);
ProcBar.Close();
return;
}
for (int i = 0; i < this.list_Table.Items.Count; i++)
{
ProcBar.SetFrmProcessBarValue((long)i);
Application.DoEvents();
if (this.list_Table.GetItemChecked(i))
{
string sTableName = this.list_Table.Items[i].ToString().Trim();
string sSQL = string.Empty;
if (this.m_TableDic.ContainsKey(sTableName))
{
/////获取建表用的SQL语句
bool bGet = this.m_TableDic.TryGetValue(sTableName, out sSQL);
if (bGet)
{
ProcBar.SetFrmProcessBarText("正在创建表：" + sTableName);
Application.DoEvents();
try
{
Com = new OracleCommand(sSQL, Con);
Com.Transaction = Tra;
Com.ExecuteNonQuery();
if (!m_CreatedTable.ContainsKey(sTableName))
{
m_CreatedTable.Add(sTableName, sSQL);
}
}
catch (Exception eError)
{
Tra.Rollback();
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "创建表：" + sTableName + "失败！\n原因：" + eError.Message);
// throw new Exception("创建表：" + sTableName + "失败！\n原因：" + eError.Message);
ProcBar.Close();
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
return;
}
}
}
}
}
///////创建完成，关闭连接，提交事务
try
{
Tra.Commit();
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "库体创建完成");
ProcBar.Close();
}
catch (Exception eError)
{
SysCommon.Error.ErrorHandle.ShowFrmErrorHandle("提示", "库体创建失败！\n原因：" + eError.Message);
//throw new Exception("库体创建失败！\n原因：" + eError.Message);
ProcBar.Close();
return;
}
finally
{
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
this.DialogResult = DialogResult.OK;
this.Close();
}
public int Update(BookModel record, int price, int instock)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "UPDATE books SET price = :price, instock = :instock WHERE isbn = :isbn"
};
#region Parameters
OracleParameter priceParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":price",
Direction = System.Data.ParameterDirection.Input,
Value = price
};
command.Parameters.Add(priceParameter);
OracleParameter instockParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = ":instock",
Direction = System.Data.ParameterDirection.Input,
Value = instock
};
command.Parameters.Add(instockParameter);
OracleParameter isbnParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":isbn",
Direction = System.Data.ParameterDirection.Input,
Value = record.Isbn
};
command.Parameters.Add(isbnParameter);
#endregion
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
try
{
affectedRows = command.ExecuteNonQuery();
ot.Commit();
}
catch (OracleException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (Exception ex)
{
ot.Rollback();
throw new FaultException(new FaultReason(ex.Message));
}
return(affectedRows);
}
private void BtnNext_Click(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection(Global.ConnectionString);
OracleTransaction tran = null;
if (Check())
{
buildingno = Tbbuildingno.Text;
unitno = Tbunitno.Text;
roomno = Tbroomno.Text;
floors = Nufloor.Value.ToString();
rooms = Nurooms.Value.ToString();
workplace = Tbworkplace.Text;
housetype = Cbhousetype.Text;
purpose = Cbroompurpose.Text;
usestatus = Cbroomstatus.Text;
contactway = Tbcontactway.Text;
try
{
conn.Open();
tran = conn.BeginTransaction();
OracleCommand cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandText = "update T_RESIDENT set buildingno=:buildingno,unitno=:unitno,roomno=:roomno,floors=:floors,rooms=:rooms,workplace=:workplace,purpose=:purpose,usestatus=:usestatus,contactway=:contactway,housetype=:housetype where idn=:idn and commid=:commid";
//rid, idn, fullname, sex, nation, birthdate, address, registerdept, validbegindate, validenddate, fingercount, fingerdesc, fingerleftbmp, fingerrightbmp, bigimage,smallimage,
//buildingno,unitno,roomno,floors,rooms,workplace,purpose,usestatus,contactway,housetype
//:buildingno,:unitno,:roomno,:floors,:rooms,:workplace,:purpose,:usestatus,:contactway,:housetype
cmd.Parameters.Add("buildingno", OracleDbType.NVarchar2, 18).Value = buildingno;
cmd.Parameters.Add("unitno", OracleDbType.NVarchar2, 32).Value = unitno;
cmd.Parameters.Add("roomno", OracleDbType.NVarchar2, 32).Value = roomno;
cmd.Parameters.Add("floors", OracleDbType.Int32).Value = floors;
cmd.Parameters.Add("rooms", OracleDbType.Int32).Value = rooms;
cmd.Parameters.Add("workplace", OracleDbType.NVarchar2, 32).Value = workplace;
cmd.Parameters.Add("purpose", OracleDbType.NVarchar2, 100).Value = purpose;
cmd.Parameters.Add("usestatus", OracleDbType.NVarchar2, 32).Value = usestatus;
cmd.Parameters.Add("contactway", OracleDbType.NVarchar2, 32).Value = contactway;
cmd.Parameters.Add("housetype", OracleDbType.NVarchar2, 32).Value = housetype;
cmd.Parameters.Add("idn", OracleDbType.NVarchar2, 18).Value = idn;
cmd.Parameters.Add("commid", OracleDbType.NVarchar2, 32).Value = commid;
cmd.ExecuteNonQuery();
tran.Commit();
LaInfo.Text = "保存成功.....";
System.Timers.Timer timersTimer = new System.Timers.Timer();
timersTimer.Interval = 2000;
timersTimer.Elapsed += new System.Timers.ElapsedEventHandler(theout);
timersTimer.SynchronizingObject = this;
timersTimer.Enabled = true;
}
catch (Exception er)
{
tran.Rollback();
LogHelper.WriteLog(LogFile.Error, er.ToString());
LaInfo.Text = "保存失败，请重试.....";
}
finally
{
conn.Close();
}
}
}
public int DiscountByAuthor(string author, int percentage)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
if (author == string.Empty)
{
throw new ArgumentNullException();
}
if (percentage < 0)
{
throw new ArgumentException();
}
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.Text,
CommandText = "UPDATE books SET discount_price = price * (1 - :percentage) WHERE author = :author"
};
OracleParameter PercentageParameter = new OracleParameter()
{
DbType = System.Data.DbType.Double,
ParameterName = ":percentage",
Direction = System.Data.ParameterDirection.Input,
Value = percentage / 100.0
};
command.Parameters.Add(PercentageParameter);
OracleParameter authorParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":author",
Direction = System.Data.ParameterDirection.Input,
Value = author
};
command.Parameters.Add(authorParameter);
command.Connection = oc;
command.Transaction = ot;
int affectedRows = 0;
affectedRows = command.ExecuteNonQuery();
ot.Commit();
return(affectedRows);
}
catch (OracleException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (ArgumentNullException ex)
{
throw new FaultException <ArgumentNullFault>(new ArgumentNullFault(ex.Message));
}
catch (ArgumentException ex)
{
throw new FaultException <ArgumentFault>(new ArgumentFault(ex.Message));
}
catch (Exception ex)
{
ot.Rollback();
throw new FaultException(new FaultReason(ex.Message));
}
}
//处理数据插入到数据库中
private void OpreratorDealData(DataView dv, string status)
{
MitsUtils miutils = new MitsUtils();
DataTable dtRllxParam = OracleHelper.ExecuteDataSet(OracleHelper.conn, "select * from RLLX_PARAM", null).Tables[0];
string rllx = string.Empty;
DataTable TempDt = new DataTable();
switch (radioGroup1.SelectedIndex)
{
case 0:
rllx = "传统能源";
TempDt = miutils.C2E(miutils.dictCTNY, dv.Table, MitsUtils.CTNY);
break;
case 1:
rllx = "非插电式混合动力";
TempDt = miutils.C2E(miutils.dictFCDSHHDL, dv.Table, MitsUtils.FCDSHHDL);
break;
case 2:
rllx = "插电式混合动力";
TempDt = miutils.C2E(miutils.dictCDSHHDL, dv.Table, MitsUtils.CDSHHDL);
break;
case 3:
rllx = "纯电动";
TempDt = miutils.C2E(miutils.dictCDD, dv.Table, MitsUtils.CDD);
break;
case 4:
rllx = "燃料电池";
TempDt = miutils.C2E(miutils.dictRLDC, dv.Table, MitsUtils.RLDC);
break;
}
using (OracleConnection con = new OracleConnection(OracleHelper.conn))
{
con.Open();
OracleTransaction tra = null; //创建事务，开始执行事务
try
{
tra = con.BeginTransaction();
foreach (DataRow drMain in TempDt.Rows)
{
#region 待生成的燃料基本信息数据存入燃料基本信息表
string vin = drMain["VIN"].ToString().Trim();
string sqlDeleteBasic = String.Format("DELETE FROM FC_CLJBXX WHERE VIN='{0}'", vin);
OracleHelper.ExecuteNonQuery(tra, sqlDeleteBasic, null);
DateTime clzzrqDate;
try
{
clzzrqDate = DateTime.ParseExact(drMain["CLZZRQ"].ToString().Trim(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
}
catch (Exception)
{
clzzrqDate = Convert.ToDateTime(drMain["CLZZRQ"]);
}
OracleParameter clzzrq = new OracleParameter("@CLZZRQ", clzzrqDate)
{
OracleDbType = OracleDbType.Date
};
DateTime uploadDeadlineDate = miutils.QueryUploadDeadLine(clzzrqDate);
OracleParameter uploadDeadline = new OracleParameter("@UPLOADDEADLINE", uploadDeadlineDate)
{
OracleDbType = OracleDbType.Date
};
OracleParameter creTime = new OracleParameter("@CREATETIME", DateTime.Now)
{
OracleDbType = OracleDbType.Date
};
OracleParameter upTime = new OracleParameter("@UPDATETIME", DateTime.Now)
{
OracleDbType = OracleDbType.Date
};
string qtxx;
if (dv.Table.Columns.Contains("CT_QTXX"))
{
qtxx = drMain["CT_QTXX"].ToString().Trim();
}
else
{
qtxx = string.Empty;
}
OracleParameter[] param =
{
new OracleParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OracleParameter("@USER_ID", Utils.localUserId),
new OracleParameter("@QCSCQY", drMain["QCSCQY"].ToString().Trim()),
new OracleParameter("@JKQCZJXS", drMain["JKQCZJXS"].ToString().Trim()),
clzzrq,
uploadDeadline,
new OracleParameter("@CLXH", drMain["CLXH"].ToString().Trim()),
new OracleParameter("@CLZL", drMain["CLZL"].ToString().Trim()),
new OracleParameter("@RLLX", drMain["RLLX"].ToString().Trim()),
new OracleParameter("@ZCZBZL", drMain["ZCZBZL"].ToString().Trim()),
new OracleParameter("@ZGCS", drMain["ZGCS"].ToString().Trim()),
new OracleParameter("@LTGG", drMain["LTGG"].ToString().Trim()),
new OracleParameter("@ZJ", drMain["ZJ"].ToString().Trim()),
new OracleParameter("@TYMC", drMain["TYMC"].ToString().Trim()),
new OracleParameter("@YYC", drMain["YYC"].ToString().Trim()),
new OracleParameter("@ZWPS", drMain["ZWPS"].ToString().Trim()),
new OracleParameter("@ZDSJZZL", drMain["ZDSJZZL"].ToString().Trim()),
new OracleParameter("@EDZK", drMain["EDZK"].ToString().Trim()),
new OracleParameter("@LJ", drMain["LJ"].ToString().Trim()),
new OracleParameter("@QDXS", drMain["QDXS"].ToString().Trim()),
new OracleParameter("@JYJGMC", drMain["JYJGMC"].ToString().Trim()),
new OracleParameter("@JYBGBH", drMain["JYBGBH"].ToString().Trim()),
new OracleParameter("@HGSPBM", drMain["HGSPBM"].ToString().Trim()),
new OracleParameter("@QTXX", qtxx),
new OracleParameter("@STATUS", status),
creTime,
upTime
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO FC_CLJBXX
( VIN,USER_ID,QCSCQY,JKQCZJXS,CLZZRQ,UPLOADDEADLINE,CLXH,CLZL,
RLLX,ZCZBZL,ZGCS,LTGG,ZJ,
TYMC,YYC,ZWPS,ZDSJZZL,EDZK,LJ,
QDXS,JYJGMC,JYBGBH,HGSPBM,QTXX,STATUS,CREATETIME,UPDATETIME
) VALUES
( @VIN,@USER_ID,@QCSCQY,@JKQCZJXS,@CLZZRQ,@UPLOADDEADLINE,@CLXH,@CLZL,
@RLLX,@ZCZBZL,@ZGCS,@LTGG,@ZJ,
@TYMC,@YYC,@ZWPS,@ZDSJZZL,@EDZK,@LJ,
@QDXS,@JYJGMC,@JYBGBH,@HGSPBM,@QTXX,@STATUS,@CREATETIME,@UPDATETIME)", param);
#endregion
#region 插入参数信息
string sqlDelParam = String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", vin);
OracleHelper.ExecuteNonQuery(tra, sqlDelParam, null);
// 待生成的燃料参数信息存入燃料参数表
var rows = dtRllxParam.Select(String.Format("FUEL_TYPE='{0}' and STATUS='1'", rllx));
foreach (DataRow drParam in rows)
{
string paramCode = drParam["PARAM_CODE"].ToString().Trim();
OracleParameter[] paramList =
{
new OracleParameter("@PARAM_CODE", paramCode),
new OracleParameter("@VIN", drMain["VIN"].ToString().Trim()),
new OracleParameter("@PARAM_VALUE", drMain[paramCode].ToString().Trim()),
new OracleParameter("@V_ID", "")
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO RLLX_PARAM_ENTITY
(PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES
(@PARAM_CODE,@VIN,@PARAM_VALUE,@V_ID)", paramList);
}
#endregion
}
tra.Commit();
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
finally
{
tra.Dispose();
con.Close();
}
}
}
/// <summary>
/// 开始一个事务
/// </summary>
public void BeginTrans()
{
trans = conn.BeginTransaction();
cmd.Transaction = trans;
}
//删除
private void btn_Delete_ItemClick(object sender, ItemClickEventArgs e)
{
if (this.gvDataInfo.DataSource == null)
{
XtraMessageBox.Show("没有可以操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
this.gvDataInfo.PostEditor();
var dataSource = (DataView)this.gvDataInfo.DataSource;
var dtSelected = dataSource.Table.Copy();
dtSelected.Clear();
if (dataSource != null && dataSource.Table.Rows.Count > 0)
{
for (int i = 0; i < dataSource.Table.Rows.Count; i++)
{
bool result = false;
bool.TryParse(dataSource.Table.Rows[i]["check"].ToString(), out result);
if (result)
{
dtSelected.Rows.Add(dataSource.Table.Rows[i].ItemArray);
}
}
}
if (dtSelected.Rows.Count == 0)
{
XtraMessageBox.Show("请选择您要操作的记录！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (XtraMessageBox.Show("确定要删除吗？", "删除确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) != DialogResult.OK)
{
return;
}
using (OracleConnection conn = new OracleConnection(OracleHelper.conn))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
foreach (DataRow dr in dtSelected.Rows)
{
try
{
OracleHelper.ExecuteNonQuery(trans, string.Format("DELETE FROM DB_NOTICEPARAM WHERE ID = '{0}'", dr["ID"]), null);
}
catch (Exception ex)
{
trans.Rollback();
XtraMessageBox.Show(String.Format("数据库操作出现异常，删除失败：{0}！", ex.Message), "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
if (trans.Connection != null)
{
trans.Commit();
}
}
}
this.refrashCurrentPage();
//记录操作日志
LogUtils.ReviewLogManager.ReviewLog(Properties.Settings.Default.LocalUserName, String.Format("{0}-{1}", this.Text, this.btn_Delete.Caption));
}
protected void AddPlayer_Click(object sender, EventArgs e)
{
List <string> PlayerNames = new List <string>();
foreach (GridViewRow row in PlayersGridView.Rows)
{
CheckBox check = (CheckBox)row.FindControl("playerSelectCheckBox");
if (check.Checked)
{
PlayerNames.Add(row.Cells[1].Text);
}
}
string oracleConnectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString;
OracleConnection con = new OracleConnection(oracleConnectionString);
try
{
con.Open();
foreach (string playerName in PlayerNames)
{
OracleCommand cmd = new OracleCommand("spAddUserTeam", con);
cmd.CommandType = CommandType.StoredProcedure;
OracleTransaction transaction;
// string encryptedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(Password.Text, "SHA1");
OracleParameter userid = new OracleParameter("USERID", (int)Session["UserId"]);
OracleParameter playername = new OracleParameter("PLAYERNAME", playerName);
OracleParameter rv = new OracleParameter("RETURNVALUE", OracleDbType.Int32, ParameterDirection.Output);
cmd.Parameters.Add(userid);
cmd.Parameters.Add(playername);
cmd.Parameters.Add(rv);
transaction = con.BeginTransaction();
cmd.ExecuteNonQuery();
transaction.Commit();
int returnCode = int.Parse(cmd.Parameters["RETURNVALUE"].Value.ToString());
//if (returnCode == 1)
//{
// Response.Write("Teams succesfully added");
//}
//else {
// Response.Write("Please enter valid username and or password");
//}
}
}
catch (OracleException ex)
{
Response.Write("<br/><br/><br/><br/><br/>" + ex);
}
finally
{
con.Close();
}
}
public string Run()
{
error = string.Empty;
var STUDY_KEY = string.Empty;
try
{
string conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
using (OracleConnection connection = new OracleConnection(conn))
{
connection.Open();
OracleCommand command = new OracleCommand(@"SELECT report.*, study.PATIENT_NAME, TO_CHAR(study.CREATION_DTTM, 'YYYY-MM-DD hh24:mi:ss') CREATION_DTTM FROM MOVEREPORT report
inner join study
on report.study_key = study.study_key WHERE STATUS = 0 and ROWNUM < 500 AND SOURCE_AETITLE not IN ('DSVR5','DSVR9')", connection);
var transaction = connection.BeginTransaction();
try
{
/*var da = new OracleDataAdapter(command);
* var cb = new OracleCommandBuilder(da);
* var ds = new DataSet();
* da.Fill(ds);*/
OracleDataReader reader;
command.Transaction = transaction;
reader = command.ExecuteReader();
while (reader.Read())
{
OracleClob clob = reader.GetOracleClob(2);
var cellValue = (string)clob.Value;
var ACCESSNUMBER = reader["ACCESSNUMBER"].ToString();
STUDY_KEY = reader["STUDY_KEY"].ToString();
var report = reader["REPORT_TEXT_LOB"].ToString().Replace("====== [Conclusion] ======", "");
var patient_name = reader["PATIENT_NAME"].ToString();
var creation_data = reader["CREATION_DTTM"].ToString().Replace(" ", "T");
var CRM = reader["CRM"].ToString();
var regex = new Regex(@"[^\d]");
CRM = regex.Replace(CRM, "");
using (OracleConnection connection1 = new OracleConnection(conn))
{
connection1.Open();
var transaction1 = connection1.BeginTransaction();
RunAsync(ACCESSNUMBER, STUDY_KEY, report, patient_name, creation_data, null, connection1, CRM, transaction1).Wait();
//====== [Conclusion] ======
}
}
}
catch (Exception ex)
{
error += $" error {{ studykey: {STUDY_KEY}, ex: {ex} }}";
command.Connection = connection;
command.CommandText = $"UPDATE MOVEREPORT SET STATUS = 1, ERROR = :clobparam WHERE STUDY_KEY = '{STUDY_KEY}'";
OracleParameter clobparam = new OracleParameter("clobparam", OracleDbType.Clob, error.Length);
clobparam.Direction = ParameterDirection.Input;
clobparam.Value = error;
command.Parameters.Add(clobparam);
command.Transaction = transaction;
command.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
catch
{
return(error);
}
return(error);
}
public string SaveRecoginze()
{
JObject json_user = Extension.Get_UserInfo(HttpContext.User.Identity.Name);
string id = Request["id"]; string cusno = Request["cusno"]; string ordercode = Request["ordercode"];
string result = "{success:false}"; string sql = "";
sql = "select * from list_filerecoginze where id =" + id;
DataTable dt = DBMgr.GetDataTable(sql);
if (dt == null)
{
return(result);
}
if (dt.Rows.Count != 1)
{
return(result);
}
if (dt.Rows[0]["STATUS"].ToString() == "已关联")//已经关联
{
return("{success:true,flag:'Y'}");
}
//----------------------------------------------------------------------------------------------------------------------
//--list_filerecoginze
string filepath = dt.Rows[0]["FILEPATH"].ToString();
string originalname = dt.Rows[0]["FILENAME"].ToString(); string filesuffix = originalname.Substring(originalname.LastIndexOf(".") + 1).ToUpper();
string direc_pdf = Request.PhysicalApplicationPath;
string bakpath = direc_pdf + @"/FileUpload/filereconginze/bak/";//备份原始文件目录
if (!Directory.Exists(bakpath))
{
Directory.CreateDirectory(bakpath);
}
FileInfo fi = new FileInfo(direc_pdf + filepath);
System.Uri Uri = new Uri("ftp://" + ConfigurationManager.AppSettings["FTPServer"] + ":" + ConfigurationManager.AppSettings["FTPPortNO"]);
string UserName = ConfigurationManager.AppSettings["FTPUserName"];
string Password = ConfigurationManager.AppSettings["FTPPassword"];
FtpHelper ftp = new FtpHelper(Uri, UserName, Password);
DataTable dt_order = new DataTable();
//根据识别出的订单号，查询是否存在此订单
sql = "select * from list_order a where a.ISINVALID=0";
if (ordercode != "")
{
sql += " and a.code='" + ordercode + "'";
}
if (cusno != "")
{
sql += " and a.cusno='" + cusno + "'";
}
dt_order = DBMgr.GetDataTable(sql);
if (dt_order == null)
{
return("{success:true,flag:'E'}");
}
if (dt_order.Rows.Count <= 0)
{
return("{success:true,flag:'E'}");
}
//如果为空的话，再次赋值
if (ordercode == "")
{
ordercode = dt_order.Rows[0]["CODE"].ToString();
}
if (cusno == "")
{
cusno = dt_order.Rows[0]["CUSNO"].ToString();
}
string associateno = dt_order.Rows[0]["ASSOCIATENO"].ToString();
string newfilepath = "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1);
OracleConnection conn = null;
OracleTransaction ot = null;
conn = DBMgr.getOrclCon();
try
{
conn.Open();
ot = conn.BeginTransaction();
string sql_insert = "";
if (associateno != "")//两单关联
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,IETYPE,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}','{9}')";
sql_insert = string.Format(sql_insert
, "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", ordercode, fi.Length, "订单文件"
, filesuffix, dt_order.Rows[0]["BUSITYPE"].ToString() == "40" ? "仅出口" : "仅进口", json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
DataTable dt_asOrder = new DataTable();
if (associateno != "")//两单关联
{
dt_asOrder = DBMgr.GetDataTable("select * from list_order a where a.ISINVALID=0 and ASSOCIATENO='" + associateno + "' and code!='" + ordercode + "'");
}
if (dt_asOrder == null)
{
}
else if (dt_asOrder.Rows.Count < 0)
{
}
else
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,IETYPE,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}','{9}')";
sql_insert = string.Format(sql_insert
, "/44/" + dt_asOrder.Rows[0]["code"].ToString() + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", dt_asOrder.Rows[0]["code"].ToString(), fi.Length, "订单文件"
, filesuffix, dt_asOrder.Rows[0]["BUSITYPE"].ToString() == "40" ? "仅出口" : "仅进口", json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
}
}
else
{
sql_insert = @"insert into LIST_ATTACHMENT (id
,filename,originalname,filetype,uploadtime,ordercode,sizes,filetypename
,filesuffix,uploaduserid,uploadusername)
values(List_Attachment_Id.Nextval
,'{0}','{1}','{2}',sysdate,'{3}','{4}','{5}'
,'{6}','{7}','{8}')";
sql_insert = string.Format(sql_insert
, "/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1), originalname, "44", ordercode, fi.Length, "订单文件"
, filesuffix, json_user.Value <string>("ID"), json_user.Value <string>("REALNAME"));
DBMgr.ExecuteNonQuery(sql_insert, conn);
}
//关联成功 ，文件挪到自动上传到文件服务器的目录，并删除原始目录的文件、修改原始路径为服务器新路径
DBMgr.ExecuteNonQuery("update list_filerecoginze set status='已关联',ordercode='" + ordercode + "',cusno='" + cusno
+ "',filepath='/44/" + ordercode + "/" + filepath.Substring(filepath.LastIndexOf(@"/") + 1) + "' where id=" + id, conn);
bool res = ftp.UploadFile(direc_pdf + filepath, newfilepath, true);
if (res)
{
ot.Commit();
fi.CopyTo(bakpath + filepath.Substring(filepath.LastIndexOf(@"/") + 1));
fi.Delete();
}
else
{
ot.Rollback();
DBMgr.ExecuteNonQuery("update list_filerecoginze set status='关联失败',ordercode='" + ordercode + "',cusno='" + cusno + "' where id=" + id);
}
//Submit(ordercode, json_user);//add 提交委托
result = "{success:true}";
}
catch (Exception ex)
{
ot.Rollback();
}
finally
{
conn.Close();
}
return(result);
}
public static void BLOBTest(OracleConnection connection)
{
Console.WriteLine(" BEGIN TRANSACTION ...");
OracleTransaction transaction = connection.BeginTransaction();
Console.WriteLine(" Drop table BLOBTEST ...");
try
{
OracleCommand cmd2 = connection.CreateCommand();
cmd2.Transaction = transaction;
cmd2.CommandText = "DROP TABLE BLOBTEST";
cmd2.ExecuteNonQuery();
}
catch (OracleException)
{
// ignore if table already exists
}
Console.WriteLine(" CREATE TABLE ...");
OracleCommand create = connection.CreateCommand();
create.Transaction = transaction;
create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
create.ExecuteNonQuery();
Console.WriteLine(" INSERT RECORD ...");
OracleCommand insert = connection.CreateCommand();
insert.Transaction = transaction;
insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
insert.ExecuteNonQuery();
OracleCommand select = connection.CreateCommand();
select.Transaction = transaction;
select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
Console.WriteLine(" SELECTING A BLOB (Binary Large Object) VALUE FROM BLOBTEST");
OracleDataReader reader = select.ExecuteReader();
if (!reader.Read())
{
Console.WriteLine("ERROR: RECORD NOT FOUND");
}
Console.WriteLine(" TESTING OracleLob OBJECT ...");
OracleLob lob = reader.GetOracleLob(0);
Console.WriteLine(" LENGTH: {0}", lob.Length);
Console.WriteLine(" CHUNK SIZE: {0}", lob.ChunkSize);
//try {
if (File.Exists(infilename) == false)
{
Console.WriteLine("Filename does not exist: " + infilename);
return;
}
FileStream fs = new FileStream(infilename, FileMode.Open, FileAccess.Read);
BinaryReader r = new BinaryReader(fs);
byte[] bytes = null;
int bufferLen = 8192;
bytes = r.ReadBytes(bufferLen);
while (bytes.Length > 0)
{
Console.WriteLine("byte count: " + bytes.Length.ToString());
lob.Write(bytes, 0, bytes.Length);
bytes1 = ByteArrayCombine(bytes1, bytes);
if (bytes.Length < bufferLen)
{
break;
}
bytes = r.ReadBytes(bufferLen);
}
r.Close();
fs.Close();
//}
//catch (Exception e) {
// Console.WriteLine("The file could not be read:");
// Console.WriteLine(e.Message);
//}
lob.Close();
Console.WriteLine(" CLOSING READER...");
reader.Close();
transaction.Commit();
transaction = null;
lob = null;
reader.Dispose();
reader = null;
create = null;
insert = null;
select = null;
}
public static void Main (string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
OracleConnection connection = null;
connection = new OracleConnection (connectionString);
connection.Open ();
Console.WriteLine("Setup test package and data...");
OracleCommand cmddrop = connection.CreateCommand();
cmddrop.CommandText = "DROP TABLE TESTTABLE";
try {
cmddrop.ExecuteNonQuery();
}
catch(OracleException e) {
Console.WriteLine("Ignore this error: " + e.Message);
}
cmddrop.Dispose();
cmddrop = null;
Console.WriteLine("Create table TESTTABLE...");
OracleCommand cmd = connection.CreateCommand();
// create table TESTTABLE
cmd.CommandText =
"create table TESTTABLE (\n" +
" col1 numeric(18,0),\n" +
" col2 varchar(32),\n" +
" col3 date, col4 blob)";
cmd.ExecuteNonQuery();
Console.WriteLine("Insert 3 rows...");
// insert some rows into TESTTABLE
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(45, 'Mono', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(136, 'Fun', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
cmd.CommandText =
"insert into TESTTABLE\n" +
"(col1, col2, col3, col4)\n" +
"values(526, 'System.Data.OracleClient', sysdate, EMPTY_BLOB())";
cmd.ExecuteNonQuery();
Console.WriteLine("commit...");
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Update blob...");
// update BLOB and CLOB columns
OracleCommand select = connection.CreateCommand ();
select.Transaction = connection.BeginTransaction();
select.CommandText = "SELECT col1, col4 FROM testtable FOR UPDATE";
OracleDataReader readerz = select.ExecuteReader ();
if (!readerz.Read ())
Console.WriteLine ("ERROR: RECORD NOT FOUND");
// update blob_value
Console.WriteLine(" Update BLOB column on table testtable...");
OracleLob blob = readerz.GetOracleLob (1);
byte[] bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
blob.Write (bytes, 0, bytes.Length);
blob.Close ();
readerz.Close();
select.Transaction.Commit();
select.Dispose();
select = null;
cmd.CommandText = "commit";
cmd.ExecuteNonQuery();
Console.WriteLine("Create package...");
// create Oracle package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE TestTablePkg\n" +
"AS\n" +
" TYPE T_CURSOR IS REF CURSOR;\n" +
"\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
// create Oracle package body for package TestTablePkg
cmd.CommandText =
"CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
" PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
" IS\n" +
" BEGIN\n" +
" OPEN tableCursor FOR\n" +
" SELECT *\n" +
" FROM TestTable;\n" +
" END GetData;\n" +
"END TestTablePkg;";
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
Console.WriteLine("Set up command and parameters to call stored proc...");
OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
Console.WriteLine("Execute...");
command.ExecuteNonQuery();
Console.WriteLine("Get OracleDataReader for cursor output parameter...");
OracleDataReader reader = (OracleDataReader) parameter.Value;
Console.WriteLine("Read data***...");
int r = 0;
while (reader.Read()) {
Console.WriteLine("Row {0}", r);
for (int f = 0; f < reader.FieldCount; f ++) {
Console.WriteLine("FieldType: " + reader.GetFieldType(f).ToString());
object val = "";
if (f==3) {
Console.WriteLine("blob");
//OracleLob lob = reader.GetOracleLob (f);
//val = lob.Value;
val = reader.GetValue(f);
if (((byte[])val).Length == 0)
val = "Empty Blob (Not Null)";
else
val = BitConverter.ToString((byte[])val);
}
else
val = reader.GetOracleValue(f);
Console.WriteLine(" Field {0} Value: {1}", f, val);
}
r ++;
}
Console.WriteLine("Rows retrieved: {0}", r);
Console.WriteLine("Clean up...");
reader.Close();
reader = null;
command.Dispose();
command = null;
connection.Close();
connection = null;
}
/// <summary>
/// ִ�ж���SQL��䣬ʵ�����ݿ�����
/// </summary>
/// <param name="SQLStringList">SQL���Ĺ�ϣ���keyΪsql��䣬value�Ǹ�����OracleParameter[]��</param>
public static void ExecuteSqlTran(Hashtable SQLStringList)
{
try
{
using (OracleConnection conn = new OracleConnection(connectionString))
{
conn.Open();
using (OracleTransaction trans = conn.BeginTransaction())
{
OracleCommand cmd = new OracleCommand();
try
{
//ѭ��
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
trans.Commit();
}
}
catch
{
trans.Rollback();
throw;
}
}
}
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
}
/// <summary>
/// Modifie un utilisateur en base
/// </summary>
/// <param name="connection"></param>
/// <param name="strPswd"></param>
/// <param name="oldAdmin"></param>
/// <returns></returns>
public bool Modify(OracleConnection connection, string strPswd, short oldAdmin)
{
bool bResult = false;
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "";
try
{
//modifie le mot de passe de l'utilisateur oracle
if (strPswd != this.m_strPassword)
{
sql = "ALTER USER " + this.m_strUserOracle + @" IDENTIFIED BY """ + strPswd + @"""";
command.CommandText = sql;
command.ExecuteNonQuery();
}
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateModification = "TO_DATE ('" + m_dtModification.Day.ToString("00") + "/" + m_dtModification.Month.ToString("00") + "/" + m_dtModification.Year.ToString("0000") + " " + m_dtModification.Hour.ToString("00") + ":" + m_dtModification.Minute.ToString("00") + ":" + m_dtModification.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateModification = "TO_DATE ('" + this.m_dtModification.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateBeginning = "NULL";
if (this.m_dtBeginning != new DateTime())
{
strDateBeginning = "TO_DATE ('" + m_dtBeginning.Day.ToString("00") + "/" + m_dtBeginning.Month.ToString("00") + "/" + m_dtBeginning.Year.ToString("0000") + " " + m_dtBeginning.Hour.ToString("00") + ":" + m_dtBeginning.Minute.ToString("00") + ":" + m_dtBeginning.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateBeginning = "TO_DATE ('" + this.m_dtBeginning.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateEnd = "NULL";
if (this.m_dtEnd != new DateTime())
{
strDateEnd = "TO_DATE ('" + m_dtEnd.Day.ToString("00") + "/" + m_dtEnd.Month.ToString("00") + "/" + m_dtEnd.Year.ToString("0000") + " " + m_dtEnd.Hour.ToString("00") + ":" + m_dtEnd.Minute.ToString("00") + ":" + m_dtEnd.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateEnd = "TO_DATE ('" + this.m_dtEnd.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
sql = "UPDATE MOU01.user_ SET user_='" + this.m_strUser.Replace("'", "''") +
"', service='" + this.m_strService.Replace("'", "''") +
"', commentary='" + this.m_strCommentary.Replace("'", "''") +
"', pwd='" + strPswd +
"', activation=" + dActivation.ToString("0") +
", date_beginning=" + strDateBeginning +
", date_end=" + strDateEnd +
", date_modification=" + strDateModification +
", dba_status=" + this.m_dAdmin.ToString("0") +
", id_user_modification=" + this.m_dIdUserModification.ToString("0") +
" WHERE id_user_=" + this.m_dIdUser.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//granter les roles de manager si utilisateur est un responsable
if (this.m_dAdmin == AccountMgmt.Common.Constants.ResponsableLevel && oldAdmin != AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "GRANT MANAGE_RIGHT_SEL TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "GRANT MANAGE_RIGHT_MOD TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//revoker les roles de manager si utilisateur ne l'est plus
if (this.m_dAdmin == AccountMgmt.Common.Constants.NoAdminLevel && oldAdmin == AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "REVOKE MANAGE_RIGHT_SEL FROM " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "REVOKE MANAGE_RIGHT_MOD FROM " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
transaction.Commit();
bResult = true;
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la modification de l'utilisateur avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
// 保存
private void saveParam()
{
using (OracleConnection con = new OracleConnection(OracleHelper.conn))
{
con.Open();
OracleTransaction tra = con.BeginTransaction(); //创建事务，开始执行事务
try
{
string strBah = this.tbbah.Text.Trim().ToUpper();
OracleHelper.ExecuteNonQuery(tra, String.Format("DELETE FROM FC_CLJBXX WHERE VIN = '{0}' AND STATUS='1'", strBah), null);
OracleHelper.ExecuteNonQuery(tra, String.Format("DELETE FROM RLLX_PARAM_ENTITY WHERE VIN ='{0}'", strBah), null);
#region 遍历参数,保存
foreach (Control c in this.tlp.Controls)
{
if (c is TextEdit || c is DevExpress.XtraEditors.ComboBoxEdit)
{
string paramCode = c.Name;
string paramValue = c.Text;
OracleParameter[] paramList =
{
new OracleParameter("PARAM_CODE", paramCode),
new OracleParameter("VIN", strBah),
new OracleParameter("PARAM_VALUE", paramValue),
new OracleParameter("V_ID", "")
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO RLLX_PARAM_ENTITY (PARAM_CODE,VIN,PARAM_VALUE,V_ID)
VALUES(:PARAM_CODE,:VIN,:PARAM_VALUE,:V_ID)", paramList);
}
}
#endregion
#region 保存车辆基本信息
// 保存车辆基本信息
DateTime clzzrqDate = DateTime.Parse(this.dtclzzrq.Text.Trim());
OracleParameter clzzrq = new OracleParameter("CLZZRQ", clzzrqDate)
{
DbType = DbType.Date
};
DateTime uploadDeadlineDate = Utils.QueryUploadDeadLine(clzzrqDate);
OracleParameter uploadDeadline = new OracleParameter("UPLOADDEADLINE", uploadDeadlineDate)
{
DbType = DbType.Date
};
OracleParameter creTime = new OracleParameter("CREATETIME", DateTime.Now)
{
DbType = DbType.Date
};
OracleParameter upTime = new OracleParameter("UPDATETIME", DateTime.Now)
{
DbType = DbType.Date
};
OracleParameter[] param =
{
new OracleParameter("VIN", this.tbbah.Text.Trim().ToUpper()),
new OracleParameter("HGSPBM", this.tbHgspbm.Text.Trim().ToUpper()),
new OracleParameter("USER_ID", Utils.localUserId),
new OracleParameter("QCSCQY", this.tbqcscqy.Text.Trim()),
new OracleParameter("JKQCZJXS", this.tbjkqczjxs.Text.Trim()),
new OracleParameter("CLXH", this.tbclxh.Text.Trim()),
new OracleParameter("CLZL", this.cbclzl.Text.Trim()),
new OracleParameter("RLLX", this.cbrllx.Text.Trim()),
new OracleParameter("ZCZBZL", this.tbzczbzl.Text.Trim()),
new OracleParameter("ZGCS", this.tbzgcs.Text.Trim()),
new OracleParameter("LTGG", this.tbltgg.Text.Trim()),
new OracleParameter("ZJ", this.tbzj.Text.Trim()),
clzzrq,
uploadDeadline,
new OracleParameter("TYMC", this.tbtymc.Text.Trim()),
new OracleParameter("YYC", this.cbyyc.Text.Trim()),
new OracleParameter("ZWPS", this.tbzwps.Text.Trim()),
new OracleParameter("ZDSJZZL", this.tbzdsjzzl.Text.Trim()),
new OracleParameter("EDZK", this.tbedzk.Text.Trim()),
new OracleParameter("LJ", this.tblj.Text.Trim()),
new OracleParameter("QDXS", this.cbqdxs.Text.Trim()),
new OracleParameter("STATUS", "1"),
new OracleParameter("JYJGMC", this.tbjcjgmc.Text.Trim()),
new OracleParameter("JYBGBH", this.tbbgbh.Text.Trim()),
new OracleParameter("QTXX", this.tbQtxx.Text.Trim()),
creTime,
upTime,
};
OracleHelper.ExecuteNonQuery(tra, (string)@"INSERT INTO FC_CLJBXX
( VIN,
HGSPBM,
USER_ID,
QCSCQY,
JKQCZJXS,
CLXH,
CLZL,
RLLX,
ZCZBZL,
ZGCS,
LTGG,
ZJ,
CLZZRQ,
UPLOADDEADLINE,
TYMC,
YYC,
ZWPS,
ZDSJZZL,
EDZK,
LJ,
QDXS,
STATUS,
JYJGMC,
JYBGBH,
QTXX,
CREATETIME,
UPDATETIME
) VALUES
( :VIN,
:HGSPBM,
:USER_ID,
:QCSCQY,
:JKQCZJXS,
:CLXH,
:CLZL,
:RLLX,
:ZCZBZL,
:ZGCS,
:LTGG,
:ZJ,
:CLZZRQ,
:UPLOADDEADLINE,
:TYMC,
:YYC,
:ZWPS,
:ZDSJZZL,
:EDZK,
:LJ,
:QDXS,
:STATUS,
:JYJGMC,
:JYBGBH,
:QTXX,
:CREATETIME,
:UPDATETIME)", param);
tra.Commit();
strVin = strBah; //备案号
#endregion
}
catch (Exception ex)
{
tra.Rollback();
throw ex;
}
}
}
public void setupdatabase(OracleConnection conn)
{
OracleTransaction txn = conn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
createTables(conn);
createSequences(conn);
createTriggers(conn);
setupSystemData(conn);
Console.WriteLine("\nSystem Database Successfully Configured");
txn.Commit();
close(conn);
}
catch(Exception e)
{
txn.Rollback();
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(e.Message);
}
}
/// <summary>
/// Ajout en base d'un utilisateur
/// </summary>
/// <param name="connection"></param>
/// <param name="strDefaultTablespace"></param>
/// <returns></returns>
public bool Add(OracleConnection connection, string strDefaultTablespace)
{
bool bResult = false;
// Start a local transaction
OracleTransaction transaction = connection.BeginTransaction();
OracleCommand command = new OracleCommand();
command.Connection = connection;
string sql = "";
OracleDataReader sqlReader = null;
/*long idTemporaryTablespace = -1;
* string strTemporaryTableSpace = "";*/
try
{
/*//récupère la tablespace disponible avec le nombre d'utilisateur min
* sql = "SELECT id_temporary_tablespace, temporary_tablespace FROM MOU01.temporary_tablespace WHERE (max_user-user_count)=(select min(max_user-user_count) from MOU01.temporary_tablespace where (max_user-user_count) > 0)";
* command.CommandText = sql;
* sqlReader=command.ExecuteReader();
* //lecture des données
* if (sqlReader.Read())
* {
* idTemporaryTablespace = sqlReader.GetInt64(0);
* strTemporaryTableSpace = sqlReader.GetString(1);
* }
* if(idTemporaryTablespace == -1)
* MessageBox.Show("Pas de TableSpace disponible, impossible de créer un nouvel utilisateur.");
* else
* {*/
//création d'un utilisateur oracle
sql = "CREATE USER " + this.m_strUserOracle + @" IDENTIFIED BY """ + this.m_strPassword + @""" " +
"DEFAULT TABLESPACE " + strDefaultTablespace.Replace("'", "''").ToUpper() + " " +
"TEMPORARY TABLESPACE " + this.TemporaryTableSpace.Replace("'", "''").ToUpper();
command.CommandText = sql;
command.ExecuteNonQuery();
//Mise à jour de la Tablespace temporaire
sql = "UPDATE MOU01.temporary_tablespace SET user_count=(user_count+1) WHERE id_temporary_tablespace=" + this.IdTemporaryTableSpace.ToString("0");
command.CommandText = sql;
command.ExecuteNonQuery();
//Ajout de l'utilisateur dans la table user_
short dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
if (this.m_bActivation)
{
dActivation = AccountMgmt.Common.Constants.ActivationLevel;
}
else
{
dActivation = AccountMgmt.Common.Constants.DesactivationLevel;
}
string strDateCreation = "TO_DATE ('" + m_dtCreation.Day.ToString("00") + "/" + m_dtCreation.Month.ToString("00") + "/" + m_dtCreation.Year.ToString("0000") + " " + m_dtCreation.Hour.ToString("00") + ":" + m_dtCreation.Minute.ToString("00") + ":" + m_dtCreation.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
//string strDateCreation = "TO_DATE ('" + this.m_dtCreation.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateBeginning = "NULL";
if (this.m_dtBeginning != new DateTime())
{
strDateBeginning = "TO_DATE ('" + m_dtBeginning.Day.ToString("00") + "/" + m_dtBeginning.Month.ToString("00") + "/" + m_dtBeginning.Year.ToString("0000") + " " + m_dtBeginning.Hour.ToString("00") + ":" + m_dtBeginning.Minute.ToString("00") + ":" + m_dtBeginning.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateBeginning = "TO_DATE ('" + this.m_dtBeginning.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
string strDateEnd = "NULL";
if (this.m_dtEnd != new DateTime())
{
strDateEnd = "TO_DATE ('" + m_dtEnd.Day.ToString("00") + "/" + m_dtEnd.Month.ToString("00") + "/" + m_dtEnd.Year.ToString("0000") + " " + m_dtEnd.Hour.ToString("00") + ":" + m_dtEnd.Minute.ToString("00") + ":" + m_dtEnd.Second.ToString("00") + "', 'DD/MM/YYYY HH24:MI:SS')";
}
//strDateEnd = "TO_DATE ('" + this.m_dtEnd.ToString("G") + "', 'DD/MM/YYYY HH24:MI:SS')";
sql = "INSERT INTO MOU01.user_ VALUES(MOU01.SEQ_USER_.NEXTVAL,33,1,'" + this.m_strUser.Replace("'", "''") + "','" +
this.m_strUserOracle + "','" + this.m_strService.Replace("'", "''") + "'," + strDateCreation + ",NULL,'" + this.m_strCommentary.Replace("'", "''") +
"'," + dActivation.ToString("0") + ",33,'" + this.m_strPassword.Replace("'", "''") + "'," + strDateBeginning + "," + strDateEnd + "," + this.m_dIdUserModification.ToString("0") + "," + this.IdTemporaryTableSpace.ToString("0") +
", NULL, " + this.m_dAdmin.ToString("0") + ", " + this.m_dIdSource.ToString("0") + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
//granter les roles de manager si utilisateur est un responsable
if (this.m_dAdmin == AccountMgmt.Common.Constants.ResponsableLevel)
{
sql = "GRANT MANAGE_RIGHT_SEL TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
sql = "GRANT MANAGE_RIGHT_MOD TO " + this.m_strUserOracle;
command.CommandText = sql;
command.ExecuteNonQuery();
}
//recherche de l'identificateur du nouvel utilisateur
sql = "SELECT id_user_ FROM MOU01.list_user WHERE user_oracle='" + this.m_strUserOracle + "'";
command.CommandText = sql;
sqlReader = command.ExecuteReader();
//lecture des données
if (sqlReader.Read())
{
this.m_dIdUser = long.Parse(sqlReader.GetValue(0).ToString()); //sqlReader.GetInt64(0);
}
bResult = true;
//}
if (bResult)
{
transaction.Commit();
}
}
catch (Exception error)
{
transaction.Rollback();
MessageBox.Show("Problème lors de la création de l'utilisateur avec l'erreur : " + error.Message);
}
finally
{
//deconnexion
try
{
// Fermeture de la base de données
if (sqlReader != null)
{
sqlReader.Close();
sqlReader.Dispose();
}
if (command != null)
{
command.Dispose();
}
}
catch (Exception error)
{
MessageBox.Show("Message d’erreur : " + error.Message);
}
}
return(bResult);
}
public static string rate(long korid, string table, string tableUpdate, string hardOrGame, string id, int rating)
{
string queryDelete = "DELETE FROM " + table + " where korid='" + korid + "' and " + hardOrGame + "='" + id + "'";
OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
string queryInsert = "insert into " + table + "(korid, " + hardOrGame + ", ocena) values (:korid, :" + hardOrGame + ", :ocena)";
string queryUpdate = "update " + tableUpdate + " set vkupnaocena = (select avg(ocena) from " + table + " where " + hardOrGame + " =:" + hardOrGame + ") where " + hardOrGame + " =:" + hardOrGame;
OracleCommand commandInsert = new OracleCommand(queryInsert, oracleConnection);
commandInsert.Parameters.Add(new OracleParameter("korid", korid));
commandInsert.Parameters.Add(new OracleParameter(hardOrGame, id));
commandInsert.Parameters.Add(new OracleParameter("ocena", rating));
OracleCommand commandUpdate = new OracleCommand(queryUpdate, oracleConnection);
commandUpdate.Parameters.Add(new OracleParameter(hardOrGame, id));
commandUpdate.Parameters.Add(new OracleParameter(hardOrGame, id));
OracleCommand commandDelete= new OracleCommand(queryDelete,oracleConnection);
int successDelete = -1;
int successInsert = -1;
int successUpdate= -1;
OracleTransaction transaction = null;
try
{
oracleConnection.Open();
transaction = oracleConnection.BeginTransaction();
commandDelete.ExecuteNonQuery();
commandInsert.ExecuteNonQuery();
commandUpdate.ExecuteNonQuery();
transaction.Commit();
return "Rating is successfull";
}
catch (Exception e)
{
transaction.Rollback();
/* if (e.Message.StartsWith("ORA-00001"))
{
string queryUpdate = "update " + table + " set ocena=:ocena where " + hardOrGame + "=:" + hardOrGame + " and korid=:korid";
OracleCommand cmd = new OracleCommand(queryUpdate, oracleConnection);
cmd.Parameters.Add(new OracleParameter("korid", korid));
cmd.Parameters.Add(new OracleParameter(hardOrGame, id));
cmd.Parameters.Add(new OracleParameter("ocena", rating));
try
{
success= cmd.ExecuteNonQuery();
s2 = commandUpdate.ExecuteNonQuery();
return "Rating is successfull";
}
catch (Exception ex)
{
return e.Message;
}
}*/
}
finally
{
oracleConnection.Close();
}
return "Има проблем со базата, пробајте повторно";
}