/// <summary>
/// Initializes a SQLITE Database by the name / directory provided.
/// Creates it, generates a table, and fills the table with 100 dummy rows of data.
/// </summary>
/// <param name="dbName">The name of the database.</param>
/// <param name="dir">The directory of the database (optional - assumes executing directory)</param>
private static void InitializeDB(string dbName, string dir = "")
{
Console.WriteLine("InitializeDB");
string combinedPath = Path.Combine(dir, dbName);
combinedPath = Path.ChangeExtension(combinedPath, ".sqlite");
Console.WriteLine("DB File Name: {0}", combinedPath);
if (File.Exists(combinedPath))
{
// If we are told to initialize the DB, let's delete it first.
Console.WriteLine("Detected DB already exists - deleting");
DeleteDB(dbName, dir);
}
Console.WriteLine("Creating SQLite Database");
// 1. Create the Database, with the file name specified.
SQLiteConnection.CreateFile(combinedPath);
Console.WriteLine("Instantiating Student Table in database");
// 2. Run a SQL Query to instantiate a table
// Note: This is wrapped in a using statement, to ensure the IDbConnection is disposed of - ALWAYS.
// Also, note that we stuff a SQLiteConnection into an IDbConnection. This allows us to more easily swap
// underlying DBMS's within C#, as any well formulated Database-implementation should adhere to the
// IDbConnection, IDbCommand, IDataReader semantics / interfaces, allowing DBMS-agnostic C#-layer code.
// (However, this will still need to have different SQL queries depending upon the variant of SQL used).
using (IDbConnection con = new SQLiteConnection(string.Format("Data Source={0};Version=3;", combinedPath))) // 2.A: Create a Connection to the database.
{
// Open the connection to the database.
con.Open();
using (IDbCommand cmd = con.CreateCommand()) // 2.B: Create a Command object, to be able to interact with the database.
{
// Set the command timeout to 60 seconds. The default Value for MS SQL is 30s, I believe...
// We're using SQLIte, which I'm unsure if command timeouts even have a meaningful place, since
// the query is executed against the local disk, rather than potentially a remote server...
// I've never tested SQLite with command timeouts.
// If CommandTimeout == 0, there is no timeout, and an infinite amount of time is allowed to lapse.
cmd.CommandTimeout = 60;
cmd.CommandText = @"
CREATE TABLE Students
(
ID INTEGER PRIMARY KEY,
Name VARCHAR(50),
Address VARCHAR(300),
PhoneNumber VARCHAR(12),
GPA DECIMAL(15,4),
BirthDate DATETIME
)";
// Execute the SQL Statement in Non-Query mode.
// This doesn't return an IDataReader, and is meant for modifying
// data, without retrieving any tuples from SQL.
// Note: This will return total # of rows affected, in the event that rows are affected.
cmd.ExecuteNonQuery();
} // End scope for cmd; it gets disposed.
} // End scope for con; it gets disposed.
Console.WriteLine("Inserting Data into Database");
// 3. Insert 100 items into the table.
using (IDbConnection con = new SQLiteConnection(string.Format("Data Source={0};Version=3;", combinedPath)))
{
con.Open();
using (IDbCommand cmd = con.CreateCommand())
{
cmd.CommandTimeout = 60;
cmd.CommandText = @"
INSERT INTO Students
(Name, Address, PhoneNumber, GPA, BirthDate)
VALUES
(@NameParameter, @AddressParameter, @PhoneNumberParameter, @GPAParameter, @BirthDateParameter)";
for (int i = 0; i < 100; i++)
{
// Note: Rather than using literals, or string-formatted literals,
// we will use SQL Command Parameterization, which "sanitizes" the SQL input, to (hypothetically) avoid SQL Injection.
// That's what the @-stuff in the VALUES section of the query represents.
cmd.Parameters.Clear(); // Clear pre-existing parameters, since we are reusing an existing IDbCommand.
// Use Static Helper Extension Method to add command parameter, to elliminate some boiler plate code.
cmd.AddCommandParameter(DbType.String, "@NameParameter", "Student" + i);
// Note below that we ommitted the "@" character at the front of the paramter name;
// this is fine in MS SQL and SQLITE.
cmd.AddCommandParameter(DbType.String, "AddressParameter", string.Format("6{0} Some Road Some City Some State Some Zip Some Country", i.ToString().PadLeft(3, '0')));
cmd.AddCommandParameter(DbType.String, "PhoneNumberParameter", "+1-801-800-9" + i.ToString().PadLeft(3, '0'));
cmd.AddCommandParameter(DbType.Decimal, "GPAParameter", (decimal)generator.Next(1000, 4000) / 1000.0m); // Randomly generate a GPA between 1.0 and 4.0 inclusive.
cmd.AddCommandParameter(DbType.DateTime, "BirthDateParameter", new DateTime(
generator.Next(1980, 2007), // Allow datetimes between 1980, and 2006 inclusive. (Random is exclusive on upper bound, in C#).
generator.Next(1, 13), // Allow datetime between months 1, and 12 inclusive.
generator.Next(1, 29))); // Allow day between 1 and 28 inclusive (that way we don't worry about months with differing max # of days)
// Execute nonquery again, as we don't need to read results, we're just storing data in the database.
cmd.ExecuteNonQuery();
}
}
}
}
