public GenCommand(String commandText, GenConnection connection) : base() { GenDatabase = connection.GenDatabase; DbCommand = GenDatabase.DbProviderFactory.CreateCommand(); DbCommand.CommandText = commandText; DbCommand.Connection = connection.InnerConnection; GenConnection = connection; if (GenDatabase.UtilityLog.LogDatabase) GenDatabase.UtilityLog.LogMessage("GenCommand.GenCommand", "id: " + connection.Id + " - creation thread: " + connection.CreationThread + " - this thread: " + System.Threading.Thread.CurrentThread.ManagedThreadId + " - command: " + commandText, LogEntryType.Database); }
public bool GetCurrentVersion(GenConnection con, String databaseType, out DBVersion version) { GenCommand cmd; if (databaseType == "SQL Server") cmd = new GenCommand("Select Major, Minor, [Release], Patch from [version]", con); else cmd = new GenCommand("Select Major, Minor, `Release`, Patch from version", con); version.major = ""; version.minor = ""; version.release = ""; version.patch = ""; try { GenDataReader dr; dr = (GenDataReader)cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); version.major = dr.GetString("Major"); version.minor = dr.GetString("Minor"); version.release = dr.GetString("Release"); version.patch = dr.GetString("Patch"); dr.Close(); return true; } else { dr.Close(); return false; } } catch (Exception e) { if (GlobalSettings.SystemServices != null) // true when service is running GlobalSettings.LogMessage("GetCurrentVersion", "Exception: " + e.Message); return false; } }
public GenDatabase(String server, String database, String username, String password, String dbType, String providerType, String providerName, String oleDbName, String connectionString, String defaultDirectory, IUtilityLog utilityLog) { DBLock = new Object(); Server = server; Database = database; Username = username; Password = password; OleDbName = oleDbName; ProviderType = providerType; ProviderName = providerName; ConnectionString = connectionString; DBType = dbType; DefaultDirectory = defaultDirectory; UtilityLog = utilityLog; GenDBType = GetDBType(dbType); GenProviderType = GetProviderType(providerType); GlobalConnection = null; // Connections = new List<GenConnection>(10); ConnectionCount = 0; try { DbProviderFactory = DbProviderFactories.GetFactory(providerName); } catch (Exception e) { throw new GenException(GenExceptionType.ProviderNotFound, "GenDatabase - constructor - cannot find provider: " + providerName + " : " + e.Message, e); } if (ConnectionString == "") { ConnectionString = BuildConnectionString(); } }
private bool CopyToTable(String fromRelation, String toTable, List<String> columns, GenConnection con) { if (columns == null || columns.Count == 0) { GlobalSettings.LogMessage("VersionManager", "CopyToTable - Empty column list", LogEntryType.ErrorMessage); return false; } String columnList = ""; foreach (String column in columns) { if (columnList == "") columnList = column; else columnList += ", " + column; } String fromSelect = "select " + columnList + " from " + fromRelation; String toInsert = "insert into " + toTable + " ( " + columnList + " ) " + fromSelect; GenCommand cmd = null; bool ret = true; try { cmd = new GenCommand(toInsert, con); int res = cmd.ExecuteNonQuery(); } catch (Exception e) { ret = false; GlobalSettings.LogMessage("VersionManager", "CopyToTable - Exception: " + e.Message, LogEntryType.ErrorMessage); } finally { if (cmd != null) cmd.Dispose(); } return ret; }
private bool CreateRelation(String tableCommand, GenConnection con) { try { GenCommand cmd = new GenCommand(tableCommand, con); int res = cmd.ExecuteNonQuery(); } catch (Exception e) { GlobalSettings.LogMessage("CreateRelation", "Error creating relation " + tableCommand + " - error: " + e.Message, LogEntryType.ErrorMessage); return false; } return true; }
internal void ConnectionClosed(GenConnection connection) { lock (DBLock) { // This list serves no purpose // Connections.Remove(connection); ConnectionCount--; if (UtilityLog.LogDatabase) UtilityLog.LogMessage("GenDatabase.ConnectionClosed", "id: " + connection.Id + " - creation thread: " + connection.CreationThread + " - closing thread: " + System.Threading.Thread.CurrentThread.ManagedThreadId + " - count: " + ConnectionCount, LogEntryType.Database); } }
public bool UpdateTo_1_4_0_3(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "4", "0", "3"); if (res >= 0) return false; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_4_0_3", "Updating database structure to suit version 1.4.0.3", LogEntryType.Information); bool success = true; if (res < 0) { if (databaseType == "SQLite") { success = AlterRelation("drop table `pvoutputlog` ", con); success &= CreateRelation(DDL.Table_pvoutputlog, con); } else { success = AlterRelation(DDL.Alter_pvoutputlog_1400b, con); } } if (success) UpdateVersion("1", "4", "0", "3", con); return success; }
private bool Update_meterreading_1700(String databaseType, DDL ddl, GenConnection con) { bool ok = true; if (databaseType == "SQLite") { try { GenCommand cmd = new GenCommand("alter table meterreading rename to meterreading_old ", con); int res = cmd.ExecuteNonQuery(); ok = res >= 0; } catch (Exception e) { ok = false; GlobalSettings.LogMessage("VersionManager", "Update_meterreading_1700: exception renaming existing: " + e.Message, LogEntryType.ErrorMessage); } if (!ok) return false; if (!CreateRelation(((SQLite_DDL)ddl).Table_meterreading_1700, con)) return false; List<String> columns = new List<String>(); columns.Add("Meter_Id"); columns.Add("ReadingTime"); columns.Add("Appliance"); columns.Add("Duration"); columns.Add("Energy"); columns.Add("Temperature"); columns.Add("Calculated"); columns.Add("MinPower"); columns.Add("MaxPower"); if (!CopyToTable("meterreading_old", "meterreading", columns, con)) return false; try { GenCommand cmd = new GenCommand("drop table meterreading_old ", con); int res = cmd.ExecuteNonQuery(); return true; } catch (Exception e) { GlobalSettings.LogMessage("VersionManager", "Update_meterreading_1700: exception dropping old: " + e.Message, LogEntryType.ErrorMessage); } } else { if (!AlterRelation(ddl.Alter_meterreading_dropPK_1700, con)) return false; if (!AlterRelation(ddl.Alter_meterreading_createPK_1700, con)) return false; return true; } return false; }
private bool Update_pvoutput_v_1836(DDL ddl, GenConnection con) { try { GenCommand cmd = new GenCommand("drop view pvoutput_v", con); int res = cmd.ExecuteNonQuery(); } catch (Exception e) { GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput_v: " + e.Message, LogEntryType.ErrorMessage); } try { GenCommand cmd = new GenCommand("drop view pvoutput5min_v", con); int res = cmd.ExecuteNonQuery(); } catch (Exception e) { GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput5min_v: " + e.Message, LogEntryType.ErrorMessage); } try { GenCommand cmd = new GenCommand("drop view pvoutput_sub_v", con); int res = cmd.ExecuteNonQuery(); } catch (Exception e) { GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1500: exception dropping pvoutput_sub_v: " + e.Message, LogEntryType.ErrorMessage); } try { if (CreateRelation(ddl.View_pvoutput_sub_v_1836, con)) if (CreateRelation(ddl.View_pvoutput_v_1836, con)) if (CreateRelation(ddl.View_pvoutput5min_v_1836, con)) return true; } catch (Exception e) { GlobalSettings.LogMessage("VersionManager", "Update_pvoutput_v_1836: exception creating views: " + e.Message, LogEntryType.ErrorMessage); } return false; }
public bool UpdateTo_1_7_1_0(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "7", "1", "0"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_7_1_0", "Updating database structure to suit version 1.7.1.0", LogEntryType.Information); success = AlterRelation(DDL.Alter_meter_1710, con); if (databaseType == "SQLite") success &= AlterRelation(((SQLite_DDL)DDL).Alter_meter_1710a, con); if (databaseType == "Jet") success &= AlterRelation(((Jet_DDL)DDL).Alter_meter_1710a, con); success &= SetMeterInstanceNo(con); if (success) UpdateVersion("1", "7", "1", "0", con); return success; }
private bool SetMeterInstanceNo(GenConnection con) { try { GenCommand cmd = new GenCommand("update meter set InstanceNo = 1 ", con); cmd.ExecuteNonQuery(); return true; } catch (Exception e) { GlobalSettings.LogMessage("SetMeterInstanceNo", "Error updating meter table - Exception: " + e.Message, LogEntryType.ErrorMessage); return false; } }
public bool UpdateTo_1_5_0_2(GenConnection con, String databaseType) { DDL DDL; int res; res = Version.CompareVersion("1", "5", "0", "2"); if (res >= 0) return false; bool success = true; if (databaseType == "SQL Server") { DDL = new SQLServer_DDL(); GlobalSettings.LogMessage("UpdateTo_1_5_0_2", "Updating database structure to suit version 1.5.0.2", LogEntryType.Information); success &= AlterRelation(((SQLServer_DDL)DDL).Alter_cmsdata_1502, con); } if (success) UpdateVersion("1", "5", "0", "2", con); return success; }
public bool UpdateTo_1_7_0_0(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "7", "0", "0"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_7_0_0", "Updating database structure to suit version 1.7.0.0", LogEntryType.Information); success = AlterRelation(DDL.Alter_inverter_1700, con); success &= Update_pvoutput_v_1700(DDL, con); success &= Update_meterreading_1700(databaseType, DDL, con); if (success) UpdateVersion("1", "7", "0", "0", con); return success; }
public bool UpdateTo_1_5_0_0(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "5", "0", "0"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_5_0_0", "Updating database structure to suit version 1.5.0.0", LogEntryType.Information); success = CreateRelation(DDL.Table_cmsdata_1500, con); success &= AlterRelation(DDL.Alter_outputhistory_1500, con); if (databaseType == "SQLite") success &= AlterRelation(((SQLite_DDL)DDL).Alter_outputhistory_1500b, con); if (databaseType == "Jet") success &= AlterRelation(((Jet_DDL)DDL).Alter_outputhistory_1500b, con); success &= Update_pvoutput_v_1500(DDL, con); if (success) UpdateVersion("1", "5", "0", "0", con); return success; }
public bool UpdateTo_1_4_2_1(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "4", "2", "1"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_4_2_1", "Updating database structure to suit version 1.4.2.1", LogEntryType.Information); success = AlterRelation(DDL.Alter_pvoutputlog_1421, con); if (success) UpdateVersion("1", "4", "2", "1", con); return success; }
public bool UpdateTo_1_4_1_9(GenConnection con, String databaseType) { DDL DDL; int res; res = Version.CompareVersion("1", "4", "1", "9"); if (res >= 0) return false; bool success = true; if (databaseType == "Jet") { DDL = new Jet_DDL(); GlobalSettings.LogMessage("UpdateTo_1_4_1_9", "Updating database structure to suit version 1.4.1.9", LogEntryType.Information); AlterRelation(DDL.Alter_pvoutputlog_1400b, con); AlterRelation(((Jet_DDL)DDL).Alter_pvoutputlog_1400b2, con); } if (success) UpdateVersion("1", "4", "1", "9", con); return success; }
private bool PopulateEmptyDatabase(GenConnection con, String databaseType) { DDL DDL; if (databaseType == "MySql") if (GlobalSettings.ApplicationSettings.DatabaseVersion == "5.6") DDL = new MySql_5_6_DDL(); else DDL = new MySql_DDL(); else if (databaseType == "Jet") DDL = new Jet_DDL(); else if (databaseType == "SQLite") DDL = new SQLite_DDL(); else if (databaseType == "SQL Server") DDL = new SQLServer_DDL(); else throw new Exception("Unexpected database type: " + databaseType); bool success = true; if (success && !RelationExists(con, "devicetype")) success &= CreateRelation(DDL.CurrentTable_devicetype, con); if (success && !RelationExists(con, "device")) success &= CreateRelation(DDL.CurrentTable_device, con); if (success && !RelationExists(con, "devicefeature")) success &= CreateRelation(DDL.CurrentTable_devicefeature, con); if (success && !RelationExists(con, "devicereading_energy")) success &= CreateRelation(DDL.CurrentTable_devicereading_energy, con); if (success && !RelationExists(con, "devicedayoutput_v")) success &= CreateRelation(DDL.CurrentView_devicedayoutput_v, con); if (success && !RelationExists(con, "pvoutputlog")) success &= CreateRelation(DDL.CurrentTable_pvoutputlog, con); if (success && !RelationExists(con, "version")) success &= CreateRelation(DDL.CurrentTable_version, con); if (success) UpdateVersion("2", "0", "0", "0", con); return success; }
public bool UpdateTo_1_3_6_0(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "3", "6", "0"); if (res >= 0) return false; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_3_6_0", "Updating database structure to suit version 1.3.6.0", LogEntryType.Information); bool success = true; if (res < 0) { success &= AlterRelation(DDL.Alter_pvoutputlog_1400a, con); if (databaseType == "Jet") success &= AlterRelation(((Jet_DDL)DDL).Alter_pvoutputlog_1400a2, con); if (databaseType == "SQLite") success &= AlterRelation(((SQLite_DDL)DDL).Alter_pvoutputlog_1400b, con); success &= CreateRelation(DDL.Table_meter, con); success &= CreateRelation(DDL.Table_meterreading, con); } // the tables created here are now 1.4.0.0 compliant if (success) UpdateVersion("1", "4", "0", "0", con); return success; }
private bool RelationExists(GenConnection con, string relationName) { GenCommand cmd = null; try { cmd = new GenCommand("Select * from " + relationName + " where 0 = 1 ", con); GenDataReader dr; dr = (GenDataReader)cmd.ExecuteReader(); dr.Close(); return true; } catch (Exception e) { if (GlobalSettings.SystemServices != null) // true when service is running GlobalSettings.LogMessage("RelationExists", "Exception: " + e.Message); return false; } }
public bool UpdateTo_1_8_3_6(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "8", "3", "6"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_8_3_6", "Updating database structure to suit version 1.8.3.6", LogEntryType.Information); success = AlterRelation(DDL.Alter_cmsdata_1836, con); success &= AlterRelation(DDL.Alter_outputhistory_1836, con); if (databaseType == "SQLite") { success &= AlterRelation(((SQLite_DDL)DDL).Alter_cmsdata_1836a, con); success &= AlterRelation(((SQLite_DDL)DDL).Alter_cmsdata_1836b, con); success &= AlterRelation(((SQLite_DDL)DDL).Alter_cmsdata_1836c, con); success &= AlterRelation(((SQLite_DDL)DDL).Alter_cmsdata_1836d, con); } if (databaseType == "Jet") { success &= AlterRelation(((Jet_DDL)DDL).Alter_cmsdata_1836a, con); success &= AlterRelation(((Jet_DDL)DDL).Alter_cmsdata_1836b, con); success &= AlterRelation(((Jet_DDL)DDL).Alter_cmsdata_1836c, con); success &= AlterRelation(((Jet_DDL)DDL).Alter_cmsdata_1836d, con); } success &= Update_pvoutput_v_1836(DDL, con); if (success) UpdateVersion("1", "8", "3", "6", con); return success; }
private void UpdateVersion(String major, String minor, String release, String patch, GenConnection con) { try { GenCommand cmd = new GenCommand("delete from version", con); cmd.ExecuteNonQuery(); } catch { } String cmdStr; if (con.DBType == GenDBType.SQLServer) cmdStr = "insert into version (Major, Minor, Release, Patch) values (@Major, @Minor, @Release, @Patch)"; else cmdStr = "insert into version (Major, Minor, `Release`, Patch) values (@Major, @Minor, @Release, @Patch)"; try { GenCommand cmd = new GenCommand(cmdStr, con); cmd.AddParameterWithValue("@Major", major); cmd.AddParameterWithValue("@Minor", minor); cmd.AddParameterWithValue("@Release", release); cmd.AddParameterWithValue("@Patch", patch); cmd.ExecuteNonQuery(); Version.major = major; Version.minor = minor; Version.release = release; Version.patch = patch; } catch (Exception e) { throw new Exception("UpdateVersion: error updating version table: " + e.Message, e); } }
public bool UpdateTo_1_9_0_2(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "9", "0", "2"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_9_0_2", "Updating database structure to suit version 1.9.0.2", LogEntryType.Information); success = AlterRelation(DDL.Drop_inverter_index_1902, con); success &= AlterRelation(DDL.Create_inverter_index_1902, con); if (success) UpdateVersion("1", "9", "0", "2", con); return success; }
public void PopulateDatabaseIfEmpty(GenConnection con) { try { if (!GetCurrentVersion(con, GlobalSettings.ApplicationSettings.DatabaseType, out Version)) PopulateEmptyDatabase(con, GlobalSettings.ApplicationSettings.DatabaseType); } catch (Exception e) { throw new Exception("Exception in PopulateDatabaseIfEmpty: " + e.Message, e); } }
public bool UpdateTo_1_3_5_4(GenConnection con, String databaseType) { int res; res = Version.CompareVersion( "1", "3", "5", "4"); if (res >= 0) return false; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_3_5_4", "Updating database structure to suit version 1.3.5.4", LogEntryType.Information); if (res < 0) { CreateRelation(DDL.Table_version, con); CreateRelation(DDL.Table_pvoutputlog, con); } if (Update_pvoutput_v(DDL, con)) { UpdateVersion("1", "3", "5", "4", con); return true; } else return false; }
public GenConnection NewConnection() { // SQLite works best when all SQL uses the same connection // This avoids Database is Locked error messages // GenDatabase creates a single Global connection for SQLite use lock (DBLock) { if (GenDBType == GenericConnector.GenDBType.SQLite) { if (GlobalConnection == null) { GlobalConnection = new GenConnection(this, true); GlobalConnection.Open(); ConnectionCount++; // Connections.Add(GlobalConnection); if (UtilityLog.LogDatabase) UtilityLog.LogMessage("GenDatabase.NewConnection", "Global connection created - id: " + GlobalConnection.Id + " - thread: " + GlobalConnection.CreationThread + " - count: " + ConnectionCount, LogEntryType.Database); } return GlobalConnection; } else { GenConnection con = new GenConnection(this); con.Open(); ConnectionCount++; // Connections.Add(con); if (UtilityLog.LogDatabase) UtilityLog.LogMessage("GenDatabase.NewConnection", "Connection created - id: " + con.Id + " - thread: " +con.CreationThread + " - count: " + ConnectionCount, LogEntryType.Database); return con; } } }
public void UpdateVersion(GenConnection con) { if (GetCurrentVersion(con, GlobalSettings.ApplicationSettings.DatabaseType, out Version)) { if (GlobalSettings.ApplicationSettings.DatabaseType != "SQL Server") { UpdateTo_1_3_5_4(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_3_6_0(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_4_0_0(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_4_0_1(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_4_0_3(con, GlobalSettings.ApplicationSettings.DatabaseType); } UpdateTo_1_4_1_9(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_4_2_1(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_5_0_0(con, GlobalSettings.ApplicationSettings.DatabaseType); if (GlobalSettings.ApplicationSettings.DatabaseType == "SQL Server") UpdateTo_1_5_0_2(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_7_0_0(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_7_1_0(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_8_3_0(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_8_3_6(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_1_9_0_2(con, GlobalSettings.ApplicationSettings.DatabaseType); UpdateTo_2_0_0_0(con, GlobalSettings.ApplicationSettings.DatabaseType); } else PopulateEmptyDatabase(con, GlobalSettings.ApplicationSettings.DatabaseType); }
public bool UpdateTo_2_0_0_0(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("2", "0", "0", "0"); if (res >= 0) return false; bool success = true; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_2_0_0_0", "Updating database structure to suit version 2.0.0.0", LogEntryType.Information); success = CreateRelation(DDL.Table_devicetype_2000, con); success &= CreateRelation(DDL.Table_device_2000, con); success &= CreateRelation(DDL.Table_devicefeature_2000, con); success &= CreateRelation(DDL.Table_devicereading_energy_2000, con); success &= CreateRelation(DDL.View_devicedayoutput_v_2000, con); if (success) UpdateVersion("2", "0", "0", "0", con); return success; }
public bool UpdateTo_1_4_0_1(GenConnection con, String databaseType) { int res; res = Version.CompareVersion("1", "4", "0", "1"); if (res >= 0) return false; DDL DDL = SelectDDL(databaseType); GlobalSettings.LogMessage("UpdateTo_1_4_0_1", "Updating database structure to suit version 1.4.0.1", LogEntryType.Information); bool success = true; if (res < 0) { if (databaseType == "SQLite") { success = AlterRelation(((SQLite_DDL)DDL).Drop_meterreading, con); success &= CreateRelation(DDL.Table_meterreading, con); } else { AlterRelation(DDL.Alter_meterreading_1401a, con); AlterRelation(DDL.Alter_meterreading_1401b, con); success = true; } success &= CreateRelation(DDL.Table_meterhistory, con); } if (success) UpdateVersion("1", "4", "0", "1", con); return success; }