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 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 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 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; } }