private static DataTable GetEmptyDataTableByNameImp(IDbConnection conn, IDbTransaction transaction, string sTableName)
{
string sFormat = null;
switch (ConnectionManager.Dialect)
{
case DatabaseDialect.MsSql: sFormat = "SELECT TOP 0 * FROM {0}"; break;
case DatabaseDialect.MySql: sFormat = "SELECT * FROM {0} LIMIT 0"; break;
case DatabaseDialect.PgSql: sFormat = "SELECT * FROM {0} _LIMIT_ 0"; break;
case DatabaseDialect.LtSql: sFormat = "SELECT * FROM {0} LIMIT 0"; break;
default:
Debug.Assert(false);
break;
}
DataTable dt = DataCopy.GetDataTable(conn, transaction, sFormat, sTableName);
//dt = dt.Clone();
return(dt);
}
public virtual void Update(IDbConnection conn, IDbTransaction transaction)
{
ExcpHelper.ThrowIf(this.Table == null, "TableSpec is not specified for type {0}", this.GetType());
using (DataTable dtUpdate = DataCopyTables.GetEmptyDataTableByName(conn, transaction, this.Table.TableName))
{
using (IDbCommand cmdUpdate = DataCopy.GenerateUpdateCommand(conn, transaction, dtUpdate, this.Table))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daUpdate = dsp as IDbDataAdapter;
Debug.Assert(daUpdate != null);
daUpdate.UpdateCommand = cmdUpdate;
DataRow dr = this.CreateDataRow(dtUpdate);
dtUpdate.Rows.Add(dr);
dtUpdate.AcceptChanges();
dr.SetModified();
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dtUpdate);
daUpdate.Update(ds);
}
}
}
}
}
private bool DeleteObjects <T>(LineObjectCollection <T> locLineObjects, TableSpecification ts) where T : IRemovableLineObject <T>
{
// DK - At the moment this method works ONLY with BIGINT type primary keys
if (locLineObjects != null)
{
IdentityList il = new IdentityList();
try
{
foreach (IRemovableLineObject <T> obj in locLineObjects.Values)
{
il.AddUnique(obj.RemoveId);
}
DataCopy.ExecuteScalar(m_conn, m_transaction, "DELETE FROM {0} WHERE {1} IN ({2})", ts.TableName, ts.IdentityNames[0], il.FormatIds());
UpdateStatistic us = m_uss.EnsureStatistic(ts.TableName);
us.DeleteCount = il.Count;
return(true);
}
catch (Exception excp)
{
m_logger.Excp(excp, "DeleteObjects<{0}> ERROR. ObjectIds: {1}", typeof(T), il.FormatIds());
throw;
}
}
return(false);
}
internal protected IdentityCache(DataCopy dsbc, string sTableName, string sLocalIdColumnName, string sSvrIdColumnName)
{
m_dsbc = dsbc;
m_sTableName = sTableName;
m_sLocalIdColumnName = sLocalIdColumnName;
m_sSvrIdColumnName = sSvrIdColumnName;
}
public static IDbCommand GenerateInsertCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts)
{
string sTemplate = @"
INSERT INTO
[{0}] ({1})
VALUES
({2})
";
IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty);
dbc.CommandTimeout = 1000;
List <string> lColStrings = new List <string>();
List <string> lSetStrings = new List <string>();
foreach (DataColumn dc in dt.Columns)
{
if (ts.IsAutoGeneratedIdentity && ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant()))
{
continue;
}
switch (ConnectionManager.Dialect)
{
case DatabaseDialect.MsSql:
case DatabaseDialect.PgSql:
lColStrings.Add(string.Format("[{0}]", dc.ColumnName));
lSetStrings.Add(string.Format("@{0}", dc.ColumnName));
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
break;
case DatabaseDialect.MySql:
case DatabaseDialect.LtSql:
lColStrings.Add(dc.ColumnName);
lSetStrings.Add(string.Format("@{0}", dc.ColumnName));
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
break;
default:
Debug.Assert(false);
break;
}
}
string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",", lColStrings.ToArray()),
string.Join(",", lSetStrings.ToArray()));
dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery);
return(dbc);
}
public static PlayerTicket GetTicket(string number, string checksum)
{
using (
DataTable dt =
DataCopy.GetDataTable("SELECT * FROM PlayerTickets WHERE number = '{0}' AND checksum='{1}'",
number, checksum))
{
return(dt.Rows != null && dt.Rows.Count == 1 ? CreateFromDataRow(dt.Rows[0]) : null);
}
}
public static List <PlayerSession> GetActivePlayerSessions()
{
var result = new List <PlayerSession>();
using (
DataTable dt =
DataCopy.GetDataTable(
"SELECT * FROM PlayerSessions WHERE closed = false ORDER BY PlayerSessionId DESC"))
{
result.AddRange(from DataRow dr in dt.Rows select CreateFromDataRow(dr));
}
return(result);
}
public static StationAppConfigSr GetValueByName(string propertyName)
{
using (DataTable dt = DataCopy.GetDataTable("SELECT * FROM StationAppConfig WHERE propertyname = '{0}'", propertyName))
{
foreach (DataRow dr in dt.Rows)
{
StationAppConfigSr sac = StationAppConfigSr.CreateFromDataRow(dr);
return(sac);
}
}
return(null);
}
public static IList <PlayerTicket> GetTicketsForSession(string sessionId)
{
var result = new List <PlayerTicket>();
using (
DataTable dt =
DataCopy.GetDataTable(
"SELECT pt.* FROM PlayerTickets AS pt LEFT JOIN PlayerSessions AS ps ON pt.playersessionid = ps.playersessionid WHERE ps.sessionid ='" +
sessionId + "' AND pt.closed = false ORDER BY PlayerTicketId DESC"))
{
result.AddRange(from DataRow dr in dt.Rows select CreateFromDataRow(dr));
}
return(result);
}
public static void UpdateLanguages()
{
if (UpdateLanguagesEvent != null)
{
try
{
DataCopy.UpdateLanguagesEvent(null);
}
catch (Exception excp)
{
m_logger.ErrorFormat("Event DataCopy.UpdateLanguagesEvent() ERROR:\r\n{0}\r\n{1}", excp, excp.Message, excp.StackTrace);
}
}
}
public static StationAppConfigSr LoadStationAppConfigByQuery(string sQuery)
{
using (DataTable dt = DataCopy.GetDataTable(sQuery))
{
if (dt != null && dt.Rows.Count > 0)
{
StationAppConfigSr sac = StationAppConfigSr.CreateFromDataRow(dt.Rows[0]);
return(sac);
}
}
return(null);
}
public static void UpdateProgressBar(int?total)
{
if (DataCopy.UpdateProgressBarEvent != null)
{
try
{
DataCopy.UpdateProgressBarEvent(total);
}
catch (Exception excp)
{
m_logger.ErrorFormat("Event DataCopy.UpdateProgressBarEvent({0}) ERROR:\r\n{1}\r\n{2}", excp, total, excp.Message, excp.StackTrace);
}
}
}
public static List <StationAppConfigSr> GetAllSettings()
{
List <StationAppConfigSr> lResult = new List <StationAppConfigSr>();
using (DataTable dt = DataCopy.GetDataTable("SELECT * FROM StationAppConfig ORDER BY StationAppConfigID"))
{
foreach (DataRow dr in dt.Rows)
{
StationAppConfigSr sac = StationAppConfigSr.CreateFromDataRow(dr);
lResult.Add(sac);
}
}
return(lResult);
}
public static long GetMaxLocalIdentity(IDbConnection conn, IDbTransaction transaction, string sTableName, string sLocalIdColumnName)
{
try
{
object objMax = DataCopy.ExecuteScalar(conn, transaction, "SELECT MAX({0}) FROM {1}", sLocalIdColumnName, sTableName);
return(objMax == DBNull.Value ? 0 : Convert.ToInt64(objMax));
}
catch (Exception excp)
{
m_logger.ErrorFormat("GetMaxLocalIdentity({0}, {1}) ERROR:{2}\r\n{3}", excp, sTableName, sLocalIdColumnName, excp.Message, excp.StackTrace);
string sError = string.Format("Cannot get local MAX Identity for [{0}][{1}]", sLocalIdColumnName, sTableName);
throw new System.Exception(sError);
}
}
public static List <TransactionQueueSr> GetTransactionQueueList()
{
var lResult = new List <TransactionQueueSr>();
using (DataTable dt = DataCopy.GetDataTableWithSqlParams("SELECT * FROM " + TableSpec.TableName, new List <IDbDataParameter>()))
{
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
TransactionQueueSr tqs = TransactionQueueSr.CreateFromDataRow(dr);
lResult.Add(tqs);
}
}
}
return(lResult);
}
public static List <StationCashSr> GetStationCashListByQuery(string sQuery, List <IDbDataParameter> lParams)
{
List <StationCashSr> lResult = new List <StationCashSr>();
using (DataTable dt = DataCopy.GetDataTableWithSqlParams(sQuery, lParams))
{
if (dt != null)
{
foreach (DataRow dr in dt.Rows)
{
StationCashSr tqs = StationCashSr.CreateFromDataRow(dr);
lResult.Add(tqs);
}
}
}
return(lResult);
}
public static IDbCommand GenerateUpdateCommand(IDbConnection connection, IDbTransaction transaction, DataTable dt, TableSpecification ts)
{
string sTemplate = @"
UPDATE
[{0}]
_SET_
{1}
WHERE
{2}
";
IDbCommand dbc = SqlObjectFactory.CreateDbCommand(connection, transaction, string.Empty);
dbc.CommandTimeout = 1000;
List <string> lSetStrings = new List <string>();
List <string> lIdentityStrings = new List <string>();
//sIdentityName = sIdentityName.ToLowerInvariant();
foreach (DataColumn dc in dt.Columns)
{
if (ts.IdentityNames.Contains(dc.ColumnName.ToLowerInvariant()))
{
lIdentityStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName));
}
else
{
lSetStrings.Add(string.Format(" [{0}] = @{0}", dc.ColumnName));
}
dbc.Parameters.Add(SqlObjectFactory.CreateParameter(dc.ColumnName, null, dc.ColumnName));
}
string sCmdQuery = string.Format(sTemplate, ts.TableName, string.Join(",\r\n", lSetStrings.ToArray()),
string.Join("\r\nAND\r\n", lIdentityStrings.ToArray()));
dbc.CommandText = DataCopy.CheckQueryByDialect(sCmdQuery);
return(dbc);
}
public void Fill(bool bUseMaxLocalIdentity)
{
const int SHOW_DUPLICATE_LIMIT = 10;
if (string.IsNullOrEmpty(m_sLocalIdColumnName) || string.IsNullOrEmpty(m_sSvrIdColumnName))
{
return;
}
try
{
using (DataTable dt = DataCopy.GetDataTable(m_dsbc.Connection, m_dsbc.Transaction, "SELECT {0}, {1} FROM {2} WHERE {0} > {3}", m_sLocalIdColumnName, m_sSvrIdColumnName, m_sTableName, bUseMaxLocalIdentity ? m_lMaxLocalIdentity : ERROR_IDENTITY))
//using (DataTable dt = DataCopy.GetDataTable(m_dsbc.Dialect, m_dsbc.Connection, m_dsbc.Transaction, "SELECT {0}, {1} FROM {2}", m_sLocalIdColumnName, m_sSvrIdColumnName, m_sTableName))
{
//this.Clear();
ErrorList elDuplicates = new ErrorList();
foreach (DataRow dr in dt.Rows)
{
FillFromDataRow(dr, elDuplicates);
}
if (elDuplicates.Count > 0)
{
m_dsbc.Warnings.AddFormat("Duplicate Server ID(s) found for Table: [{0}], [{1}]: {2}", m_sTableName, m_sSvrIdColumnName, elDuplicates.ToFormatStringWithLimit(", ", SHOW_DUPLICATE_LIMIT));
}
// this.Trace();
}
}
catch (Exception excp)
{
string sError = string.Format("Error FillIdentityCache ({0}, {1}, {2}, {3})", m_sTableName, m_sLocalIdColumnName, m_sSvrIdColumnName, m_lMaxLocalIdentity);
m_logger.ErrorFormat("{0}:\r\n{1}\r\n{2}", excp, sError, excp.Message, excp.StackTrace);
throw new System.Exception(sError);
}
}
public virtual void Insert(IDbConnection conn, IDbTransaction transaction)
{
ExcpHelper.ThrowIf(this.Table == null, "TableSpec is not specified for type {0}", this.GetType());
using (DataTable dtInsert = DataCopyTables.GetEmptyDataTableByName(conn, transaction, this.Table.TableName))
{
using (IDbCommand cmdInsert = DataCopy.GenerateInsertCommand(conn, transaction, dtInsert, this.Table))
{
using (IDisposable dsp = SqlObjectFactory.CreateDbAdapter(null) as IDisposable)
{
IDbDataAdapter daInsert = dsp as IDbDataAdapter;
Debug.Assert(daInsert != null);
daInsert.InsertCommand = cmdInsert;
dtInsert.AcceptChanges();
DataRow dr = this.CreateDataRow(dtInsert);
dtInsert.Rows.Add(dr);
using (DataSet ds = new DataSet())
{
ds.Tables.Add(dtInsert);
daInsert.Update(ds);
}
if (this.Table.IsAutoGeneratedIdentity)
{
Debug.Assert(this.Table.IdentityNames.Count > 0);
m_objJustInsertedIdentity = ConnectionManager.GetLastInsertId(conn, transaction, this.Table.IdentityNames[0]);
}
}
}
}
}
public static PlayerSession GetSession(string sessionId)
{
using (
var dt = DataCopy.GetDataTable("SELECT * FROM PlayerSessions WHERE sessionid = '{0}'", sessionId))
return(dt.Rows != null && dt.Rows.Count == 1 ? CreateFromDataRow(dt.Rows[0]) : null);
}
/*
* private static bool DeleteMatches(IDbConnection conn, IDbTransaction transaction)
* {
* LineObjectCollection<MatchLn> locMatches = LineSr.Instance.ObjectsToRemove.GetLineObjectCollection<MatchLn>();
*
* if (locMatches != null && locMatches.Count > 0)
* {
* IdentityList il = new IdentityList();
*
* foreach (MatchLn mtch in locMatches.Values)
* {
* il.AddUnique(mtch.MatchId);
* }
*
* string sMatchIds = il.FormatIds();
*
* DataCopy.ExecuteScalar(conn, transaction, DELETE_MATCH_ODDS_QUERY, sMatchIds);
* DataCopy.ExecuteScalar(conn, transaction, DELETE_MATCH_BETDOMAIN_QUERY, sMatchIds);
* DataCopy.ExecuteScalar(conn, transaction, DELETE_MATCH_TO_GROUP_QUERY, sMatchIds);
* DataCopy.ExecuteScalar(conn, transaction, DELETE_LIVE_MATCH_INFO_QUERY, sMatchIds);
* DataCopy.ExecuteScalar(conn, transaction, DELETE_MATCHES_QUERY, sMatchIds);
*
* return true;
* }
*
* return false;
* }
*/
public static eFileSyncResult UpdateDatabase(IDbConnection conn, eUpdateType eut, string sProviderDescription, UpdateStatistics us)
{
eFileSyncResult fsr = eFileSyncResult.Failed;
DateTime dtStart = DateTime.Now;
string sErrorString = string.Empty;
DataCopy dc = null;
string sUpdateDescription = eut.ToString();
CheckTime ct = new CheckTime("UpdateAll({0}) Entered", sUpdateDescription);
DictionaryOfLineObjectCollectionLight dlocToModify = LineSr.Instance.NewOrChangedObjects;
DictionaryOfLineObjectCollectionLight dlocToDelete = LineSr.Instance.ObjectsToRemove;
try
{
using (IDbTransaction transaction = conn.BeginTransaction())
{
dc = new DataCopy(conn, transaction, sUpdateDescription);
try
{
UpdatesLn updatesLn = new UpdatesLn(eut, string.Format("Saving {0} and Deleting {1} line tables", dlocToModify.Count, dlocToDelete.Count), sProviderDescription);
updatesLn.Save(conn, transaction);
Debug.Assert(updatesLn.UpdateId > 0);
// Insert or Update objects
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <TaggedStringLn>(), TaggedStringLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <TimeTypeLn>(), TimeTypeLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <ScoreTypeLn>(), ScoreTypeLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <BetTypeLn>(), BetTypeLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <BetDomainTypeLn>(), BetDomainTypeLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <GroupLn>(), GroupLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <CompetitorLn>(), CompetitorLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <MatchLn>(), MatchLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <CompetitorToOutrightLn>(), CompetitorToOutrightLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <LiveMatchInfoLn>(), LiveMatchInfoLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <MatchResultLn>(), MatchResultLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <MatchToGroupLn>(), MatchToGroupLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <BetDomainLn>(), BetDomainLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <ResourceRepositoryLn>(), ResourceRepositoryLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <ResourceAssignmentLn>(), ResourceAssignmentLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <CompetitorInfosLn>(), CompetitorInfosLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <MatchInfosLn>(), MatchInfosLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <TournamentInfosLn>(), TournamentInfosLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <OddLn>(), OddLn.TableSpec, updatesLn);
//dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection<LiabilityLn>(), LiabilityLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <LanguageLn>(), LanguageLn.TableSpec, updatesLn);
dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection <MultistringGroupLn>(), MultistringGroupLn.TableSpec, updatesLn);
//dc.InsertOrUpdate(dlocToModify.GetLineObjectCollection<TournamentMatchLocksLn>(), TournamentMatchLocksLn.TableSpec, updatesLn);
// Delete Objects
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <OddLn>(), OddLn.TableSpec);
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <BetDomainLn>(), BetDomainLn.TableSpec);
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <MatchToGroupLn>(), MatchToGroupLn.TableSpec);
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <LiveMatchInfoLn>(), LiveMatchInfoLn.TableSpec);
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <CompetitorToOutrightLn>(), CompetitorToOutrightLn.TableSpec);
dc.DeleteObjects(dlocToDelete.GetLineObjectCollection <MatchLn>(), MatchLn.TableSpec);
if (dc.Statistics.IsInsrtedOrUpdatedOrDeleted)
{
// DK: If You like to test data import then uncomment line bellow:
// throw new SystemException("Developer Exception: Always rollback transaction for test purposes");
transaction.Commit();
fsr = eFileSyncResult.Succeeded;
ct.AddEvent("Commited");
}
else
{
transaction.Rollback();
fsr = eFileSyncResult.Skipped;
ct.AddEvent("Rolled Back");
}
if (us != null)
{
us.Append(dc.Statistics);
}
}
catch (Exception excp)
{
transaction.Rollback();
m_logger.Excp(excp, "UpdateAllWithSqlBulkCopy General Transaction Exception");
}
finally
{
DateTime dtEnd = DateTime.Now;
if (fsr != eFileSyncResult.Skipped)
{
// DK: Let's write to log ONLY Succeeded or Failed file syncrhonization info.
string sInfo = @"
{7} UpdateAllWithSqlBulkCopy({0}) {1}
{2}
{3}
Start: {4};
End: {5};
Time: {6};
";
m_logger.InfoFormat(
sInfo, // Format
sProviderDescription, // Provider Description
fsr, // Result
sErrorString, // Errors if any
dc.Statistics, // Statistic
dtStart,
dtEnd,
dtEnd - dtStart, // Time
sUpdateDescription); // LiveBet or PreMatch
}
else
{
m_logger.InfoFormat("{0} UpdateAllWithSqlBulkCopy Skipped. Time: {1}", sUpdateDescription, dtEnd - dtStart);
}
if (fsr == eFileSyncResult.Succeeded)
{
}
ct.AddEvent("UpdateAll({0}) completed", sUpdateDescription);
ct.Info(m_logger);
}
}
// }
}
catch (Exception excp)
{
sErrorString += ExcpHelper.FormatException(excp, "UpdateAllWithSqlBulkCopy General Exception");
m_logger.Error(sErrorString, excp);
}
return(fsr);
}
public virtual DataRow CreateDataRow(DataTable dtSample)
{
//throw new System.Exception(string.Format("CreateDataRow() is not overriden for {0}", this.GetType()));
return(DataCopy.CreateDataRow(dtSample, this, new ErrorList()));
}
public override void Delete(IDbConnection conn, IDbTransaction transaction)
{
DataCopy.ExecuteScalar(conn, transaction, "DELETE FROM {0} WHERE {1} = {2}", this.Table.TableName, this.Table.IdentityNames[0], this.ORMID);
}
