public List <CardItem> GetAllCard(out int total, int skip, int take, int used = 0, int telcoId = 0, int value = 0, string seri = "") { var rs = new List <CardItem>(); var sql = "SELECT * FROM [portal].[dbo].exchangeCardInfo "; var sqlTotalQuery = "SELECT Count(id) as totalRecord FROM [portal].[dbo].exchangeCardInfo"; sql += " where used = " + used; sqlTotalQuery += " where used = " + used; if (telcoId > 0) { sql += " and telcoId = " + telcoId; sqlTotalQuery += " and telcoId = " + telcoId; } if (value > 0) { sql += " and value = " + value; sqlTotalQuery += " and value = " + value; } if (!string.IsNullOrEmpty(seri)) { sql += " and serial = '" + seri + "'"; sqlTotalQuery += " and serial = '" + value + "'"; } sql += " ORDER BY id desc OFFSET " + skip + " ROWS FETCH NEXT " + take + " ROWS ONLY "; var conn = new SqlConnection(Constants.DBConnection); var MyCommand = new SqlCommand(sql, conn); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var u = new CardItem { id = MyReader.GetInt32(MyReader.GetOrdinal("id")), serial = MyReader.GetString(MyReader.GetOrdinal("serial")), //cardNo = MyReader.GetString(MyReader.GetOrdinal("cardNo"), dateInput = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")), dateInputString = MyReader.GetDateTime(MyReader.GetOrdinal("dateInput")).ToShortDateString(), dateExpiredString = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")).ToShortDateString(), dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")), used = MyReader.GetByte(MyReader.GetOrdinal("used")), value = MyReader.GetInt32(MyReader.GetOrdinal("value")), telcoId = MyReader.GetInt32(MyReader.GetOrdinal("telcoId")) }; if (used > 0) { u.dateUse = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse")); u.dateUseString = MyReader.GetDateTime(MyReader.GetOrdinal("dateUse")).ToShortDateString(); } rs.Add(u); } MyReader.Close(); MyCommand = new SqlCommand(sqlTotalQuery, conn); MyReader = MyCommand.ExecuteReader(); total = 0; while (MyReader.Read()) { total = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); return(rs); }