Sqlite how to use drop and create table with a button click

Question

0.00/5 (No votes)

See more:

How can I use Drop and Create Table in a Button Click the same way you do in the Server compact tool? I am currently using code what have you tried below but its not resetting the ID number back to 1.

-- Script Date: 5/5/2021 4:47 PM  - ErikEJ.SqlCeScripting version 3.5.2.87
DROP TABLE [Import_Data];
CREATE TABLE [Import_Data] (
  [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

What I have tried:

C#

 private void btn_Delete_Table_Info_Click(object sender, EventArgs e)
        {



            cmd = new SQLiteCommand();
            con.Open();
            cmd.Connection = con;
            cmd.CommandText = "delete from Import_Data where ID=  id ";
            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Successful Deleted", "Data Delete", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}

Posted 5-May-21 11:52am

Member 12349103

Updated 5-May-21 17:23pm

Add a Solution

Comments

[no name] 5-May-21 22:20pm

That's because in your "code", you're doing a "delete" (records) and not a drop (table).

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2021-05-05T17:23:00

If I understand your question correctly, you would like to drop and recreate the table in order to reset the auto-incremented key for the table.

Before going to the details you should consider if you need to reset the auto-increment field at all. Auto-incremented keys are also called surrogate keys[^]. The point with this type of key is that the value is unique but meaningless to the application or to user. The sole purpose is to identify the record. So even if you have 1000 rows in the table, you delete them all, why does it matter if the id for the next record inserted is 1001? It shouldn't.

If there is a solid reason to reset the id: While you could drop and re-create the table it probably would be easier just to reset the auto-increment after you have deleted all the rows. Looking at the documentation

SQLite Autoincrement[^]:
The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes.

So once yo have deleted all the rows you can set the increment value to zero using a statement like

SQL

UPDATE SQLITE_SEQUENCE 
SET    seq = 0
WHERE  name = 'Import_Data'

IMPORTANT! If you don't delete all of the rows, you don't want to set the value to something that would generate duplicates. In such case you would fetch the greatest value from you table and use that for the next value. Something like

SQL

UPDATE SQLITE_SEQUENCE 
SET    seq = (SELECT MAX(id)
              FROM Import_Data)
WHERE  name = 'Import_Data'