public static DataTable GetEndMatchView(Guid leagueGuid)
{
// string sql = @"SELECT match.*, teamH.TeamDisplayName as HomeDisplay, teamH.TeamEnglishName as HomeEng, teamH.TeamLogo as HomeLogo, teamA.TeamLogo as AwayLogo,
// teamH.Ground as Ground, teamH.Capacity as Capacity, teamA.TeamDisplayName as AwayDisplay, teamA.TeamEnglishName as AwayEng from
// (select distinct MatchGuid from dbo.AcnCasino_CasinoItem where MatchGuid IS NOT NULL) item
// INNER JOIN acncasino_match match
// ON match.MatchGuid = item.MatchGuid
// INNER JOIN arsenal_team teamH
// ON match.home = teamH.TeamGuid
// INNER JOIN arsenal_team teamA
// ON match.away = teamA.TeamGuid
// INNER JOIN arsenal_league league
// ON match.LeagueGuid = league.LeagueGuid
// WHERE match.LeagueGuid = @leagueGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
// ORDER BY match.PlayTime DESC";
var sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.LeagueGuid = @leagueGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
ORDER BY match.PlayTime DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@leagueGuid", leagueGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetEndMatchView(Guid leagueGuid, Guid groupGuid, bool isTable)
{
string sql;
if (!isTable)
{
sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.LeagueGuid = @leagueGuid AND match.GroupGuid = @groupGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL
ORDER BY match.PlayTime DESC";
}
else
{
sql = @"SELECT match.* FROM
(SELECT DISTINCT MatchGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NOT NULL) item
INNER JOIN acncasino_match match ON match.MatchGuid = item.MatchGuid
WHERE match.LeagueGuid = @leagueGuid AND match.ResultHome IS NOT NULL AND match.ResultAway IS NOT NULL AND
(match.home IN (SELECT TeamGuid FROM dbo.Arsenal_RelationGroupTeam AS GroupTeam1 WHERE GroupGuid = @groupGuid)) AND
(match.away IN (SELECT TeamGuid FROM dbo.Arsenal_RelationGroupTeam AS GroupTeam2 WHERE GroupGuid = @groupGuid))
ORDER BY match.PlayTime DESC";
}
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@leagueGuid", leagueGuid), new SqlParameter("@groupGuid", groupGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetTopGamblerTotalBet(out int months)
{
var iDay = DateTime.Today;
months = 0;
do
{
var monthStart = iDay.AddDays(1 - iDay.Day);
var nextStart = monthStart.AddMonths(1);
var sql = $@"SELECT TOP 5 * FROM dbo.AcnCasino_Gambler gambler INNER JOIN
(SELECT UserID, SUM(ISNULL(Bet, 0)) AS TotalBetMonthly FROM dbo.AcnCasino_Bet
WHERE (Bet IS NOT NULL) AND (BetTime >= '{monthStart}') AND (BetTime < '{nextStart}')
GROUP BY UserID) bet ON gambler.UserID = bet.UserID ORDER BY TotalBetMonthly DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0 || months < -12)
{
return(ds.Tables[0]);
}
months--;
iDay = monthStart.AddMonths(-1);
} while (true);
}
// ReSharper disable once InconsistentNaming
public static DataTable GetTopGamblerRP(out int months)
{
var iDay = DateTime.Today;
months = 0;
do
{
var monthStart = iDay.AddDays(1 - iDay.Day);
var nextStart = monthStart.AddMonths(1);
var sql = $@"SELECT TOP 5 dbo.AcnCasino_Gambler.UserID, dbo.AcnCasino_Gambler.UserName, COUNT(*) AS RPBonus
FROM dbo.AcnCasino_Gambler INNER JOIN dbo.AcnCasino_Bet ON dbo.AcnCasino_Gambler.UserID = dbo.AcnCasino_Bet.UserID
WHERE (dbo.AcnCasino_Bet.Earning = 0) AND (dbo.AcnCasino_Bet.EarningDesc = 'RP+1') AND (dbo.AcnCasino_Bet.IsWin = 1) AND (BetTime >= '{monthStart}') AND (BetTime < '{nextStart}')
GROUP BY dbo.AcnCasino_Gambler.UserID, dbo.AcnCasino_Gambler.UserName ORDER BY RPBonus DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0 || months < -12)
{
return(ds.Tables[0]);
}
months--;
iDay = monthStart.AddMonths(-1);
} while (true);
}
public static DataTable GetOpenMatchView(int casinoValidDays)
{
// string sql = @"SELECT match.MatchGuid, match.Home, match.Away, match.ResultHome, match.ResultAway, match.PlayTime, match.LeagueGuid, match.LeagueName,
// match.Round, teamH.TeamDisplayName AS HomeDisplay, teamH.TeamEnglishName AS HomeEng, teamH.TeamLogo AS HomeLogo,
// teamA.TeamDisplayName AS AwayDisplay, teamA.TeamEnglishName AS AwayEng, teamA.TeamLogo AS AwayLogo, teamH.Ground as Ground, teamH.Capacity as Capacity,
// league.LeagueName, league.LeagueSeason, league.LeagueGuid, league.LeagueLogo
// FROM (SELECT DISTINCT MatchGuid FROM AcnCasino_CasinoItem
// WHERE (MatchGuid IS NOT NULL) AND (CloseTime > GETDATE()) AND (CloseTime < DATEADD(d,@casinoValidDays,GETDATE())) AND (Earning IS NULL)) AS item INNER JOIN
// AcnCasino_Match AS match ON match.MatchGuid = item.MatchGuid AND match.ResultHome IS NULL AND match.ResultAway IS NULL INNER JOIN
// Arsenal_Team AS teamH ON match.Home = teamH.TeamGuid INNER JOIN
// Arsenal_Team AS teamA ON match.Away = teamA.TeamGuid INNER JOIN
// Arsenal_League AS league ON match.LeagueGuid = league.LeagueGuid ORDER BY match.PlayTime, HomeEng";
var sql =
@"SELECT match.* FROM (SELECT DISTINCT MatchGuid FROM AcnCasino_CasinoItem WHERE (MatchGuid IS NOT NULL) AND (CloseTime > GETDATE()) AND (CloseTime < DATEADD(d, @casinoValidDays, GETDATE())) AND (Earning IS NULL)) AS item
INNER JOIN AcnCasino_Match AS match ON match.MatchGuid = item.MatchGuid AND match.ResultHome IS NULL AND match.ResultAway IS NULL ORDER BY match.PlayTime";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@casinoValidDays", casinoValidDays));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static void InsertRelationGroupTeam(Guid groupGuid, Guid teamGuid)
{
var sql = " INSERT INTO dbo.Arsenal_RelationGroupTeam (GroupGuid, TeamGuid) VALUES (@groupGuid, @teamGuid)";
SqlParameter[] para = { new SqlParameter("@groupGuid", groupGuid), new SqlParameter("@teamGuid", teamGuid) };
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static void DeleteDictionaryItem(int itemID)
{
var sql = "DELETE dbo.Arsenalcn_DictionaryItem WHERE ID = @itemID";
SqlParameter[] para = { new SqlParameter("@itemID", itemID) };
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static void DeleteLogEvent(int logID)
{
var sql = "DELETE dbo.Arsenalcn_LogEvent WHERE ID = @logID";
SqlParameter[] para = { new SqlParameter("@logID", logID) };
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static int GetGamblerCount()
{
var sql = "SELECT COUNT(ID) FROM AcnCasino_Gambler";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql);
return(obj.Equals(DBNull.Value) ? 0 : Convert.ToInt32(obj));
}
public static int GetOtherCasinoItemCount()
{
var sql = "SELECT COUNT(CasinoItemGuid) FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid IS NULL";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql);
return(obj.Equals(DBNull.Value) ? 0 : Convert.ToInt32(obj));
}
//public static float GetUserTotalBetCash(int userid)
//{
// var sql = "SELECT ISNULL(SUM(Bet), 0) FROM dbo.AcnCasino_Bet WHERE UserID = @userid";
// var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
// new SqlParameter("@userid", userid));
// return obj.Equals(DBNull.Value) ? 0f : Convert.ToSingle(obj);
//}
public static float GetUserTotalWinCash(int userid)
{
var sql = "SELECT ISNULL(SUM(Earning), 0) FROM dbo.AcnCasino_Bet WHERE UserID = @userid";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@userid", userid));
return(obj.Equals(DBNull.Value) ? 0f : Convert.ToSingle(obj));
}
//public static void CleanBet(SqlTransaction trans)
//{
// var sql =
// "DELETE FROM dbo.AcnCasino_Bet WHERE (CasinoItemGuid NOT IN (SELECT CasinoItemGuid FROM dbo.AcnCasino_CasinoItem))";
// if (trans != null)
// SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql);
// else
// SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql);
//}
public static float GetUserMatchTotalBet(int userid, Guid matchGuid)
{
var sql = @"SELECT ISNULL(SUM(bet), 0) FROM dbo.AcnCasino_Bet
WHERE UserID = @userid AND CasinoItemGuid IN (SELECT CasinoItemGuid FROM dbo.AcnCasino_CasinoItem WHERE MatchGuid = @guid)";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@userid", userid), new SqlParameter("@guid", matchGuid));
return(obj.Equals(DBNull.Value) ? 0f : Convert.ToSingle(obj));
}
public static float GetGamblerTotalBetByUserID(int userid, Guid leagueGuid)
{
var sql = @"SELECT SUM(ISNULL(Bet, 0)) AS TotalBet FROM dbo.vw_AcnCasino_BetInfo
WHERE (Earning IS NOT NULL) AND (Bet IS NOT NULL) AND (ItemType = 2) AND (UserID = @userid) AND (LeagueGuid = @leagueGuid)";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@userid", userid), new SqlParameter("@leagueGuid", leagueGuid));
return(obj.Equals(DBNull.Value) ? 0f : Convert.ToSingle(obj));
}
public static int GetRelationGroupTeamCount(Guid groupGuid, Guid teamGuid)
{
var sql =
"SELECT COUNT(*) FROM dbo.Arsenal_RelationGroupTeam WHERE TeamGuid = @teamGuid AND GroupGuid = @groupGuid";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@groupGuid", groupGuid), new SqlParameter("@teamGuid", teamGuid));
return(obj.Equals(DBNull.Value) ? 0 : Convert.ToInt32(obj));
}
public void Dispose()
{
if (SQLConn != null)
{
if (SQLConn.State == ConnectionState.Open)
{
SQLConn.Close();
}
SQLConn = null;
}
}
//public static Guid GetRandomOpenMatch()
//{
// string sql = "SELECT TOP 1 MatchGuid FROM dbo.AcnCasino_Match WHERE PlayTime > getdate() ORDER BY NEWID()";
// Guid? matchGuid = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql) as Guid?;
// return matchGuid.GetValueOrDefault(Guid.Empty);
//}
public static void UpdateMatch(Guid matchGuid, Guid home, Guid away, short?resultHome, short?resultAway,
DateTime playTime, Guid leagueGuid, string leagueName, int?round, Guid?groupGuid)
{
var sql =
"UPDATE dbo.AcnCasino_Match SET Home = @home, Away = @away, ResultHome = @resultHome, ResultAway = @resultAway, PlayTime = @playTime, LeagueGuid = @leagueGuid, LeagueName = @leagueName, Round = @round, GroupGuid = @groupGuid WHERE MatchGuid = @guid";
var para = new SqlParameter[10];
para[0] = new SqlParameter("@guid", matchGuid);
para[1] = new SqlParameter("@home", home);
para[2] = new SqlParameter("@away", away);
if (resultHome.HasValue)
{
para[3] = new SqlParameter("@resultHome", resultHome);
}
else
{
para[3] = new SqlParameter("@resultHome", DBNull.Value);
}
if (resultAway.HasValue)
{
para[4] = new SqlParameter("@resultAway", resultAway);
}
else
{
para[4] = new SqlParameter("@resultAway", DBNull.Value);
}
para[5] = new SqlParameter("@playTime", playTime);
para[6] = new SqlParameter("@leagueGuid", leagueGuid);
para[7] = new SqlParameter("@leagueName", leagueName);
if (round.HasValue)
{
para[8] = new SqlParameter("@round", round);
}
else
{
para[8] = new SqlParameter("@round", DBNull.Value);
}
if (groupGuid.HasValue)
{
para[9] = new SqlParameter("groupGuid", groupGuid);
}
else
{
para[9] = new SqlParameter("groupGuid", DBNull.Value);
}
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static DataTable GetConfigs()
{
var sql = @"SELECT * FROM dbo.Arsenalcn_Config ORDER BY ConfigSystem, ConfigKey";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
//public static DataTable GetAllRanks(int rankYear, int rankMonth)
//{
// var sql = "SELECT * FROM AcnCasino_Rank WHERE RankYear = @rankYear and RankMonth = @rankMonth";
// var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
// new SqlParameter("@rankYear", rankYear), new SqlParameter("@rankMonth", rankMonth));
// if (ds.Tables[0].Rows.Count == 0)
// return null;
// return ds.Tables[0];
//}
//public static void InsertRank(int rankYear, int rankMonth, int winnerUserID, string winnerUserName,
// float winnerProfit, float winnerTotalBet, int loserUserID, string loserUserName, float loserProfit,
// float loserTotalBet, int rpUserID, string rpUserName, int rpAmount)
//{
// var sql = @"INSERT INTO AcnCasino_Rank VALUES (
// @rankYear, @rankMonth, @winnerUserID, @winnerUserName, @winnerProfit, @winnerTotalBet,
// @loserUserID, @loserUserName, @loserProfit, @loserTotalBet, @rpUserID, @rpUserName, @rpAmount)";
// SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql,
// new SqlParameter("@rankYear", rankYear),
// new SqlParameter("@rankMonth", rankMonth),
// new SqlParameter("@winnerUserID", winnerUserID),
// new SqlParameter("@winnerUserName", winnerUserName),
// new SqlParameter("@winnerProfit", winnerProfit),
// new SqlParameter("@winnerTotalBet", winnerTotalBet),
// new SqlParameter("@loserUserID", loserUserID),
// new SqlParameter("@loserUserName", loserUserName),
// new SqlParameter("@loserProfit", loserProfit),
// new SqlParameter("@loserTotalBet", loserTotalBet),
// new SqlParameter("@rpUserID", rpUserID),
// new SqlParameter("@rpUserName", rpUserName),
// new SqlParameter("@rpAmount", rpAmount));
//}
//public static void UpdateRank(int rankYear, int rankMonth, int winnerUserID, string winnerUserName,
// float winnerProfit, float winnerTotalBet, int loserUserID, string loserUserName, float loserProfit,
// float loserTotalBet, int rpUserID, string rpUserName, int rpAmount)
//{
// var sql = @"UPDATE AcnCasino_Rank SET WinnerUserID = @winnerUserID, WinnerUserName = @winnerUserName,
// WinnerProfit = @winnerProfit, WinnerTotalBet = @winnerTotalBet, LoserUserID = @loserUserID,
// LoserUserName = @loserUserName, LoserProfit = @loserProfit, LoserTotalBet = @loserTotalBet,
// RPUserID = @rpUserID, RPUserName = @rpUserName, RPAmount = @rpAmount
// WHERE RankYear = @RankYear AND RankMonth = @RankMonth";
// SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql,
// new SqlParameter("@RankYear", rankYear),
// new SqlParameter("@RankMonth", rankMonth),
// new SqlParameter("@WinnerUserID", winnerUserID),
// new SqlParameter("@WinnerUserName", winnerUserName),
// new SqlParameter("@WinnerProfit", winnerProfit),
// new SqlParameter("@WinnerTotalBet", winnerTotalBet),
// new SqlParameter("@LoserUserID", loserUserID),
// new SqlParameter("@LoserUserName", loserUserName),
// new SqlParameter("@LoserProfit", loserProfit),
// new SqlParameter("@LoserTotalBet", loserTotalBet),
// new SqlParameter("@rpUserID", rpUserID),
// new SqlParameter("@rpUserName", rpUserName),
// new SqlParameter("@rpAmount", rpAmount));
//}
public static DataTable GetTopGamblerMonthly()
{
var sql = @"SELECT * FROM dbo.AcnCasino_Rank ORDER BY RankYear DESC, RankMonth DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0]);
}
return(null);
}
public IEnumerable <Test> Get()
{
_ConnectionFactory = new SQLConn();
IEnumerable <Test> nsctube = new List <Test>();
using (var cn = _ConnectionFactory.CreateConnection())
{
string sql = " select T6091F08 from DTB6091 where T6091F08='8937' ";
nsctube = cn.Query <Test>(sql).ToList();
}
return(nsctube);
}
public static int GetActiveClubCount()
{
var sql = "SELECT COUNT(*) FROM dbo.AcnClub_Club WHERE IsActive = 1";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(int.MinValue);
}
//public static DataRow GetLeagueByID(Guid leagueGuid)
//{
// string sql = "SELECT * FROM dbo.Arsenal_League WHERE LeagueGuid = @guid";
// DataSet ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql, new SqlParameter("@guid", leagueGuid));
// if (ds.Tables[0].Rows.Count == 0)
// return null;
// else
// return ds.Tables[0].Rows[0];
//}
//public static DataTable GetLeagueAllSeason(Guid league)
//{
// string sql = "SELECT * FROM dbo.Arsenal_League WHERE LeagueName in (SELECT LeagueName FROM dbo.Arsenal_League WHERE LeagueGuid = @guid) ORDER BY LeagueOrder, LeagueOrgName";
// DataSet ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql, new SqlParameter("@guid", league));
// if (ds.Tables[0].Rows.Count == 0)
// return null;
// else
// return ds.Tables[0];
//}
public static DataTable GetLeagues()
{
var sql = "SELECT * FROM Arsenal_League ORDER BY LeagueOrder, LeagueOrgName";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
//public static DataRow GetTeamByID(Guid teamGuid)
//{
// string sql = "SELECT * FROM dbo.Arsenal_Team WHERE TeamGuid = @guid";
// DataSet ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql, new SqlParameter("@guid", teamGuid));
// if (ds.Tables[0].Rows.Count == 0)
// return null;
// else
// return ds.Tables[0].Rows[0];
//}
public static DataTable GetTeams()
{
var sql = "SELECT * FROM Arsenal_Team ORDER BY TeamEnglishName";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static int GetActiveUserCount()
{
var sql = "SELECT COUNT(DISTINCT UserID) FROM dbo.AcnClub_RelationUserClub uc WHERE uc.IsActive = 1";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(int.MinValue);
}
public static void DeleteConfig(string configSystem, string configKey)
{
var sql = "DELETE dbo.Arsenalcn_Config WHERE ConfigSystem = @configSystem AND ConfigKey = @configKey";
SqlParameter[] para =
{
new SqlParameter("@configSystem", configSystem),
new SqlParameter("@configKey", configKey)
};
SqlHelper.ExecuteNonQuery(SQLConn.GetConnection(), CommandType.Text, sql, para);
}
public static int GetOptionTotalCount(Guid itemGuid, string optionValue)
{
var sql = @"SELECT COUNT(*) FROM dbo.AcnCasino_Bet bet
INNER JOIN dbo.AcnCasino_BetDetail detail
ON bet.[ID] = detail.[BetID]
WHERE bet.CasinoItemGuid = @guid AND detail.DetailName = @optionValue";
var obj = SqlHelper.ExecuteScalar(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@guid", itemGuid), new SqlParameter("@optionValue", optionValue));
return(obj.Equals(DBNull.Value) ? 0 : Convert.ToInt32(obj));
}
public static DataTable GetAllClubs()
{
var sql = "SELECT * FROM AcnClub_Club WHERE IsActive = 1 ORDER BY RankScore DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetChoiceOptions(Guid itemGuid)
{
var sql = "SELECT * FROM AcnCasino_ChoiceOption WHERE CasinoItemGuid = @guid ORDER BY OrderID";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@guid", itemGuid));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetDictionaryItems()
{
var sql =
"SELECT ID, DictionaryID, Code, Name, Description, CustomCode, Spell, ShortSpell, ParentID, OrderNum FROM Arsenalcn_DictionaryItem ORDER BY ID";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataTable GetUserVideos()
{
var sql = @"SELECT ID, UserID, UserName, VideoGuid, ActiveDate, UserDesc, IsPublic
FROM AcnClub_RelationUserVideo ORDER BY ActiveDate DESC";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql);
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0]);
}
public static DataRow GetDictionaryItemByID(int itemID)
{
var sql = "SELECT * FROM dbo.Arsenalcn_DictionaryItem WHERE ID = @itemID";
var ds = SqlHelper.ExecuteDataset(SQLConn.GetConnection(), CommandType.Text, sql,
new SqlParameter("@itemID", itemID));
if (ds.Tables[0].Rows.Count == 0)
{
return(null);
}
return(ds.Tables[0].Rows[0]);
}
