In this article I'm doing this side-step to explain the basics of SQLite. I Create a Table in the SQLite File, Insert a Record into a Table, Read a Record from a Table. We'll also be looking at a SQLite Wrapper Class, Overwriting an existing Database File, Accessing a SQLite Database from Multiple Threads. The article also provides a sample application that describes a solution that can be used to write XML files from data stored in a relational database.
Index
The recent gains in memory and processing capabilities of PC, server, and laptop computers make the demand for manipulating and displaying complex data structures in everyday applications more and more common. This article provides a C#/.Net 101 style introduction to a well known and widely used open source database called SQLite (note that the spelling is not SQLLite or SQL Light).
The SQLite database engine is available for multiple operating systems (Android, IOS, Linux, Windows), programming languages, and can be used as in memory engine or as a local file based database engine. The engine requires no additional Set-up since it is implemented in a set of DLLs that are referenced in a given VS project.
Using an embedded database engine like SQLite enables us to work efficiently with complex data structures without having to either worry about:
- re-inventing the wheel when it comes to implementing complex structures, such as, an additional index or
- having additional Set-up/maintenance/security efforts for running a dedicated database server.
The application of embedded databases includes (but is not limited to):
- storing and retrieving data structures in an optimal format (Application File Format)
- computing difficult analysis on the fly without having to use an additional server
A file based database cannot be accessed by 2 threads at any given time which is why it is important to always close a database after its usage since a potentially hanging thread could block a later call to Open(). This default limitation can be worked around by using the correct journal mode as we will see in the Accessing a SQLite Database from Multiple Threads section below.
The enforcement of Foreign Keys is for backward compatability reasons switched off by default. How Foreign Keys can be enforced is shown in the Enforcing Foreign Keys sections.
The SQLite database engine can be used via NuGet. Just create a new VS project (eg.: Console) and look for the System.Data.SQLite package. Install the package and start coding. You can also mannually download the binaries from:
and include a static reference in your project.
I have created a series of Advanced WPF TreeView articles which resulted in some feedback along the line:
How do I store or retrieve TreeView based data to/from a file?
To answer this with SQlite in an applied way, I am doing this side-step to explain the basics of SQLite, and later on, build on it with a separate article that will explain storing tree structured data in a relational database engine (see also XML solution over here).
A relational database system includes usually some sort of (text based) SQL query application (eg.: SQL Management Studio or VS for SQL Server). The SQLite ecosystem contains tons of such client query applications. There is, for example, a SQLite / SQL Server Compact Toolbox Plug-in for Visual Studio that can be used to state SQL queries or review the current Data Definition of the database.
I am on the other hand a big fan of open source and so I am preferring for this and other reasons the SQLite Manager Plug-in for FireFox, since its available on all platforms and does not change my VS settings.
So, you can use either of these tools to check-up on your database whenever you perform any of the steps described below.
The sample applications in this tutorial are simple WPF projects with textual output. The code can be found in the MainWindow.xaml.cs file (unless stated differently).
- Download 00_SQLite_tut.zip
- Download 01_SQLite.zip
The SQLite code in the sample projects is pretty self explanatory but it might raise questions for those who are not used to working with relational database systems. So, here are some snippets to explain the basics:
You can use the following snippet to create a SQLite database file:
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=database.sqlite;Version=3;");
SQLiteCommand sqlite_conn.Open();
Dim sqlite_conn As SQLiteConnection
sqlite_conn = New SQLiteConnection("Data Source=database.sqlite;Version=3;")
sqlite_conn.Open()
The first line creates a SQLiteConnection object with a connection string as parameter to the constructor. This statement creates a file called database.sqlite in the current directory under Debug/bin or Release/bin. The minimal required version of SQLite is version 3.
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=:memory:;Version=3;");
SQLiteCommand sqlite_conn.Open();
Dim sqlite_conn As SQLiteConnection
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
sqlite_conn.Open()
The above statement creates a SQLite database in-memory. Each in-memory database instance is unique and ceases to exist when the connection is closed.
The statement below creates a new file based database and creates a table within it (The SQLite system does not (by default) recreate a table. You should receive a corresponding exception, if you execute the statement twice):
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=database.sqlite;Version=3;");
SQLiteCommand sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
sqlite_cmd.ExecuteNonQuery();
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));"
sqlite_cmd.ExecuteNonQuery()
You can use the last 2 lines to perform pretty much any SQL on the SQLite system. Typically, You end-up using the ExecuteNonQuery() method to perform operations on the data dictionary (create, drop etc.), while other methods, such as, ExecuteReader() can be used to retrieve (extended) results of that query defined in the CommandText property.
Use the create table statement below to create a table only for the first time. The table is otherwise re-used each time you insert or select data on it (see 01_SQLite_tut.zip).
sqlite_cmd.CommandText =
@"CREATE TABLE IF NOT EXISTS
[Mytable] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NULL)";
sqlite_cmd.CommandText =
@"CREATE TABLE IF NOT EXISTS
[Mytable] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NULL)"
Here is a 'Hello World' example that writes 1 record with 2 values into an existing table:
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
SQLiteCommand sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Hello World');";
sqlite_cmd.ExecuteNonQuery();
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Hello World');"
sqlite_cmd.ExecuteNonQuery()
Here is a 'Hello World' example that reads records with 2 values from an existing table:
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;New=True;");
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM test";
sqlite_datareader = sqlite_cmd.ExecuteReader();
while (sqlite_datareader.Read())
{
object idReader = sqlite_datareader.GetValue(0);
string textReader = sqlite_datareader.GetString(1);
OutputTextBox.Text += idReader + " '" + textReader +"' "+ "\n";
}
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
Dim sqlite_datareader As SQLiteDataReader
sqlite_conn = New SQLiteConnection("Data Source=database.sqlite;Version=3;New=True;")
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
sqlite_cmd.CommandText = "SELECT * FROM test"
sqlite_datareader = sqlite_cmd.ExecuteReader()
While (sqlite_datareader.Read())
Begin
Dim idReader as object
Dim textReader as String
idReader = sqlite_datareader.GetValue(0)
textReader = sqlite_datareader.GetString(1)
OutputTextBox.Text = OutputTextBox.Text + idReader + " '" + textReader +"' "+ Environment.NewLine
End
The while loop in the above code sample executes until the query runs out of result data - or never executes, if the query cannot be satisfied with any results.
The GetValue(1) method shown above returns a .Net object value that can be converted into other types using reflection. You can also use the field index based syntax to get the same result: sqlite_datareader["text"].
You can also use alternative get value methods of the SQLiteDataReader object, such as, GetString(1) or GetInt32(1), if you know what the targeting .Net data-type should be based on the SQLite data:
bool GetBoolean(int i);
byte GetByte(int i);
char GetChar(int i);
DateTime GetDateTime(int i);
decimal GetDecimal(int i);
double GetDouble(int i);
float GetFloat(int i);
Guid GetGuid(int i);
short GetInt16(int i);
int GetInt32(int i);
long GetInt64(int i);
Function GetBoolean(int i) as Boolean
Function GetByte(int i) as Byte
Function GetChar(int i) as Char
Function GetDateTime(int i) as DateTime
Function GetDecimal(int i) as Decimal
Function GetDouble(int i) as Double
Function GetFloat(int i) as Single
Function GetGuid(int i) as Guid
Function GetInt16(int i) as Short
Function GetInt32(int i) as Integer
Function GetInt64(int i) as Long
You either know the correct data type for a SQLiteDataReader column (since you just created it) or you can use a method below to determine the correct data type of a given column in a deterministic way:
string GetDataTypeName(int i);
Type GetFieldType(int i);
string GetName(int i);
int GetOrdinal(string name);
string GetOriginalName(int i);
Function GetDataTypeName(ByVal i as Integer) as String
Function GetFieldType(ByVal i as Integer) as Type
Function GetName(ByVal i as Integer) as String
Function GetOrdinal(ByVal name as String) as Integer
Function GetOriginalName(ByVal i as Integer) as String
A C#/.Net application usually comes with its own requirements and architectural patterns. This section discusses SQLite properties and methods that might be worth be wrapped in a first software version.
We will briefly review the Models.SQLiteDatabase database wrapper class, which hides some implementation details, such as, the name of the database file:
private const string _DBfileName = "database.sqlite";
Private _DBFileName As String = "database.sqlite"
The Models.SQLiteDatabase class also has custom properties for error logging and status display:
public Exception Exception { get; set; }
public string Status { get; private set; }
public string ExtendendStatus{ get; set; }
Public Property Exception As Exception
Public Property Status As String
Public Property ExtendendStatus As String
The database class also hides how a database connection is technically established and whether the SQLite database file is re-created on database connect or not:
private void ConstructConnection(bool overwriteFile = false)
{
if (overwriteFile == true)
{
if (System.IO.File.Exists(@".\" + _DBfileName) == true)
{
SQLiteConnection.CreateFile(_DBfileName);
}
}
_Connection = new SQLiteConnection("Data Source=" + _DBfileName);
Status = "Database is open.";
}
Private Sub ConstructConnection(ByVal Optional overWriteFile As Boolean = False)
Dim connectString As SQLiteConnectionStringBuilder = New SQLiteConnectionStringBuilder()
connectString.DataSource = DBFileNamePath
connectString.ForeignKeys = EnforceForeignKeys
connectString.JournalMode = GetJournalMode()
_Connection = New SQLiteConnection(connectString.ToString())
If System.IO.File.Exists(DBFileNamePath) = False Then
SQLiteConnection.CreateFile(DBFileNamePath)
Status = "Created New Database."
Else
If overWriteFile = False Then
Status = "Using exsiting Database."
Else
SQLiteConnection.CreateFile(DBFileNamePath)
End If
End If
End Sub
A SQLite client application is required to format a connection string to open a connaction to a SQLite database as explained in previous sections- The SQLite APi offers a SQLiteConnectionStringBuilder class for this very purpose. Consider the following code snippet:
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.ForeignKeys = true;
connectString.JournalMode = SQLiteJournalModeEnum.Wal;
System.Console.WriteLine(connectString.ToString());
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
SQLiteCommand sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.ForeignKeys = true
connectString.JournalMode = SQLiteJournalModeEnum.Wal
System.Console.WriteLine(connectString.ToString())
Dim sqlite_conn as SQLiteConnection
sqlite_conn = new SQLiteConnection(connectString.ToString());
Dim sqlite_conn as SQLiteCommand
sqlite_conn.Open()
The output of the snippet above is: "data source=.\database.sqlite;foreign keys=True;journal mode=Wal", which is the string that is required by the SQLiteConnection object. The result of using the SQLiteConnectionStringBuilder is usually more readable and better maintainable throughout future versions, since SQLite should know best what the correct format of the connection string should be.
The SQLite database file specification has a user version property that can be readout or set via a non-SQL proprietary statement as shown below:
pragma user_version; orpragma user_version = 1;
The user_version; attribute can be used by the client software to determine whether a given data file is current and how it could gracefully be handled if the file version appears to be too old or new.
public long UserVersion()
{
using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
{
cmd.CommandText = "pragma user_version;";
return (long)cmd.ExecuteScalar();
}
}
public long UserVersionIncrease()
{
long version = UserVersion();
using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
{
cmd.CommandText = string.Format("pragma user_version = {0};"
, version + 1);
cmd.ExecuteNonQuery();
}
return UserVersion();
}
Public Function UserVersion() As Long
Using cmd As SQLiteCommand = New SQLiteCommand(_Connection)
cmd.CommandText = "pragma user_version;"
Return CLng(cmd.ExecuteScalar())
End Using
End Function
Public Function UserVersionIncrease() As Long
Dim version = UserVersion()
Using cmd As SQLiteCommand = New SQLiteCommand(_Connection)
cmd.CommandText = String.Format("pragma user_version = {0};", version + 1)
cmd.ExecuteNonQuery()
End Using
Return UserVersion()
End Function
The 02_SQLite_tut.zip demo application uses the above code to write a new user version each time when it inserts data (re-using the existing database file). We can see that the SQLite user version default value is 0.
Please review the links below to learn more about pragma statements in SQLite:
- sqlite.org - PRAGMA Statements
- www.tutorialspoint.com - SQLite PRAGMA
The SQLite database system will throw an error if the file is accessed by more than 1 thread at a time. This limitation is only a default limitation and can be eliminated as Scott suggested in the Forum section below. The corresponding option to set in this case is:
Quote:
"PRAGMA journal_mode = WAL"
This pragma option is actually a string at the SQLite engine. But it is modeled as an enumeration in the demo:
public enum JournalMode
{
DELETE = 0,
TRUNCATE = 1,
PERSIST = 2,
MEMORY = 3,
WAL = 4,
OFF = 5
}
Public Enum JournalMode
DELETE = 0
TRUNCATE = 1
PERSIST = 2
MEMORY = 3
WAL = 4
OFF = 5
End Enum
You can use the SQLiteDatabase wrapper class method:
public void JournalMode(JournalMode journalMode) to set the journal mode on an active connection and
- the
public string JournalMode() method to read out the currently used journal mode.
Each JournalMode pragma option can be set per database connection through the above method.. That is, the SQLite engine will go back to its default (DELETE), if the client does not state the journal mode on the next connection. An exception to this rule is the 'WAL' pragma option, which is actually persisted and used as default, if the client does not state a journal mode on the next connection.
You can verify this with the test app if you create a new database file (check Overwrite Database) and check the result label on the next connection without the Set Pragma Journal Mode option:
| | Step 1 | Step 2 |
| TRUNCATE | | |
| WAL | | |
There is also an alternative WAL mode solution, which is to state this option as part of the connection string:
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.JournalMode = SQLiteJournalModeEnum.Wal;
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
Dim sqlite_conn as SQLiteConnection
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.JournalMode = SQLiteJournalModeEnum.Wal
sqlite_conn = new SQLiteConnection(connectString.ToString())
sqlite_conn.Open()
A simple application that wants to read a file at start-up time and writes the changed informaton back at the end of the application's life time should not require this multi-thread access, but enabling it should help those applications that need to access multiple tables from multible threads to implement complex parellel computations.
The following code creates 2 tables in a SQLite database:
string createQuery =
@"CREATE TABLE IF NOT EXISTS
[itemtype] (
[id] INTEGER NOT NULL PRIMARY KEY,
[name] VARCHAR(256) NOT NULL
)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
createQuery =
@"CREATE TABLE IF NOT EXISTS
[solution] (
[id] INTEGER NOT NULL PRIMARY KEY,
[parent] INTEGER NOT NULL,
[level] INTEGER NOT NULL,
[name] VARCHAR(256) NOT NULL,
[itemtypeid] INTEGER NOT NULL,
FOREIGN KEY (itemtypeid) REFERENCES itemtype(id)
)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
Dim createQuery As String = "CREATE TABLE IF NOT EXISTS
[itemtype] (
[id] INTEGER NOT NULL PRIMARY KEY,
[name] VARCHAR(256) NOT NULL
)"
Using cmd As SQLiteCommand = New SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
createQuery =
@"CREATE TABLE IF NOT EXISTS
[solution] (
[id] INTEGER NOT NULL PRIMARY KEY,
[parent] INTEGER NOT NULL,
[level] INTEGER NOT NULL,
[name] VARCHAR(256) NOT NULL,
[itemtypeid] INTEGER NOT NULL,
FOREIGN KEY (itemtypeid) REFERENCES itemtype(id)
)"
Using cmd as SQLiteCommand = new SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
The above SQL code create a table constrain in the SQLite database. A table constrain means in this case that the itemtypeid column in the solution table is limited to the values in the id column of the itemtype table.
A database engine usually enforces Foreign Key rules by throwing exceptions when the stored data is in-consistent at the end of a data changing transaction (on commit transaction). But the SQLite engine does not implement this behavior by default as Scott hinted below in the forum.
The SQLite engine lets you create the tables with the above code, but will not throw an exception by default.
We have to turn on a connection string option if we want the exception 'feedback' on foreign keys:
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.ForeignKeys = true;
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
Dim sqlite_conn as SQLiteConnection
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.ForeignKeys = true
sqlite_conn = new SQLiteConnection(connectString.ToString())
sqlite_conn.Open()
The above code snippets in this section setup and enforce the usage of foreign keys. The database engine will throw an exception ('FOREIGN KEY constraint failed'), if the client application attempts to insert a value in the intemtypeid column and that value cannot be referenced in the id column of the itemtype table.
The DataRaeder section shows that SQLite can convert its data directly into a typed safe .Net object. The inverse direction - writing types safe .Net data objects into the database - is also supported by SQLite.
List<CategoryModel> values = new List<CategoryModel>();
values.Add(new CategoryModel(1,"ELECTRONICS"));
values.Add(new CategoryModel(2,"TELEVISIONS",1));
values.Add(new CategoryModel(3,"TUBE",2));
string query = "INSERT INTO category ([category_id],[name],[parent])VALUES(@category_id,@name,@parent)";
using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
int result = 0;
using (var transaction = cmd.Connection.BeginTransaction())
{
foreach (var item in values)
{
cmd.Parameters.AddWithValue("@category_id", item.ID);
cmd.Parameters.AddWithValue("@name", item.Name);
cmd.Parameters.AddWithValue("@parent", item.Parent);
result += cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
Dim values As List(Of CategoryModel) = New List(Of CategoryModel)()
values.Add(new CategoryModel(1,"ELECTRONICS"))
values.Add(new CategoryModel(2,"TELEVISIONS",1))
values.Add(new CategoryModel(3,"TUBE",2))
Dim query as String
query = "INSERT INTO category ([category_id],[name],[parent])VALUES(@category_id,@name,@parent)"
Using cmd as SQLiteCommand = new SQLiteCommand(query, DB.Connection)
Dim result as Integer
result = 0
Using transaction = cmd.Connection.BeginTransaction()
For Each item In values
cmd.Parameters.AddWithValue("@category_id", item.ID)
cmd.Parameters.AddWithValue("@name", item.Name)
cmd.Parameters.AddWithValue("@parent", item.Parent)
result += cmd.ExecuteNonQuery()
Next
transaction.Commit()
End Using
End Using
The above statements create a list of CategoryModel objects and writes that contents of that list into the database table called category.
The SQLite system implements by default one implicit begin transaction and transaction commit per changed (insert, update, delete) record. This behavior can be a significant slow down if you have to change 100s or more records at a time. A simple optimization that can be had here is to increase a transaction over multiple records.
The above sample code implements BeginTransaction and transaction.Commit() statements to change the default transaction behaviour. A transaction is now multiple record changes long and ensures that multiple inserts are performed more efficiently.
- Efficiently insert/update records
using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
using (var transaction = cmd.Connection.BeginTransaction())
{
...
transaction.Commit();
}
}
Using cmd as SQLiteCommand = new SQLiteCommand(query, DB.Connection)
Using transaction = cmd.Connection.BeginTransaction()
...
transaction.Commit()
End Using
End Using
The sample application in this section describes a solution that can be used to write XML files from data stored in a relational database. The background of this application is that I wanted to have some interesting sample data to play with when it comes to writing article series on tree structures and other data related topics. So, I got myself a little data conversion tool that outputs XML data based on a SQLLite database.
The SQLite database was generated from PL-SQL statements that originated from a data project about all the cities, regions, and countries in the world of 2012: lokasyon.sql.gz. I converted the file linked above into a file that did not have the Insert and Values statement part as in the original posting above. From there, we are using the following code to read the data into a SQLite database (see ViewModels/AppViewModel.cs):
string createQuery =
@"CREATE TABLE IF NOT EXISTS
[meta_location] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[iso] VARCHAR(50) DEFAULT NULL,
[local_name] varchar(255) DEFAULT NULL,
[type] char(2) DEFAULT NULL,
[in_location] unsigned int DEFAULT NULL,
[geo_lat] double(18,11) DEFAULT NULL,
[geo_lng] double(18,11) DEFAULT NULL,
[db_id] varchar(50) DEFAULT NULL)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
var cmdDeleteTable = new SQLiteCommand("delete from meta_location", db.Connection);
cmdDeleteTable.ExecuteNonQuery();
var lines = System.IO.File.ReadLines(@".\Resources\lokasyon.txt");
query = "INSERT INTO meta_location "
+ "([id], [iso], [local_name], [type], [in_location], [geo_lat], [geo_lng], [db_id])VALUES";
using (var transaction = db.Connection.BeginTransaction())
{
foreach (var line in lines)
{
if (line.Trim().Length > 0)
{
var valueLine = line.Replace("),", ")");
valueLine = valueLine.Replace("\\'", "''");
valueLine = valueLine.Replace("\\r", "");
valueLine = valueLine.Replace("\\n", "");
queryString = query + valueLine;
var cmdInsert = new SQLiteCommand(queryString, db.Connection);
cmdInsert.ExecuteNonQuery();
iLines++;
}
}
transaction.Commit();
}
Dim createQuery as String
Dim query as String
createQuery =
@"CREATE TABLE IF NOT EXISTS
[meta_location] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[iso] VARCHAR(50) DEFAULT NULL,
[local_name] varchar(255) DEFAULT NULL,
[type] char(2) DEFAULT NULL,
[in_location] unsigned int DEFAULT NULL,
[geo_lat] double(18,11) DEFAULT NULL,
[geo_lng] double(18,11) DEFAULT NULL,
[db_id] varchar(50) DEFAULT NULL)"
Using cmd as SQLiteCommand = new SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
cmdDeleteTable = new SQLiteCommand("delete from meta_location", db.Connection)
cmdDeleteTable.ExecuteNonQuery()
lines = System.IO.File.ReadLines(@".\Resources\lokasyon.txt")
query = "INSERT INTO meta_location "
+ "([id], [iso], [local_name], [type], [in_location], [geo_lat], [geo_lng], [db_id])VALUES"
Using transaction = db.Connection.BeginTransaction()
For Each line in lines
if line.Trim().Length > 0 Then
valueLine = line.Replace("),", ")")
valueLine = valueLine.Replace("\\'", "''")
valueLine = valueLine.Replace("\\r", "")
valueLine = valueLine.Replace("\\n", "")
queryString = query + valueLine
cmdInsert = new SQLiteCommand(queryString, db.Connection)
cmdInsert.ExecuteNonQuery()
iLines = iLines + 1
End If
Next
transaction.Commit()
End Using
The code snippet above reads the input file line by line into a lines string collection. The lines collection is then processed to generate a query string that performs a SQLite Insert statement, which in turn, is executed, row by row, in the last foreach loop shown above.
To generate a one file for all items (cities, regions, and countries) was not a clever choice because the resulting file would be too large and not every project might need all data items. Therefore, every data level (cities, regions, and countries) is written into a separate cs text file, which I then included into the project at: Models/Generate*.cs.
Here is the C# sample code generated from the SQLite database:
public static List<MetaLocationModel> Countries(List<MetaLocationModel> c)
{
...
c.Add(new MetaLocationModel(78, "GE", "Georgia", LocationType.Country, -1, 42, 43.5, "GE"));
c.Add(new MetaLocationModel(56, "DE", "Germany", LocationType.Country, -1, 51, 9, "DE"));
c.Add(new MetaLocationModel(81, "GH", "Ghana", LocationType.Country, -1, 8, -2, "GH"));
...
return c;
}
Public Shared Function Countries(ByRef par_countries As List(Of MetaLocationModel)) As List(Of MetaLocationModel)
...
par_countries.Add(New MetaLocationModel(78, "GE", "Georgia", LocationType.Country, -1, 42, 43.5, "GE"))
par_countries.Add(New MetaLocationModel(56, "DE", "Germany", LocationType.Country, -1, 51, 9, "DE"))
par_countries.Add(New MetaLocationModel(81, "GH", "Ghana", LocationType.Country, -1, 8, -2, "GH"))
...
Return par_regions
End Function
The C# code requires a PoJo class called MetaLocationModel. The MetaLocationModel class and the collection above can then be used to generate the XML output file:
private void WriteCityXmlModels(string filename)
{
var items = new List<MetaLocationModel>();
items = GenerateCity.Items(items);
using (StreamWriter sw = new StreamWriter(filename))
{
using (TextWriter writer = TextWriter.Synchronized(sw))
{
new XmlSerializer(typeof(List<MetaLocationModel>)).Serialize(writer, items);
}
}
}
Private Sub WriteCityXmlModels(ByVal filename As String)
Dim items = New List(Of MetaLocationModel)()
items = GenerateCity.Items(items)
Using sw As StreamWriter = New StreamWriter(filename)
Using writer As TextWriter = TextWriter.Synchronized(sw)
Dim serialization As XmlSerializer = New XmlSerializer(GetType(List(Of MetaLocationModel)))
serialization.Serialize(writer, items)
End Using
End Using
End Sub
There is one method per generated XML file and there are 3 XML files in the end:
- bin\<Debug or Release>\Resources\countries.xml
- bin\<Debug or Release>\Resources\regions.xml
- bin\<Debug or Release>\Resources\cities.xml
The above data model gives a brief overview on the structure that is based around the iso column. The iso can be used as connecting element since the iso of a country is a sub-string of the iso in a region and the iso of a region is a sub-string of the iso in a city.
Using SQLite with large datasets (beyond 100.000 records) is easy, reliable and flexible, if you have a beginners understanding in relational databases, but using this embedded database system can also be recommended for non-database minded people since its application is not complex and it never hurts to have another technology stack ready for application.
The conversion application at the end of the article is a real plus and good future investment for data centered evaluations coming up in the future. Please let me know if you know other free data sources that contain more accurate city, region, and country data items than this database from 2012.
The points visited above show clearly that a custom (proprietary) file format can be a waste of time, if you are dealing with small projects budgets and large data structures that cannot be stored and retrieved in such a flexible and efficient manner using standard techniques like XML.
The SQLite eco-system is extremely large and also available in Java or Objectiv-C (on Apple computers). Taking this together with the available performance and stability is one single reasons (among many) for using this system more often then not.
- SQLite References
- sqlite.org
- www.tutorialspoint.com
- CodeProject - SQLite Helper (C#)
- Cities of the World Database
Hints on StackOverflow
lokasyon.sql.gz
