public int GetGameBySearch(DateTime start, DateTime end, string team, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select gametime,league,home,visitor,sn from game "); strSql.Append("where 1 = 1 "); if (start != null) { strSql.Append("and gametime >= @start "); } if (end != null) { strSql.Append("and gametime <= @end "); } if (team != "") { strSql.Append("and home like @home or visitor like @visitor "); } strSql.Append(" order by gametime desc"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@start", DbType.DateTime,start), sqliteHelper.MakeSQLiteParameter("@end", DbType.DateTime,end), sqliteHelper.MakeSQLiteParameter("@home", DbType.String,32,"%"+team+"%"), sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String,32,"%"+team+"%") }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return result; }
public int GetGameBySearch(DateTime start, DateTime end, string team, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select gametime,league,home,visitor,sn from game "); strSql.Append("where 1 = 1 "); if (start != null) { strSql.Append("and gametime >= @start "); } if (end != null) { strSql.Append("and gametime <= @end "); } if (team != "") { strSql.Append("and home like @home or visitor like @visitor "); } strSql.Append(" order by gametime desc"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@start", DbType.DateTime, start), sqliteHelper.MakeSQLiteParameter("@end", DbType.DateTime, end), sqliteHelper.MakeSQLiteParameter("@home", DbType.String, 32,"%" + team + "%"), sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String, 32,"%" + team + "%") }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return(result); }
public int GetGameDetailByKey(int gameSN, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select changetime,win,draw,lose,changeminute from gamedetail "); strSql.Append("where gamesn = @gamesn "); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32,gameSN) }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return result; }
public int GetGameByKey(int gameSN, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select * from game "); strSql.Append("where sn = @sn order by gametime desc"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32,gameSN) }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return result; }
public int GetGameDetailByKey(int gameSN, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select changetime,win,draw,lose,changeminute from gamedetail "); strSql.Append("where gamesn = @gamesn "); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32, gameSN) }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return(result); }
public int GetGameByKey(int gameSN, ref DataTable dt) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); strSql.Append("select * from game "); strSql.Append("where sn = @sn order by gametime desc"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32, gameSN) }; int result = sqliteHelper.ExecuteDataTable(strSql, para, ref dt); return(result); }
public int InsertGameInfo(Game game, DataTable dtGameDetail) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); //Hashtable sqlStringList = new Hashtable(); Dictionary <StringBuilder, SQLiteParameter[]> sqlStringList = new Dictionary <StringBuilder, SQLiteParameter[]>(); strSql = new StringBuilder(); strSql.Append("insert into game ("); strSql.Append("sn,league,home,visitor,gametime,score,result,finalSP)"); strSql.Append(" values ("); strSql.Append("@sn,@league,@home,@visitor,@gametime,@score,@result,@finalSP)"); SQLiteParameter[] parameters = { sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32, game.SN), sqliteHelper.MakeSQLiteParameter("@league", DbType.String, 16,game.League), sqliteHelper.MakeSQLiteParameter("@home", DbType.String, 32,game.Home), sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String, 32,game.Visitor), sqliteHelper.MakeSQLiteParameter("@gametime", DbType.DateTime, game.GameTime), sqliteHelper.MakeSQLiteParameter("@score", DbType.String, game.Score), sqliteHelper.MakeSQLiteParameter("@result", DbType.String, game.Result), sqliteHelper.MakeSQLiteParameter("@finalSP", DbType.String, game.FinalSP) }; sqlStringList.Add(strSql, parameters); for (int i = 0; i <= dtGameDetail.Rows.Count - 1; i++) { strSql = new StringBuilder(); strSql.Append("insert into gamedetail ("); strSql.Append("gamesn,changetime,win,draw,lose,changeminute)"); strSql.Append(" values ("); strSql.Append("@gamesn,@changetime,@win,@draw,@lose,@changeminute)"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32, game.SN), sqliteHelper.MakeSQLiteParameter("@changetime", DbType.DateTime, dtGameDetail.Rows[i][0]), sqliteHelper.MakeSQLiteParameter("@win", DbType.Decimal, dtGameDetail.Rows[i][4]), sqliteHelper.MakeSQLiteParameter("@draw", DbType.Decimal, dtGameDetail.Rows[i][5]), sqliteHelper.MakeSQLiteParameter("@lose", DbType.Decimal, dtGameDetail.Rows[i][6]), sqliteHelper.MakeSQLiteParameter("@changeminute", DbType.Int32, dtGameDetail.Rows[i][7]) }; sqlStringList.Add(strSql, para); } int result = sqliteHelper.ExecuteSqlTran(sqlStringList); return(result); }
public int InsertGameInfo(Game game, DataTable dtGameDetail) { SQLiteDBHelper sqliteHelper = new SQLiteDBHelper(); StringBuilder strSql = new StringBuilder(); //Hashtable sqlStringList = new Hashtable(); Dictionary<StringBuilder, SQLiteParameter[]> sqlStringList = new Dictionary<StringBuilder, SQLiteParameter[]>(); strSql = new StringBuilder(); strSql.Append("insert into game ("); strSql.Append("sn,league,home,visitor,gametime,score,result,finalSP)"); strSql.Append(" values ("); strSql.Append("@sn,@league,@home,@visitor,@gametime,@score,@result,@finalSP)"); SQLiteParameter[] parameters = { sqliteHelper.MakeSQLiteParameter("@sn", DbType.Int32,game.SN), sqliteHelper.MakeSQLiteParameter("@league", DbType.String,16,game.League), sqliteHelper.MakeSQLiteParameter("@home", DbType.String,32,game.Home), sqliteHelper.MakeSQLiteParameter("@visitor", DbType.String,32,game.Visitor), sqliteHelper.MakeSQLiteParameter("@gametime", DbType.DateTime,game.GameTime), sqliteHelper.MakeSQLiteParameter("@score", DbType.String,game.Score), sqliteHelper.MakeSQLiteParameter("@result", DbType.String,game.Result), sqliteHelper.MakeSQLiteParameter("@finalSP", DbType.String,game.FinalSP) }; sqlStringList.Add(strSql, parameters); for (int i = 0; i <= dtGameDetail.Rows.Count - 1; i++) { strSql = new StringBuilder(); strSql.Append("insert into gamedetail ("); strSql.Append("gamesn,changetime,win,draw,lose,changeminute)"); strSql.Append(" values ("); strSql.Append("@gamesn,@changetime,@win,@draw,@lose,@changeminute)"); SQLiteParameter[] para = { sqliteHelper.MakeSQLiteParameter("@gamesn", DbType.Int32,game.SN), sqliteHelper.MakeSQLiteParameter("@changetime", DbType.DateTime,dtGameDetail.Rows[i][0]), sqliteHelper.MakeSQLiteParameter("@win", DbType.Decimal,dtGameDetail.Rows[i][4]), sqliteHelper.MakeSQLiteParameter("@draw", DbType.Decimal,dtGameDetail.Rows[i][5]), sqliteHelper.MakeSQLiteParameter("@lose", DbType.Decimal,dtGameDetail.Rows[i][6]), sqliteHelper.MakeSQLiteParameter("@changeminute", DbType.Int32,dtGameDetail.Rows[i][7]) }; sqlStringList.Add(strSql, para); } int result = sqliteHelper.ExecuteSqlTran(sqlStringList); return result; }