Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }