none
Making the id's reset to order in database everytime i delete data RRS feed

  • Question

  • Hello there, i created a program that heavily depends on databases, everything was going fine however i coudn't find a way to reorder the id number, here what it looks like:

    What im trying to achieve was when i delete for example second data, i want 3 to be 2, 4 to be 3, etc.

    Thursday, July 11, 2019 10:43 PM

Answers

  • Hi xanrer,

    Thank you for posting here.

    Based on your description, you want to find a way to reorder the id number.

    I write a simple code, you could have a look.

      private void Button1_Click(object sender, EventArgs e)
            {
                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                int index = dataGridView1.CurrentCell.RowIndex;
                string a = dataGridView1.Rows[index].Cells[1].Value.ToString();
                string sql = "delete  from Score where Name='" + a + "'";
                SqlCommand command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                this.dataGridView1.DataSource = null;
                show();
                DataTable dt =(DataTable) dataGridView1.DataSource;
                var list = dt.AsEnumerable().Select(m => m.Field<int>("Id")).ToList();
                for (int i = 0; i < list.Count; i++)
                {
                    list[i] = i + 1;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i][0] = list[i];
                }
                string sql1 = string.Empty;
                for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
                {
                    dataGridView1.Rows[i].Cells[0].Value = list[i];
                }
                sql = "DROP table Score";
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                sql = "CREATE TABLE [dbo].[Score]([Id] INT NOT NULL , [Name] NVARCHAR(50) NOT NULL)";
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                using (SqlBulkCopy copy = new SqlBulkCopy(connection))
                {
                    copy.DestinationTableName = "Score";
                    copy.WriteToServer(dt);
                }
                    MessageBox.Show("success");
    
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                show();
            }
    
            public void show()
            {
                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                string sql = "select * from Score";
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                this.dataGridView1.DataSource = dataSet.Tables[0];
                connection.Close();
                connection.Dispose();
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by xanrer Saturday, July 13, 2019 4:28 PM
    Friday, July 12, 2019 7:28 AM
  • Thank you karen, the main problem is because of the client, im trying to keep the program light as possible, adding a new library will create some issues for them. However i'll talk with them to explain the situation.
    When talking to them explain that this is a lightweight solution that is not complex. I can also provide an extremely easy method to implement exception handling also. Will not be able to respond if you have other questions as I will be out all day.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by xanrer Saturday, July 13, 2019 4:29 PM
    Saturday, July 13, 2019 2:31 PM
    Moderator

All replies

  • If the ID is a primary-key to the record and it's an auto incremented number assigned by the DB engine, then no you can't do it or shouldn't be doing it. It's not a good concept IMHO in programming against a DB  even if you could do it.

    And besides, what if you're trying to do something like what you're trying to do and it's an one-2-many relationship where the primary-key ID of the parent record is the foreign-key to all records in child tables  related to the parent record? It would be one hell of a mess and a pure train wreck waiting to happen that you created.


    • Edited by DA924x Friday, July 12, 2019 12:52 AM
    Friday, July 12, 2019 12:51 AM
  • If this is MSSQL, and the id column allows update, then try a query like this:

    ; WITH n AS

    (

           SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id

           FROM MyTable

    )

    UPDATE MyTable

    SET id = new_id

    FROM MyTable AS t

    INNER JOIN n ON n.id = t.id

     

    If there are other tables that contain “cascade” foreign keys, then the changes are automatically propagated.

    There are statements to deal with identity columns as well.

    Friday, July 12, 2019 5:19 AM
  • Hi xanrer,

    Thank you for posting here.

    Based on your description, you want to find a way to reorder the id number.

    I write a simple code, you could have a look.

      private void Button1_Click(object sender, EventArgs e)
            {
                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                int index = dataGridView1.CurrentCell.RowIndex;
                string a = dataGridView1.Rows[index].Cells[1].Value.ToString();
                string sql = "delete  from Score where Name='" + a + "'";
                SqlCommand command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                this.dataGridView1.DataSource = null;
                show();
                DataTable dt =(DataTable) dataGridView1.DataSource;
                var list = dt.AsEnumerable().Select(m => m.Field<int>("Id")).ToList();
                for (int i = 0; i < list.Count; i++)
                {
                    list[i] = i + 1;
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i][0] = list[i];
                }
                string sql1 = string.Empty;
                for (int i = 0; i < dataGridView1.Rows.Count-1; i++)
                {
                    dataGridView1.Rows[i].Cells[0].Value = list[i];
                }
                sql = "DROP table Score";
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                sql = "CREATE TABLE [dbo].[Score]([Id] INT NOT NULL , [Name] NVARCHAR(50) NOT NULL)";
                command = new SqlCommand(sql, connection);
                command.ExecuteNonQuery();
                using (SqlBulkCopy copy = new SqlBulkCopy(connection))
                {
                    copy.DestinationTableName = "Score";
                    copy.WriteToServer(dt);
                }
                    MessageBox.Show("success");
    
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                show();
            }
    
            public void show()
            {
                string con = @"";
                SqlConnection connection = new SqlConnection(con);
                connection.Open();
                string sql = "select * from Score";
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                this.dataGridView1.DataSource = dataSet.Tables[0];
                connection.Close();
                connection.Dispose();
            }

    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by xanrer Saturday, July 13, 2019 4:28 PM
    Friday, July 12, 2019 7:28 AM
  • If this is MSSQL, and the id column allows update, then try a query like this:

    ; WITH n AS

    (

           SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS new_id

           FROM MyTable

    )

    UPDATE MyTable

    SET id = new_id

    FROM MyTable AS t

    INNER JOIN n ON n.id = t.id

     

    If there are other tables that contain “cascade” foreign keys, then the changes are automatically propagated.

    There are statements to deal with identity columns as well.

    Sorry i have limited knowledge on sql.
    As now it gives me errors no matter what i tried.

    Edit: i added "GO;" before "WITH" this time i have SQL70001 error.
    • Edited by xanrer Friday, July 12, 2019 4:49 PM
    Friday, July 12, 2019 4:30 PM
  • Sorry i have limited knowledge on sql.
    As now it gives me errors no matter what i tried.

    Edit: i added "GO;" before "WITH" this time i have SQL70001 error.

    One has to consider how the DB engine views the table column. If the column is a primary-key column and it's an auto incremented column too, the DB engine may very well reject what you are trying to do.

    Do you know how the DB engine view the table column?  How is the column defined in the table?

    Friday, July 12, 2019 9:50 PM
  • Sorry i have limited knowledge on sql.
    As now it gives me errors no matter what i tried.

    Edit: i added "GO;" before "WITH" this time i have SQL70001 error.

    One has to consider how the DB engine views the table column. If the column is a primary-key column and it's an auto incremented column too, the DB engine may very well reject what you are trying to do.

    Do you know how the DB engine view the table column?  How is the column defined in the table?

    Yes it "Id" is my key and there is the line "PRIMARY KEY CLUSTERED ([Id] ASC)" but changing key to anything else or changing "is clustered" to "false" doesn't fix the error.

    Here is how it looks:

    CREATE TABLE [dbo].[ABCDTable] (
        [Id] INT   NOT NULL,
        [Dn] VARCHAR(30) NOT NULL,
        [Sd] VARCHAR(MAX) NOT NULL,
        PRIMARY KEY CLUSTERED ([Id] ASC)
    )
    GO;
    
    WITH n AS
    (
           SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) AS new_Id
           FROM ABCDTable
    )
    UPDATE ABCDTable
    SET Id = new_Id
    FROM ABCDTable AS t
    INNER JOIN n ON n.Id = t.Id;
    

    Friday, July 12, 2019 11:50 PM
  • Hello,

    Altering the primary key is never a good idea, my recommendation is to use an extra field that represents the ordering to suit your needs.

    Full example with scripts to create a table and populate with 1,000 rows 

    https://github.com/karenpayneoregon/MoveFromAccessToSqlServerWithVisualBasicNet/tree/master/SqlServerDeleteReorderExample

    In the code sample a table Person uses RowPosition for showing information on the screen be it a ListBox, ListView or DataGridView.

    The demo is presented with a DataGridView, one button to delete the current row, the other to show if needed how to reorder all rows for RowPosition.

    Form code

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using SqlServerDeleteReorderExample.Classes;
    using SqlServerDeleteReorderExample.LanguageExtensions;
    using static SqlServerDeleteReorderExample.Classes.Dialogs;
    
    namespace SqlServerDeleteReorderExample
    {
        public partial class Form1 : Form
        {
            private readonly BindingSource _bindingSource = new BindingSource();
            private readonly DataOperations _dataOperations = new DataOperations();
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                _bindingSource.DataSource = _dataOperations.ReadPeople();
                dataGridView1.DataSource = _bindingSource;
                FormClosing += Form1_FormClosing;
            }
            /// <summary>
            /// Perform update for RowPosition field on all existing rows
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void Form1_FormClosing(object sender, FormClosingEventArgs e)
            {
                ((DataTable)_bindingSource.DataSource).ReorderPositionMarker();
                _dataOperations.UpdateAllRowsPosition(((DataTable)_bindingSource.DataSource));
            }
            private void EraseCurrentRowButton_Click(object sender, EventArgs e)
            {
                if (_bindingSource.Current == null) return;
                var id = ((DataRowView)_bindingSource.Current).Row.Field<int>("id");
                _bindingSource.RemoveCurrent();
                _dataOperations.RemoveRow(id);
    
            }
    
            private void ResetManualKeysButton_Click(object sender, EventArgs e)
            {
                if (Question("Do you really want to manually reset row position for all records?"))
                {
                    _dataOperations.ResetIdentifiers();
                }            
            }
        }
    }
    

    Class for data operations

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace SqlServerDeleteReorderExample.Classes
    {
        /// <summary>
        /// Zero exception handling to keep things clear
        /// </summary>
        public class DataOperations
        {
            /*
             * Must change the server name to match your server
             */
            private string ConnectionString = "Data Source=KARENS-PC;" + 
                                              "Initial Catalog=ForumExample;" + 
                                              "Integrated Security=True";
    
            private readonly string _keyPositionFieldName = "RowPosition";
    
            /// <summary>
            /// Read all person records into a DataTable
            /// </summary>
            /// <returns></returns>
            public DataTable ReadPeople()
            {
                var dt = new DataTable();
    
                const string selectStatement = 
                    "SELECT Id,RowPosition  ,FirstName + ' ' + LastName AS FullName FROM dbo.Person";
    
                using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement })
                    {
    
                        cn.Open();
                        dt.Load(cmd.ExecuteReader());
    
                        dt.Columns["id"].ColumnMapping = MappingType.Hidden;
    
                    }
                }
    
                return dt;
            }
            /// <summary>
            /// Remove row which is called from the "Erase data" button in the form
            /// </summary>
            /// <param name="id">Person primary key</param>
            public void RemoveRow(int id)
            {
                const string deleteStatement = "DELETE FROM dbo.Person WHERE id = @id";
                using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand() { Connection = cn, CommandText = deleteStatement })
                    {
                        cmd.Parameters.AddWithValue("@id", id);
                        cn.Open();
                        cmd.ExecuteNonQuery();                    
                    }
                }
            }
    
            public void ResetIdentifiers()
            {
                const string deleteStatement = "UPDATE ForumExample.dbo.Person " + 
                                               "SET @MaxSurrogateKey = RowPosition = @MaxSurrogateKey + 1 ";
    
                using (var cn = new SqlConnection() {ConnectionString = ConnectionString})
                {
                    using (var cmd = new SqlCommand() {Connection = cn, CommandText = deleteStatement})
                    {
                        cmd.Parameters.AddWithValue("@MaxSurrogateKey", 0);
                        cn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
    
            }
    
            /// <summary>
            /// Reorder all keys identified with _keyPositionFieldName
            /// </summary>
            /// <param name="pDataTable"></param>
            public void UpdateAllRowsPosition(DataTable pDataTable)
            {
    
                var updateStatement = $"UPDATE dbo.Person SET {_keyPositionFieldName} =" +
                                        $" @{_keyPositionFieldName} WHERE id = @PersonId";
    
    
                using (var cn = new SqlConnection() { ConnectionString = ConnectionString })
                {
                    using (var cmd = new SqlCommand() { Connection = cn })
                    {
    
                        cmd.CommandText = updateStatement;
                        cmd.Parameters.Add(new SqlParameter()
                        {
                            ParameterName = $"@{_keyPositionFieldName}",
                            SqlDbType = SqlDbType.Int
                        });
    
                        cmd.Parameters.Add(new SqlParameter()
                        {
                            ParameterName = "@PersonId",
                            SqlDbType = SqlDbType.Int
                        });
    
    
                        cn.Open();
    
                        int newPosition = 1;
    
                        for (var rowIndex = 0; rowIndex < pDataTable.Rows.Count; rowIndex++)
                        {
                            // set new row position
                            cmd.Parameters[$"@{_keyPositionFieldName}"].Value = newPosition;
    
                            cmd.Parameters["@PersonId"].Value =
                                pDataTable.Rows[rowIndex].Field<int>("Id");
    
                            cmd.ExecuteNonQuery();
                            newPosition += 1;
                        }
    
                    }
                }
            }
        }
    }
    
    

    Helper language extension

    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    
    namespace SqlServerDeleteReorderExample.LanguageExtensions
    {
        public static class DataTableExtensions
        {
            public static void ReorderPositionMarker(this DataTable sender)
            {
                if (!sender.Columns.Contains("RowPosition")) return;
    
                var indexer = 1;
    
                var rows = sender.AsEnumerable().Where(row => row.RowState != DataRowState.Deleted).ToList();
    
                for (var rowIndex = 0; rowIndex < rows.Count; rowIndex++)
                {
                    rows[rowIndex].SetField("RowPosition", indexer);
                    indexer += 1;
                }
                sender.AcceptChanges();
            }       
        }
    }
    

    MessageBox wrapper

    using System;
    using System.Collections.Generic;
    using System.Diagnostics;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace SqlServerDeleteReorderExample.Classes
    {
        public static class Dialogs
        {
            [DebuggerStepThrough]
            public static bool Question(string pText)
            {
                return (MessageBox.Show(
                            pText,
                            Application.ProductName,
                            MessageBoxButtons.YesNo,
                            MessageBoxIcon.Question,
                            MessageBoxDefaultButton.Button2) == DialogResult.Yes);
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, July 13, 2019 12:38 AM
    Moderator
  • Like Karen is mentioning, there could be another column call it SquenceNo you can call it whatever you want and manipulate the column, but leave the primary-key column alone. 
    Saturday, July 13, 2019 3:33 AM
  • Thank you karen, the main problem is because of the client, im trying to keep the program light as possible, adding a new library will create some issues for them. However i'll talk with them to explain the situation.
    Saturday, July 13, 2019 10:32 AM
  • Thank you karen, the main problem is because of the client, im trying to keep the program light as possible, adding a new library will create some issues for them. However i'll talk with them to explain the situation.
    When talking to them explain that this is a lightweight solution that is not complex. I can also provide an extremely easy method to implement exception handling also. Will not be able to respond if you have other questions as I will be out all day.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by xanrer Saturday, July 13, 2019 4:29 PM
    Saturday, July 13, 2019 2:31 PM
    Moderator
  • I gave it a try while i was waiting for my client and both yours and Jacks worked liked wonders, thank you two very much!

    From what DA924x and you said, yours is the safest way to do but the further decision is on my client, thank you all.
    Saturday, July 13, 2019 4:40 PM