// <Snippet1>
public void CreateOracleParameter()
{
OracleParameter parameter = new OracleParameter(
"DName", OracleType.VarChar,
11, ParameterDirection.Output, true, 0, 0, "DName",
DataRowVersion.Current, "ENGINEERING");
Console.WriteLine(parameter.ToString());
}
public bool InsertFaxbox(decimal deFaxId)
{
string strProcedureName = "PKG_PRC_PSC.USP_INSERT_FAXBOX";
lock (m_connection)
{
if (!ReOpen())
{
return(false);
}
try
{
using (OracleCommand command = new OracleCommand())
{
command.Connection = m_connection;
command.CommandText = strProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("P_FAX_ID", deFaxId);
OracleParameter param = new OracleParameter("P_RESULT", OracleType.VarChar, 100);
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);
int cnt = command.ExecuteNonQuery();
if (cnt < 1)
{
return(false);
}
if (param.ToString().Substring(0, 1) == "F")
{
return(false);
}
}
}
catch (Exception ex)
{
ExceptionMsg(ex, "PROCEDURE : " + strProcedureName);
return(false);
}
Close();
return(true);
}
}
public string verificaExpediente(string expediente)
{
string ermsj;
int idError;
//string fecha;
//Abre la conexión
OperacionesOracle objCon = new OperacionesOracle();
OracleConnection cn = objCon.connect();
OracleParameter op = null;
idError = 1;
//cn.Open();
//Stored Procedure
OracleCommand cmd = cn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
idError = 2;
cmd.CommandText = "BFP_IMAGENES_EXPELEC_PKG.valida_expediente";
cmd.Parameters.Add("p_id_expediente", OracleDbType.Varchar2).Value = expediente;
idError = 3;
//RETORNA LA FECHA
//cmd.Parameters.Add("p_fecha", OracleDbType.Varchar2).Direction = ParameterDirection.Output;
op = new OracleParameter("p_existe", OracleDbType.Varchar2);
op.Direction = ParameterDirection.Output;
op.Size = 4000;
cmd.Parameters.Add(op);
idError = 4;
out_existe = op.ToString();
idError = 5;
try
{
cmd.ExecuteNonQuery();
cn.Dispose();
cn.Close();
idError = 6;
//return "SP correcto";
return(op.Value.ToString());
}
catch (Exception ex)
{
switch (idError)
{
case 1:
ermsj = "Error al crear SP ---> " + ex.ToString();
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
case 2:
ermsj = "Error al asignar variable al parametro---> " + ex.ToString();
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
case 3:
ermsj = "Error al obtener la fecha de salida---> " + ex.ToString();
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
case 4:
ermsj = "Error al obtener variable existe---> " + ex.ToString();
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
case 5:
ermsj = "Error al cerrar la conexion---> " + ex.ToString();
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
case 6:
ermsj = "Exito";
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
default:
ermsj = "Error al iniciar";
out_error = ermsj;
cn.Dispose();
cn.Close();
return("0");
}
}
//return "Si hay expediente";
}
private void button3_Click(object sender, EventArgs e)
{
DB_Connect();
comm = new OracleCommand();
com11 = new OracleCommand();
comm1 = new OracleCommand();
comm11 = new OracleCommand();
comm2 = new OracleCommand();
comm22 = new OracleCommand();
comm3 = new OracleCommand();
comm111 = new OracleCommand();
String x = label8.Text;
String y = textBox2.Text;
//Executing proc dbs2 to check if client exists
c8 = new OracleCommand();
c8.CommandText = "dbs2";
c8.CommandType = CommandType.StoredProcedure;
c8.Connection = conn;
OracleParameter pa88 = new OracleParameter();
pa88.ParameterName = "counter";
pa88.DbType = DbType.Int32;
pa88 = comm.Parameters.Add("counter", OracleDbType.Int32, ParameterDirection.Output);
//pa88.Value = 1;
int cx;
int.TryParse(pa88.ToString(), out cx);
OracleParameter pa8 = new OracleParameter();
pa8.ParameterName = "cid";
pa8.Value = y;
pa8.DbType = DbType.String;
c8.Parameters.Add(pa8);
Console.WriteLine(cx);
//MessageBox.Show(cx.ToString());
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("No Products selected to Confirm Order !");
}
else if (textBox2.Text == "")
{
MessageBox.Show("Enter valid client ID");
}
//else if (cx == 0)
//{
// MessageBox.Show("Client doesn't exist");
// MessageBox.Show(str);
//}
else
{
//insertion into order_table(order_ID , ord_amt , order_DATE) Table
DateTime tod = DateTime.UtcNow.Date;
string strDate = tod.ToString("yyyy-MM-dd");
String strDate1 = "2019-04-16";
comm111.CommandText = "select product_ID , qty from temp_orders";
comm111.CommandType = CommandType.Text;
comm111.Connection = conn;
ds111 = new DataSet();
da111 = new OracleDataAdapter(comm111.CommandText, conn);
da111.Fill(ds111, "tmp1236");
dt111 = ds111.Tables["tmp1236"];
int yy = dt111.Rows.Count - 1;
int ordq = 0;
while (yy >= 0)
{
dr111 = dt111.Rows[yy];
String d = dr111["product_ID"].ToString();
int quan;
int.TryParse(dr111["qty"].ToString(), out quan);
com11.CommandText = "insert into tempord values ('" + x + "','" + d + "'," + quan + ")";
com11.CommandType = CommandType.Text;
com11.Connection = conn;
com11.ExecuteNonQuery();
ordq += quan;
yy--;
}
comm3.Connection = conn;
comm3.CommandText = "insert into order_table values ('" + x + "'," + ordq + ", date '" + strDate1 + "')";
comm3.CommandType = CommandType.Text;
comm3.ExecuteNonQuery();
//MessageBox.Show("inserted into order_table");
OracleCommand cmd11 = new OracleCommand();
cmd11.Connection = conn;
cmd11.CommandText = "delete from tempord";
cmd11.ExecuteNonQuery();
//MessageBox.Show("inserted into tempord");
//insertion into place_order(order_ID , cust_ID) Table
comm.CommandText = "insert into place_order values ('" + x + "','" + y + "')";
comm.CommandType = CommandType.Text;
comm.Connection = conn;
comm.ExecuteNonQuery();
//MessageBox.Show("inserted into Place_order");
//insertion into order_items_table Table
comm2.CommandText = "select product_ID from temp_orders";
comm2.CommandType = CommandType.Text;
comm2.Connection = conn;
ds2 = new DataSet();
da2 = new OracleDataAdapter(comm2.CommandText, conn);
da2.Fill(ds2, "tmp1234");
dt2 = ds2.Tables["tmp1234"];
int q1 = dt2.Rows.Count - 1;
while (q1 >= 0)
{
dr2 = dt2.Rows[q1];
String d = dr2["product_ID"].ToString();
comm22.Connection = conn;
comm22.CommandText = "insert into order_items_table values ('" + x + "','" + d + "')";
comm22.CommandType = CommandType.Text;
comm22.ExecuteNonQuery();
q1--;
}
//MessageBox.Show("inserted into order_items_table");
//insertion into order_details_table(order_id , prod_ID , qty) Table
comm1.CommandText = "select product_ID , qty from temp_orders";
comm1.CommandType = CommandType.Text;
comm1.Connection = conn;
ds1 = new DataSet();
da1 = new OracleDataAdapter(comm1.CommandText, conn);
da1.Fill(ds1, "tmp123");
dt1 = ds1.Tables["tmp123"];
q1 = dt1.Rows.Count - 1;
while (q1 >= 0)
{
dr1 = dt1.Rows[q1];
String d = dr1["product_ID"].ToString();
int quan;
int.TryParse(dr1["qty"].ToString(), out quan);
comm11.CommandText = "insert into order_details_table values ('" + x + "','" + d + "'," + quan + ")";
comm11.CommandType = CommandType.Text;
comm11.Connection = conn;
comm11.ExecuteNonQuery();
q1--;
}
//MessageBox.Show("inserted into order_details_table");
//c9 = new OracleCommand();
c9 = new OracleCommand();
c9.CommandText = "dbs1";
c9.CommandType = CommandType.StoredProcedure;
c9.Connection = conn;
OracleParameter pa1 = new OracleParameter();
pa1.Value = label8.Text;
pa1.ParameterName = "oid";
pa1.DbType = DbType.String;
c9.Parameters.Add(pa1);
c9.ExecuteNonQuery();
MessageBox.Show("Procedure executed!");
new Form5(newid, y, eid1).Show();
this.Hide();
}
conn.Close();
}
public static void AddTagToNote(string tagName, int noteId)
{
TagVO newTag = new TagVO();
using (OracleConnection conn = DbHelper.NewConnection())
{
conn.Open();
String checkSql = "select * from tag where tag_name like :CheckName";
OracleCommand checkCmd = new OracleCommand
{
Connection = conn,
CommandText = checkSql
};
OracleParameter CheckName = checkCmd.Parameters.Add("CheckName", OracleDbType.Varchar2);
CheckName.Value = tagName;
OracleDataReader reader = checkCmd.ExecuteReader();
if (reader.HasRows) //중복 태그가 있는 경우
{
newTag.Tag_Id = int.Parse(reader["TAG_ID"].ToString());
reader.Close();
}
else // 중복 태그가 없는 경우
{
String sql = $"INSERT INTO TAG (TAG_ID, TAG_NAME) VALUES (:TAGID, :NAME)";
OracleCommand cmd = new OracleCommand
{
Connection = conn,
CommandText = sql
};
OracleParameter TagId = cmd.Parameters.Add("TAGID", OracleDbType.Int32);
TagId.Value = GetNewTagId();
OracleParameter Name = cmd.Parameters.Add("Name", OracleDbType.Varchar2);
Name.Value = tagName;
cmd.ExecuteNonQuery();
newTag.Tag_Id = int.Parse(TagId.ToString());
}
//note_tag_map 테이블에 관계 추가
String mapSql = $"INSERT INTO NOTE_TAG_MAP (NOTE_ID, TAG_ID ) VALUES ({noteId},{newTag.Tag_Id})";
OracleCommand mapCmd = new OracleCommand
{
Connection = conn,
CommandText = mapSql
};
mapCmd.ExecuteNonQuery();
conn.Close();
}
}
