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);
}
