The Visioneers

In one of my projects, there is a need to implement a server-less SQL database.  The result of my exploration led me to SQLITE.  I’m lucky to have found a good wrapper since I’m using C#.  All is well until the need to re-create the database arises. This is required when the application is being readied for deployment.  In my application, one of the requirements was to be able to reset the database  or re create the database inside the application.

To serve this purpose, I developed a small C#console application to create the database for me.  The code is quiet simple , far from optimized and buggy but of course it is up to you to improve it.

public static class Util
<pre> {
public static bool CreateDb(string db)
{
try
{
Console.WriteLine("Please wait creating database " + db);
SQLiteConnection.CreateFile(db);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}

return true;
}

public static bool ExecuteScript(string script, string connectionString)
{
try
{
if (!File.Exists(script)) throw new FileNotFoundException(script);

DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
using (DbConnection cnn = fact.CreateConnection())
{
cnn.ConnectionString = connectionString;

using (var cmd = cnn.CreateCommand())
{
var fileInfo = new FileInfo(script);
var sql = fileInfo.OpenText().ReadToEnd();

var tables = System.Text.RegularExpressions.Regex.Split(sql, "\r\n\r\n");

if (tables.Length > 0)
{
cnn.Open();

foreach (var table in tables)
{
Console.WriteLine("Writing TABLE: \n" + table);
cmd.CommandText = table;
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();
}
}

cnn.Close();
return true;
}
}

}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return false;
}
}
}</pre>
<pre>

In your app.config, you need the following entry:

</pre>
<pre><?xml version="1.0"?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite"?
      description=".Net Framework Data Provider for SQLite"
      type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/></pre>
<pre>    </DbProviderFactories>
  </system.data>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
</configuration></pre>
<pre>

Finally, add a reference to the first file, the second file below should be in the same location as the executable:

1. System.Data.SQLite.dll
2. System.Data.SQLite.lib