Before jumping into C# code we really need to understand how to setup a template database to query and setup. This article will show non-coding ways to setup and define your database schema. Next one we will dive fully into the code aspect of the SQLite interface.

Using the native tool – SQLite3.exe

Unfortunately while the package “http://sourceforge.net/projects/sqlite-dotnet2” does contain the SQLite runtime access DLL’s it doesn’t contain all the tools that are supplied as part of the base package. The most useful one is SQLite3, this can be found on the SQLite download page. Search for “Precompiled Binaries For Windows” and find the SQLite_x_y_z.zip download, at the time of writing this is the current version sqlite-3_6_15.zip (note if you are reading this at some point later than 24 June I suggest you ensure you get the latest version).

SQLite3.exe provides command line access to the SQLite engine, for full usage and capabilities check out http://www.sqlite.org/sqlite.html for more information.

However to create a very (and I really do mean VERY simplistic) database we can write the SQL schema and then add it into a new database.  So lets quickly knock up a very simple example:

CREATE TABLE Publisher
(
PublisherId     int primary key
,Name         varchar(200)
);
CREATE TABLE Book
(
BookISBN     varchar(14) primary key
,Name         varchar(200)
,PublisherId    int
);

INSERT INTO BOOK(BookISBN,Name) VALUES(“1-84356-028-3″, “CSharpHacker SQLite Manual”);
INSERT INTO BOOK(BookISBN,Name) VALUES(“1-84356-029-3″, “SQLite vs VistaDB”);
INSERT INTO BOOK(BookISBN,Name) VALUES(“1-84356-030-3″, “SQLite vs SQL Server CE”);
INSERT INTO BOOK(BookISBN,Name) VALUES(“1-84356-031-3″, “SQLite vs Velocity Caching”);
.quit

This is our super simple schema that will evolve as this series moves on, but say for now we think this is the best schema since sliced bread was invented! You will notice the .quit at the end, this is used because we are just going to pipe this into the Sqlite3 application to create the database. So you need to save the above code into a file “CSharpRamblingCoder.sql” and then run the below:

sqlite3.exe ramblingCoder.db3 < CSharpRamblingCoder.sql

You will notice this will auto create the database file and fill it up for you. In fact one of the quirkier aspects of this database is that the default behavior is that it will auto create the data file if it cant find the one you specified. For example if you use SQLite3 or the .Net provider and supply a DB file that doesn’t exist then that DB container file gets created on the fly. We will go into a bit more detail on the SQL constructs SQLite uses in a later series, but for now I think this covers the initial “How do I create a new SQLite database?” and “How do I get my schema into a new SQLite database?”

Users of Visual Studio 2008 Full Edition

So assuming you followed the instructions in Part 1 and selected the full Visual Studio 2008 add-in you have a second way to create, setup and interrogate the database. This has to do with the “Server Explorer”, note at the time of writing this is an early cut of code so just be aware of that .

So if you now look at the “Server Explorer” you should see something like this (or have previous connections setup):

From here if you right mouse button on “Data Connections” you will be prompted to add a “New Connection…”:

This then will bring you to the “Add Connection” dialog, ensure that the “Data source:” is setup to be “SQLite Database File …” rather than anything else.

If this is not the case you need to “Change…” and select the SQLite driver:

Now make sure you have filled in the Database file name as a pathed location.

And congratulations you have just made an empty SQLite database!!!

To confirm this open up the CSharpCoderSample and see that you have holding places for Tables, System Tables and views.

Having a DB without any tables or functionality obviously isnt terribly useful, so lets add a table via the UI. Right mouse on the “tables” and select “Add New Table”.

This opens up a new tab in Visual Studio and you can start to fill in the information as you would normally:

Here is a new table I just imagined I may need at some point in the future

From here you save the table, I prefer Control+S but any save option works and you are prompted for the table name.

Ok we now have our first graphically built table – woo hoo!

So to expedite things you can run:

sqlite3.exe c:\CSharpCoderSample.db3 < CSharpRamblingCoder.sql

And after pressing refresh you can see the tables updated in the Server explorer.

Note since this add in is a early release it is not as fully featured as you would ideally like, but it is a nice way to peer into the database structure and actual data without needing to leave the IDE which is always a nice to have!

So now we know how to create a empty database & add schema. The final part is how to delete the database! Thankfully this is equally as obvious , delete the file c:\CSharpCoderSample.db3.

Now we are ready to get coding! Part 3 covers our first C# application to utilize the SQLite database.

Related Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

This entry was posted on Wednesday, June 17th, 2009 at 7:51 am and is filed under Development, SQLite. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.