ExecuteNonQuery stops program

  • Thread starter Thread starter rafal
  • Start date Start date
R

rafal

I run this code below which is part of method.
ds.Tables["tabelka"].Rows.Count has always value 13117 but sometimes the
code executes only 1000 or 12000 times etc and freezes without error or
exception not coming to the end of the method. In debug window it prints
"before" which means it stops during or before executing
upd.ExecuteNonQuery();
I don' know what is going on. Thanks for help.


for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++)
{
string account_id = ds.Tables["tabelka"].Rows[y][0].ToString();
string date = ds.Tables["tabelka"].Rows[y][1].ToString();
int temp1 = 0;
double temp2 = 0.00;
int val = 0;
val = y/1874;
string updateCommand = "update wielka set "+columnName+"="+val.ToString()+ "
where account_id="+account_id+" and date="+date;
SqlConnection sqlConnection3 = new SqlConnection(connString);
SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3);
if (sqlConnection3.State.ToString() == "Closed")
{
sqlConnection3.Open();
}
try
{
Console.WriteLine("before");
upd.ExecuteNonQuery();
Console.WriteLine("discretization "+y);
}
catch (Exception exc)
{
Console.WriteLine("Exception 3: "+exc.ToString());
Console.WriteLine("UpdateCommand: "+updateCommand);
}
sqlConnection3.Close();
}
 
U¿ytkownik "Sahil Malik said:
Are these commands a part of a transaction?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


rafal said:
I run this code below which is part of method.
ds.Tables["tabelka"].Rows.Count has always value 13117 but sometimes the
code executes only 1000 or 12000 times etc and freezes without error or
exception not coming to the end of the method. In debug window it prints
"before" which means it stops during or before executing
upd.ExecuteNonQuery();
I don' know what is going on. Thanks for help.


for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++)
{
string account_id = ds.Tables["tabelka"].Rows[y][0].ToString();
string date = ds.Tables["tabelka"].Rows[y][1].ToString();
int temp1 = 0;
double temp2 = 0.00;
int val = 0;
val = y/1874;
string updateCommand = "update wielka set "+columnName+"="+val.ToString()+
"
where account_id="+account_id+" and date="+date;
SqlConnection sqlConnection3 = new SqlConnection(connString);
SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3);
if (sqlConnection3.State.ToString() == "Closed")
{
sqlConnection3.Open();
}
try
{
Console.WriteLine("before");
upd.ExecuteNonQuery();
Console.WriteLine("discretization "+y);
}
catch (Exception exc)
{
Console.WriteLine("Exception 3: "+exc.ToString());
Console.WriteLine("UpdateCommand: "+updateCommand);
}
sqlConnection3.Close();
}
Click to expand...
Click to expand...
No, separate inserts into database.
 
I'd Debug.Writeline the command that's being fired and see if it will work
in Query Analyzer - or run a trace and see what's happening w/ it. I've
never quite seen that behaviour you mention - but using Profiler will help
you see what's going on back there.

Just as an aside - you may want to paramaterize your query for performance,
security etc. Also, if this is being called 10,000 times or whatever, just
check that sqlConnection3.State==ConnectionState.Closed - that way you
aren't creating a string each time just to compare it to one. In a loop
this big that could affect performance a little . I'd also just trap a
SqlException here b/c the line your trying - that should be the only type of
exception that would be raised. Just on GP I'd bump the Close into a
finally block.

Good Luck

Bill
 
Okay so ..

You don't have a transactional scenario.
The code hangs after an unpredicatable # of updates.

Are you getting the same behavior when you run 13117 updates from Query
Analyzer?

Can you run a quick experiment?
a) Backup your database.
b) Run your code - see at what count it blocks.
c) Now restore the db
d) Run the code again - see at what count it blocks.

Does it block at exactly the same count between b and d?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik



rafal said:
U¿ytkownik "Sahil Malik said:
Are these commands a part of a transaction?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik


rafal said:
I run this code below which is part of method.
ds.Tables["tabelka"].Rows.Count has always value 13117 but sometimes
the
code executes only 1000 or 12000 times etc and freezes without error or
exception not coming to the end of the method. In debug window it
prints
"before" which means it stops during or before executing
upd.ExecuteNonQuery();
I don' know what is going on. Thanks for help.


for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++)
{
string account_id = ds.Tables["tabelka"].Rows[y][0].ToString();
string date = ds.Tables["tabelka"].Rows[y][1].ToString();
int temp1 = 0;
double temp2 = 0.00;
int val = 0;
val = y/1874;
string updateCommand = "update wielka set "+columnName+"="+val.ToString()+
"
where account_id="+account_id+" and date="+date;
SqlConnection sqlConnection3 = new SqlConnection(connString);
SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3);
if (sqlConnection3.State.ToString() == "Closed")
{
sqlConnection3.Open();
}
try
{
Console.WriteLine("before");
upd.ExecuteNonQuery();
Console.WriteLine("discretization "+y);
}
catch (Exception exc)
{
Console.WriteLine("Exception 3: "+exc.ToString());
Console.WriteLine("UpdateCommand: "+updateCommand);
}
sqlConnection3.Close();
}
Click to expand...
Click to expand...
No, separate inserts into database.
Click to expand...
 
U¿ytkownik "rafal said:
I run this code below which is part of method.
ds.Tables["tabelka"].Rows.Count has always value 13117 but sometimes the
code executes only 1000 or 12000 times etc and freezes without error or
exception not coming to the end of the method. In debug window it prints
"before" which means it stops during or before executing
upd.ExecuteNonQuery();
I don' know what is going on. Thanks for help.


for (int y = 0; y < ds.Tables["tabelka"].Rows.Count; y++)
{
string account_id = ds.Tables["tabelka"].Rows[y][0].ToString();
string date = ds.Tables["tabelka"].Rows[y][1].ToString();
int temp1 = 0;
double temp2 = 0.00;
int val = 0;
val = y/1874;
string updateCommand = "update wielka set "+columnName+"="+val.ToString()+ "
where account_id="+account_id+" and date="+date;
SqlConnection sqlConnection3 = new SqlConnection(connString);
SqlCommand upd = new SqlCommand(updateCommand, sqlConnection3);
if (sqlConnection3.State.ToString() == "Closed")
{
sqlConnection3.Open();
}
try
{
Console.WriteLine("before");
upd.ExecuteNonQuery();
Console.WriteLine("discretization "+y);
}
catch (Exception exc)
{
Console.WriteLine("Exception 3: "+exc.ToString());
Console.WriteLine("UpdateCommand: "+updateCommand);
}
sqlConnection3.Close();
}
Click to expand...

I have found solution. There is possibly bug in VS.net 1. Above loop is part
of another loop and I print about 60000 lines to the debug window. When I
stopped printing so much problem does not appear.
It also appeared that program does not stop on ExecuteNonQuery but on
Console.WriteLine("before"). When I put there more text /string is longer/
it didn't print the whole string. So the solution is not to print so much to
the debug window. Thanks for help
 
Back
Top