[C#] SQLite - Creating and changing databases on the fly

Question

Today I use SQLite and send a database-file with the project.

However I want the database to be created when the user first starts the software.

Is there a way to copy the code needed to create a database based on the existing database? The problem is that when a user downloads a new version he might be tricked into copying over his last database and lose the data. I'd like a nice way to check the version of the database and modify it if I need new columns or tables etc. Or, if it does not exist at all, create a new database?

I know I can probably make create the code to make the database from the beginning but I want it to be based on the existing database I have created by a gui.

Answered. Final code:

            //Copy the database from the resource
            using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("resourcename"))
            {
                if (stream == null)
                    throw new NullReferenceException("Stream is null. Cannot find the database in the resources");
                FileStream writer = new FileStream(Constants.SqLiteFile, FileMode.Create);
                int Length = 256;
                Byte[] buffer = new Byte[Length];
                int bytesRead = stream.Read(buffer, 0, Length);
                // write the required bytes
                while (bytesRead > 0)
                {
                    writer.Write(buffer, 0, bytesRead);
                    bytesRead = stream.Read(buffer, 0, Length);
                }
                stream.Close();
                writer.Close();
            }

Answer 1

It sounds like you just need a location to save the version of their current system. Create a config table and have a record that maintains whatever version created/update the database. When they run an installer/upgrade, have it check that version to see if it exists, and/or what version is in place to determine what steps to take.

Answer 2

Well... I think you could go for doing a sinchronization between the two sqlite databases, my idea is getting the tables and fields of each database, and loop over them to see what tables and fields are in one and not in the other.

For getting started, these queries can be useful for you.

-- For getting tables in current db
SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name;

-- For getting info about a table
PRAGMA table_info('tabla_name');