Injected SQL query in C# : deleting records from DB then updating the record ID to the last ID sequence

Question

I am letting the user able to delete a specific record in a SQL table. The problem is that I want to set the id(automatically) to the last id sequence presented into the db after the deletion. ex(delete item 14,when I add another item the id of that item won't be 15 but 14 because after the delete I've reset the id to 13 which is the last one after the delete)

private void btnCanc_Click(object sender, RoutedEventArgs e)
{


 sqliteCon.Open();
  try
  {
    string Test = null;//estrazione1

    SqlCommand q = new SqlCommand("DELETE FROM tabSE  WHERE idSE =" + txtIDL.Text.ToString(), sqliteCon);
    //string q = "DELETE FROM tabSE  WHERE idSE =" + txtIDL.Text.ToString();

    SqlCommand q1 = new SqlCommand("DELETE FROM tabL  WHERE idL =" + txtIDL.Text.ToString(), sqliteCon);
    //string q1 = "DELETE FROM tabL  WHERE idL =" + txtIDL.Text.ToString();

    SqlCommand q2 = new SqlCommand("DELETE FROM tabSD  WHERE id =" + txtIDL.Text.ToString(), sqliteCon);
    //string q2 = "DELETE FROM tabSD  WHERE id =" + txtIDL.Text.ToString();


    q.ExecuteNonQuery();
    q1.ExecuteNonQuery();
    q2.ExecuteNonQuery();



    SqlCommand m = new SqlCommand("SELECT idL  FROm tabL", sqliteCon);
    SqlDataReader idLRdr = null;//estrazione2
    idLRdr = m.ExecuteReader();//estrazione3
    while (idLRdr.Read())//estrazione4
    {
      Test = idLRdr["idL"].ToString();//estrazione5
    }

    SqlCommand r = new SqlCommand("DBCC CHECKIDENT(tabL,tabSE,tabSD,RESEED,'" + Test + "')", sqliteCon);  
    r.ExecuteNonQuery();

    SqlCommand r1 = new SqlCommand("DBCC CHECKIDENT(tabL,RESEED,'" + Test + "')", sqliteCon);
    r1.ExecuteNonQuery();
    SqlCommand r2 = new SqlCommand("DBCC CHECKIDENT(tabSE,RESEED,'" + Test + "')", sqliteCon);
    r2.ExecuteNonQuery();
    SqlCommand r3 = new SqlCommand("DBCC CHECKIDENT(tabSD,RESEED,'" + Test + "')", sqliteCon);
    r3.ExecuteNonQuery();
  }
  catch (SqlException ex)
  {
    MessageBox.Show(ex.Message);
  }
  MessageBox.Show("Dato Cancellato Correttamente");
  sqliteCon.Close();

}

code improved but it update the value of the id of the table but not the real id of each table(idL,idSE,id)(those are my custom ids)

OK I'VE MADE MY TESTS,THE PROBLEM IS THAT THE ID'S OF EACH TABLE(idL(TABLE tabL),idSE(TABLE tabSE),id(TABLE tabSD))AREN'T UPDATED BY MY CODE DBCC WHILE THE ID'S OF EACH TABLE(THOSE WHICH AREN'T CUSTOM MADE) ARE UPDATE AUTOMATICALLY... I NEED TO UPDATE idL,idSE,id

Answer 1

Of course the first problem is the fact that you don't execute the commands, but there are a lot of things that could be improved in your code. First and foremost is the concatenation of strings to build a sql command. This leads to problems with parsing and to the dangerous Sql Injection trick used to hack a databases. I have changed your code to use a parameter and avoid these problems.

The second improvement is given by the ability to execute batch commands. In other words you can put all your command texts in a single string and separate each one using a semicolon. Then just execute the command only one time and everything will be executed by the database engine.

But the real problem in your code is the DBCC CheckIdentity RESEED part. This part requires you to know which value to set as the new Identity because the RESEED option without a new value works only if the next identity value is lower than the current max value in the IDENTITY column, moreover this could be done safely only if you have exclusive access to the database (I mean, none is adding records to these tables while you get the MAX value for the ID)

sqliteCon.Open();
try
{
    string sqlText = @"DELETE FROM tabStoreExec  WHERE idSE = @idse;
                       DELETE FROM tabList  WHERE idL = @idse;
                       DELETE FROM tabStoricoDetail  WHERE id = @idse";
   SqlCommand q = new SqlCommand(sqlText, sqliteCon);
   q.Parameters.Add("@idse", SqlDbType.Int).Value = Convert.ToInt32(txtIDL.Text);
   q.ExecuteNonQuery();


   // This is the point where you change the identity.
   // To minimize concurrency problems we execute a stored procedure instead 
   sqlText = "ResetIdentity";
   q.CommandText = sqlText;
   q.CommandType = CommandType.StoredProcedure;
   q.Parameters.Clear();
   q.ExecuteNonQuery();
   MessageBox.Show("Dato Cancellato Correttamente");
}
catch (SqlException ex)
{
   MessageBox.Show(ex.Message);
}
sqliteCon.Close();

This is the possible code for your stored procedure, note that I don't know what is the name of the IDENTITY column in your tables. For this example I have used always id as the name for the IDENTITY column

create procedure ResetIdentity
as
    begin
    declare @maxval integer

    set @maxval = (select MAX(id) from tabStoreExec);
    dbcc checkident('tabStoreExec', RESEED, @maxval);

    set @maxval = (select MAX(id) from tabStoricoDetail);
    dbcc checkident('tabStoricoDetail', RESEED, @maxval);

    set @maxval = (select MAX(id) from tabList);
    dbcc checkident('tabList', RESEED, @maxval);
end 

There is another improvement to your code and it is related to the using statement to create disposable objects like the database connection. The correct usage of such objects should be something like this

using(SqlConnection sqlConn = new SqlConnection(......))
{
     sqlConn.Open();
     .... do you database code here ....
} // This will close and dispose the connection

Answer 2

i will go as well with what @Steve said , the ID here is for the table (PK,FK)relation , why don't just add another field of sequential numbers that you update using LINQ as desired . and also your code is missing the query execution command ,

SqlCommand q = new SqlCommand("DELETE FROM tabStoreExec  WHERE idSE =" + 
txtIDL.Text.ToString(), sqliteCon);
//string q = "DELETE FROM tabStoreExec  WHERE idSE =" + txtIDL.Text.ToString();
q.ExecuteNonQuery();

 OR

con.Open();

using (SqlCommand q= new SqlCommand("DELETE FROM tabStoreExec  WHERE idSE =" + txtIDL.Text.ToString(), sqliteCon))

  {
       q.ExecuteNonQuery();
  }

con.Close();