public static long GetLastInsertId(IDbConnection conn, IDbTransaction transaction, string sIdentityName) { switch (ConnectionManager.Dialect) { #if MySQL case DatabaseDialect.MySql: object objLastInsertId = DataCopy.ExecuteScalar(conn, transaction, "SELECT last_insert_id()"); return(Convert.ToInt64(objLastInsertId)); #endif case DatabaseDialect.MsSql: case DatabaseDialect.PgSql: object objLastInsertId = DataCopy.ExecuteScalar(conn, transaction, "SELECT lastval()"); return(Convert.ToInt64(objLastInsertId)); #if SQLite case DatabaseDialect.LtSql: #endif default: Debug.Assert(false); break; } return(0); }
public void ATestModeMatchesCreationTest() { ConfigurationManager.AppSettings["CreateDatabase"] = "1"; StationSettingsUtils.m_sStartupPath = Path.GetDirectoryName(Assembly.GetAssembly(typeof(DatabaseTests)).Location); string sDatabaseResourcesFolderName = StationSettingsUtils.StartupPath; string sTestMatchesFileName = Path.Combine(sDatabaseResourcesFolderName, "CreateTestMatches.config"); string CreateTestMatchesStatement = System.IO.File.ReadAllText(sTestMatchesFileName); DatabaseManager.DropDatabase(false); DatabaseManager.EnsureDatabase(false); try { DataCopy.ExecuteScalar(CreateTestMatchesStatement); } catch { } long count; var sql = "select count(" + MatchLn.TableSpec.IdentityNames.First() + ") FROM " + MatchLn.TableSpec.TableName + " where updateid = 71"; long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count); Assert.IsTrue(count > 0); DatabaseManager.DropDatabase(false); }
public void CreateTestMatches() { string sDatabaseResourcesFolderName = StationSettingsUtils.StartupPath; string sTestMatchesFileName = Path.Combine(sDatabaseResourcesFolderName, "DatabaseResources\\CreateTestMatches.config"); string CreateTestMatchesStatement = System.IO.File.ReadAllText(sTestMatchesFileName); //XmlDocument TestMatchesDocument = new XmlDocument(); //TestMatchesDocument.Load(sTestMatchesFileName); //string CreateTestMatchesStatement = XmlHelper.GetElementInnerText(TestMatchesDocument, "configuration/createTestSoccerMatch"); try { using (var conn2 = ConnectionManager.GetConnection()) { using (IDbTransaction transaction = conn2.BeginTransaction()) { DataCopy.ExecuteScalar(conn2, transaction, CreateTestMatchesStatement); transaction.Commit(); } } } catch (Exception e) { m_logger.Error(e.Message, e); } }
public void SetVesrion(string version) { try { using (var conn2 = ConnectionManager.GetConnection()) { DataCopy.ExecuteScalar(conn2, null, "Insert into version (id,version) values (1,'{0}')", version); } } catch (Exception) { } }
public void ClearDatabase() { ConfigurationManager.AppSettings["CreateDatabase"] = "1"; StationSettingsUtils.m_sStartupPath = Path.GetDirectoryName(Assembly.GetAssembly(typeof(DatabaseTests)).Location); DatabaseManager.EnsureDatabase(false); DatabaseCache.EnsureDatabaseCache(); LineSr.EnsureFromCache(); UpdateFileEntrySr updateFileEntrySr = new UpdateFileEntrySr(); updateFileEntrySr.DataSyncCacheType = eDataSyncCacheType.Statistic.ToString(); UpdateFileEntrySr updateFileEntrySr2 = new UpdateFileEntrySr(); updateFileEntrySr2.DataSyncCacheType = eDataSyncCacheType.Match.ToString(); updateFileEntrySr.Save(); updateFileEntrySr2.Save(); long count; var sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Statistic.ToString() + "'"; long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count); Assert.IsTrue(count > 0); sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Match.ToString() + "'"; long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count); Assert.IsTrue(count > 0); DbManager.Instance.DeleteOldObjects(); sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Statistic.ToString() + "'"; long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count); Assert.IsTrue(count > 0); sql = "select count(" + UpdateFileEntrySr.TableSpec.IdentityNames.First() + ") FROM " + UpdateFileEntrySr.TableSpec.TableName + " where DataSyncCacheType = '" + eDataSyncCacheType.Match.ToString() + "'"; long.TryParse(DataCopy.ExecuteScalar(sql).ToString(), out count); Assert.AreEqual(0, count); DatabaseManager.DropDatabase(false); }
public static void DropDatabase(bool isTestMode) { string sDatabaseName = ConfigurationManager.AppSettings["database_name"]; string sConnectionString = ConfigurationManager.AppSettings["database_connection_string"]; if (isTestMode) { sConnectionString.Replace(sDatabaseName, "test_" + sDatabaseName); sDatabaseName = "test_" + sDatabaseName; } try { using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName))) { //conn.Open(); eExistResult eerDatabase = DatabaseManager.DoesDatabaseExist(conn, sDatabaseName); ExcpHelper.ThrowIf(eerDatabase == eExistResult.Error, "Cannot recognize if database '{0}' exists.", sDatabaseName); if (eerDatabase == eExistResult.Exists) { try { DataCopy.ExecuteScalar(conn, null, "select pg_terminate_backend(procpid) from pg_stat_activity where datname='{0}';", sDatabaseName); using (IDbConnection conn2 = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName))) { DataCopy.ExecuteScalar(conn2, null, DatabaseManager.Schema.DeleteDatabaseStatement, sDatabaseName); } m_logger.InfoFormat("Database '{0}' Successfully deleted", sDatabaseName); } catch (Exception excp) { m_logger.Error(ExcpHelper.FormatException(excp, "EnsureDatabase() ERROR - cannot delete {0} Database '{1}'", ConnectionManager.Dialect, sDatabaseName), excp); throw; } } } } catch (System.Exception excpInner) { string sError = string.Format("Cannot verify PostGreSQL Server. Either Server is not installed or invalid priveleges ({0}).\r\n{1}\r\n{2}", sConnectionString, excpInner.Message, excpInner.StackTrace); //MessageBox.Show(sError, "Station Start Error", MessageBoxButtons.OK, MessageBoxIcon.Error); throw new Exception(sError, excpInner); } }
public static eExistResult DoesDatabaseExist(IDbConnection conn, string sDatabaseName) { try { object objCount = DataCopy.ExecuteScalar(conn, null, DatabaseManager.Schema.DatabaseExistsStatement, sDatabaseName.ToLowerInvariant()); int iCount = Convert.ToInt32(objCount); m_logger.InfoFormat("DatabaseManager.DoesDatabaseExist(DatabaseName='{0}') result = {1}", sDatabaseName, iCount > 0 ? "Exists" : "DOES NOT Exist"); return(iCount > 0 ? eExistResult.Exists : eExistResult.DoesNotExist); } catch (Exception excp) { m_logger.Excp(excp, "DoesDatabaseExist(DatabaseName='{0}') ERROR", sDatabaseName); } return(eExistResult.Error); }
public override void Save(IDbConnection conn, IDbTransaction transaction) { ExcpHelper.ThrowIf(this.BtrCompetitorID == 0, "BtrCompetitorID is Invalid"); if (this.IsModified) { base.Save(conn, transaction); if (this.CompetitorID == 0) { object objCompetitorId = DataCopy.ExecuteScalar(conn, transaction, "SELECT CompetitorId FROM Competitor WHERE BtrCompetitorID = {0}", this.BtrCompetitorID); this.CompetitorID = Convert.ToInt64(objCompetitorId); Debug.Assert(this.CompetitorID > 0); } this.IsModified = false; } }
public bool DeleteOldObjects() { try { using (IDbConnection conn = new NpgsqlConnection(DalStationSettings.Instance.ConnectionString.Replace(DalStationSettings.Instance.DatabaseName, ConnectionManager.SystemDatabaseName))) { conn.Open(); if (DatabaseManager.DoesDatabaseExist(conn, DalStationSettings.Instance.DatabaseName) == DatabaseManager.eExistResult.Exists) { if (DatabaseManager.OneOfRequiredTablesExist() != DatabaseManager.eExistResult.DoesNotExist) { ExcpHelper.ThrowIf(DatabaseManager.DeleteFromTables(), "DeleteOldObjects() ERROR. Cannot clear tables."); ExcpHelper.ThrowIf(!DatabaseManager.DropTables(), "DeleteOldObjects() ERROR. Cannot drop tables."); } else { using (var conn2 = ConnectionManager.GetConnection()) { //foreach (var sqlDelete in DatabaseManager.Schema.DeleteFromTablesStatement.Split(';')) //{ // DataCopy.ExecuteScalar(conn2, null, sqlDelete); //} DataCopy.ExecuteScalar(conn2, null, "Delete From UpdateFileEntry Where DataSyncCacheType not in('" + eDataSyncCacheType.Resources + "','" + eDataSyncCacheType.Statistic + "')"); foreach (var sqlDelete in DatabaseManager.Schema.DropTablesStatement.Split(';')) { DataCopy.ExecuteScalar(conn2, null, sqlDelete); } } } } } return(true); } catch (Exception e) { m_logger.Error(e.Message, e); } return(false); }
public static bool DeleteFromTables() { bool bResult = false; ConnectionManager.ProcessTransaction(delegate(IDbConnection conn, IDbTransaction transaction) { try { DataCopy.ExecuteScalar(conn, transaction, DatabaseManager.Schema.DeleteFromTablesStatement); bResult = true; } catch (Exception excp) { m_logger.Error(ExcpHelper.FormatException(excp, "DeleteFromTables() ERROR"), excp); throw; } }); return(bResult); }
private void FillFromDatabase() { try { using (IDbConnection conn = ConnectionManager.GetConnection()) { //FillFromDataTable<RelatedStringLn>(conn, "SELECT * FROM RelatedString", m_diAll.RelatedStringsByKey); FillFromDataTable <TaggedStringLn>(conn, "SELECT * FROM strings", m_diAll.TaggedStrings); FillFromDataTable <TimeTypeLn>(conn, "SELECT * FROM time_type", m_diAll.TimeTypes); FillFromDataTable <ScoreTypeLn>(conn, "SELECT * FROM score_type", m_diAll.ScoreTypes); FillFromDataTable <BetTypeLn>(conn, "SELECT * FROM bet_type", m_diAll.BetTypes); FillFromDataTable <BetDomainTypeLn>(conn, "SELECT * FROM betdomain_type", m_diAll.BetDomainTypes); FillFromDataTable <GroupLn>(conn, "SELECT * FROM Groups", m_diAll.Groups); FillFromDataTable <CompetitorLn>(conn, "SELECT * FROM Competitor", m_diAll.Competitors); FillFromDataTable <MatchLn>(conn, "SELECT * FROM Matches", m_diAll.Matches); FillFromDataTable <CompetitorToOutrightLn>(conn, "SELECT * FROM competitor_to_outright", m_diAll.CompetitorsToOutright); FillFromDataTable <LiveMatchInfoLn>(conn, "SELECT * FROM LiveMatchInfo", m_diAll.LiveMatchInfos); FillFromDataTable <MatchToGroupLn>(conn, "SELECT * FROM MatchToGroup", m_diAll.MatchesToGroups); FillFromDataTable <BetDomainLn>(conn, "SELECT * FROM BetDomain", m_diAll.BetDomains); FillFromDataTable <OddLn>(conn, "SELECT * FROM Odd", m_diAll.Odds); FillFromDataTable <MatchResultLn>(conn, "SELECT * FROM MatchResult", m_diAll.MatchResults); FillFromDataTable <ResourceRepositoryLn>(conn, "SELECT * FROM ResourseRepository", m_diAll.Resources); FillFromDataTable <ResourceAssignmentLn>(conn, "SELECT * FROM ResourceAssignment", m_diAll.ResourceAssignments); FillFromDataTable <CompetitorInfosLn>(conn, "SELECT * FROM competitorinfos", m_diAll.CompetitorInfos); FillFromDataTable <MatchInfosLn>(conn, "SELECT * FROM matchinfos", m_diAll.MatchInfos); FillFromDataTable <TournamentInfosLn>(conn, "SELECT * FROM tournamentinfos", m_diAll.TournamentInfos); FillFromDataTable <LiabilityLn>(conn, "SELECT * FROM conffactor", m_diAll.Liabilities); FillFromDataTable <TournamentMatchLocksLn>(conn, "SELECT * FROM tournament_match_lock", m_diAll.TournamentMatchLocks); FillFromDataTable <LanguageLn>(conn, "SELECT * FROM languages", m_diAll.Languages); FillFromDataTable <MultistringGroupLn>(conn, "SELECT * FROM MultistringGroup", m_diAll.MultistringGroups); } long.TryParse(DataCopy.ExecuteScalar("select MAX(GroupId) FROM Groups").ToString(), out m_lNextGroupId); } catch (Exception excp) { m_logger.Excp(excp, "DatabaseCache.FillFromDatabase() ERROR"); throw; } }
private static void EnsureDatabaseImp(bool isTestMode) { string sDatabaseName = ""; string sConnectionString = ""; if (isTestMode) { sDatabaseName = "test_" + ConnectionManager.DatabaseName; sConnectionString = ConnectionManager.ConnectionString.Replace(ConnectionManager.DatabaseName, sDatabaseName); ConnectionManager.DatabaseName = sDatabaseName; ConnectionManager.ConnectionString = sConnectionString; } else { sDatabaseName = ConnectionManager.DatabaseName; sConnectionString = ConnectionManager.ConnectionString; } try { int counter = 0; while (true) { try { using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName))) { conn.Open(); } break; } catch (Exception) { Thread.Sleep(1000); counter++; if (counter > 15) { throw; } } } using (IDbConnection conn = new NpgsqlConnection(sConnectionString.Replace(sDatabaseName, ConnectionManager.SystemDatabaseName))) { conn.Open(); eExistResult eerDatabase = DatabaseManager.DoesDatabaseExist(conn, sDatabaseName); ExcpHelper.ThrowIf(eerDatabase == eExistResult.Error, "Cannot recognize if database '{0}' exists.", sDatabaseName); if (eerDatabase == eExistResult.DoesNotExist) { try { DataCopy.ExecuteScalar(conn, null, DatabaseManager.Schema.CreateDatabaseStatement, sDatabaseName); m_logger.InfoFormat("Database '{0}' Successfully created", sDatabaseName); } catch (Exception excp) { m_logger.Excp(excp, "EnsureDatabase() ERROR - cannot create {0} Database '{1}'", ConnectionManager.Dialect, sDatabaseName); throw; } } } foreach (TableStatement ts in DatabaseManager.Schema.TableStatements) { using (IDbConnection conn = ConnectionManager.GetConnection()) { eExistResult eerTable = DoesTableExist(conn, ts.TableName); ExcpHelper.ThrowIf(eerTable == eExistResult.Error, "Cannot recognize if table '{0}' exists.", ts.TableName); foreach (SqlStatement statement in ts.Statements) { if (statement.SqlExecuteType == eSqlExecuteType.Always || // Always (eerTable == eExistResult.Exists && statement.SqlExecuteType == eSqlExecuteType.Exists) || // Table exists (eerTable == eExistResult.DoesNotExist && statement.SqlExecuteType == eSqlExecuteType.DoesNotExist)) // Table does not exist { try { DataCopy.ExecuteScalar(statement.Statement); m_logger.InfoFormat("{0} Successfully executed", statement); } catch (Exception excp) { m_logger.Excp(excp, "EnsureDatabase() ERROR - cannot execute {0} for {1} Database '{2}'", statement, ConnectionManager.Dialect, sDatabaseName); throw; } } else { m_logger.InfoFormat("{0} is skipped. Table {1}.", statement, eerTable); } } } } } catch (System.Exception excpInner) { string sError = string.Format("Cannot verify Server. Either Server is not installed or invalid priveleges ({0}).\r\n{1}\r\n{2}", sConnectionString, excpInner.Message, excpInner.StackTrace); //MessageBox.Show(sError, "Station Start Error", MessageBoxButtons.OK, MessageBoxIcon.Error); throw new Exception(sError, excpInner); } }