/// <summary> /// 分页功能 /// </summary> /// <param name="tableName">表名,可以是多个表,最好用别名</param> /// <param name="primarykey">主键,可以为空,但@order为空时该值不能为空</param> /// <param name="fields">要取出的字段,可以是多个表的字段,可以为空,为空表示select * </param> /// <param name="pageSize">每页记录数</param> /// <param name="currentpage">当前页,表示第页</param> /// <param name="filter">条件,可以为空,不用填where</param> /// <param name="group">分组依据,可以为空,不用填group by</param> /// <param name="order">排序,可以为空,为空默认按主键升序排列,不用填order by</param> /// <param name="recnums">记录个数</param> /// <param name="pagenums">页数</param> /// <returns></returns> public DataTable GetPagingInfo(string tableName, string primarykey, string fields, int pageSize, int currentpage, string filter, string group, string order, out int recnums, out int pagenums) { DataTable Dt = new DataTable("data"); try { SqlParameter[] sqlParameters = { new SqlParameter("@tablenames", tableName), new SqlParameter("@primarykey", primarykey), new SqlParameter("@fields", fields), new SqlParameter("@pagesize", pageSize), new SqlParameter("@currentpage", currentpage), new SqlParameter("@filter", filter), new SqlParameter("@group", group), new SqlParameter("@order", order), new SqlParameter("@recnums", 0), new SqlParameter("@pagenums", 0) }; sqlParameters[8].Direction = ParameterDirection.Output; sqlParameters[9].Direction = ParameterDirection.Output; //只读服务器 Obj = new MSSqlDataAccess("DBConnectionString"); Dt = Obj.ExecuteDataTable("dbo.pPagingLarge", CommandType.StoredProcedure, sqlParameters); recnums = StringHelper.StringToInt(sqlParameters[8].Value.ToString()); pagenums = StringHelper.StringToInt(sqlParameters[9].Value.ToString()); return(Dt); } catch (Exception ex) { ErrorLog.WriteErrorMessage(ErrorLog.LogType.baselog, ex.ToString()); ErrorLog.WriteErrorMessage(ErrorLog.LogType.baselog, fields + "->" + tableName + "->" + filter); recnums = 0; pagenums = 0; return(Dt); } }
/// <summary> /// Get the options of Gold Point exchangeable /// </summary> /// <returns></returns> private DataTable get_Change_Gash_List() { string strSQL = string.Empty; MSSqlDataAccess mss = new MSSqlDataAccess("WebCommon"); mss.CommandType = CommandType.Text; mss.CommandTimeout = 90; SqlParameter[] spArray = new SqlParameter[] { IDataAccess.New<SqlParameter>("@GameRegion" , gi.GashRegion.ToUpper(), ParameterDirection.Input , DbType.String,2), IDataAccess.New<SqlParameter>("@PID" , PID, ParameterDirection.Input , DbType.Int16), }; strSQL = @"Select GashPoints,GamePoints,GameBonus from dbo.Buy_Point_Exchange with(nolock) where PID = @PID and GameRegion = @GameRegion"; DataTable dt = mss.ExecuteReader(strSQL, spArray); if (mss.HasException) { dt = null; Gamania.SD.IRS.ErrorLog.InsErrLog(215, "confirm.aspx", "get_Change_Gash_List", "get_Change_Gash_List", "Fail to get DB server list:" + mss.ExceptionText); } return dt; }
private static DataTable Get_Server_Name_From_DB(int intTest, out string errMsg) { errMsg = string.Empty; string strSQL = string.Empty; MSSqlDataAccess mss = new MSSqlDataAccess("GTEvent"); mss.CommandType = CommandType.Text; mss.CommandTimeout = 90; strSQL = @"SELECT ServerID,ServerName FROM dbo.ServerList WITH(NOLOCK)"; if (intTest == 1) { strSQL += " WHERE Flag = 1"; } DataTable dt = mss.ExecuteReader(strSQL); if (mss.HasException) errMsg = mss.ExceptionText; return dt; }
private void UpdateLog(string outputMsg, int logID, int flag) { MSSqlDataAccess mss = new MSSqlDataAccess("GTEvent"); mss.CommandType = CommandType.Text; DataTable dt = new DataTable(); string strSQL = "UPDATE dbo.BuyPoint_Log WITH(XLOCK) SET [Flag] = @Flag,[Memo] = @Memo WHERE Seq = @ID;"; SqlParameter[] sp = new SqlParameter[] { IDataAccess.New<SqlParameter>("@Flag" , flag , ParameterDirection.Input, DbType.Int16), IDataAccess.New<SqlParameter>("@Memo" , outputMsg, ParameterDirection.Input, DbType.String,500), IDataAccess.New<SqlParameter>("@ID" , logID, ParameterDirection.Input, DbType.Int32) }; mss.ExecuteNonQuery(strSQL, sp); if (mss.HasException) { Gamania.SD.IRS.ErrorLog.InsErrLog(215, "reconfirm.aspx", "UpdateLog", "UpdateLog", "UpdateLog Fail," + mss.ExceptionText); AlertThenClose("Sorry! System is busy now, please try again!(8)", true); } }
private bool Insert_Log(string ServerID, string AliasAccountID, string CharacterID, int gashPoints, int gamePoints, int gameBonus, out int logID) { logID = 0; MSSqlDataAccess mss = new MSSqlDataAccess("GTEvent"); mss.CommandType = CommandType.Text; DataTable dt = new DataTable(); string strSQL = @"Insert into dbo.BuyPoint_Log ([PID],[GashID],[GameID],[AliasAccountID],[ServerID],[Character],[GashPoints] ,[GamePoints],[GameBonus],[GashRegion],[Flag],[Memo],[CreateDate]) values (@PID, @GashID,@Game_ID,@AliasAccountID,@ServerID,@Character,@GashPoints,@GamePoints,@GameBonus,@GashRegion,0,'',getdate()); select scope_identity();"; SqlParameter[] sp = new SqlParameter[] { IDataAccess.New<SqlParameter>("@PID" , PID, ParameterDirection.Input, DbType.Int32), IDataAccess.New<SqlParameter>("@GashID" , gi.GashAccount, ParameterDirection.Input, DbType.String,20), IDataAccess.New<SqlParameter>("@Game_ID" , gi.GameAccount, ParameterDirection.Input, DbType.String,20), IDataAccess.New<SqlParameter>("@AliasAccountID" , AliasAccountID, ParameterDirection.Input, DbType.String,20), IDataAccess.New<SqlParameter>("@GashRegion" , gi.GashRegion, ParameterDirection.Input, DbType.String,2), IDataAccess.New<SqlParameter>("@ServerID" , ServerID, ParameterDirection.Input, DbType.String,10), IDataAccess.New<SqlParameter>("@Character" , CharacterID, ParameterDirection.Input, DbType.String,12), IDataAccess.New<SqlParameter>("@GashPoints" , gashPoints, ParameterDirection.Input, DbType.Int16), IDataAccess.New<SqlParameter>("@GamePoints" , gamePoints, ParameterDirection.Input, DbType.Int16), IDataAccess.New<SqlParameter>("@GameBonus" , gameBonus, ParameterDirection.Input, DbType.Int16) }; dt = mss.ExecuteReader(strSQL, sp); if (mss.HasException || dt == null || dt.Rows.Count < 1 || !Code.IsInt(dt.Rows[0][0].ToString(), out logID)) { Gamania.SD.IRS.ErrorLog.InsErrLog(215, "reconfirm.aspx", "Insert_Log", "Insert_Log", "Fail to InsertLog," + mss.ExceptionText); return false; } else return true; }