public List <Employee> getEmployees()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT EMPLOYEE.* FROM EMPLOYEE"
};
var employees = new List <Employee>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
employees.Add(new Employee
{
EmployeeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("EMPLOYEEID"))),
EmployeeName = reader.GetString(reader.GetOrdinal("EMPLOYEENAME")),
Bod = reader.GetDateTime(reader.GetOrdinal("BOD")),
ShomerShabat = reader.GetString(reader.GetOrdinal("SHOMERSHABAT")) == "1",
StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID"))),
RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID")))
});
}
return(employees);
}
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);
}
public List <Pizza> getPizzas()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT Pizza.* FROM Pizza"
};
var pizzas = new List <Pizza>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
pizzas.Add(new Pizza
{
BaseID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("BASEID"))),
OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))),
ToppingIdL = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDL"))),
ToppingIdR = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPPINGIDR"))),
PizzaID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PIZZAID")))
});
}
return(pizzas);
}
public List <Customer> getCustomers()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT CUSTOMERS.* FROM CUSTOMERS"
};
var customers = new List <Customer>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
customers.Add(new Customer
{
CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))),
CustName = reader.GetString(reader.GetOrdinal("CUSTNAME")),
CustAddress = reader.GetString(reader.GetOrdinal("CUSTADDRESS")),
CustCc = reader.GetString(reader.GetOrdinal("CUSTCC")),
CustCredit = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTCREDIT")))
});
}
return(customers);
}
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();
}
public List <Order> getOrders()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT ORDERS.* FROM ORDERS"
};
var orders = new List <Order>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
orders.Add(new Order
{
OrderID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("ORDERID"))),
OrderDeliv = reader.GetString(reader.GetOrdinal("ORDERDELIV")) == "1",
CustID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("CUSTID"))),
StoreID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("STOREID")))
});
}
return(orders);
}
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);
}
private void CreateOracleParameter(IDbCommand command, OracleDataAccessClient.OracleCommand commandNew)
{
foreach (OracleParameter para in command.Parameters)
{
OracleDataAccessClient.OracleDbType oracleType = ConvertOracleType(para.OracleType);
OracleDataAccessClient.OracleParameter paraNew = new OracleDataAccessClient.OracleParameter(para.ParameterName, oracleType);
paraNew.Value = para.Value;
commandNew.Parameters.Add(paraNew);
}
}
public DataSet GetRecord(string cmdText, CommandType cmdType, List <Oracle.DataAccess.Client.OracleParameter> parameters = null)
{
Oracle.DataAccess.Client.OracleCommand com = new Oracle.DataAccess.Client.OracleCommand("CUSTOMERORDER.CgetorderProcedure", con);
com.CommandType = CommandType.StoredProcedure;
var da = new Oracle.DataAccess.Client.OracleDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
public DataSet gcustomerrecord(int customerCode)
{
var sql = "Select Initcap(Pur_Nom)Pur_Nom,P13.Crd_Cod,P13.Itm_Cod,P13.srl_num,Itm_Des,Uom_Abr,Qtd_Rte,Qtn_Num,Qty_Prc,Pmt_Flg From Nml.Pgi_00_13 P13, Nml.Crp_00_27 C27 Where P13.Vld_Dte Is Not Null And C27.Pur_Cod = P13.Pur_Cod And P13.Crd_Cod ='" + customerCode + "' Order By P13.Crd_Cod,P13.Itm_Cod";
Oracle.DataAccess.Client.OracleCommand com = new Oracle.DataAccess.Client.OracleCommand(sql, con);
com.CommandType = CommandType.Text;
var da = new Oracle.DataAccess.Client.OracleDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
public DataSet GetPopUpRecord(int user)
{
//var sql = "select NVL(PUR_COD,-1) PUR_COD,NVL(SRL_NUM,-1)SRL_NUM,NVL(SRL_NUM,-1)SPR_NUM,NVL(CRD_COD,-1)CRD_COD,NVL(BAS_COD,-1)BAS_COD,NVL(ITM_COD,-1)ITM_COD,NVL(QTD_RTE,-1)QTD_RTE,NVL(QTN_NUM,-1)QTN_NUM,NVL(QTY_PRC,-1) QTY_PRC,NVL(ITM_DES,' ')ITM_DES,NVL(PMT_FLG,-1)PMT_FLG from pgi_00_13" + " where PUR_COD= '" + user + "'";
var sql = "select NVL(UOM_ABR,-1)UOM_ABR,ITM_COD,ITM_DES,CRD_COD,NVL(QTD_RTE,-1)QTD_RTE,NVL(QTN_NUM,-1)QTN_NUM,NVL(QTY_PRC,-1) QTY_PRC,NVL(PMT_FLG,-1)PMT_FLG from pgi_00_13" + " where ITM_COD= '" + user + "'";
Oracle.DataAccess.Client.OracleCommand com = new Oracle.DataAccess.Client.OracleCommand(sql, con);
com.CommandType = CommandType.Text;
var da = new Oracle.DataAccess.Client.OracleDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
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 static int ExecuteNonQuery(string query)
{
Oracle.DataAccess.Client.OracleCommand cmd = null;
try
{
cmd = new OracleCommand(query, DbConnection);
cmd.CommandType = System.Data.CommandType.Text;
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
return(-1);
}
}
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 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 Oracle.DataAccess.Client.OracleDataReader ExecuteDataReader(string query)
{
Oracle.DataAccess.Client.OracleCommand cmd = null;
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
cmd = new OracleCommand(query, DbConnection);
cmd.CommandType = System.Data.CommandType.Text;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
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 static int ExecuteNonQuery(string sprocName, OracleParameter[] sprocParams)
{
Oracle.DataAccess.Client.OracleCommand cmd = null;
try
{
cmd = new OracleCommand(sprocName, DbConnection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sprocParams != null && sprocParams.Length > 0)
{
cmd.Parameters.AddRange(sprocParams);
}
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
return(-1);
}
}
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();
}
private void updateDataGrid()
{
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT KUPACID,NAZIV,PIB FROM KUPACPRAVNOLICE";
cmd.CommandType = CommandType.Text;
Oracle.DataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
// DataTable dt = new DataTable();
// dt.Load(dr);
// myDataGrid.ItemsSource = dt.DefaultView;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("KUPACID");
dt.Columns.Add("NAZIV");
dt.Columns.Add("PIB");
ds.Tables.Add(dt);
OracleParameter valueToReturn = null;
while (dr.Read())
{
DataRow red = dt.NewRow();
red["KUPACID"] = dr.GetValue(0);
red["NAZIV"] = dr.GetString(1);
Pib mBroj = (Pib)dr.GetValue(2);
// Pib mm = new Pib();
// mm= (Pib)dr.GetValue(2);
// red["PIB"] = Convert.ToInt64(mm.Pibb);
red["PIB"] = Convert.ToInt64(mBroj.Pibb);
}
dr.Close();
}
public List <Dough> getDoughs()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT DOUGH.* FROM DOUGH"
};
var doughs = new List <Dough>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
doughs.Add(new Dough
{
DoughID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("DOUGHID"))),
Name = reader.GetString(reader.GetOrdinal("NAME"))
});
}
return(doughs);
}
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 DataSet DataSet(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 = 300;
sCmd.CommandType = cmdType;
if (null != parameters)
{
sCmd.Parameters.AddRange(parameters.ToArray());
}
using (var sDAdapter = new Oracle.DataAccess.Client.OracleDataAdapter(sCmd))
{
using (var ds = new DataSet())
{
sDAdapter.Fill(ds);
return(ds);
}
}
}
}
}
public List <PizzaBaseSize> getPizzaBaseSizes()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT PIZZABASESIZE.* FROM PIZZABASESIZE"
};
var pizzaBaseSizes = new List <PizzaBaseSize>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
pizzaBaseSizes.Add(new PizzaBaseSize()
{
PbsID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("PBSID"))),
PdsSize = reader.GetString(reader.GetOrdinal("PDSSIZE"))
});
}
return(pizzaBaseSizes);
}
//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);
}
public List <Rank> getRanks()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT RANKS.* FROM RANKS"
};
var ranks = new List <Rank>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
ranks.Add(new Rank()
{
RankID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKID"))),
RankDescription = reader.GetString(reader.GetOrdinal("RANKDESCRIPTION")),
RankSalary = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("RANKSALARY")))
});
}
return(ranks);
}
public List <ToppingType> getToppingTypes()
{
var con = new OracleConnection(constr);
con.Open();
var cmd = new OracleCommand
{
Connection = con,
CommandText = "SELECT TOPPINGTYPE.* FROM TOPPINGTYPE"
};
var toppingTypes = new List <ToppingType>();
var reader = cmd.ExecuteReader();
while (reader.Read())
{
toppingTypes.Add(new ToppingType()
{
TopTypeID = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("TOPTYPEID"))),
TopTypeName = reader.GetString(reader.GetOrdinal("TOPTYPENAME")),
TopTypeType = reader.GetString(reader.GetOrdinal("TOPTYPETYPE"))
});
}
return(toppingTypes);
}