using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Text;
using System.Data;
using System.Linq;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using System.Diagnostics;
using Serilog;
namespace SQLite.BusinessLayer
{
/// <summary>
/// http://zetcode.com/csharp/sqlite/
/// https://sqlite.org/cli.html
///
/// </summary>
public sealed class SPSQLite
{
protected SQLiteConnection conn = null;
protected string dbFilePath = string.Empty;
protected string table_name = string.Empty;
private static readonly Lazy<SPSQLite> lazy = new Lazy<SPSQLite>(() => new SPSQLite());
public static SPSQLite Instance { get { return lazy.Value; } }
public SPSQLite()
{
dbFilePath = SPGlobal.DB_PATH;
table_name = "TABLE_NAME";
}
public int CreateTable()
{
using (var cmd = new SQLiteCommand(conn))
{
try
{
cmd.CommandText =
$"CREATE TABLE IF NOT EXISTS {table_name}(window_id TEXT PRIMARY KEY, pos_x INT, pos_y INT, height INT, width INT)";
return cmd.ExecuteNonQuery();
}
catch { }
return 0;
}
}
public bool InsertOrUpdateRecord(string win_id, int x, int y, int h, int w, object obj)
{
if (!IsConnected())
Reconnect();
using (var cmd = new SQLiteCommand(conn))
{
try
{
string commandTxt = string.Format($"REPLACE INTO {table_name}(window_id, pos_x, pos_y, height, width) " +
$"VALUES('{win_id}', {x}, {y}, {h}, {w})");
cmd.CommandText = commandTxt;
var num = cmd.ExecuteNonQuery();
if (num == 1)
return true;
}
catch
{
Disconnect();
}
return false;
}
}
public bool UpdateRecord(string win_id, int x, int y, int h, int w, object obj)
{
if (!IsConnected())
Reconnect();
using (var cmd = new SQLiteCommand(conn))
{
try
{
string commandTxt = string.Format($"UPDATE {table_name} SET " +
$"pos_x = {x}, pos_y = {y}, height = {h}, width = {w}, " +
$"WHERE window_id = '{win_id}'");
cmd.CommandText = commandTxt;
var num = cmd.ExecuteNonQuery();
if (num == 1)
return true;
}
catch
{
Disconnect();
}
return false;
}
}
public void Initialize()
{
Connect();
if (IsConnected())
{
CreateTable();
}
}
public void Terminate()
{
Disconnect();
}
public bool DeleteRecord(string win_id)
{
return DeleteTableRecord(table_name, win_id);
}
// protected members
public void Connect()
{
try
{
var cs = string.Format(@"URI=file:{0}", dbFilePath);
conn = new SQLiteConnection(cs);
conn.Open();
}
catch (Exception e)
{
Log.Error("SPSQLite.Connect Exception: {0}", e.StackTrace);
}
}
public void Disconnect()
{
if (conn != null)
{
conn.Close();
conn = null;
}
}
public bool IsConnected()
{
return (conn != null ? true : false);
}
public bool Reconnect()
{
Disconnect();
Connect();
return IsConnected();
}
public void DropTable(string table_name)
{
using var cmd = new SQLiteCommand(conn);
string commandTxt = string.Format($"DROP TABLE IF EXISTS {table_name}");
cmd.CommandText = commandTxt;
cmd.ExecuteNonQuery();
}
public bool DeleteTableRecord(string table_name, string win_id)
{
if (!IsConnected())
Reconnect();
using (var cmd = new SQLiteCommand(conn))
{
try
{
string commandTxt = string.Format($"DELETE FROM {table_name} WHERE window_id = '{win_id}'");
cmd.CommandText = commandTxt;
cmd.ExecuteNonQuery();
return true;
}
catch
{
Disconnect();
}
return false;
}
}
// public members
public DataTable SelectTableRecord(string win_id)
{
if (!IsConnected())
Reconnect();
using (var cmd = new SQLiteCommand(conn))
{
try
{
string commandTxt = string.Format($"SELECT * FROM {table_name} WHERE window_id = '{win_id}'");
cmd.CommandText = commandTxt;
SQLiteDataReader dr = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(dr);
return dt;
}
catch { }
return null;
}
}
}
}