locked
DELETE command in a C# program is not executed as expected please help

    Question

  • I have an SQL table on my SQL server. I have written a program that accesses it and do some calculations. I want content or the table rows to be null everytime I execute my program. I have written the following code. It does not give any build errors but it does not do the job.

      public void SavingDataBase()
            {
                using (var connect = new SqlConnection(ConnectionString))
                using (var cmd = connect.CreateCommand())// intializing an SQL command object for transactions
                   
                {
                   connect.Open();
                  
                    cmd.CommandText = "DELETE FROM MyTable";
                    cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)";

                    foreach (var number in this.Numbers)
                    try
                    {
                        SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connect);
                        cmdIns.Parameters.AddWithValue("@C", number.Caculations.C);
                        cmdIns.Parameters.AddWithValue("@D", number.Caculations.D);
                        cmdIns.Parameters.AddWithValue("@E", number.Caculations.E);
                        cmdIns.Parameters.AddWithValue("@F", number.Caculations.F);
                       
                        cmdIns.ExecuteNonQuery();
                        cmdIns.Dispose();
                        cmdIns = null;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString(), ex);
                    }
                    finally
                    {
     
                    }
                }

    I want the table to be empty every time I change the parameters in the following calling function in the program class


      var CalProcess = Caculations.change(new value(5, 4, 3, 4));

      calProcess.SavingDataBase();

     

    May I know where I have made the mistake? The delete statement in the code is not executed as I expect it. What happens is every time I change the parameters in the new values() the outcome is the old outcome plus the new outcome and the old outcome never changes or gets deleted. I want the exisitng table data to be deleted first and then changed with the new calculated outcome.

    Please point out my mistake.
    Many thanks.

     



    Wednesday, July 06, 2011 7:54 AM

Answers

  • Welcome to MSDN Forums.

    Your first code is nice, just need an execute for "DELETE" command. i.e.

    public void SavingDataBase()
        {
          using (var connect = new SqlConnection(ConnectionString))
          using (var cmd = connect.CreateCommand())// intializing an SQL command object for transactions
            
          {
            connect.Open();
            
            cmd.CommandText = "DELETE FROM MyTable";
            cmd.ExecuteNonQuery();//YOU'VE FORGOT THIS!
    
            cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)";
    
            foreach (var number in this.Numbers)
            try
            {
              SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connect);
              cmdIns.Parameters.AddWithValue("@C", number.Caculations.C);
              cmdIns.Parameters.AddWithValue("@D", number.Caculations.D);
              cmdIns.Parameters.AddWithValue("@E", number.Caculations.E);
              cmdIns.Parameters.AddWithValue("@F", number.Caculations.F);
              
              cmdIns.ExecuteNonQuery();
              cmdIns.Dispose();
              cmdIns = null;
            }
            catch (Exception ex)
            {
              throw new Exception(ex.ToString(), ex);
            }
            finally
            {
     
            }
          }
    


    Your Sincerely,

    Yasser.


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    • Proposed as answer by Stefan HoffmannMVP Wednesday, July 06, 2011 11:43 AM
    • Marked as answer by NickTom Wednesday, July 06, 2011 11:44 AM
    Wednesday, July 06, 2011 11:31 AM
  • WOW Yasser, It worked like a Charm. I was having a hectic time trying this and that.

    Thank you for your advice.

    One more thing, does your second comment say that I should have a sepeerate method like DeleteDB() and call it from the program.cs? Will that be a more suitable way of doing this?

    Thank you:)

    Nick

    You're welcome :)

    No, my second comment does not mean this. If you don't delete the table records in another place then you don't need to seperate them. However reanaming "SavingDataBase" method to "RefreshMyTable" is a nice action now ;)

    But by the second comment, I meant that if you execute "Delete ....; Insert..." command (both in one) in each loop then in each loop at first your code deletes the data and then insert one record BUT you want to delete the data once (outside of the loop) and insert one record in each loop, don't you? ;)


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    • Marked as answer by NickTom Wednesday, July 06, 2011 12:12 PM
    Wednesday, July 06, 2011 12:02 PM

