public GiftCodeResult GetAll(bool used, int skip, int take) { var rs = new GiftCodeResult(); List <GiftCodeItem> data = new List <GiftCodeItem>(); int totalRecord = 0; string sql = ""; if (used) { if (used == true) { sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id WHERE gc.user_id > 0 ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; } else { sql = "SELECT u.username, gc.* FROM [newDB].[dbo].giftcode gc inner join [newDB].[dbo].g_user u on gc.user_id = u.user_id ORDER BY gc.id DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; } SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { var item = new GiftCodeItem { code = MyReader.GetString(MyReader.GetOrdinal("code")), ID = MyReader.GetInt32(MyReader.GetOrdinal("id")), dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")), dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired")), name = MyReader.GetString(MyReader.GetOrdinal("name")), value = MyReader.GetInt32(MyReader.GetOrdinal("value")), user_id = MyReader.GetInt32(MyReader.GetOrdinal("user_id")), username = MyReader.GetString(MyReader.GetOrdinal("username")), fullname = MyReader.GetString(MyReader.GetOrdinal("fullname")), isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash")) }; item.sdateCreated = item.dateCreated.ToShortDateString(); item.sdateExpired = item.dateExpired.ToShortDateString(); data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 1"; MyCommand = new SqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); } else { sql = "SELECT * FROM [portal].[dbo].giftcode WHERE used = 0 ORDER BY dateCreated DESC OFFSET @skip ROWS FETCH NEXT @take ROWS ONLY"; SqlConnection conn = new SqlConnection(Constants.DBConnection); SqlCommand MyCommand = new SqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); SqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { GiftCodeItem item = new GiftCodeItem(); item.code = MyReader.GetString(MyReader.GetOrdinal("code")); item.ID = MyReader.GetInt32(MyReader.GetOrdinal("id")); item.dateCreated = MyReader.GetDateTime(MyReader.GetOrdinal("dateCreated")); item.dateExpired = MyReader.GetDateTime(MyReader.GetOrdinal("dateExpired"));; item.used = false; item.name = MyReader.GetString(MyReader.GetOrdinal("name")); item.value = MyReader.GetInt32(MyReader.GetOrdinal("value")); item.isVCash = MyReader.GetInt32(MyReader.GetOrdinal("isVCash")); item.user_id = 0; item.username = ""; item.fullname = ""; item.sdateCreated = item.dateCreated.ToShortDateString(); item.sdateExpired = item.dateExpired.ToShortDateString(); data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from [portal].[dbo].giftcode WHERE used = 0"; MyCommand = new SqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32(MyReader.GetOrdinal("totalRecord")); } MyReader.Close(); conn.Close(); } rs.totalRecord = totalRecord; rs.data = data; return(rs); }
public GiftCodeResult GetAll(bool used, int skip, int take) { var rs = new GiftCodeResult(); List <GiftCodeItem> data = new List <GiftCodeItem>(); int totalRecord = 0; string sql = ""; if (used) { sql = "SELECT u.username, u.fullname, gc.* FROM newDB.giftcode gc inner join newDB.view_user_info u on gc.user_id = u.id WHERE used = 1 ORDER BY gc.id DESC LIMIT @skip, @take"; MySqlConnection conn = new MySqlConnection(Constants.DBConnection); MySqlCommand MyCommand = new MySqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); MySqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { GiftCodeItem item = new GiftCodeItem(); item.code = MyReader.GetString("code"); item.ID = MyReader.GetInt32("id"); item.dateCreated = MyReader.GetDateTime("dateCreated"); item.dateExpired = MyReader.GetDateTime("dateExpired");; item.used = false; item.name = MyReader.GetString("name"); item.value = MyReader.GetInt32("value"); item.user_id = MyReader.GetInt32("user_id"); item.username = MyReader.GetString("username"); item.fullname = MyReader.GetString("fullname"); data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from newDB.giftcode WHERE used = 1"; MyCommand = new MySqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32("totalRecord"); } MyReader.Close(); conn.Close(); } else { sql = "SELECT * FROM newDB.giftcode WHERE used = 0 ORDER BY id DESC LIMIT @skip, @take"; MySqlConnection conn = new MySqlConnection(Constants.DBConnection); MySqlCommand MyCommand = new MySqlCommand(sql, conn); MyCommand.Parameters.AddWithValue("skip", skip); MyCommand.Parameters.AddWithValue("take", take); MySqlDataReader MyReader; conn.Open(); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { GiftCodeItem item = new GiftCodeItem(); item.code = MyReader.GetString("code"); item.ID = MyReader.GetInt32("id"); item.dateCreated = MyReader.GetDateTime("dateCreated"); item.dateExpired = MyReader.GetDateTime("dateExpired");; item.used = false; item.name = MyReader.GetString("name"); item.value = MyReader.GetInt32("value"); item.user_id = 0; item.username = ""; item.fullname = ""; data.Add(item); } MyReader.Close(); sql = "Select count(*) as totalRecord from newDB.giftcode WHERE used = 0"; MyCommand = new MySqlCommand(sql, conn); MyReader = MyCommand.ExecuteReader(); while (MyReader.Read()) { totalRecord = MyReader.GetInt32("totalRecord"); } MyReader.Close(); conn.Close(); } rs.totalRecord = totalRecord; rs.data = data; return(rs); }