public int CreateTables()
{
try {
int result = 0;
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql = "CREATE TABLE if not exists HostSettings " +
" (" +
"ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
"DataSource VARCHAR(200) NULL, " +
"DatabaseName VARCHAR(100) NULL, " +
"UserLogin VARCHAR(30) NULL," +
"Password VARCHAR(30) NULL," +
"SALogin VARCHAR(30) NULL," +
"SAPassword VARCHAR(30) NULL," +
"BaseKey TEXT NULL" +
")";
DbCommand.CommandText = sql;
result = DbConnection.ExecuteCommand(DbCommand);
// Build HostOptions Table
sql = "CREATE TABLE if not exists HostOptions " +
" (" +
"ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
"SessionTimeout int NULL," +
"LockTimeout int NULL" +
");";
DbCommand.CommandText = sql;
result = DbConnection.ExecuteCommand(DbCommand);
// Build HostOptions Table
sql = "CREATE TABLE if not exists Accounting" +
" (" +
"InterfaceId int NULL," +
"Setting1 TEXT NULL," +
"Setting2 TEXT NULL," +
"Setting3 TEXT NULL," +
"Setting4 TEXT NULL," +
"Setting5 TEXT NULL" +
");";
DbCommand.CommandText = sql;
result = DbConnection.ExecuteCommand(DbCommand);
// Remains Last to indicate setup complete
// Insert Defaults
sql = "SELECT COUNT(ID) AS COUNT FROM HostOptions";
DbCommand.CommandText = sql;
int count = Convert.ToInt32(DbConnection.ExecuteScalar(DbCommand));
if (count <= 0) {
sql = "insert into HostOptions (SessionTimeout, LockTimeout) Values(480,30);";
DbCommand.CommandText = sql;
result = DbConnection.ExecuteCommand(DbCommand);
}
}
}
}
return result;
}catch {
throw;
}
}
public int SaveHostSettings(IHostSettings hostSettings)
{
try {
int result = -1;
string sql;
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
DbCommand.Parameters.Clear();
if (hostSettings.Id <= 0) {
sql =
"INSERT INTO HostSettings " +
"(datasource, databasename, userlogin, password, salogin, sapassword,basekey) " +
"Values(@DataSource,@DatabaseName,@UserLogin, @Password, @SaLogin, @SaPassword, @BaseKey);";
DbCommand.Parameters.AddWithValue("@BaseKey", Security.GetSha2Hash(hostSettings.BaseKey, Crc32.Compute(hostSettings.BaseKey).ToString()));
}else {
sql =
"UPDATE HostSettings " +
"SET datasource = @DataSource, databasename = @DatabaseName, userlogin = @UserLogin, password = @Password, salogin = @SaLogin, sapassword = @SaPassword " +
"WHERE id = @ID";
DbCommand.Parameters.AddWithValue("@ID", hostSettings.Id);
}
DbCommand.CommandText = sql;
DbCommand.Parameters.AddWithValue("@DataSource", hostSettings.DataSource);
DbCommand.Parameters.AddWithValue("@DatabaseName", hostSettings.DatabaseName);
DbCommand.Parameters.AddWithValue("@UserLogin", hostSettings.UserLogin);
DbCommand.Parameters.AddWithValue("@Password", hostSettings.Password);
DbCommand.Parameters.AddWithValue("@SaLogin", hostSettings.SALogin);
DbCommand.Parameters.AddWithValue("@SaPassword", hostSettings.SAPassword);
result = DbConnection.ExecuteCommand(DbCommand);
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public bool VerifySetup()
{
try {
bool result = false;
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql =
"SELECT DatabaseName from HostSettings WHERE id = 1";
DbCommand.CommandText = sql;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
result = row["DatabaseName"] != DBNull.Value;
row = null;
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public int SaveHostOptions(HostOptions hostOptions)
{
try {
int result = -1;
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql =
"UPDATE HostOptions " +
"SET sessiontimeout = @SessionTimeout, locktimeout = @LockTimeout " +
"WHERE id = 1";
DbCommand.CommandText = sql;
DbCommand.Parameters.Clear();
DbCommand.Parameters.AddWithValue("@SessionTimeout", hostOptions.SessionTimeout);
DbCommand.Parameters.AddWithValue("@LockTimeout", hostOptions.LockTimeout);
result = DbConnection.ExecuteCommand(DbCommand);
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public int SaveAccountingSettings(AccountingSettings accountingSettings)
{
try {
int result = -1;
bool isNew = true;
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql = "SELECT COUNT(*) AS COUNT FROM Accounting ";
DbCommand.CommandText = sql;
isNew = Convert.ToBoolean(DbConnection.ExecuteCommand(DbCommand));
if (isNew) {
sql = "insert into Accounting (InterfaceId,Setting1,Setting2,Setting3,Setting4,Setting5) Values(@InterfaceId,@Setting1,@Setting2,@Setting3,@Setting4,@Setting5)";
}else {
sql =
@"UPDATE Accounting
SET InterfaceId = @InterfaceId, Setting1 = @Setting1,Setting2 = @Setting2,Setting3 = @Setting3,Setting4 = @Setting4,Setting5 = @Setting5
";
}
DbCommand.CommandText = sql;
DbCommand.Parameters.Clear();
DbCommand.Parameters.AddWithValue("@InterfaceId", accountingSettings.InterfaceId);
DbCommand.Parameters.AddWithValue("@Setting1", accountingSettings.Setting1 ?? string.Empty);
DbCommand.Parameters.AddWithValue("@Setting2", accountingSettings.Setting2 ?? string.Empty);
DbCommand.Parameters.AddWithValue("@Setting3", accountingSettings.Setting3 ?? string.Empty);
DbCommand.Parameters.AddWithValue("@Setting4", accountingSettings.Setting4 ?? string.Empty);
DbCommand.Parameters.AddWithValue("@Setting5", accountingSettings.Setting5 ?? string.Empty);
result = DbConnection.ExecuteCommand(DbCommand);
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public IHostSettings GetHostSettings()
{
try {
IHostSettings result = new HostSettings();
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql =
"SELECT Id, DataSource,DatabaseName, UserLogin, Password, SALogin, SAPassword, BaseKey from HostSettings " +
"WHERE Id = 1";
DbCommand.CommandText = sql;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
result.Id = Convert.ToInt32(row["Id"]);
result.DataSource = row["DataSource"].ToString();
result.DatabaseName = row["DatabaseName"].ToString();
result.UserLogin = row["UserLogin"].ToString();
result.Password = row["Password"].ToString();
result.SALogin = row["SALogin"].ToString();
result.SAPassword = row["SAPassword"].ToString();
result.BaseKey = row["BaseKey"].ToString();
row = null;
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public HostOptions GetHostOptions()
{
try {
HostOptions result = new HostOptions();
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql =
"SELECT SessionTimeout, LockTimeout from HostOptions";
DbCommand.CommandText = sql;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count == 1) {
DataRow row = returnDT.Rows[0];
result.SessionTimeout = Convert.ToInt32(row["SessionTimeout"]);
result.LockTimeout = Convert.ToInt32(row["LockTimeout"]);
row = null;
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
public AccountingSettings GetAccountingSettings()
{
try {
AccountingSettings result = new AccountingSettings();
using (DbConnection = new SQLitePersistence(DbConnectionSettings)) {
if (DbConnection.IsConnected()) {
using (DbCommand = new SQLiteCommand()) {
string sql =
"SELECT InterfaceId, Setting1, Setting2, Setting3, Setting4, Setting5 from Accounting";
DbCommand.CommandText = sql;
DataTable returnDT = DbConnection.ExecuteQuery(DbCommand);
if (returnDT.Rows.Count > 0) {
DataRow row = returnDT.Rows[0];
result.InterfaceId = Convert.ToInt32(row["InterfaceId"]);
result.Setting1 = row["Setting1"].ToString();
result.Setting2 = row["Setting2"].ToString();
result.Setting3 = row["Setting3"].ToString();
result.Setting4 = row["Setting4"].ToString();
result.Setting5 = row["Setting5"].ToString();
row = null;
}
returnDT = null;
}
}else {
throw new Exception("Unable to Connect");
}
}
return result;
}catch {
throw;
}
}
