CODEWITHSUNDEEP
c#databasevisual-studiosqlitesystem.data.sqlite
Robert Johnson
Robert Johnson

Reputation: 23

c# sqlite not loading after reopening program

I have a simple program written in c# and I am using SQLite to save a list of profiles (emails, passwords). Everything works great until I close the program and then reopen it. When I do that the table is empty. This code is located in my form constructor that fires first when the program loads (it's a single form program, very basic). I am using the System.Data.SQLite library. I can see the file in my project folder (bin/debug/..). Can anyone please explain why this information is not being saved and available to read on reopening the program?

SQLiteConnection.CreateFile("MyDatabase.db");
m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.db;Version=3;");
m_dbConnection.Open();
string sql = "CREATE TABLE " + profileTable + " (" + emailCol + " VARCHAR(320), " + passwordCol + " VARCHAR(30))";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "SELECT * FROM "+profileTable+" order by "+emailCol+" desc";
command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    emailProfileListBox.Items.Add(reader[emailCol] as String);
}

Here is my INSERT statement that does insert and has been validated.

string sql1 = "INSERT INTO "+profileTable+" ("+emailCol+", "+passwordCol+") VALUES (\'"+from_email_address.Text+"\', \'"+passwordTextBox.Text+"\')";
SQLiteCommand command1 = new SQLiteCommand(sql1, m_dbConnection);
command1.ExecuteNonQuery();

Upvotes: 1

Views: 274

Answers (2)

Igor
Igor

Reputation: 62228

First line, if you create a new file every time it runs the file would be overwritten.

SQLiteConnection.CreateFile("MyDatabase.db");

Wrap the creation statement in an if block instead checking to see if the file exists on disk.

if (!System.IO.File.Exists("MyDatabase.db")) 
{
   SQLiteConnection.CreateFile("MyDatabase.db");
   // continue your creation script here
}

See the documentation

Creates or overwrites a database file in the specified path. This just creates a zero-byte file which SQLite will turn into a database when the file is opened properly. Note that an existing file will be overwritten.

Side notes

  • You should also wrap your connections and any other instances where the type implements IDisposable in using blocks to ensure that external resources are always released.
  • You should use parameterized statements for any values you pass to them. So your inserts, updates, and conditions in your selects should use parameters to pass the values.
  • Never store passwords, not even encrypted ones. Create a one way salted hash of the password instead and store that. There are plenty of existing libraries / code fragements out there that can do this for you.

Upvotes: 1

kw1jybo
kw1jybo

Reputation: 152

Could it be because you are creating the table when you rerun the program?

Upvotes: 0

Related Questions