0

I'm trying to update some fields(previously selected and displayed in DataGridView). For some reason WinForms application not responding when i'm trying to execute sql update statement (ExecuteNonQuery). Anything wrong with my code?

private void button19_Click(object sender, EventArgs e)
    {        
        if (ora_dataset.Tables["SWAP_2G_CELLS"].Rows.Count > 0)
        {
           foreach (DataRow row in ora_dataset.Tables["SWAP_2G_CELLS"].Rows)
           {
               string swap = row.ItemArray[13].ToString();
               string flag = row.ItemArray[14].ToString();
               string data = row.ItemArray[15].ToString();

               string sql_update = System.IO.File.ReadAllText("SQL/SWAP/swap_2g_update.dat");

               sql_update = sql_update.Replace("%SWAP_PARAM%", swap);
               sql_update = sql_update.Replace("%FLAG_PARAM%", flag);
               sql_update = sql_update.Replace("%DATE_PARAM%", data);
               sql_update = sql_update.Replace("%SITE_ID_PARAM%", row.ItemArray[1].ToString());
               sql_update = sql_update.Replace("%LAC_PARAM%", row.ItemArray[7].ToString());

               OracleCommand cmd = new OracleCommand();
               OracleConnection con = new OracleConnection(connstring);
               cmd.Connection = con;
               //cmd.CommandType = CommandType.Text;
               cmd.CommandText = sql_update;
               cmd.Connection.Open();
               int count = cmd.ExecuteNonQuery();
               cmd.Connection.Close();
               cmd.Dispose();
           }
        }

After parameters replacement sql string looks like this for first row in my dataset: 

UPDATE SWAP_2G
SET 
SWAP = 'P'
,FLAG1 = '1'
,DATA_SWAP = TO_DATE('12-02-15 00:00:00', 'dd-mm-yy hh24:mi:ss')
WHERE SITE_ID = '2664' AND LAC LIKE '39002'

I'm trying to execute it with Oracle SQL Developer, and it's work fine.

Artvart
  • 1
  • Sounds like a transaction isn't committing? – Tim Feb 12 '15 at 21:04
  • Data in db stays the same. – Artvart Feb 12 '15 at 21:09
  • 1
    Does it freeze up the first time or does it get through the loop a few times? BTW: It's really inefficient to put everything inside the loop, you could more the connection outside the loop and open and close it just once. – Steve Wellens Feb 12 '15 at 21:15
  • what if you force the loop to only run once to see if it works? I think you need to commit, and it's possible that your DB is locked when the second loop runs. – DrewJordan Feb 12 '15 at 21:38
  • yup, see this: http://stackoverflow.com/questions/5380688/oraclecommand-command-executenonquery-issue I think you need to use a Transaction and commit each one – DrewJordan Feb 12 '15 at 21:45
  • 1
    You should really get rid of the AND LAC LIKE '39002', since it doesn't have a wildcard search in it. Use AND LAC = '39002'. The LAC may be causing you to table scan. Here's another thread showing how to do an update with replacing the values: http://stackoverflow.com/questions/5765962/how-to-execute-a-update-statement-using-oracle-odp-net-in-c-sharp – Mike Feb 12 '15 at 21:46
  • @Mike I *think* Oracle should transform that into LAC = '39002'. edited because that was NOT the right documentation. Anyhow, I'd love it if someone could prove me wrong. – DrewJordan Feb 12 '15 at 21:57
  • @DrewJordan One would hope, but I have found that it shuts off indexing and table scans under the right scenarios. It never hurts to do it right the first time. – Mike Feb 12 '15 at 22:22
  • AND LAC LIKE '39002' - used because i need to compare it with data from other db, and if this data mismatch it should be able to controlled by user (like '%') Due to the related question, I deleted lac condition for test, and it wasn't the issue. – Artvart Feb 12 '15 at 22:39
  • Anyway, there was a problem with initialization. Fixed, after adding OracleCommand cmd = con.CreateCommand(); – Artvart Feb 12 '15 at 22:58
  • @Artvart You should probably move the Oracle connection creation, open and close outside of the foreach loop as well, so you don't have to wait on the connection to be created for each update through the loop. Creating a transaction would speed the whole thing up as well (so all rows updated are in a single transaction). Nice example here: https://msdn.microsoft.com/en-us/library/xx4z1eb6%28v=vs.110%29.aspx – Mike Feb 12 '15 at 23:03

0 Answers0