All replies

  • 1st of all, you cannot execute two or more queries with one command. You have to do it one by one. So create a command to Delete and a new command for Insert query.

    for deleating data from table do:

    "DELETE FROM MyTable"
    


    Mitja
    Wednesday, July 06, 2011 8:13 AM
  • Hello nick

    Write your query on this way

    cmd.CommandText = "DELETE * FROM table_name;INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F);";

    So don't write

    cmd.CommandText = "DELETE FROM MyTable";
                    cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)"; at a time

    If you want to multiple query use storeprocedure

    Thanks


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.

    Wednesday, July 06, 2011 8:14 AM
  • I would suggest the step to delete the tables contents to a stored proc and passing the parameters to stored proc and doing insertion in it.
    Mark Answered, if it solves your question
    Rohit Arora
    Wednesday, July 06, 2011 8:48 AM
  • Sure you can, the problem is, that he is overwriting the first statement with the second. E.g.

    const string connectionString = @"Data Source=(local);Initial Catalog=tempdb;Integrated Security=SSPI;";
    using (var connect = new SqlConnection(connectionString))
    {
        using (var cmd = connect.CreateCommand())
        {
            connect.Open();
            cmd.CommandText = "DELETE FROM TestTabel; ";
            cmd.CommandText += "INSERT INTO TestTable  VALUES ('SomeValue') ;";
            cmd.ExecuteNonQuery();
        }
    }

    with

    USE tempdb ;
    GO
    
    CREATE TABLE TestTable
        (
          ID INT IDENTITY(1, 1) ,
          Payload NVARCHAR(32)
        ) ;
    
    INSERT  INTO TestTable
    VALUES  ( 'InitialValue' ) ;
    
    SELECT  *
    FROM    TestTable ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, July 06, 2011 9:02 AM
  • I tried using the following command as suggested by Sankar but it is not executed at all. Even the INSERT command is not executed.

    cmd.CommandText = "DELETE * FROM table_name;INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F);";

    Can you please explain how to use a stored procedure  in paticular to this problem?. That will be helpful.

    Many thanks.

    Nick

    Wednesday, July 06, 2011 9:45 AM
  • I used a different command for DELETE but it did not work either. I used it as follows.

      public void SavingDataBase()
            {
                using (var connect = new SqlConnection(ConnectionString))
                using (var cmd = connect.CreateCommand())// intializing an SQL command object for transactions
                   
                {
                   connect.Open();
                  

                    var cmd1 = connection.CreateCommand();
                    cmd1.CommandText = "DELETE FROM MyTable";
                    cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)";

                    foreach (var number in this.Numbers)
                    try
                    {
                        SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connect);
                        cmdIns.Parameters.AddWithValue("@C", number.Caculations.C);
                        cmdIns.Parameters.AddWithValue("@D", number.Caculations.D);
                        cmdIns.Parameters.AddWithValue("@E", number.Caculations.E);
                        cmdIns.Parameters.AddWithValue("@F", number.Caculations.F);
                       
                        cmdIns.ExecuteNonQuery();
                        cmdIns.Dispose();
                        cmdIns = null;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString(), ex);
                    }
                    finally
                    {
     
                    }
                }

    I want the table to be empty every time I change the parameters in the following calling function in the program class


      var CalProcess = Caculations.change(new value(5, 4, 3, 4));

      calProcess.SavingDataBase();

     

    I am sure I am missing the logic in this. I understood Steffan's comment on overwriting. I think I have overwritten the command before. But when I write it in a different command where do I make the mistake? Now even the INSERT is not executed.

    Hope someone can help a little.

    Many thanks to you all.

    Nick

    Wednesday, July 06, 2011 9:57 AM
  • Take a close look at my example again. You need one (prepared) command. The difference is the += for the second statement:

    cmd.CommandText = "DELETE FROM TestTabel; ";
    cmd.CommandText += "INSERT INTO TestTable VALUES ('SomeValue') ;";
    



    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, July 06, 2011 10:04 AM
  • Hello

    Write a store procedure in database with the name of

    ___________________________________________________

    create  procedure usp_Savedatabase

    (@C varchar(50),@D varchar(50),@E  varchar(50),@F  varchar(50)) -- Here should be as your datatype

    as begin

    DELETE FROM MyTable;

    INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F);

    end

    _________________________________________________________

    public void SavingDataBase()
            {
                using (var connect = new SqlConnection(ConnectionString))
                using (var cmd = connect.CreateCommand())// intializing an SQL command object for transactions
                   
                {
                   connect.Open();
                  

                    var cmd1 = connection.CreateCommand();

     cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = usp_Savedatabase;//storeprocedure_name;


                    //cmd1.CommandText = "DELETE FROM MyTable";
                    //cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)";

                    foreach (var number in this.Numbers)
                    try
                    {
                        SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connect);
                        cmdIns.Parameters.AddWithValue("@C", number.Caculations.C);
                        cmdIns.Parameters.AddWithValue("@D", number.Caculations.D);
                        cmdIns.Parameters.AddWithValue("@E", number.Caculations.E);
                        cmdIns.Parameters.AddWithValue("@F", number.Caculations.F);
                       
                        cmdIns.ExecuteNonQuery();
                        cmdIns.Dispose();
                        cmdIns = null;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString(), ex);
                    }
                    finally
                    {
     
                    }
                }


    Please mark the post answered your question as the answer, and mark other helpful posts as helpful, so they will appear differently to other users who are visiting your thread for the same problem.
    Wednesday, July 06, 2011 10:09 AM
  • Hi all,

    May be I did not understand Steffan's comment. Because when I use the += operator the exception handling in the code opens with an error : Incorrect syntax near the keyword 'INTO'.

    Thank you Sankar for the explanation on storedprocedure, But is it not possible to write everything in the C# code? I mean without using a stored procedure in the database? Is it not possible to write the stored procedure in the code block it self? something that will look like below.

    connection.Open();     
    string sql1 = "SELECT ID,FirstName,LastName FROM VP_PERSON";    
    string sql2 = "SELECT Address,City,State,Code FROM VP_ADDRESS";     
    using (SqlCommand command = new SqlCommand(sql1,connection))    
    {        
    //Command 1       
     using (SqlDataReader reader = command.ExecuteReader())        
    {             // reader.Read iteration etc         }   
     } // command is disposed.   
      using (SqlCommand command = new SqlCommand(sql2,connection))    
    {          //Command 1     
       using (SqlDataReader reader = command.ExecuteReader())        
    {             // reader.Read iteration etc         }

     

    This is just an example from stackflow which I tried to alter but it gives me full of errors. Is it possible to use something of this nature in the code with a IF condition.

    If the table content is not null execute the DELETE command, Else execute the INSERT INTO command. Is that possible?

    Please advise.

    Thank you.

    Wednesday, July 06, 2011 10:49 AM
  • The missing += was your error in C#. Of course you need to craft a valid SQL batch after all. Thus you need to separate the two statements. In my example I use the standard ANSI-SQL statement delimiter (;) at the end of each statement. So my combined statement is

    DELETE FROM TestTabel; INSERT INTO TestTable  VALUES ('SomeValue') ;

    In your case you don't have any delimiter thus the result should look like

    [sql]
    DELETE FROM MyTableINSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)
    [(sql]

    Which is indeed wrong SQL syntax.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, July 06, 2011 11:27 AM
  • Welcome to MSDN Forums.

    Your first code is nice, just need an execute for "DELETE" command. i.e.

    public void SavingDataBase()
        {
          using (var connect = new SqlConnection(ConnectionString))
          using (var cmd = connect.CreateCommand())// intializing an SQL command object for transactions
            
          {
            connect.Open();
            
            cmd.CommandText = "DELETE FROM MyTable";
            cmd.ExecuteNonQuery();//YOU'VE FORGOT THIS!
    
            cmd.CommandText = "INSERT INTO MyTable (C, D, E,F) VALUES (@C,@D,@E,@F)";
    
            foreach (var number in this.Numbers)
            try
            {
              SqlCommand cmdIns = new SqlCommand(cmd.CommandText, connect);
              cmdIns.Parameters.AddWithValue("@C", number.Caculations.C);
              cmdIns.Parameters.AddWithValue("@D", number.Caculations.D);
              cmdIns.Parameters.AddWithValue("@E", number.Caculations.E);
              cmdIns.Parameters.AddWithValue("@F", number.Caculations.F);
              
              cmdIns.ExecuteNonQuery();
              cmdIns.Dispose();
              cmdIns = null;
            }
            catch (Exception ex)
            {
              throw new Exception(ex.ToString(), ex);
            }
            finally
            {
     
            }
          }
    


    Your Sincerely,

    Yasser.


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    • Proposed as answer by Stefan HoffmannMVP Wednesday, July 06, 2011 11:43 AM
    • Marked as answer by NickTom Wednesday, July 06, 2011 11:44 AM
    Wednesday, July 06, 2011 11:31 AM
  • Another thing that I should say is: "You should not contact the DELETE statement with the INSERT one as someones suggestion because if you do this then the DELETE statement will execute in each loop and finally you'll have only last number group (one record) in your table".

    Good Luck!


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    Wednesday, July 06, 2011 11:37 AM
  • D'oh! Of course... shame upon me.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, July 06, 2011 11:42 AM
  • WOW Yasser, It worked like a Charm. I was having a hectic time trying this and that.

    Thank you for your advice.

    One more thing, does your second comment say that I should have a sepeerate method like DeleteDB() and call it from the program.cs? Will that be a more suitable way of doing this?

    Thank you:)

    Nick

    Wednesday, July 06, 2011 11:47 AM
  • WOW Yasser, It worked like a Charm. I was having a hectic time trying this and that.

    Thank you for your advice.

    One more thing, does your second comment say that I should have a sepeerate method like DeleteDB() and call it from the program.cs? Will that be a more suitable way of doing this?

    Thank you:)

    Nick

    You're welcome :)

    No, my second comment does not mean this. If you don't delete the table records in another place then you don't need to seperate them. However reanaming "SavingDataBase" method to "RefreshMyTable" is a nice action now ;)

    But by the second comment, I meant that if you execute "Delete ....; Insert..." command (both in one) in each loop then in each loop at first your code deletes the data and then insert one record BUT you want to delete the data once (outside of the loop) and insert one record in each loop, don't you? ;)


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    • Marked as answer by NickTom Wednesday, July 06, 2011 12:12 PM
    Wednesday, July 06, 2011 12:02 PM
  • D'oh! Of course... shame upon me.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

    No shame, these are usuall in coding :)
    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    Wednesday, July 06, 2011 12:07 PM
  • Yes Yasser,

    What I want is my table to be empty that means without any data BEFORE I execute the INSERT command.

    So everytime I change the parameters in the expression  " var CalProcess = Caculations.change(new value(5, 4, 3, 4)); " the table has to be updates with the new outcome from the method and should not have old values.

    Yes I will change my table name now with RefreshMyTable :)

    Many thanks again. I learnt something new.

    Have a nice day!!

    Nick:)


    Wednesday, July 06, 2011 12:12 PM
  • Will that be a more suitable way of doing this?

    I would like to say another thing. This question shows that the code quality is important for you and it's so great :) but in my experience and also according to "Agile Software Developement Methodologies", you can make a code better, better and better but don't do this when your current code does the job! your situation is a good example. You asked me to seperate DELETE and INSERT in two methods, ok, this is good but not now that you delete data in only one place. But consider your code grows and you need to delete data from MyTable in another method, yes, now is the time to seperate them to avoid duplicate code (duplicate code make code changibility poor).

    For more info please search the bolded text in web.

    Have a nice day and code ;)


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    Wednesday, July 06, 2011 12:37 PM
  • This is exactly what I meant as Yasser showed in his example code. As said, you cannot add two sql queries to one command. But as he did - created one and executed it, then created another and executed it.

    I didnt have time before to do an example, but nice you came up.

    But I would still do two seperated methods, one for deleting and another one for insertion. Its better to have things (operations) seperated. Then when you will some other time need only to delete, or only insert, what will you do? Create a new method? From this particular reason, its best to do such operations seperated in seperated methods. Then if you need them both, you call one after another.

     

     


    Mitja
    Wednesday, July 06, 2011 1:20 PM
  • when you will some other time need only to delete, or only insert, what will you do? Create a new method?
    Mitja

    No, we will seperate them at that other time not now!

    Mitja, in Agile Development approach there is a nice rule: "If customer want a software that add 2 with 2 and shows the result then write a software that shows 4 not a software that get a and b then shows a+b".

    Nick have wrote a code which does the job in good performance so in Agile approach he should not rewrite the code just because of a guess, instead he should go to the next step by only logging this guess and finally when his guess occured (if occured) for first time, he goes to the code to rewrite. this will decrease software delivery time!

    Hope I describe this issue well.


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    Wednesday, July 06, 2011 1:49 PM
  • Mitja, in Agile Development approach there is a nice rule: "If customer want a software that add 2 with 2 and shows the result then write a software that shows 4 not a software that get a and b then shows a+b".

    While the point you're trying to make is correct, I strongly disagree with your example.

    When a customer would ask for such a software, then I would immediately ask "Over what group?". And the second would be "On what base?". Assuming that he means plain arithmetics on a decimal base is an assumption which should be avoided. So assuming 4 as answer is as valid as 42.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Wednesday, July 06, 2011 2:01 PM
  • So assuming 4 as answer is as valid as 42.
    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann


    @Stefan

    This is a symbolic example not real. Customer says "I want a software that adds 2 with 2 and shows the result". Agile says you should write a software that shows 4 in it's output (42 is not valid because 2+2<>42). Agile says you should not write a software that gets 2 inputs then adds them then shows the result because the customer does not need this now!


    LEARN HOW TO USE WINDOWS API DURING A QUICK, SIMPLE AND PRACTICAL HOW TO:
    How To: Changing TextBox blinking caret using Windows API
    Thursday, July 07, 2011 5:49 PM