voile/Voile.Storage.Sqlite/SqliteDataStorage.cs

using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Voile.Patchouli.Data;

namespace Voile.Storage.Sqlite
{
	internal class SqliteDataStorage : IVoileDataStorage
	{
		private readonly SqliteConnectionStringBuilder connectionStringBuilder;

		public SqliteDataStorage(SqliteConnectionStringBuilder connectionStringBuilder) 
		{
			this.connectionStringBuilder = connectionStringBuilder;
		}

		public Exception Ping()
		{
			SqliteConnection conn = new SqliteConnection(connectionStringBuilder.ConnectionString);
			conn.Open();

			bool tableAlreadyExists = false;

			using (SqliteCommand selectMaster = conn.CreateCommand())
			{
				selectMaster.CommandText = "SELECT rootpage FROM sqlite_master WHERE name = 'a_connection_test'";
				SqliteDataReader sqliteDataReader = selectMaster.ExecuteReader();
				tableAlreadyExists = sqliteDataReader.Read();
				sqliteDataReader.Close();
				selectMaster.Dispose();
			}

			if (!tableAlreadyExists)
			{
				using (SqliteCommand createTableCommand = conn.CreateCommand())
				{
					createTableCommand.CommandText = "create table a_connection_test\r\n(\r\n    serial    integer                        not null\r\n        constraint a_connection_test_pk\r\n            primary key autoincrement,\r\n    dateadded TEXT default CURRENT_TIMESTAMP not null,\r\n    hostname  TEXT                           not null\r\n);\r\n\r\n";
					int v = createTableCommand.ExecuteNonQuery();
				}
			}

			using (SqliteCommand insertCommand = conn.CreateCommand())
			{
				insertCommand.CommandText = "INSERT INTO a_connection_test (hostname) VALUES (@hostname)";
				insertCommand.Parameters.AddWithValue("@hostname", Environment.MachineName);
				int v = insertCommand.ExecuteNonQuery();
				if (v != 1)
				{
					insertCommand.Dispose();
					conn.Dispose();
					return new VoileDataException("Failed to do a test INSERT");
				}
			}

			conn.Close();
			conn.Dispose();
			return null;
		}
	}
}