public void addPizza(Pizza d) { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "insert into pizza values (:BASEID, :ORDERID, :TOPPINGIDL, :TOPPINGIDR, :PIZZAID)" }; cmd.Parameters.Add("BASEID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["BASEID"].Value = d.BaseID; cmd.Parameters.Add("ORDERID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["ORDERID"].Value = d.OrderID; cmd.Parameters.Add("TOPPINGIDL", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["TOPPINGIDL"].Value = d.ToppingIdL; cmd.Parameters.Add("TOPPINGIDR", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["TOPPINGIDR"].Value = d.ToppingIdR; cmd.Parameters.Add("PIZZAID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["PIZZAID"].Value = d.PizzaID; cmd.ExecuteNonQuery(); DataSource.pizzas = getPizzas(); }
/// <summary> /// 针对包含Blob字段的特殊处理 /// 【由于使用微软企业库导致使用了Blob字段的存储过程报错:不能将System.Byte[] 绑定到 Blob。。。。,所以这里要对含有Blob字段的存储过程做特殊处理】 /// </summary> /// <param name="db"></param> /// <param name="command"></param> /// <param name="rowsAffected">受影响的行数</param> /// <returns></returns> public bool ExecuteContainBlob(Database db, IDbCommand command, out int rowsAffected) { bool isContainBlob = false; rowsAffected = 0; if (db is OracleDatabase) { for (int i = 0; i < command.Parameters.Count; i++) { if (((OracleParameter)command.Parameters[i]).OracleType.ToString() == "Blob") { isContainBlob = true; break; } } if (isContainBlob && command.CommandType == CommandType.StoredProcedure) { //OracleDataAccessClient.OracleConnection conn = new OracleDataAccessClient.OracleConnection(db.ConnectionString); //conn.Open(); using (OracleDataAccessClient.OracleConnection conn = new OracleDataAccessClient.OracleConnection(db.ConnectionString)) { OracleDataAccessClient.OracleCommand commandNew = new OracleDataAccessClient.OracleCommand(command.CommandText, conn); commandNew.CommandType = CommandType.StoredProcedure; CreateOracleParameter(command, commandNew); rowsAffected = commandNew.ExecuteNonQuery(); //commandNew.Dispose(); //conn.Close(); } } } return(isContainBlob); }
public bool new_customers(int id) { var con = new OracleConnection(constr); con.Open(); var objCmd = new OracleCommand { Connection = con, CommandText = "Tables.new_customers", CommandType = CommandType.StoredProcedure }; var prm = new OracleParameter { Direction = ParameterDirection.ReturnValue, DbType = DbType.Int64 }; objCmd.Parameters.Add(prm); objCmd.Parameters.Add("new_cust", OracleDbType.Int64).Direction = ParameterDirection.Input; objCmd.Parameters["new_cust"].Value = id; objCmd.ExecuteNonQuery(); return(Convert.ToInt32(objCmd.Parameters[0].Value) == 1); }
/// <summary> /// 更新数据库的Blob数据类型,需指定sql参数为length=1的参数 /// </summary> /// <param name="strSql"></param> /// <param name="ImageData"></param> /// <returns></returns> public int InputBlob(string strSql, byte[] ImageData) { // string block="INSERT INTO test_image(id,name, image) VALUES (2,'a', :blobtodb)"; this.command.Connection = this.con as OracleConnection; command.CommandText = strSql + ""; command.CommandType = System.Data.CommandType.Text; string strParam = ""; int i = strSql.IndexOf(":", 0); if (i <= 0) { this.Err = "未指定参数!" + strSql; this.WriteErr(); return(-1); } strParam = strSql.Substring(i + 1, 1); OracleParameter param = command.Parameters.Add(strParam, OracleDbType.Blob); param.Direction = System.Data.ParameterDirection.Input; // Assign Byte Array to Oracle Parameter param.Value = ImageData; // Step 5 // Execute the Anonymous PL/SQL Block try { command.ExecuteNonQuery(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Number; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.WriteErr(); return(-1); } return(0); }
static void Main(string[] args) { Oracle.DataAccess.Client.OracleConnection conn = DBUtils.GetDBConnection(); conn.Open(); try { // Tạo một đối tượng Command gọi thủ tục Get_Employee_Info. Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("Get_Employee_Info", conn); // Kiểu của Command là StoredProcedure cmd.CommandType = System.Data.CommandType.StoredProcedure; // Thêm tham số @p_Emp_Id và sét giá trị của nó = 100. cmd.Parameters.Add("@p_Emp_Id", Oracle.DataAccess.Client.OracleDbType.Int32).Value = 100; // Thêm tham số @v_Emp_No kiểu Varchar(20). cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("@v_Emp_No", Oracle.DataAccess.Client.OracleDbType.Varchar2, 20)); cmd.Parameters.Add(new OracleParameter("@v_First_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50)); cmd.Parameters.Add(new OracleParameter("@v_Last_Name", Oracle.DataAccess.Client.OracleDbType.Varchar2, 50)); cmd.Parameters.Add(new OracleParameter("@v_Hire_Date", OracleDbType.Date)); // Đăng ký tham số @v_Emp_No là OUTPUT. cmd.Parameters["@v_Emp_No"].Direction = System.Data.ParameterDirection.Output; cmd.Parameters["@v_First_Name"].Direction = System.Data.ParameterDirection.Output; cmd.Parameters["@v_Last_Name"].Direction = System.Data.ParameterDirection.Output; cmd.Parameters["@v_Hire_Date"].Direction = System.Data.ParameterDirection.Output; // Thực thi thủ tục. cmd.ExecuteNonQuery(); // Lấy các giá trị đầu ra. string empNo = cmd.Parameters["@v_Emp_No"].Value.ToString(); string firstName = cmd.Parameters["@v_First_Name"].Value.ToString(); string lastName = cmd.Parameters["@v_Last_Name"].Value.ToString(); object hireDateObj = cmd.Parameters["@v_Hire_Date"].Value; Console.WriteLine("hireDateObj type: " + hireDateObj.GetType().ToString()); Oracle.DataAccess.Types.OracleDate hireDate = (Oracle.DataAccess.Types.OracleDate)hireDateObj; Console.WriteLine("Emp No: " + empNo); Console.WriteLine("First Name: " + firstName); Console.WriteLine("Last Name: " + lastName); Console.WriteLine("Hire Date: " + hireDate); } catch (Exception e) { Console.WriteLine("Error: " + e); Console.WriteLine(e.StackTrace); } finally { conn.Close(); conn.Dispose(); } Console.Read(); }
private string SaveToDatabase(byte[] btReport, string id) { // Write to J Drive string filepath = ""; string timestamp = DateTime.Now.ToString("MMddHHmmssfff"); string filename = "fax" + id + "_" + timestamp + ".pdf"; string credentials = Data.getJCredential(); String[] credentialsArray = credentials.Split("|".ToCharArray()); string COMPUTER_IP = credentialsArray[0].ToString().Split(':')[1]; string DOMAIN = credentialsArray[1].ToString().Split(':')[1]; string USER_NAME = credentialsArray[2].ToString().Split(':')[1]; string PASSWORD = credentialsArray[3].ToString().Split(':')[1]; try { using (NetworkShareAccesser.Access(COMPUTER_IP, DOMAIN, USER_NAME, PASSWORD)) { filepath = "\\\\" + COMPUTER_IP + "\\j_drive\\Clients\\LifeEvent\\" + filename; File.WriteAllBytes(filepath, btReport); filepath = filepath.Replace("\\\\" + COMPUTER_IP + "\\j_drive\\", "J:\\"); } } catch { } // Save to Database Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); try { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.SaveFaxImage", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", SQLStatic.Sessions.GetUserName(HttpContext.Current.Request.Cookies["Session_ID"].Value.ToString())); Oracle.DataAccess.Client.OracleParameter parm; parm = new Oracle.DataAccess.Client.OracleParameter( "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport); cmd.Parameters.Add(parm); try { cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } finally { conn.Close(); conn.Dispose(); } return(filename); }
protected void ingresarTarea_Click(object sender, EventArgs e) { try { lblidddl.Text = ""; //Para el Usuario ora.Open(); //Llamar al Stored Procedure Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora); comando1.CommandType = System.Data.CommandType.StoredProcedure; //Asignar variable de cursor comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreo.Text; comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output); comando1.ExecuteNonQuery(); Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader(); int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString()); lblidddl.Text = id.ToString(); ora.Close(); //Para la Tarea string tipo = ddlTipoTarea.SelectedItem.ToString(); lblidddl.Text = tipo; int flujo = ddlFlujo.SelectedIndex + 1; //FECHA var date = fecha.SelectedDate; ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2); comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text; comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.Text; comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id; comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4; comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo; comando2.Parameters.Add("P_FLUJO", OracleType.VarChar).Value = flujo; comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString(); comando2.ExecuteNonQuery(); // ora2.Close(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>"); txtNombre.Text = ""; txtDescripcion.Text = ""; // txtCorreo.Text = ""; } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>"); } }
public int ExecuteNonQuery(string cmdText, CommandType cmdType, List <Oracle.DataAccess.Client.OracleParameter> parameters = null) { using (var sCon = new Oracle.DataAccess.Client.OracleConnection(GetConnectionString())) { using (var sCmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, sCon)) { sCmd.CommandTimeout = 600; sCmd.CommandType = cmdType; if (null != parameters) { sCmd.Parameters.AddRange(parameters.ToArray()); } sCon.Open(); return(sCmd.ExecuteNonQuery()); } } }
public static IEnumerable <Customer> getCustomerStored() { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "tables.getcustomertable", CommandType = CommandType.StoredProcedure }; var reader = cmd.ExecuteNonQuery(); return(null); }
public static void SaveArray(int intCount, string[] strRecordID, string[] strFieldName, string[] strRate, string[] strUserName) { Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(" pkg_automated_rate_update.saverate", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.ArrayBindCount = intCount; OracleParameter paramRecordID = new OracleParameter("record_id_", OracleDbType.Varchar2); paramRecordID.Direction = ParameterDirection.Input; paramRecordID.Value = strRecordID; cmd.Parameters.Add(paramRecordID); OracleParameter paramFieldName = new OracleParameter("fieldname_", OracleDbType.Varchar2); paramFieldName.Direction = ParameterDirection.Input; paramFieldName.Value = strFieldName; cmd.Parameters.Add(paramFieldName); OracleParameter paramRates = new OracleParameter("rates_", OracleDbType.Varchar2); paramRates.Direction = ParameterDirection.Input; paramRates.Value = strRate; cmd.Parameters.Add(paramRates); OracleParameter paramUserName = new OracleParameter("user_name_", OracleDbType.Varchar2); paramUserName.Direction = ParameterDirection.Input; paramUserName.Value = strUserName; cmd.Parameters.Add(paramUserName); try { cmd.ExecuteNonQuery(); } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); } }
public void addPizzaBase(PizzaBase d) { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "insert into pizzabase values (:BASEID, :PBSID, :DOUGH)" }; cmd.Parameters.Add("BASEID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["BASEID"].Value = d.BaseID; cmd.Parameters.Add("PBSID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["PBSID"].Value = d.PbsID; cmd.Parameters.Add("DOUGH", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["DOUGH"].Value = d.Dough; cmd.ExecuteNonQuery(); DataSource.pizzasBase = getPizzaBases(); }
private void SaveToDatabase2(byte[] btReport) { // SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString(); //string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]); //if (reccord_id.Equals("-1")) // return; //SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id); //SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", btReport); Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); try { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", Request.Params["DpNo"]); Oracle.DataAccess.Client.OracleParameter parm; parm = new Oracle.DataAccess.Client.OracleParameter( "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport); cmd.Parameters.Add(parm); try { cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } finally { conn.Close(); conn.Dispose(); } }
public void addOrder(Order d) { var con = new OracleConnection(constr); con.Open(); var cmd = new OracleCommand { Connection = con, CommandText = "insert into Orders values (:ORDERID, :ORDERDELIV, :CUSTID, :STOREID)" }; cmd.Parameters.Add("ORDERID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["ORDERID"].Value = d.OrderID; cmd.Parameters.Add("ORDERDELIV", OracleDbType.Char).Direction = ParameterDirection.Input; cmd.Parameters["ORDERDELIV"].Value = d.OrderDeliv?1:0; cmd.Parameters.Add("CUSTID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["CUSTID"].Value = d.CustID; cmd.Parameters.Add("STOREID", OracleDbType.Decimal).Direction = ParameterDirection.Input; cmd.Parameters["STOREID"].Value = d.StoreID; cmd.ExecuteNonQuery(); }
public int promotion_average_credit() { var con = new OracleConnection(constr); con.Open(); var objCmd = new OracleCommand { Connection = con, CommandText = "Tables.promotion_average_credit", CommandType = CommandType.StoredProcedure }; var prm = new OracleParameter { Direction = ParameterDirection.ReturnValue, DbType = DbType.Int64 }; objCmd.Parameters.Add(prm); objCmd.ExecuteNonQuery(); return(Convert.ToInt32(objCmd.Parameters[0].Value)); }
//public string BulkCancelationProcess(DataTable dt, string tableName) public string BulkCancelationProcess(int count, string[] arr_Company_code, string[] arr_xpin, string[] arr_Filename, int[] arrSize, string sp_name) { //OracleBulkCopy obj = new OracleBulkCopy(ConnectionString); string retval = ""; try { #region //obj.BulkCopyTimeout = 10000; //obj.DestinationTableName = tableName; //obj.Connection.Open(); //obj.WriteToServer(dt); //retval = "0;File successfully Uploaded."; #endregion Oracle.DataAccess.Client.OracleConnection ocnn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); ocnn.Open(); Oracle.DataAccess.Client.OracleCommand ocmd = ocnn.CreateCommand(); ocmd.CommandText = sp_name;// "sp_bulk_cancel_detail"; ocmd.CommandType = CommandType.StoredProcedure; ocmd.BindByName = true; ocmd.ArrayBindCount = count; ocmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, arr_Company_code, ParameterDirection.Input); ocmd.Parameters.Add("p_xpin", OracleDbType.Varchar2, arr_xpin, ParameterDirection.Input); ocmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, arr_Filename, ParameterDirection.Input); ocmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; int result = ocmd.ExecuteNonQuery(); ocnn.Close(); retval = ((Oracle.DataAccess.Types.OracleString[])(ocmd.Parameters["p_retval"].Value))[0].ToString(); } catch (Exception ex) { retval = "1;" + ex.Message; } //obj.Connection.Close(); return(retval); }
protected void Button1_Click(object sender, EventArgs e) { string connStr = "Data Source=OIC;User Id=AGDOI;Password=password;"; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr); if (conn.State == System.Data.ConnectionState.Open) { conn.Close(); } conn.Open(); Oracle.DataAccess.Client.OracleCommand comm = new Oracle.DataAccess.Client.OracleCommand(); comm.CommandType = System.Data.CommandType.StoredProcedure; comm.CommandText = "AG_GET_PETNAME"; // "AG_TESTING_RUNNING"; comm.Connection = conn; //comm.BindByName = true; //comm.CommandType = System.Data.CommandType.Text; //comm.CommandText = "Select * From ag_app_running_no_t;"; //comm.Connection = conn; //comm.Parameters.Add("I_Date", OracleDbType.Date).Value = DateTime.Now.Date; //comm.Parameters.Add("i_license_type", OracleDbType.Varchar2).Value = ""; //comm.Parameters.Add("I_Exam_Status", OracleDbType.Varchar2).Value = ""; //comm.Parameters.Add("V_Year", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.ReturnValue; //comm.Parameters.Add("V_Last_No", OracleDbType.Varchar2).Direction = System.Data.ParameterDirection.ReturnValue; //OracleDataAdapter da = new OracleDataAdapter(comm); //System.Data.DataTable dt = new System.Data.DataTable(); //da.Fill(dt); comm.Parameters.Add("I_Code", OracleDbType.Varchar2, 2, "11", System.Data.ParameterDirection.Input); comm.Parameters.Add("V_Name", OracleDbType.Varchar2, 120).Direction = System.Data.ParameterDirection.Output; comm.ExecuteNonQuery(); //string vName = comm.Parameters["V_Name"].Value.ToString(); //string vYear = comm.Parameters["V_Year"].Value.ToString(); //string vLastNo = comm.Parameters["V_Last_No"].Value.ToString(); //Response.Write(string.Format("vYear = {0}, vLastNo = {1}", vYear, vLastNo)); //Oracle.DataAccess.Client.OracleParameter p1 = new Oracle.DataAccess.Client.OracleParameter("I_Date", OracleDbType.Date); //p1.Value = DateTime.Now.Date; //Oracle.DataAccess.Client.OracleParameter p2 = new Oracle.DataAccess.Client.OracleParameter("I_License_Type", OracleDbType.Varchar2); //p2.Value = string.Empty; //Oracle.DataAccess.Client.OracleParameter p3 = new Oracle.DataAccess.Client.OracleParameter("I_Exam_Status", OracleDbType.Varchar2); //p3.Value = string.Empty; //comm.Parameters.Add(p1); //comm.Parameters.Add(p2); //comm.Parameters.Add(p3); //Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(comm); //System.Data.DataSet ds = new System.Data.DataSet(); //da.Fill(ds); }
public void insertCustomer() { objConn.Open(); objInsertCmd.ExecuteNonQuery(); // var da = new OracleDataAdapter(objCmd); }
/// <summary> /// Executes the command. /// </summary> /// <param name="dbCommand">The current sql command.</param> /// <param name="commandText">The command text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>-1 if command execution failed.</returns> public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. dbCommand = null; Int32 returnValue = -1; OracleClient.OracleConnection orlConnection = null; OracleClient.OracleTransaction orlTransaction = null; try { // Create a new connection. using (orlConnection = new OracleClient.OracleConnection(connectionString)) { // Open the connection. orlConnection.Open(); // Start a new transaction. orlTransaction = orlConnection.BeginTransaction(); // Create the command and assign any parameters. dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.OracleDataType, commandText), orlConnection); dbCommand.CommandType = commandType; dbCommand.Transaction = orlTransaction; if (values != null) { foreach (OracleClient.OracleParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Execute the command. returnValue = dbCommand.ExecuteNonQuery(); // Commit the transaction. orlTransaction.Commit(); // Close the database connection. orlConnection.Close(); } // Return true. return(returnValue); } catch (Exception ex) { try { // Attempt to roll back the transaction. if (orlTransaction != null) { orlTransaction.Rollback(); } } catch { } // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (orlConnection != null) { orlConnection.Close(); } } }
public string[] CustomerFindingUpload(int count, string Userid, DataTable dt, string batch_no, out string val) { string retval = ""; string[] retmsg = new string[count]; string[] retCount = new string[count]; Oracle.DataAccess.Client.OracleConnection conn = null; try { #region string[] p_company_code = new string[count]; string[] p_value_date = new string[count]; string[] p_amount = new string[count]; string[] p_control_no = new string[count]; string[] p_dated = new string[count]; string[] p_narration = new string[count]; string[] p_ref1 = new string[count]; string[] p_ref2 = new string[count]; string[] p_ref3 = new string[count]; string[] p_ref4 = new string[count]; string[] p_ref5 = new string[count]; string[] p_ref6 = new string[count]; string[] p_userid = new string[count]; string[] p_retval = new string[count]; string[] p_batch_no = new string[count]; int[] arrSize = new int[count]; for (int i = 0; i < count; i++) { p_company_code[i] = dt.Rows[i][0].ToString(); p_value_date[i] = dt.Rows[i][1].ToString(); p_amount[i] = dt.Rows[i][2].ToString(); p_control_no[i] = dt.Rows[i][3].ToString(); p_dated[i] = dt.Rows[i][4].ToString(); p_narration[i] = dt.Rows[i][5].ToString(); p_ref1[i] = dt.Rows[i][6].ToString(); p_ref2[i] = dt.Rows[i][7].ToString(); p_ref3[i] = dt.Rows[i][8].ToString(); p_ref4[i] = dt.Rows[i][9].ToString(); p_ref5[i] = dt.Rows[i][10].ToString(); p_ref6[i] = dt.Rows[i][11].ToString(); p_userid[i] = Userid; p_batch_no[i] = batch_no; arrSize[i] = 1000; } #endregion #region conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "sp_cust_funding_upload"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = count; cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input); cmd.Parameters.Add("p_value_date", OracleDbType.Varchar2, p_value_date, ParameterDirection.Input); cmd.Parameters.Add("p_amount", OracleDbType.Varchar2, p_amount, ParameterDirection.Input); cmd.Parameters.Add("p_control_no", OracleDbType.Varchar2, p_control_no, ParameterDirection.Input); cmd.Parameters.Add("p_dated", OracleDbType.Varchar2, p_dated, ParameterDirection.Input); cmd.Parameters.Add("p_narration", OracleDbType.Varchar2, p_narration, ParameterDirection.Input); cmd.Parameters.Add("p_ref1", OracleDbType.Varchar2, p_ref1, ParameterDirection.Input); cmd.Parameters.Add("p_ref2", OracleDbType.Varchar2, p_ref2, ParameterDirection.Input); cmd.Parameters.Add("p_ref3", OracleDbType.Varchar2, p_ref3, ParameterDirection.Input); cmd.Parameters.Add("p_ref4", OracleDbType.Varchar2, p_ref4, ParameterDirection.Input); cmd.Parameters.Add("p_ref5", OracleDbType.Varchar2, p_ref5, ParameterDirection.Input); cmd.Parameters.Add("p_ref6", OracleDbType.Varchar2, p_ref6, ParameterDirection.Input); cmd.Parameters.Add("p_userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input); cmd.Parameters.Add("p_batch_no", OracleDbType.Varchar2, p_batch_no, ParameterDirection.Input); cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, p_retval, ParameterDirection.Output).ArrayBindSize = arrSize; #endregion conn.Open(); int result = cmd.ExecuteNonQuery(); conn.Close(); for (int i = 0; i < count; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString(); } val = "0;File successfully uploaded."; } catch (Exception ex) { if (conn.State == ConnectionState.Open) { conn.Close(); } val = "1;" + ex.Message; } return(retCount); }
public string A2ATransaction(int count, string companyCode, string fileName, GridView gv) { string retval = ""; string[] retCount = new string[count]; try { string[] p_company_code = new string[1]; string[] p_filename = new string[1]; string[] p_row_id = new string[1]; int[] arrSize = new int[1]; int y = 0; for (int i = 0; i < count; i++) { CheckBox cb = ((CheckBox)gv.Rows[i].FindControl("cbPublish")); if (((CheckBox)gv.Rows[i].FindControl("cbPublish")).Checked == true) { /*arrSize[i] = 1000; * p_company_code[i] = companyCode; * p_filename[i] = fileName; * p_row_id[i] = gv.Rows[i].Cells[0].Text;*/ Array.Resize(ref arrSize, y + 1); arrSize[y] = 1000; Array.Resize(ref p_company_code, y + 1); p_company_code[y] = companyCode; Array.Resize(ref p_filename, y + 1); p_filename[y] = fileName; Array.Resize(ref p_row_id, y + 1); p_row_id[y] = gv.Rows[i].Cells[0].Text; y++; } } count = y; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "corebank_system_int_symbol"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = count; cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input); cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_filename, ParameterDirection.Input); cmd.Parameters.Add("p_row_id", OracleDbType.Varchar2, p_row_id, ParameterDirection.Input); cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; int result = cmd.ExecuteNonQuery(); for (int i = 0; i < count; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString(); if (retCount[i].ToString().StartsWith("1") == false) { return(retCount[i]); } } retval = retCount[0]; } catch (Exception ex) { retval = ex.Message; } return(retval); }
//public string CoreBankingPoolCall(string arr_company_code, string arr_file_name, string arr_userid) //{ // try // { // string[] p_company_code = new string[1]; // p_company_code[0] = arr_company_code; // string[] p_file_name = new string[1]; // p_file_name[0] = arr_file_name; // string[] p_userid = new string[1]; // p_userid[0] = arr_userid; // int[] arrSize = new int[1]; // arrSize[0] = 1000; // Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); // conn.Open(); // Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); // cmd.CommandText = "corebank_system_sym_pub"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.BindByName = true; // cmd.ArrayBindCount = 1; // cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input); // cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input); // cmd.Parameters.Add("p_userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input); // cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; // int result = cmd.ExecuteNonQuery(); // string p_return = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[0].ToString(); // } // catch (Exception ex) // { // return ex.Message; // } // return ""; //} public string force_updation(GridView gv, string User_ID, string type, int count, string company_code, string file_name) { string retval = ""; string[] retCount; try { int updateCount = 0; string[] arr = new string[count]; int m = 0; for (int i = 0; i < count; i++) { //if (((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value != "" && ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "") if (((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "Branch not found" && ((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text != "" && ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text != "" && //((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Account Title not found." && ((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "Symbol Title" && ((TextBox)gv.Rows[i].FindControl("txtSymbolTitle")).Text != "") { arr[i] = m.ToString(); updateCount++; } else { arr[i] = "N"; } m++; } string[] p_Rowid = new string[updateCount]; string[] p_branch_code = new string[updateCount]; string[] p_Account_no = new string[updateCount]; string[] p_benename = new string[updateCount]; string[] P_Userid = new string[updateCount]; string[] P_type = new string[updateCount]; string[] P_company_code = new string[updateCount]; string[] p_file_name = new string[updateCount]; int[] arrSize = new int[updateCount]; retCount = new string[updateCount]; int u = 0; for (int i = 0; i < count; i++) { if (arr[i].ToString() != "N") { arrSize[u] = 1000; p_Rowid[u] = gv.Rows[i].Cells[0].Text; //p_branch_code[u] = (((HiddenField)gv.Rows[Convert.ToInt32(arr[i])].FindControl("hf_BranchCode")).Value == "" ? gv.Rows[i].Cells[3].Text : ((HiddenField)gv.Rows[i].FindControl("hf_BranchCode")).Value); p_branch_code[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtBranchCode")).Text == "" ? gv.Rows[i].Cells[3].Text : ((TextBox)gv.Rows[i].FindControl("txtBranchCode")).Text); p_Account_no[u] = (((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtaccountno_symbol")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text); p_benename[u] = ((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text; //(((TextBox)gv.Rows[Convert.ToInt32(arr[i])].FindControl("txtSymbolTitle")).Text == "" ? gv.Rows[i].Cells[1].Text : ((TextBox)gv.Rows[i].FindControl("txtaccountno_symbol")).Text); P_Userid[u] = User_ID; P_type[u] = type; P_company_code[u] = company_code; p_file_name[u] = file_name; u++; } } if (p_Rowid.Length > 0) { Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "sp_force_Updation"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = updateCount; cmd.Parameters.Add("p_rowid", OracleDbType.Varchar2, p_Rowid, ParameterDirection.Input); cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input); cmd.Parameters.Add("p_Account_no", OracleDbType.Varchar2, p_Account_no, ParameterDirection.Input); cmd.Parameters.Add("p_benename", OracleDbType.Varchar2, p_benename, ParameterDirection.Input); cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input); cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input); cmd.Parameters.Add("p_company_code", OracleDbType.Varchar2, P_company_code, ParameterDirection.Input); cmd.Parameters.Add("p_file_name", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input); cmd.Parameters.Add("p_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; int result = cmd.ExecuteNonQuery(); for (int i = 0; i < updateCount; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["p_retval"].Value))[i].ToString(); if (retCount[i].ToString().StartsWith("0") == true) //Error { trans.Rollback(); conn.Close(); return(retCount[i]); } } trans.Commit(); conn.Close(); retval = retCount[0]; } } catch (Exception ex) { retval = "0;" + ex.Message; } return(retval); }
public string postLeadger(string arr_company_Code, string arr_FileName, string arr_trans_type, string arr_Rowid, string arr_bank_code, string arr_branch_code, string arr_Userid, string arr_type) { string p_return = ""; try { int[] arrSize = new int[1]; arrSize[0] = 1000; string[] p_empno = new string[1]; string[] p_company_code = new string[1]; p_company_code[0] = arr_company_Code; string[] p_file_name = new string[1]; p_file_name[0] = arr_FileName; string[] p_trans_type = new string[1]; p_trans_type[0] = arr_trans_type; string[] p_rowid = new string[1]; p_rowid[0] = arr_Rowid; string[] p_bank_code = new string[1]; p_bank_code[0] = arr_bank_code; string[] p_branch_code = new string[1]; p_branch_code[0] = arr_branch_code; string[] p_userid = new string[1]; p_userid[0] = arr_Userid; string[] p_type = new string[1]; p_type[0] = arr_type; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "SP_Data_Segregation"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = 1; cmd.Parameters.Add("P_company_code", OracleDbType.Varchar2, p_company_code, ParameterDirection.Input); cmd.Parameters.Add("P_FILE_NAME", OracleDbType.Varchar2, p_file_name, ParameterDirection.Input); cmd.Parameters.Add("P_Trans_type", OracleDbType.Varchar2, p_trans_type, ParameterDirection.Input); cmd.Parameters.Add("P_Rowid", OracleDbType.Varchar2, p_rowid, ParameterDirection.Input); cmd.Parameters.Add("p_bank_code", OracleDbType.Varchar2, p_bank_code, ParameterDirection.Input); cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input); cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, p_userid, ParameterDirection.Input); cmd.Parameters.Add("P_type", OracleDbType.Varchar2, p_type, ParameterDirection.Input); cmd.Parameters.Add("DATA_RESULTSET", OracleDbType.RefCursor, "", ParameterDirection.Output).ArrayBindSize = arrSize; cmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; int result = cmd.ExecuteNonQuery(); p_return = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["v_retval"].Value))[0].ToString(); #region /*string v_return = ""; * OracleParameter[] parm = new OracleParameter[10]; * int pno = 0; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_company_code", OracleType.VarChar, ParameterDirection.Input, ddlCompanyCode.SelectedValue.ToString()); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_FILE_NAME", OracleType.VarChar, ParameterDirection.Input, ddlFile.SelectedValue.ToString()); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_Trans_type", OracleType.VarChar, ParameterDirection.Input, ""); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_Rowid", OracleType.VarChar, ParameterDirection.Input, ""); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("p_bank_code", OracleType.VarChar, ParameterDirection.Input, ""); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("p_branch_code", OracleType.VarChar, ParameterDirection.Input, ""); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_Userid", OracleType.VarChar, ParameterDirection.Input, Session["U_NAME"].ToString()); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("P_type", OracleType.VarChar, ParameterDirection.Input, "14"); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("DATA_RESULTSET", OracleType.Cursor, ParameterDirection.Output, ""); * pno++; * parm[pno] = new OracleParameter(); * parm[pno] = _dbConfig.Oracle_Param("v_retval", OracleType.VarChar, ParameterDirection.Output, ""); * * v_return = _dbConfig.TransSPOracle_GetReturnStringVal1(conn, tran, "SP_Data_Segregation", parm, pno); * lblMessage.Text = v_return.Split(',').GetValue(1).ToString(); * return v_return;*/ #endregion } catch (Exception ex) { p_return = ex.Message; } return(p_return); }
public string DataPublishTrans(int count, DataTable dt, string Company_code, string FielName, string User_ID, string type) { string retval = ""; string[] retCount = new string[count]; try { string[] arr_company_Code = new string[count]; string[] arr_FielName = new string[count]; string[] P_Trans_type = new string[count]; string[] P_Rowid = new string[count]; string[] p_bank_code = new string[count]; string[] p_branch_code = new string[count]; string[] P_Userid = new string[count]; string[] P_type = new string[count]; int[] arrSize = new int[count]; for (int i = 0; i < count; i++) { arrSize[i] = 1000; arr_company_Code[i] = Company_code; arr_FielName[i] = FielName; P_Trans_type[i] = dt.Rows[i]["TRANS_TYPE"].ToString(); P_Rowid[i] = dt.Rows[i]["ROWID"].ToString(); p_bank_code[i] = ""; p_branch_code[i] = ""; P_Userid[i] = User_ID; P_type[i] = type; } Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); conn.Open(); Oracle.DataAccess.Client.OracleTransaction trans = conn.BeginTransaction(); Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "sp_Data_Publish"; cmd.CommandType = CommandType.StoredProcedure; cmd.BindByName = true; cmd.ArrayBindCount = count; cmd.Parameters.Add("P_company_code", OracleDbType.Varchar2, arr_company_Code, ParameterDirection.Input); cmd.Parameters.Add("P_FILE_NAME", OracleDbType.Varchar2, arr_FielName, ParameterDirection.Input); cmd.Parameters.Add("P_Trans_type", OracleDbType.Varchar2, P_Trans_type, ParameterDirection.Input); cmd.Parameters.Add("P_Rowid", OracleDbType.Varchar2, P_Rowid, ParameterDirection.Input); cmd.Parameters.Add("p_bank_code", OracleDbType.Varchar2, p_bank_code, ParameterDirection.Input); cmd.Parameters.Add("p_branch_code", OracleDbType.Varchar2, p_branch_code, ParameterDirection.Input); cmd.Parameters.Add("P_Userid", OracleDbType.Varchar2, P_Userid, ParameterDirection.Input); cmd.Parameters.Add("P_type", OracleDbType.Varchar2, P_type, ParameterDirection.Input); cmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; #region int result = cmd.ExecuteNonQuery(); for (int i = 0; i < count; i++) { retCount[i] = ((Oracle.DataAccess.Types.OracleString[])(cmd.Parameters["v_retval"].Value))[i].ToString(); if (retCount[i].ToString().StartsWith("04") == false) { trans.Rollback(); conn.Close(); return(retCount[i]); } } string val = postLeadger(arr_company_Code[0], arr_FielName[0], P_Trans_type[0], P_Rowid[0], p_bank_code[0], p_branch_code[0], P_Userid[0], "14"); if (val.StartsWith("04")) { trans.Commit(); conn.Close(); //CoreBankingPoolCall(arr_company_Code[0], arr_FielName[0], P_Userid[0]); retval = retCount[0]; } else { trans.Rollback(); conn.Close(); return(val); } #endregion } catch (Exception ex) { retval = ex.Message; } return(retval); }
public string UploadProcess(int count, DataTable dt, string Company_code, string ConfID, string FielName) { string retval = ""; try { #region //Array Declare string[] arr_company_Code = new string[count]; string[] arr_ConfID = new string[count]; string[] arr_FielName = new string[count]; string[] F1 = new string[count]; string[] F2 = new string[count]; string[] F3 = new string[count]; string[] F4 = new string[count]; string[] F5 = new string[count]; string[] F6 = new string[count]; string[] F7 = new string[count]; string[] F8 = new string[count]; string[] F9 = new string[count]; string[] F10 = new string[count]; string[] F11 = new string[count]; string[] F12 = new string[count]; string[] F13 = new string[count]; string[] F14 = new string[count]; string[] F15 = new string[count]; string[] F16 = new string[count]; string[] F17 = new string[count]; string[] F18 = new string[count]; string[] F19 = new string[count]; string[] F20 = new string[count]; string[] F21 = new string[count]; string[] F22 = new string[count]; string[] F23 = new string[count]; string[] F24 = new string[count]; string[] F25 = new string[count]; string[] F26 = new string[count]; string[] F27 = new string[count]; string[] F28 = new string[count]; string[] F29 = new string[count]; string[] F30 = new string[count]; string[] F31 = new string[count]; string[] F32 = new string[count]; string[] F33 = new string[count]; string[] F34 = new string[count]; string[] F35 = new string[count]; string[] F36 = new string[count]; string[] F37 = new string[count]; string[] F38 = new string[count]; string[] F39 = new string[count]; string[] F40 = new string[count]; string[] F41 = new string[count]; string[] F42 = new string[count]; string[] F43 = new string[count]; string[] F44 = new string[count]; string[] F45 = new string[count]; string[] F46 = new string[count]; string[] F47 = new string[count]; string[] F48 = new string[count]; string[] F49 = new string[count]; string[] F50 = new string[count]; #endregion int col = 0; int[] arrSize = new int[count]; for (int i = 0; i < count; i++) { arrSize[i] = 1000; #region // Array Bind arr_company_Code[i] = Company_code; arr_ConfID[i] = ConfID; arr_FielName[i] = FielName; col = 0; F1[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F2[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F3[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F4[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F5[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F6[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F7[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F8[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F9[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F10[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F11[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F12[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F13[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F14[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F15[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F16[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F17[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F18[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F19[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F20[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F21[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F22[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F23[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F24[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F25[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F26[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F27[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F28[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F29[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F30[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F31[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F32[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F33[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F34[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F35[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F36[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F37[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F38[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F39[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F40[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F41[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F42[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F43[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F44[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F45[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F46[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F47[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F48[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F49[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; col++; if (dt.Columns.Count <= col) { continue; } F50[i] = (dt.Columns.Count >= col) ? dt.Rows[i][col].ToString() : ""; #endregion //arrSize[i] = 1000; } Oracle.DataAccess.Client.OracleConnection ocnn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); ocnn.Open(); Oracle.DataAccess.Client.OracleCommand ocmd = ocnn.CreateCommand(); ocmd.CommandText = "SP_RAW_DATALOAD"; ocmd.CommandType = CommandType.StoredProcedure; ocmd.BindByName = true; ocmd.ArrayBindCount = count; #region //Pass array to parameter ocmd.Parameters.Add("P_COMPANY_CODE", OracleDbType.Varchar2, arr_company_Code, ParameterDirection.Input); ocmd.Parameters.Add("P_CONF_ID", OracleDbType.Varchar2, arr_ConfID, ParameterDirection.Input); ocmd.Parameters.Add("P_FILE_Name", OracleDbType.Varchar2, arr_FielName, ParameterDirection.Input); ocmd.Parameters.Add("P_F1", OracleDbType.Varchar2, F1, ParameterDirection.Input); ocmd.Parameters.Add("P_F2", OracleDbType.Varchar2, F2, ParameterDirection.Input); ocmd.Parameters.Add("P_F3", OracleDbType.Varchar2, F3, ParameterDirection.Input); ocmd.Parameters.Add("P_F4", OracleDbType.Varchar2, F4, ParameterDirection.Input); ocmd.Parameters.Add("P_F5", OracleDbType.Varchar2, F5, ParameterDirection.Input); ocmd.Parameters.Add("P_F6", OracleDbType.Varchar2, F6, ParameterDirection.Input); ocmd.Parameters.Add("P_F7", OracleDbType.Varchar2, F7, ParameterDirection.Input); ocmd.Parameters.Add("P_F8", OracleDbType.Varchar2, F8, ParameterDirection.Input); ocmd.Parameters.Add("P_F9", OracleDbType.Varchar2, F9, ParameterDirection.Input); ocmd.Parameters.Add("P_F10", OracleDbType.Varchar2, F10, ParameterDirection.Input); ocmd.Parameters.Add("P_F11", OracleDbType.Varchar2, F11, ParameterDirection.Input); ocmd.Parameters.Add("P_F12", OracleDbType.Varchar2, F12, ParameterDirection.Input); ocmd.Parameters.Add("P_F13", OracleDbType.Varchar2, F13, ParameterDirection.Input); ocmd.Parameters.Add("P_F14", OracleDbType.Varchar2, F14, ParameterDirection.Input); ocmd.Parameters.Add("P_F15", OracleDbType.Varchar2, F15, ParameterDirection.Input); ocmd.Parameters.Add("P_F16", OracleDbType.Varchar2, F16, ParameterDirection.Input); ocmd.Parameters.Add("P_F17", OracleDbType.Varchar2, F17, ParameterDirection.Input); ocmd.Parameters.Add("P_F18", OracleDbType.Varchar2, F18, ParameterDirection.Input); ocmd.Parameters.Add("P_F19", OracleDbType.Varchar2, F19, ParameterDirection.Input); ocmd.Parameters.Add("P_F20", OracleDbType.Varchar2, F20, ParameterDirection.Input); ocmd.Parameters.Add("P_F21", OracleDbType.Varchar2, F21, ParameterDirection.Input); ocmd.Parameters.Add("P_F22", OracleDbType.Varchar2, F22, ParameterDirection.Input); ocmd.Parameters.Add("P_F23", OracleDbType.Varchar2, F23, ParameterDirection.Input); ocmd.Parameters.Add("P_F24", OracleDbType.Varchar2, F24, ParameterDirection.Input); ocmd.Parameters.Add("P_F25", OracleDbType.Varchar2, F25, ParameterDirection.Input); ocmd.Parameters.Add("P_F26", OracleDbType.Varchar2, F26, ParameterDirection.Input); ocmd.Parameters.Add("P_F27", OracleDbType.Varchar2, F27, ParameterDirection.Input); ocmd.Parameters.Add("P_F28", OracleDbType.Varchar2, F28, ParameterDirection.Input); ocmd.Parameters.Add("P_F29", OracleDbType.Varchar2, F29, ParameterDirection.Input); ocmd.Parameters.Add("P_F30", OracleDbType.Varchar2, F30, ParameterDirection.Input); ocmd.Parameters.Add("P_F31", OracleDbType.Varchar2, F31, ParameterDirection.Input); ocmd.Parameters.Add("P_F32", OracleDbType.Varchar2, F32, ParameterDirection.Input); ocmd.Parameters.Add("P_F33", OracleDbType.Varchar2, F33, ParameterDirection.Input); ocmd.Parameters.Add("P_F34", OracleDbType.Varchar2, F34, ParameterDirection.Input); ocmd.Parameters.Add("P_F35", OracleDbType.Varchar2, F35, ParameterDirection.Input); ocmd.Parameters.Add("P_F36", OracleDbType.Varchar2, F36, ParameterDirection.Input); ocmd.Parameters.Add("P_F37", OracleDbType.Varchar2, F37, ParameterDirection.Input); ocmd.Parameters.Add("P_F38", OracleDbType.Varchar2, F38, ParameterDirection.Input); ocmd.Parameters.Add("P_F39", OracleDbType.Varchar2, F39, ParameterDirection.Input); ocmd.Parameters.Add("P_F40", OracleDbType.Varchar2, F40, ParameterDirection.Input); ocmd.Parameters.Add("P_F41", OracleDbType.Varchar2, F41, ParameterDirection.Input); ocmd.Parameters.Add("P_F42", OracleDbType.Varchar2, F42, ParameterDirection.Input); ocmd.Parameters.Add("P_F43", OracleDbType.Varchar2, F43, ParameterDirection.Input); ocmd.Parameters.Add("P_F44", OracleDbType.Varchar2, F44, ParameterDirection.Input); ocmd.Parameters.Add("P_F45", OracleDbType.Varchar2, F45, ParameterDirection.Input); ocmd.Parameters.Add("P_F46", OracleDbType.Varchar2, F46, ParameterDirection.Input); ocmd.Parameters.Add("P_F47", OracleDbType.Varchar2, F47, ParameterDirection.Input); ocmd.Parameters.Add("P_F48", OracleDbType.Varchar2, F48, ParameterDirection.Input); ocmd.Parameters.Add("P_F49", OracleDbType.Varchar2, F49, ParameterDirection.Input); ocmd.Parameters.Add("P_F50", OracleDbType.Varchar2, F50, ParameterDirection.Input); ocmd.Parameters.Add("v_retval", OracleDbType.Varchar2, "", ParameterDirection.Output).ArrayBindSize = arrSize; #endregion int result = ocmd.ExecuteNonQuery(); ocnn.Close(); retval = ((Oracle.DataAccess.Types.OracleString[])(ocmd.Parameters["v_retval"].Value))[0].ToString(); } catch (Exception ex) { retval = ex.Message; } return(retval); }
static void Main(string[] args) { DateTime d1 = DateTime.Now; int hora, minuto; List <Product> products = new List <Product>(); try { Console.WriteLine("Digite a hora: "); hora = int.Parse(Console.ReadLine()); Console.WriteLine("Digite os minutos: "); minuto = int.Parse(Console.ReadLine()); Console.WriteLine("Dê enter para iniciar o programa!"); Console.ReadLine(); do { IntPtr hWnd = GetConsoleWindow(); ShowWindow(hWnd, 0); do { d1 = DateTime.Now; } while (!(d1.Hour == hora && d1.Minute == minuto)); string data = "'" + d1.ToString("dd/MMM/yyyy", CultureInfo.InvariantCulture) + "'"; ShowWindow(hWnd, 1); OracleConnection conn = DBUtils.GetDBConnection(); OracleCommand cmd = new OracleCommand(); Console.WriteLine("Get Connection: " + conn); conn.Open(); Console.WriteLine("Conexão bem sucedida"); string consulta = "SELECT ESTOQUE.CODPROD ,ESTOQUE.QTESTGER ,VENDAS.GIRO ,ESTOQUE.QTESTGER - VENDAS.GIRO SALDO_FINAL FROM (SELECT CODPROD, QTESTGER " + "FROM PCEST " + "WHERE CODFILIAL = 1 AND CODPROD IN(SELECT A.CODPROD FROM PCMOV A, PCNFSAID B WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " " + "AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' " + "AND B.DTCANCEL IS NULL)) ESTOQUE, " + "(SELECT A.CODPROD,SUM(A.QT) GIRO " + "FROM PCMOV A, PCNFSAID B " + "WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " " + "AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' " + "AND B.DTCANCEL IS NULL " + "GROUP BY CODPROD) VENDAS " + "WHERE ESTOQUE.CODPROD = VENDAS.CODPROD " + "ORDER BY VENDAS.GIRO DESC "; using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.CommandText = consulta; comm.Connection = conn; using (var reader = comm.ExecuteReader()) { while (reader.Read()) { var codprodVar = reader["CODPROD"]; int codprod = (int)codprodVar; var qtestgerVar = reader["QTESTGER"]; decimal qtestger = (decimal)qtestgerVar; var giroVar = reader["GIRO"]; decimal giro = (decimal)giroVar; var saldo_finalVar = reader["SALDO_FINAL"]; decimal saldoFinal = (decimal)saldo_finalVar; products.Add(new Product(codprod, qtestger, giro, saldoFinal)); } } } string update = "UPDATE PCEST SET QTESTGER = QTESTGER - (SELECT SUM(A.QT) " + "FROM PCMOV A, PCNFSAID B" + " WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " AND A.CODFILIAL = 50 " + "AND A.CODOPER = 'S' AND B.DTCANCEL IS NULL " + "AND A.CODPROD = PCEST.CODPROD) " + "WHERE CODFILIAL = 1 AND CODPROD IN" + "(SELECT A.CODPROD FROM PCMOV A, PCNFSAID B WHERE A.NUMTRANSVENDA = B.NUMTRANSVENDA " + "AND A.DTMOV = " + data + " AND A.CODFILIAL = 50 AND A.CODOPER = 'S' AND B.DTCANCEL IS NULL)"; using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.Connection = conn; comm.CommandText = update; comm.ExecuteNonQuery(); } using (var comm = new Oracle.DataAccess.Client.OracleCommand()) { comm.CommandText = consulta; comm.Connection = conn; using (var reader = comm.ExecuteReader()) { int i = 0; while (reader.Read()) { var codprod = reader["CODPROD"]; var qtestger = reader["QTESTGER"]; decimal qtestgerdec = (decimal)qtestger; var giro = reader["GIRO"]; var saldo_final = reader["SALDO_FINAL"]; if (qtestgerdec != products[i].Qtestger) { using (StreamWriter sw = File.AppendText("entrada2.txt")) { sw.WriteLine(" --------------------------- Execução com erro: " + DateTime.Now + " -----------------------------"); sw.WriteLine("Erro no codigo: " + codprod); } foreach (Product prod in products) { using (var comm2 = new Oracle.DataAccess.Client.OracleCommand()) { comm2.Connection = conn; comm2.CommandText = "UPDATE PCEST SET QTESTGER = :Qtestger where codprod = :Codprod and codfilial = 1"; comm2.Parameters.Add("Qtestger", prod.Qtestger); comm2.Parameters.Add("Codprod", prod.Codprod); comm2.ExecuteNonQuery(); } } break; } using (StreamWriter sw = File.AppendText("entrada2.txt")) { sw.WriteLine(" --------------------------- Execução completa: " + DateTime.Now + " -----------------------------"); foreach (Product prod in products) { sw.WriteLine(prod); } } i++; } } } products.Clear(); do { d1 = DateTime.Now; } while (d1.Hour == hora && d1.Minute == minuto); } while (true); } catch (Exception ex) { Console.WriteLine("## ERROR: " + ex.Message); Console.Read(); return; } }
private void SaveToDatabase(byte[] btReport) { // SQLStatic.SQL.ExecScaler("select max(record_id) from ee_dependent_verify_docs v where v.dep_pending_id = " + Request.Params["DpNo"]).ToString(); string reccord_id = Data.newFaxDocRecordID(Request.Params["DpNo"]); if (reccord_id.Equals("-1")) { return; } SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "reprt", reccord_id); SQLStatic.Sessions.SetBLOBSessionValue(Request.Cookies["Session_ID"].Value.ToString(), ViewState["EE"].ToString() + " " + Request.Params["DpNo"], btReport); Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); try { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependents_Audit.SaveFaxImage", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_record_id", reccord_id); Oracle.DataAccess.Client.OracleParameter parm; parm = new Oracle.DataAccess.Client.OracleParameter( "value_", Oracle.DataAccess.Client.OracleDbType.Blob, btReport.Length, System.Data.ParameterDirection.Input, true, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, btReport); cmd.Parameters.Add(parm); try { cmd.ExecuteNonQuery(); } finally { cmd.Dispose(); } } finally { conn.Close(); conn.Dispose(); } // Here you save bytes to the database //using (OracleConnection con = new OracleConnection(ConnStr)) //{ // using (OracleCommand cmd = new OracleCommand("PKG_Name.Procedure_Save_PDF", con)) // { // con.Open(); // try // { // foreach (ReportParameters p in parameters) // { // try // { // // GenerateReportByteArray will return byte array for the database // cmd.Parameters.Add("file_blob_", OracleDbType.Blob, ParameterDirection.Input); // cmd.Parameters["file_blob_"].Value = btReport; // cmd.ExecuteNonQuery(); // } // catch (Exception ex) // { // Message = ex.Message; // } // } // Message = "Insert done"; // } // catch (Exception ex) // { // Message = ex.Message; // } // finally // { // if (con.State != ConnectionState.Closed) con.Close(); // } //} //} }
public static void SaveCBArray(int intCount, string[] account_number, string[] processing_year, string[] in_open_enrollment, string[] category_code, string[] categoty_plan, string[] class_code, string[] batch_id) { Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(" pkg_automated_rate_update.Save_In_OE", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.ArrayBindCount = intCount; OracleParameter parmaAccountNumber = new OracleParameter("account_number_", OracleDbType.Varchar2); parmaAccountNumber.Direction = ParameterDirection.Input; parmaAccountNumber.Value = account_number; cmd.Parameters.Add(parmaAccountNumber); OracleParameter paramprocessing_year = new OracleParameter("processing_year_", OracleDbType.Varchar2); paramprocessing_year.Direction = ParameterDirection.Input; paramprocessing_year.Value = processing_year; cmd.Parameters.Add(paramprocessing_year); OracleParameter paramRates = new OracleParameter("in_open_enrollment_", OracleDbType.Varchar2); paramRates.Direction = ParameterDirection.Input; paramRates.Value = in_open_enrollment; cmd.Parameters.Add(paramRates); OracleParameter paramcategory_code = new OracleParameter("category_code_", OracleDbType.Varchar2); paramcategory_code.Direction = ParameterDirection.Input; paramcategory_code.Value = category_code; cmd.Parameters.Add(paramcategory_code); OracleParameter parmcategoty_plan = new OracleParameter("categoty_plan_", OracleDbType.Varchar2); parmcategoty_plan.Direction = ParameterDirection.Input; parmcategoty_plan.Value = categoty_plan; cmd.Parameters.Add(parmcategoty_plan); OracleParameter paramclass_code = new OracleParameter("class_code_", OracleDbType.Varchar2); paramclass_code.Direction = ParameterDirection.Input; paramclass_code.Value = class_code; cmd.Parameters.Add(paramclass_code); OracleParameter parambatch_id = new OracleParameter("batch_id_", OracleDbType.Varchar2); parambatch_id.Direction = ParameterDirection.Input; parambatch_id.Value = batch_id; cmd.Parameters.Add(parambatch_id); try { cmd.ExecuteNonQuery(); } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); } }