Delete in oracle 10g from asp.net web site - wait on commint?
I need delete data in oracle 10g database from ASP.NET 2.0 web site.
Method DeleteMonthPlan I use on execute delete command. Problem is that this command is executing long time "in browser" and finally delete command is not executed. Maybe it waits on commit? What is root of problem?
This SQL command DELETE C_PPC_PLAN WHERE MFG_MONTH='VALUE' is OK.
MFG_MONTH column type is VARCHAR2(16)
First I need call method DeleteMonthPlan and than I need call InsertDatePlan.
private static void DeleteMonthPlan(string monthIndex)
{
try
{
using (var conn = new OracleConnection(GenerateConnectionString()))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = string.Format("DELETE C_PPC_PLAN WHERE MFG_MONTH='{0}'", monthIndex);
cmd.ExecuteNonQuery();
}
}
catch (Exception exception)
{
throw exception;
}
}
For example this method I use on insert and it is OK.
public void InsertDatePlan(DatePlan dp,
string monthIndex)
{
开发者_如何学Go DeleteMonthPlan(monthIndex);
try
{
using (var conn = new OracleConnection(GenerateConnectionString()))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.Parameters.Add(":Site", OracleType.VarChar).Value = dp.Site;
cmd.Parameters.Add(":Week", OracleType.VarChar).Value = dp.MfgWeek;
cmd.Parameters.Add(":Month", OracleType.VarChar).Value = dp.MfgMonth;
cmd.Parameters.Add(":Year", OracleType.VarChar).Value = dp.MfgYear;
cmd.Parameters.Add(":Input", OracleType.Number).Value = dp.Input;
cmd.Parameters.Add(":Output", OracleType.Number).Value = dp.Output;
cmd.Parameters.Add(":LMUser", OracleType.VarChar).Value = dp.LmUser;
cmd.Parameters.Add(":PartNo", OracleType.VarChar).Value = dp.PartNo;
cmd.Parameters.Add(":PartNoDesc", OracleType.VarChar).Value = dp.PartNoDesc;
cmd.CommandText = string.Format("INSERT INTO C_PPC_PLAN (CREATE_TIME, SITE, MFG_DAY,MFG_WEEK,MFG_MONTH,MFG_YEAR,INPUT,OUTPUT,LM_TIME,LM_USER,PART_NO,PART_NO_DESC)"
+ " VALUES (to_date('{0}', 'dd-mm-yyyy hh24:mi:ss'), :Site ,to_date('{1}', 'dd-mm-yyyy hh24:mi:ss'),:Week,"
+ ":Month,:Year,:Input,:Output,to_date('{2}', 'dd-mm-yyyy hh24:mi:ss'),:LMUser,:PartNo,:PartNoDesc)"
, dp.CreateTime, dp.MfgDate, dp.LmTime);
cmd.ExecuteNonQuery();
}
}
catch (Exception exception)
{
throw exception;
}
}
I tried use transaction. I call this method on the bottom but is never finish it means that part
trans.Rollback(); or conn.Close();
is never executed.
private static void DeleteMonthPlan(string monthIndex)
{
var conn = new OracleConnection(GenerateConnectionString());
conn.Open();
OracleCommand cmd= conn.CreateCommand();
OracleTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
cmd.Transaction = trans;
try
{
cmd.CommandText = "DELETE C_PPC_PLAN WHERE MFG_MONTH='6'";
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception e)
{
trans.Rollback();
}
finally
{
conn.Close();
}
}
try
DELETE FROM C_PPC_PLAN WHERE MFG_MONTH='6'
BTW your code uses "literals" in some places instead of bind variables (params) which makes it vulnerable to SQL injection which is a really serious security problem!
精彩评论