예제 #1
0
        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);
        }
예제 #2
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}");
     }
 }
예제 #3
0
        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());
        }
예제 #4
0
 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
         }
     });
 }
예제 #5
0
 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
         }
     });
 }
예제 #6
0
 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
         }
     });
 }
예제 #7
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
 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}");
     }
 }
예제 #8
0
 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
         }
     }));
 }
예제 #9
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
 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}");
     }
 }
예제 #10
0
        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);
        }
예제 #11
0
        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);
        }
예제 #12
0
 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
         }
     });
 }
예제 #13
0
 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
         }
     });
 }
예제 #14
0
 /// <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
         }
     });
 }
예제 #15
0
        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());
        }
예제 #16
0
        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());
        }
예제 #17
0
 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
         }
     });
 }
예제 #18
0
        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);
        }
예제 #19
0
        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());
        }
예제 #20
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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;
            }
        }
예제 #21
0
        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
                }
            });
        }
예제 #22
0
 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
         }
     });
 }
예제 #23
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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}");
                }
            }
        }
예제 #24
0
        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);
        }
예제 #25
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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}");
                    }
                }
            }
        }
예제 #26
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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}");
                }
            }
        }
예제 #27
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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}");
            }
        }
예제 #28
0
파일: BrookTest.cs 프로젝트: jiansoft/brook
        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();
                }
            }
        }
예제 #29
0
 /// <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
         }
     });
 }