public void addMakers(string TypeOfRepair, int Costs, DateTime repairdate)
{
OracleConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
// MessageBox.Show("Open1");
OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "System.cwpack2.addMakers";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("repairtype", OracleDbType.NVarchar2).Value = TypeOfRepair;
cmd.Parameters.Add("price", OracleDbType.Int32).Value = Costs + amount;
cmd.Parameters.Add("repairdate", OracleDbType.Date).Value = repairdate;
// Выполнить процедуру.
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
MessageBox.Show(ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void ChangeStatus(int idor)
{
OracleConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
// MessageBox.Show("Open1");
OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "System.cwpack2.changeOrderStatus";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("idor", OracleDbType.Int32).Value = idor;
// Выполнить процедуру.
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
MessageBox.Show(ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
public void ImportDataTable(Cells c)
{
DataTable tableData = ImportWorkSheet(c);
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO Cofeed VALUES( ";
for (var i = 0; i < tableData.Columns.Count; i++)
{
cmdText += ":v" + i + ",";
}
cmdText += "sysdate,null)";
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < tableData.Columns.Count; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
/// <summary>
/// 執行非查詢的SQL語句
/// </summary>
/// <param name="SQL">SQL語句</param>
/// <param name="DBUrl">數據庫鏈接地址</param>
/// <returns></returns>
public int GetNonQueryBySQL(string SQL, string DBUrl)
{
if (DBUrl.ToString().Trim() == "" || DBUrl == null)
{
throw new Exception("數據庫鏈接地址不能為空");
}
// 获取与数据库的连接对象並且绑定连接字符串
Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(DBUrl);
conn.Open(); //打開資源
//获取数据库操作对象
Oracle.ManagedDataAccess.Client.OracleCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = SQL;
int num = cmd.ExecuteNonQuery();
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
return(num);
}
catch (Exception ex)
{
cmd.Dispose(); //釋放資源
conn.Dispose(); //釋放資源
conn.Close(); //關閉
throw ex;
}
}
public void ImportDataTable(Cells c, out int total)
{
DataTable datatable = ImportWorkSheet(c);
total = datatable.Rows.Count;
using (OracleConnection conn = new OracleConnection(Connectionstr))
{
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
try
{
foreach (DataRow dr in datatable.Rows)
{
string sql1 = CreateInsertSql(dr);
using (OracleCommand cmd = new OracleCommand(sql1, conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
conn.Close();
throw;
}
}
}
public int LoginUsingPassword(string username, string password)
{
var result = 0;
var userNameParam = new OracleParameter("USERNAME", OracleDbType.Varchar2, username, ParameterDirection.Input);
var userPassParam = new OracleParameter("PASSWORD", OracleDbType.Varchar2, password, ParameterDirection.Input);
var resultPassParam = new OracleParameter("result_tab", OracleDbType.Int32, ParameterDirection.ReturnValue);
using (OracleConnection connection = new OracleConnection(_connectionString))
{
OracleCommand cmd = new OracleCommand("LOGINFUNC", connection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.Parameters.Add(resultPassParam);
cmd.Parameters.Add(userNameParam);
cmd.Parameters.Add(userPassParam);
connection.Open();
cmd.ExecuteNonQuery();
var res = cmd.Parameters["result_tab"].Value.ToString();
result = Convert.ToInt32(res);
connection.Close();
// context.SaveChanges();
}
return(result);
}
public void Order(int eq_Id, int client_Id, int empl_Id, int status_id, int maker_id, DateTime dateO)
{
OracleConnection conn = DBUtils.GetDBConnection();
try
{
conn.Open();
// MessageBox.Show("Open1");
OracleCommand cmd = new Oracle.ManagedDataAccess.Client.OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "System.cwpack1.makeOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("eq_Id", OracleDbType.Int32).Value = eq_Id;
cmd.Parameters.Add("client_Id", OracleDbType.Int32).Value = client_Id;
cmd.Parameters.Add("empl_Id", OracleDbType.Int32).Value = empl_Id;
cmd.Parameters.Add("status_id", OracleDbType.Int32).Value = status_id;
cmd.Parameters.Add("dateO", OracleDbType.Date).Value = dateO;
// Выполнить процедуру.
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex);
MessageBox.Show(ex.StackTrace);
}
finally
{
conn.Close();
conn.Dispose();
}
}
protected void RegisterButton_Click(object sender, EventArgs e)
{
if (Page.IsValid)
{
// Hash and salt the password using Bcyrpt before it gets sent to the database
var saltedPassword = BCryptHelper.HashPassword(password.Text, Global.Salt);
using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
{
// Declare the stored procedure to execute and send it the parameters it needs
OracleCommand objCmd = new OracleCommand("tickets_api.insertGuest", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
objCmd.Parameters.Add("p_FirstName", OracleDbType.Varchar2, first_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_LastName", OracleDbType.Varchar2, last_name.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Email", OracleDbType.Varchar2, email.Text, ParameterDirection.Input);
objCmd.Parameters.Add("p_Password", OracleDbType.Varchar2, saltedPassword, ParameterDirection.Input);
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
}
catch (Exception)
{
labelMessage.Text = "Could not register. Try again later.";
}
objConn.Close();
}
if (string.IsNullOrEmpty(labelMessage.Text))
Response.Redirect("Login.aspx");
}
}
public IHttpActionResult CreateTestEntry(Process p)
{
string ConStr = Static.Secrets.ApiConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string iStr = @"INSERT INTO ifc.qmes_tpm_repairs_imp (order_nr, manager_nr, closemean_nr, initial_diagnosis, repair_actions, STATUS, IS_ADJUSTMENT, REASONCODE2, REASONCODE3)
VALUES ('{0}','{1}','{2}','{3}', '{4}', '{5}','{6}','{8}','{9}')";
iStr = string.Format(iStr, p.Number, p.Manager, p.FinishedBy, p.InitialDiagnosis, p.RepairActions, p.Status, p.IsAdjustment, p.ReasonCode2, p.ReasonCode3);
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(iStr, Con);
try
{
Command.ExecuteNonQuery();
return(Ok());
}
catch (Exception ex)
{
return(InternalServerError(ex));
}
}
public void ExecuteCneZcxExcel()
{
string zcxStr = ConfigurationManager.AppSettings["ZCXConnStr"].ToString();
using (var zcxConn = new OracleConnection(zcxStr))
{
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXEnergyYieldData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
//chemical ver. 2.3 rewrite this class
using (OracleCommand cmd = new OracleCommand())
{
zcxConn.Open();
cmd.Connection = zcxConn;
cmd.CommandText = "sp_MergeZCXChemistryOutputData";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
zcxConn.Close();
}
}
}
public void insertData(string sql)
{
/*using (OracleConnection connection = new OracleConnection(connectionString))
* using (OracleCommand command = new OracleCommand("p", connection))
* {
* command.Parameters.Add(new OracleParameter("PRIX", 50));
* command.Parameters.Add(new OracleParameter("IDDORTOIR", 1));
*
* command.Connection.Open();
* command.ExecuteNonQuery();
* command.Connection.Close();
* }*/
OracleConnection connection = new OracleConnection(connectionString);
//GIVE PROCEDURE NAME
OracleCommand command = new OracleCommand("p", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new OracleParameter("PRIX", 5000));
command.Parameters.Add(new OracleParameter("IDDORTOIR", 1));
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
public void ExecuteLongZhongExcel(OracleConnection conn, OracleTransaction tran)
{
ExecuteCneZcxExcel();
// energy map 2 max , ver. 2.3
using (OracleCommand cmd = new OracleCommand())
{
cmd.Transaction = tran;
cmd.Connection = conn;
cmd.CommandText = "createMaxEnergyYieldTable";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
//chemical map 2 max
using (OracleCommand cmd = new OracleCommand())
{
cmd.Transaction = tran;
cmd.Connection = conn;
cmd.CommandText = "createMaxChemistryOutputTable";
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 300;
cmd.ExecuteNonQuery();
}
}
public void ExecuteCoffedAndMetalMax(OracleConnection conn, OracleTransaction tran)
{
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxMetalOutputTable";
cmd.CommandTimeout = 300;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "CREATEMAXAGRICULTRUEOutput";
cmd.CommandTimeout = 300;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
//create agricultrue inventory max table 20140710 yy
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxAgricultrueInventory";
cmd.CommandTimeout = 300;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
public int GetMaxListId(Connection connection)
{
int max_id = -1;
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("getMaximumListId", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("index_max", OracleDbType.Int32);
command.ExecuteNonQuery();
max_id = Int32.Parse(command.Parameters["index_max"].Value.ToString());
connection.Close();
}
catch (Exception e)
{
throw;
// connection.Close();
}
finally
{
}
return(max_id);
}
public void InsertProductInList(int list_id, int product_id, int quantity, Connection connection)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("insert_product_info", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("list_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = list_id;
command.Parameters.Add("product_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = product_id;
command.Parameters.Add("quantity_2", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = quantity;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
connection.Close();
}
catch (Exception e)
{
connection.Close();
throw;
}
}
public void insert_into_common_table(Connection connection, int id_store, int id_client, int dist)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("insert_into_distance_table", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("store_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id_store;
command.Parameters.Add("client_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id_client;
command.Parameters.Add("distance", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = dist;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
connection.Close();
}
catch (Exception e)
{
connection.Close();
throw;
}
}
//string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.130.23)(PORT=8903))(CONNECT_DATA=(SERVICE_NAME = settle_primary)));User Id=settle;Password=settle;";
static void Main123(string[] args)
{
ArrayList arr = GetAttId1();
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
for (int i = 0; i < arr.Count; i++)
{
double ds = 0;
int total = 1;
for (int j = 0; j < total; j++)
{
DateTime startDate = DateTime.Now;
OracleParameter[] oracleParameters = new OracleParameter[1];
oracleParameters[0] = new OracleParameter("p_attachmentId", arr[i]);
OracleCommand cmd = new OracleCommand("SP_CHECKCINEMA_MT_BYATTID", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
cmd.ExecuteNonQuery();
DateTime endDate = DateTime.Now;
ds += (endDate - startDate).TotalSeconds;
}
Console.WriteLine(arr[i] + "：" + ds / total);
}
conn.Close();
}
Console.ReadLine();
}
/// <summary>
/// 执行sql执行增删改
/// </summary>
/// <param name="cmdText">sql语句</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static int ExecToSqlNonQuery(string cmdText)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return(result);
}
}
public void ExecuteMetals(OracleConnection conn, OracleTransaction tran)
{
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxMetalTable";
cmd.CommandTimeout = 300;
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
public void ImportData(DataTable tb, string[] filters, StringBuilder sb)
{
int updateRows = 0, insertRows = 0;
using (OracleConnection conn = new OracleConnection(connstr))
{
conn.Open();
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
foreach (DataRow dr in tb.Rows)
{
string strWhere = CreateFilter(filters, dr);
string sqlExists = "select * from " + tb.TableName + strWhere;
cmd.CommandText = sqlExists;
OracleDataAdapter sda = new OracleDataAdapter(cmd);
DataTable t = new DataTable();
sda.Fill(t);
if (t != null && t.Rows.Count == 1)
{
//更新数据；
string updatesql = CreateUpdateSql(tb, dr, strWhere);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
updateRows++;
}
else
{
//插入数据；
string updatesql = CreateInsertSql(tb, dr);
cmd.CommandText = updatesql;
cmd.ExecuteNonQuery();
insertRows++;
}
}
}
conn.Close();
sb.Append(" update:" + updateRows + " insert:" + insertRows + "\r\n");
}
}
public void ExecuteMax()
{
try
{
using (conn = new OracleConnection(ConfigurationManager.AppSettings["mergeData"]))
{
conn.Open();
tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxChemicalIndustryTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyGasTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyTable";
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.Transaction = tran;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxEnergyOilTable";
cmd.ExecuteNonQuery();
}
tran.Commit();
}
}
catch (Exception e)
{
tran.Rollback();
}
finally
{
conn.Close();
}
}
public void insert_new_address_client(Connection connection, int id_client_max, string city)
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("get_total_price1", connection._connection);
command.CommandText = "insert into addressC VALUES(:id_c,:city)";
command.Parameters.Add(new OracleParameter("id_c", id_client_max));
command.Parameters.Add(new OracleParameter("city", city));
command.ExecuteNonQuery();
connection.Close();
}
public void AdjustStock(int quantity, int product_d, Connection connection)
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("adjust_stock", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("list_id", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = quantity;
command.Parameters.Add("id_product", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = product_d;
command.ExecuteNonQuery();
connection.Close();
}
public string Add()
{
string _Result = "OK";
string ConStr = Static.Secrets.OracleConnectionString;
var Con = new Oracle.ManagedDataAccess.Client.OracleConnection(ConStr);
if (Con.State == System.Data.ConnectionState.Closed)
{
Con.Open();
}
string iStr = @"INSERT INTO QMES_WIP_ORDER (ORDER_ID, ORDER_NR, NAME, DESCRIPTION_LONG, ORDER_TYPE_ID, SCHEDULED_START_DATE, SCHEDULED_END_DATE, MACHINE_ID, STATUS, C_USER, C_DATE, LM_USER, LM_DATE, TYPE_STATE_ID)
VALUES (:TheId, :TheNumber, :TheName, :Description, :TypeId, :ScheduledStart, :ScheduledFinish, :MachineId, :Status, :CreatedBy, :CreatedOn, :LmBy, :LmOn, :StateId)";
try
{
var Command = new Oracle.ManagedDataAccess.Client.OracleCommand(iStr, Con);
OracleParameter[] parameters = new OracleParameter[]
{
new OracleParameter("TheId", 1051275),
new OracleParameter("TheNumber", this.Number),
new OracleParameter("TheName", this.Name),
new OracleParameter("Description", this.Description),
new OracleParameter("TypeId", (int)this.Type),
new OracleParameter("ScheduledStart", this.ScheduledStartDate),
new OracleParameter("ScheduledFinish", this.ScheduledFinishDate),
new OracleParameter("MachineId", this.Machine.MesId),
new OracleParameter("Status", "OK"),
new OracleParameter("CreatedBy", 247),
new OracleParameter("CreatedOn", DateTime.Now),
new OracleParameter("LmBy", 247),
new OracleParameter("LmOn", DateTime.Now),
new OracleParameter("StateId", 10)
};
Command.Parameters.AddRange(parameters);
//OracleParameter outputParameter = new OracleParameter("returnedId", OracleDbType.Decimal);
//outputParameter.Direction = System.Data.ParameterDirection.Output;
//Command.Parameters.Add(outputParameter);
Command.ExecuteNonQuery();
//decimal id = Convert.ToDecimal(outputParameter.Value);
}catch (Exception ex)
{
_Result = $"Error: {ex.Message}";
}
return(_Result);
}
/// <summary>
/// 执行存储过程没有返回值
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="outParameters">参数</param>
/// <param name="oracleParameters">所传参数（必须按照存储过程参数顺序）</param>
/// <param name="strConn">链接字符串</param>
/// <returns></returns>
public static void ExecToStoredProcedure(string cmdText, OracleParameter[] oracleParameters, string strConn)
{
using (OracleConnection conn = new OracleConnection(strConn))
{
OracleCommand cmd = new OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(oracleParameters);
cmd.CommandTimeout = 1800;
conn.Open();
Console.WriteLine("3：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
cmd.ExecuteNonQuery();
Console.WriteLine("4：" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"));
Console.WriteLine(cmd.Parameters["p_returnvalue"].Value.ToString());
conn.Close();
}
}
public static void ExecCinema_MT(int attid)
{
Console.WriteLine(DateTime.Now.ToString(_formatString));
//OracleConnection conn, OracleTransaction tran
string conStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME = orcl)));User Id=settle;Password=settle;";
OracleConnection conn = new OracleConnection(conStr);
conn.Open();
OracleTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
var paramArray = new[]
{
new OracleParameter("@p_attachmentId", OracleDbType.Int32)
{
Value = attid
},
//new OracleParameter("@QueryDateType", OracleDbType.Varchar2) { Value = queryDateType },
//new OracleParameter("@OrgType", OracleDbType.Varchar2) { Value = orgType },
//new OracleParameter("@TrustType", OracleDbType.NVarchar2) { Value = trustType },
// new OracleParameter("@Total", OracleDbType.Int32,ParameterDirection.Output),
//new OracleParameter("@cur",OracleDbType.RefCursor,ParameterDirection.Output)
};
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SP_CHECKCinema_MT_BYattID";
cmd.CommandTimeout = 90;
cmd.Parameters.AddRange(paramArray);
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
tran.Commit();
}
}
catch (Exception ee)
{
tran.Rollback();
Console.WriteLine(ee.Message);
}
finally {
conn.Close();
}
Console.WriteLine(DateTime.Now.ToString(_formatString));
}
public void reserver(int idclient, int idchambre, DateTime debut, DateTime fin)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
cmd.CommandText = "pr_reservation";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("leclient", OracleDbType.Int32).Value = idclient;
cmd.Parameters.Add("lachambre", OracleDbType.Int32).Value = idchambre;
cmd.Parameters.Add("ledebut", OracleDbType.Date).Value = debut;
cmd.Parameters.Add("lafin", OracleDbType.Date).Value = fin;
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
public void Execute(OracleConnection conn, OracleTransaction tran)
{
using (OracleCommand cmd = new OracleCommand("delete from GDT_AgricultureMax", conn))
{
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "createMaxAgricultrueTable";
cmd.Transaction = tran;
cmd.ExecuteNonQuery();
}
}
public void ExecuteInsertSql(string tableName, DataTable tableData, string minReDate, int paraCount, bool isHasHistoryValue = false)
{
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmd = new OracleCommand("delete " + tableName + " where RE_DATE>='" + minReDate + "'", con);
cmd.ExecuteNonQuery();
con.Close();
}
using (var con = new OracleConnection(Connectionstr))
{
con.Open();
var cmdText = "INSERT INTO " + tableName + " VALUES( ";
for (var i = 0; i < paraCount; i++)
{
cmdText += ":v" + i + ",";
}
if (isHasHistoryValue)
{
cmdText += "sysdate,null)";
}
else
{
cmdText += "sysdate)";
}
var cmd = new OracleCommand(cmdText, con);
for (var j = 0; j < tableData.Rows.Count; j++)
{
cmd.Parameters.Clear();
for (var i = 0; i < paraCount; i++)
{
var par = new OracleParameter("v" + i, OracleDbType.NVarchar2)
{
Direction = ParameterDirection.Input,
Value = tableData.Rows[j][i].ToString()
};
cmd.Parameters.Add(par);
}
cmd.ExecuteNonQuery();
}
con.Close();
}
}
private void button1_Click(object sender, EventArgs e)
{
string name = textBoxName.Text;
string surname = textBoxSurname.Text;
Connection connection = new Connection();
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("GETUSERORDERS1", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("nume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = name;
command.Parameters.Add("prenume", OracleDbType.Varchar2, System.Data.ParameterDirection.Input).Value = surname;
Oracle.ManagedDataAccess.Client.OracleParameter p_rc = command.Parameters.Add("rc", OracleDbType.RefCursor,
DBNull.Value,
System.Data.ParameterDirection.Output);
List <Order> orders_list = new List <Order>();
// Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("l_cursor", OracleDbType.RefCursor);
// output.Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
// Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
// Dim reader As OracleDataReader
DbDataReader reader =
command.ExecuteReader(CommandBehavior.SequentialAccess);
// var reader = command.Parameters("rc").Value;
while (reader.Read())
{
Order order = new Order(reader.GetInt32(0));
orders_list.Add(order);
}
connection.Close();
seeOrdersForm seeOrdersForm = new seeOrdersForm(connection, orders_list);
}
public List <Products_Info> GetDbProductsList(Connection connection, int id)
{
try
{
if (connection._connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
var command = new Oracle.ManagedDataAccess.Client.OracleCommand("returnCartPro", connection._connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
List <Products_Info> products_Info_list = new List <Products_Info>();
command.Parameters.Add("ID_INPUT", OracleDbType.Int32, System.Data.ParameterDirection.Input).Value = id;
Oracle.ManagedDataAccess.Client.OracleParameter p_rc = command.Parameters.Add("rc", OracleDbType.RefCursor,
DBNull.Value,
System.Data.ParameterDirection.Output);
// Oracle.ManagedDataAccess.Client.OracleParameter output = command.Parameters.Add("rc", OracleDbType.RefCursor);
// output.Direction = System.Data.ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
Oracle.ManagedDataAccess.Client.OracleDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Products_Info products_Info = new Products_Info(
reader.GetInt32(0),
reader.GetString(1),
reader.GetInt32(2),
reader.GetInt32(3),
reader.GetString(4),
reader.GetString(5));
products_Info_list.Add(products_Info);
}
connection.Close();
return(products_Info_list);
// return null;
}
catch (Exception e)
{
connection.Close();
throw;
}
}