public static int DoSettle(long matchID, int oddsPlayType)
{
try
{
var list = Brook.Load(DbName.DevMainDb).Execute(
CommandType.StoredProcedure,
"[dbo].[USP_Backend_ReSettle]",
new DbParameter[]
{
new SqlParameter("@intMatchID", SqlDbType.BigInt)
{
Value = matchID
},
new SqlParameter("@intOddsPlayType", SqlDbType.Int)
{
Value = oddsPlayType
},
new SqlParameter("@strResult", SqlDbType.VarChar)
{
Size = 10,
Direction = ParameterDirection.Output
}
});
return(list);
}
catch (Exception e)
{
Log.Error(e, $"{e.StackTrace} {e.Message}");
}
return(0);
}
public static void DeleteAndBackupOdds(long matchId)
{
try
{
Brook.Load(DbName.DevMainDb).Execute(
CommandType.StoredProcedure,
"[dbo].[USP_Odds_Replay]",
new DbParameter[]
{
new SqlParameter("@intMatchID", SqlDbType.BigInt)
{
Value = matchId
},
new SqlParameter("@strResult", SqlDbType.VarChar)
{
Size = 10,
Direction = ParameterDirection.Output
}
});
}
catch (Exception e)
{
Log.Error(e, $"{e.StackTrace} {e.Message}");
}
}
public static ScoutStruct[] FetchLiveScout(long matchid)
{
var startTime = Brook.Load(DbName.DevBetradar).One <DateTime>(
"SELECT MIN([ServerTime]) FROM [Betradar].[dbo].[LiveScoutSoccerEvent] WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
}
});
var serverTime = startTime.AddMinutes(15);
var list = Brook.Load(DbName.DevBetradar).Query <ScoutStruct>(
"SELECT [MatchId],[EventId],[TypeId]AS[TypeId],[ScoutFeedType],[BetStatus],[Info],[Side],[MatchTime],[MatchScore],[ServerTime],[Player1],[Player2],[PosX],[PosY],[ExtraInfo],[CreateTime] FROM [Betradar].[dbo].[LiveScoutSoccerEvent] WHERE [MatchId] = @MatchId AND [ServerTime] >= @ServerTime order by [EventId];",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@ServerTime", SqlDbType.DateTime)
{
Value = serverTime
}
});
return(list.ToArray());
}
public static void UpdateMatchStatus(long matchid, long statusCode)
{
Brook.Load(DbName.DevBetradar).Execute(
"UPDATE [dbo].[LiveScore] SET [StatusCode]= @StatusCode WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@StatusCode", SqlDbType.Int)
{
Value = statusCode
}
});
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Matches] SET [StatusCode]= @StatusCode WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@StatusCode", SqlDbType.Int)
{
Value = statusCode
}
});
}
public static void SwitchOddsActive(long matchid)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Odds] SET [Status] = 2 WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
}
});
}
public static void UpdateMatchCurrentPeriodStart(long matchId)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Matches] SET [CurrentPeriodStart] = GETDATE() WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchId
}
});
}
public void TestValue()
{
using (var db = Brook.Load("sqlserver"))
{
var maxId = db.Value <long>("[dbo].[sp_MaxAccountId_Sel]");
TestContext.WriteLine($"MaxId = {maxId}");
maxId = db.Value <long>(CommandType.StoredProcedure, "[dbo].[sp_MaxAccountId_Sel]");
TestContext.WriteLine($"MaxId = {maxId}");
maxId = db.Value <long>(15, CommandType.StoredProcedure, "[dbo].[sp_MaxAccountId_Sel]");
TestContext.WriteLine($"MaxId = {maxId}");
}
}
public static string FetchMatchTournamentZh(long matchid)
{
return(Brook.Load(DbName.DevMainDb).One <string>(
"SELECT [TournamentZH] AS [TournamentZh] FROM [MainDB].[dbo].[TB_Matches] WHERE [MatchID] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
}
}));
}
public void TestOne()
{
using (var db = Brook.Load("sqlserver"))
{
var maxId = db.One <long>(
@"DECLARE @intId BIGINT;SELECT @intId = MAX(id) FROM [test].[dbo].[account]; SELECT @intId;");
TestContext.WriteLine($"MaxId = {maxId}");
maxId = db.One <long>(15, CommandType.Text,
@"DECLARE @intId BIGINT;SELECT @intId = MAX(id) FROM [test].[dbo].[account]; SELECT @intId;");
TestContext.WriteLine($"MaxId = {maxId}");
}
}
public static DataTable FetchOddsByBetradar(long matchid)
{
var dt = Brook.Load(DbName.BetaBetradar).Table(160,
CommandType.Text,
@"select JsonData,MsgNr from Betradar.dbo.LiveOddsData (nolock) where MatchID = @MatchId and CodeEvent = 'OddsChange' and Msgnr > 0 order by MsgNr asc",
new DbParameter[] {
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
});
return(dt);
}
public static List <ReplayMatchList> MatchListEnableGet(List <long> matches)
{
string matchesID = "";
foreach (var id in matches)
{
matchesID += id + ",";
}
if (matchesID.Length > 0)
{
matchesID = matchesID.Substring(0, matchesID.Length - 1);
}
string sqlStr = "select aaa.MatchID,aaa.TournamentZH,aaa.Team1ZH,aaa.Team2ZH,aaa.MatchScore, ";
sqlStr += "Convert(Varchar(19), aaa.MatchDate, 120)MatchDate,Case when bbb.CNT > 1 Then '全場/半場' else '全場' End PlayType ";
sqlStr += "from dbo.TB_Matches (nolock) aaa ";
sqlStr += "left join ";
sqlStr += "( ";
sqlStr += " select MatchID , count(0) CNT ";
sqlStr += " from ( ";
sqlStr += " select MatchID , OddsPlayType ";
sqlStr += " from ( ";
sqlStr += " select distinct a.MatchID, a.OddsID,b.OddsPlayType ";
sqlStr += " from dbo.TB_Odds (nolock) a ";
sqlStr += " join dbo.TB_OddsTypes (nolock) b ";
sqlStr += " on a.OddsID = b.OddsID ";
sqlStr += " where a.MatchID in ( "+ matchesID + " ) ";
sqlStr += " ) aa ";
sqlStr += " group by MatchID , OddsPlayType ";
sqlStr += " ) bb ";
sqlStr += " group by MatchID ";
sqlStr += ") bbb ";
sqlStr += "on aaa.MatchID = bbb.MatchID ";
sqlStr += "where aaa.MatchID in ( " + matchesID + " ) ";
sqlStr += "Order by aaa.MatchDate asc; ";
var dt = Brook.Load(DbName.DevMainDb).Table(
160,
CommandType.Text,
sqlStr,
new DbParameter[] {
});
var returnData = JsonConvert.DeserializeObject <List <ReplayMatchList> >(JsonConvert.SerializeObject(dt));
return(returnData);
}
public static void UpdateMatcScore(long matchId, string score)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Matches] SET [MatchScore] = @MatchScore WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchId
},
new SqlParameter("@MatchScore", SqlDbType.VarChar, 32)
{
Value = score
}
});
}
public static void UpdateOddsStatus(long matchId, int status)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Odds] SET [Status] = @Status WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchId
},
new SqlParameter("@Status", SqlDbType.Int)
{
Value = status
}
});
}
/// <summary>
/// 刪除走地事件
/// </summary>
public static void DeleteScoutEvent(ScoutStruct scout)
{
Brook.Load(DbName.DevBetradar).Execute(
"DELETE FROM [Betradar].[dbo].[LiveScoutSoccerEvent] WHERE [MatchId] = @MatchId AND [EventId] >= @EventId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = scout.MatchId
},
new SqlParameter("@EventId", SqlDbType.BigInt)
{
Value = scout.EventId
}
});
}
public static long[] FetchLiveScout()
{
var t = Brook.Load(DbName.DevBetradar).Table(
160,
CommandType.Text,
"SELECT [MatchId] FROM [Betradar].[dbo].[LiveScoutSoccerEvent] WITH(NOLOCK) GROUP BY [MatchID];",
new DbParameter[] { });
var list = new List <long>();
foreach (DataRow row in t.Rows)
{
var id = long.Parse(row["MatchId"].ToString());
list.Add(id);
}
return(list.ToArray());
}
public static IEnumerable <long> FetchOdds()
{
var t = Brook.Load(DbName.DevMainDb).Table(
160,
CommandType.Text,
"SELECT [MatchID] FROM [MainDB].[dbo].[TB_Odds] WITH(NOLOCK) WHERE MsgNr < 10000000 GROUP BY [MatchID] order by [MatchID];",
new DbParameter[] { });
var list = new List <long>();
foreach (DataRow row in t.Rows)
{
var id = long.Parse(row["MatchId"].ToString());
list.Add(id);
}
return(list.ToArray());
}
public static void UpdateMatchReplayTitel(long matchid, string title)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Matches] SET [TournamentZH] = @Title WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@Title", SqlDbType.NVarChar)
{
Size = 64,
Value = title
}
});
}
public static List <ReplayMatchList> MatchListGet(string matchDate)
{
var dt = Brook.Load(DbName.DevMainDb).Table(
160,
CommandType.Text,
@"select MatchID,TournamentZH,Team1ZH,Team2ZH,MatchScore,Convert(Varchar(19), MatchDate, 120)MatchDate from dbo.TB_Matches (nolock)
where Convert(Varchar(10), MatchDate, 120) = @MatchDate
Order by MatchDate asc; ",
new DbParameter[] {
new SqlParameter("@MatchDate", SqlDbType.VarChar)
{
Value = matchDate
},
});
var returnData = JsonConvert.DeserializeObject <List <ReplayMatchList> >(JsonConvert.SerializeObject(dt));
return(returnData);
}
public static OddsStruct[] FetchOdds(long matchid)
{
var list = Brook.Load(DbName.DevMainDb).Query <OddsStruct>(
@"SELECT [Tid],[MatchId],[MsgNr],a.[OddsID] AS [OddsId],cast(b.OddsTypeID as varchar(20)) as OddsTypeID,
cast(b.Subtype as varchar(20)) as Subtype,[Odds],[SpecialBetValue],[OddsID_ori] AS [OddsIdOri],[Score],[ForTheRest],[CreateTime]
FROM [MainDB].[dbo].[TB_Odds] (nolock) a
left join [MainDB].[dbo].[TB_OddsTypes] (nolock) b
on a.OddsID = b.OddsID " +
"WHERE [MatchId] = @MatchId AND MsgNr < 10000000 order by [MsgNr],CreateTime asc;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
}
});
return(list.ToArray());
}
public void TestQuery()
{
try
{
using (var db = Brook.Load("sqlserver"))
{
db.Query <Account>(15, CommandType.Text, ConvertSeparate(Query, DatabaseType.SQLServer));
}
var sw = new Stopwatch();
sw.Reset();
sw.Start();
Brook.Load("sqlserver").Query <Account>(15, CommandType.Text, ConvertSeparate(Query, DatabaseType.SQLServer));
sw.Stop();
TestContext.WriteLine($"Query 总毫秒:{sw.ElapsedMilliseconds}");
sw.Reset();
sw.Start();
Brook.Load("sqlserver").Query <Account>(15, CommandType.Text, ConvertSeparate(Query, DatabaseType.SQLServer));
sw.Stop();
TestContext.WriteLine($"Query 总毫秒:{sw.ElapsedMilliseconds}");
sw.Reset();
sw.Start();
Brook.Load("sqlserver").Query <Account>(15, CommandType.Text, ConvertSeparate(Query, DatabaseType.SQLServer));
sw.Stop();
TestContext.WriteLine($"Query 总毫秒:{sw.ElapsedMilliseconds}");
sw.Reset();
sw.Start();
var l1 = Brook.Load("sqlserver").Query <Account>(15, CommandType.Text, ConvertSeparate(Query, DatabaseType.SQLServer));
sw.Stop();
TestContext.WriteLine($"Query 总毫秒:{sw.ElapsedMilliseconds}");
TestContext.WriteLine($"Account : {l1.Count}");
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public static void SwitchOddsActive(long matchid, /*long oddsId,*/ long msgNr)
{
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Odds] SET [Status] = 1 WHERE [MatchId] = @MatchId /*AND [OddsID] = @OddsID*/ AND [MsgNr] = @MsgNr;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
// new SqlParameter("@OddsID", SqlDbType.BigInt)
// {
// Value = oddsId
// },
new SqlParameter("@MsgNr", SqlDbType.Int)
{
Value = msgNr
}
});
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Odds] SET [Status] = 2 WHERE [MatchId] = @MatchId /*AND [OddsID] = @OddsID*/ AND [MsgNr] < @MsgNr;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
// new SqlParameter("@OddsID", SqlDbType.Int)
// {
// Value = oddsId
// },
new SqlParameter("@MsgNr", SqlDbType.Int)
{
Value = msgNr
}
});
}
public static void UpdateMatchReplayStatus(long matchid, long statusCode)
{
Brook.Load(DbName.DevBetradar).Execute(
"UPDATE [dbo].[LiveScore] SET [MatchDate] = @MatchDate ,[StatusCode]= @StatusCode WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@MatchDate", SqlDbType.DateTime)
{
Value = DateTime.Now
},
new SqlParameter("@StatusCode", SqlDbType.Int)
{
Value = statusCode
}
});
Brook.Load(DbName.DevMainDb).Execute(
"UPDATE [dbo].[TB_Matches] SET [MatchDate] = @MatchDate ,[PreMatchDate]= @MatchDate,[CurrentPeriodStart] = @MatchDate,[StatusCode]= @StatusCode,[LiveBet]=1 WHERE [MatchId] = @MatchId;",
new DbParameter[]
{
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = matchid
},
new SqlParameter("@MatchDate", SqlDbType.DateTime)
{
Value = DateTime.Now
},
new SqlParameter("@StatusCode", SqlDbType.Int)
{
Value = statusCode
}
});
}
public void TestLoad()
{
foreach (var dbName in _sqlType)
{
DatabaseType dt;
switch (dbName)
{
case "mssql":
dt = DatabaseType.SQLServer;
break;
case "posql":
dt = DatabaseType.PostgreSQL;
break;
case "mysql":
dt = DatabaseType.MySQL;
break;
default:
dt = DatabaseType.SQLite;
break;
}
var providerNme = Enum.GetName(typeof(DatabaseType), dt);
TestContext.WriteLine($"{providerNme}");
try
{
Brook.LoadFromConnectionString(Brook.Load(dbName).ConnectionSource, dt);
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
}
}
public static int InsertAccount(string account)
{
try
{
var list = Brook.Load(DbName.DevMainDb).Execute(
CommandType.StoredProcedure,
"[dbo].[USP_Account_Create]",
new DbParameter[]
{
new SqlParameter("@strAccount", SqlDbType.VarChar)
{
Value = account
},
new SqlParameter("@intAccountType", SqlDbType.TinyInt)
{
Value = 1
},
new SqlParameter("@strPassword", SqlDbType.VarChar)
{
Value = "3c3839b6a1c387864ca752e654101a52"
},
new SqlParameter("@strResult", SqlDbType.VarChar)
{
Size = 10,
Direction = ParameterDirection.Output
}
});
return(list);
}
catch (Exception e)
{
Log.Error(e, $"{e.StackTrace} {e.Message}");
}
return(0);
}
public void TestCrud()
{
foreach (var dbName in _sqlType)
{
DatabaseType dt;
switch (dbName)
{
case "mssql":
//continue;
dt = DatabaseType.SQLServer;
break;
case "posql":
dt = DatabaseType.PostgreSQL;
break;
case "mysql":
dt = DatabaseType.MySQL;
break;
default:
continue;
/*dt = DatabaseType.SQLite;
* using (var db = jIAnSoft.Brook.Mapper.Brook.Load(dbName))
* {
* db.Execute(@"CREATE TABLE IF NOT EXISTS account (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT not null, email TEXT not null);");
* }
*
* break;*/
}
var providerNme = Enum.GetName(typeof(DatabaseType), dt);
using (var db = Brook.Load(dbName))
{
var id = db.One <long>(
ConvertSeparate(GetLastId(Insert, dt), dt),
new[]
{
db.Parameter("@name", $"{Generator.CreateId()}"),
db.Parameter("@email", $"{Generator.CreateId()}@{providerNme}.com")
});
TestContext.WriteLine($"{providerNme} insert id:{id}");
var first = db.First <Account>(
ConvertSeparate(FindById, dt),
new[] { db.Parameter("@id", id, DbType.Int32) });
TestContext.WriteLine($"{providerNme} first {first.Id} {first.Name} {first.Email}");
var query = db.Query <Account>(ConvertSeparate(Query, dt));
foreach (var acc in query)
{
TestContext.WriteLine($"{providerNme} query {acc.Id} {acc.Name} {acc.Email}");
}
var ds = db.DataSet(
ConvertSeparate(FindById, dt),
new[] { db.Parameter("@id", 1, DbType.Int32) });
foreach (DataRow row in ds.Tables[0].Rows)
{
TestContext.WriteLine($"{providerNme} dataSet {row["Id"]} {row["name"]} {row["email"]}");
}
var table = db.Table(
ConvertSeparate(FindById, dt),
new[] { db.Parameter("@id", 1, DbType.Int32) });
foreach (DataRow row in table.Rows)
{
TestContext.WriteLine($"{providerNme} table {row["Id"]} {row["name"]} {row["email"]}");
}
var count = db.Execute(
CommandType.Text,
ConvertSeparate(Delete, dt),
new[] { db.Parameter("@id", id, DbType.Int64) });
TestContext.WriteLine($"{providerNme} delete id:{id} count:{count}");
count = db.Execute(
1,
CommandType.Text,
ConvertSeparate(Delete, dt),
new[] { db.Parameter("@id", id, DbType.Int64) });
TestContext.WriteLine($"{providerNme} delete id:{id} count:{count}");
count = db.Execute(
1,
CommandType.Text,
ConvertSeparate(Delete, dt),
new[] { db.Parameter("@id", id, DbType.Int64) });
TestContext.WriteLine($"{providerNme} delete id:{id} count:{count}");
count = db.Execute(ConvertSeparate(Update, dt));
TestContext.WriteLine($"{providerNme} update count:{count}");
var counts = db.Execute(
ConvertSeparate(Delete, dt),
new List <DbParameter[]> {
new[] { db.Parameter("@id", id, DbType.Int64) }
});
foreach (var i in counts)
{
TestContext.WriteLine($"{providerNme} delete id:{id} count:{i}");
}
}
}
}
public void TestException()
{
foreach (var dbName in _sqlType)
{
DatabaseType dt;
switch (dbName)
{
case "mssql":
continue;
/*dt = DatabaseType.SQLServer;
* break;*/
case "posql":
dt = DatabaseType.PostgreSQL;
break;
case "mysql":
dt = DatabaseType.MySQL;
break;
default:
continue;
/*dt = DatabaseType.SQLite;
* using (var db = jIAnSoft.Brook.Mapper.Brook.Load(dbName))
* {
* db.Execute(
* @"CREATE TABLE IF NOT EXISTS account (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT not null, email TEXT not null);");
* }
*
* break;*/
}
var providerNme = Enum.GetName(typeof(DatabaseType), dt);
TestContext.WriteLine($"{providerNme}");
try
{
Brook.Load("");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
using (var db = Brook.Load(dbName))
{
db.Execute("SELECT * FROM A", new[]
{
db.Parameter("@name", $"{Generator.CreateId()}"),
db.Parameter("@email", $"{Generator.CreateId()}@{providerNme}.com")
});
}
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).Table("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).DataSet("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).First <Account>("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).Query <Account>("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).One <long>("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
try
{
Brook.Load(dbName).Value <long>("SELECT * FROM A");
}
catch (Exception e)
{
TestContext.WriteLine($"{providerNme} {e.Message}");
}
}
}
public void TestExecute()
{
using (var db = Brook.Load("sqlserver"))
{
var name = $"{Generator.CreateId()}";
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.SQLServer), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[1] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
name = $"{Generator.CreateId()}";
db.Execute(CommandType.Text, ConvertSeparate(Insert, DatabaseType.SQLServer), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[2] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
name = $"{Generator.CreateId()}";
db.Execute(ConvertSeparate(Insert, DatabaseType.SQLServer), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[3] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
name = $"{Generator.CreateId()}";
db.Execute(ConvertSeparate(Insert, DatabaseType.SQLServer), new List <DbParameter[]>
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[4] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
name = $"{Generator.CreateId()}";
var count = db.Execute(CommandType.Text, ConvertSeparate(Insert, DatabaseType.SQLServer),
new List <DbParameter[]>
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine(
$"[5] Id = {account.Id} Name = {account.Name} Email = {account.Email} count:{JsonConvert.SerializeObject(count)}");
name = $"{Generator.CreateId()}";
count = db.Execute(5, CommandType.Text, ConvertSeparate(Insert, DatabaseType.SQLServer),
new List <DbParameter[]>
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine(
$"[6] Id = {account.Id} Name = {account.Name} Email = {account.Email} count:{JsonConvert.SerializeObject(count)}");
name = $"{Generator.CreateId()}";
db.Execute($"insert into [account] ([name],[email])values('{name}', '{name}@sqlserver.com');");
account = db.First <Account>(
ConvertSeparate(FindByName, DatabaseType.SQLServer),
new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[7] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
}
}
public void TestTransaction()
{
var sqlCmd = $"{ConvertSeparate(Insert, DatabaseType.PostgreSQL)}; update account set \"name\" = 'Eddie in transaction' where id in (SELECT currval(pg_get_serial_sequence('account','id')));";
var result = Brook.Load("posql").Transaction(sqlCmd, new List <DbParameter[]> {
new[]
{
Brook.Load("posql").Parameter("@name", "QQ1"),
Brook.Load("posql").Parameter("@email", $"*****@*****.**")
}, new[]
{
Brook.Load("posql").Parameter("@name", "QQ2"),
Brook.Load("posql").Parameter("@email", $"*****@*****.**")
}
});
if (!result.Ok)
{
TestContext.WriteLine($"Why:{result.Err}");
}
result = Brook.Load("posql").Transaction("insert into account (name,email)values('Eddie', '@email');");
if (!result.Ok)
{
TestContext.WriteLine($"Why:{result.Err}");
}
var qq = Brook.Load("posql").First <Account>("SELECT id AS \"Id\", name AS \"Name\", email AS \"Email\",time AS \"Time\" FROM account order by id desc limit 1;");
SqlMapper db = null;
try
{
db = Brook.Load("posql");
var name = $"我是交易1-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var t = db.Table(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[Table] Id = {t.Rows[0]["Id"]} Name = {t.Rows[0]["Name"]} Email = {t.Rows[0]["Email"]} ");
db.CommitTransaction();
name = $"我是交易2-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var account = db.First <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[First] Id = {account.Id} Name = {account.Name} Email = {account.Email}");
var accounts = db.Query <Account>(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[Query] Id = {accounts[0].Id} Name = {accounts[0].Name} Email = {accounts[0].Email}");
db.CommitTransaction();
name = $"我是交易3-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
db.RollbackTransaction();
name = $"我不是交易1-{DateTime.Now:HHmmss}";
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
var ds = db.DataSet(ConvertSeparate(FindByName, DatabaseType.PostgreSQL), new[] { db.Parameter("@name", name) });
TestContext.WriteLine($"[DataSet] Id = {ds.Tables[0].Rows[0]["Id"]} Name = {ds.Tables[0].Rows[0]["Name"]} Email = {ds.Tables[0].Rows[0]["Email"]}");
name = $"我是交易4-{DateTime.Now:HHmmss}";
db.BeginTransaction();
db.Execute(15, CommandType.Text, ConvertSeparate(Insert, DatabaseType.PostgreSQL), new[]
{
new[]
{
db.Parameter("@name", name),
db.Parameter("@email", $"{name}@sqlserver.com")
}
});
db.ChangeDatabase("postgres");
//throw new Exception("QQ");
}
catch (Exception e)
{
TestContext.WriteLine(e);
if (db != null)
{
db.RollbackTransaction();
db.Dispose();
}
}
}
/// <summary>
/// 寫入走地事件
/// </summary>
/// <param name="scout"></param>
public static void InsertScoutEvent(ScoutStruct scout)
{
Brook.Load(DbName.DevBetradar).Execute(
"INSERT INTO [dbo].[LiveScoutSoccerEvent]([ScoutFeedType],[MatchId],[BetStatus],[EventId],[Info],[Side],[TypeId],[MatchTime],[MatchScore],[ServerTime],[Player1],[Player2],[PosX],[PosY],[ExtraInfo],[CreateTime])VALUES(@ScoutFeedType,@MatchId,@BetStatus,@EventId,@Info,@Side,@TypeId,@MatchTime,@MatchScore,@ServerTime,@Player1,@Player2,@PosX,@PosY,@ExtraInfo,@CreateTime);",
new DbParameter[]
{
new SqlParameter("@ScoutFeedType", SqlDbType.Int)
{
Value = scout.ScoutFeedType
},
new SqlParameter("@MatchId", SqlDbType.BigInt)
{
Value = scout.MatchId
},
new SqlParameter("@BetStatus", SqlDbType.Int)
{
Value = scout.BetStatus
},
new SqlParameter("@EventId", SqlDbType.BigInt)
{
Value = scout.EventId
},
new SqlParameter("@Info", SqlDbType.NVarChar, -1)
{
Value = scout.Info
},
new SqlParameter("@Side", SqlDbType.Int)
{
Value = scout.Side
},
new SqlParameter("@TypeId", SqlDbType.Int)
{
Value = scout.TypeId
},
new SqlParameter("@MatchTime", SqlDbType.VarChar, 24)
{
Value = scout.MatchTime
},
new SqlParameter("@MatchScore", SqlDbType.VarChar, 12)
{
Value = scout.MatchScore
},
new SqlParameter("@ServerTime", SqlDbType.DateTime)
{
Value = scout.ServerTime
},
new SqlParameter("@Player1", SqlDbType.BigInt)
{
Value = scout.Player1
},
new SqlParameter("@Player2", SqlDbType.BigInt)
{
Value = scout.Player2
},
new SqlParameter("@PosX", SqlDbType.Int)
{
Value = scout.PosX
},
new SqlParameter("@PosY", SqlDbType.Int)
{
Value = scout.PosY
},
new SqlParameter("@ExtraInfo", SqlDbType.BigInt)
{
Value = scout.ExtraInfo
},
new SqlParameter("@CreateTime", SqlDbType.DateTime)
{
Value = scout.CreateTime
}
});
}