public void GetUserById(int userId)
 {
     using (SqlConnection con = GetConnection())
     {
         //string sql = "select* from tblUser where UserId=@Id";
         using (SqlCommand cmd = new SqlCommand("spGetUserById", con))
         {
             cmd.CommandType = CommandType.StoredProcedure;
             SqlParameter param = new SqlParameter();
             param.ParameterName = "@Id";
             param.Value         = userId;
             cmd.Parameters.Add(param);
             User123       user = new User123();
             SqlDataReader rd   = cmd.ExecuteReader();
             while (rd.Read())
             {
                 user.UserId      = Convert.ToInt32(rd["UserId"]);
                 user.UserName    = rd["UserName"].ToString();
                 user.Password    = rd["Password"].ToString();
                 user.Email       = rd["Email"].ToString();
                 user.PhoneNumber = rd["PhoneNumber"].ToString();
                 user.Address     = rd["Address"].ToString();
                 user.UserRole    = Convert.ToInt32(rd["UserroleId"]);
                 user.Gender      = rd["Gender"].ToString();
             }
             JavaScriptSerializer js = new JavaScriptSerializer();
             Context.Response.Write(js.Serialize(user));
         }
     }
 }
        public void GetAllUserbyRole()
        {
            using (SqlConnection con = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand("select * from tblUser where UserroleId=2", con))
                {
                    cmd.CommandType = CommandType.Text;
                    //cmd.Parameters.AddWithValue("@roleid", roleid);
                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        User123 user = new User123();
                        user.UserId      = Convert.ToInt32(rd["UserId"]);
                        user.UserName    = rd["UserName"].ToString();
                        user.Password    = rd["Password"].ToString();
                        user.Email       = rd["Email"].ToString();
                        user.PhoneNumber = rd["PhoneNumber"].ToString();
                        user.Address     = rd["Address"].ToString();
                        user.UserRole    = Convert.ToInt32(rd["UserroleId"]);
                        user.Gender      = rd["Gender"].ToString();
                        list.Add(user);
                    }

                    JavaScriptSerializer js = new JavaScriptSerializer();
                    Context.Response.Write(js.Serialize(list));
                }
            }
        }
        public void CreateUser(User123 user)
        {
            using (SqlConnection con = GetConnection())
            {
                using (SqlCommand cmd = new SqlCommand("spCreateUser", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //     ParameterName= "@username",
                    //     Value=user.UserName

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@password",
                    //    Value = user.Password

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@email",
                    //    Value = user.Email

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@phonenumber",
                    //    Value = user.PhoneNumber

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@address",
                    //    Value = user.Address

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@userrole",
                    //    Value = user.UserRole

                    //});
                    //cmd.Parameters.Add(new SqlParameter()
                    //{
                    //    ParameterName = "@gender",
                    //    Value = user.Gender*

                    //});
                    cmd.Parameters.AddWithValue("@username", user.UserName);
                    cmd.Parameters.AddWithValue("@password", user.Password);
                    cmd.Parameters.AddWithValue("@email", user.Email);
                    cmd.Parameters.AddWithValue("@phonenumber", user.PhoneNumber);
                    cmd.Parameters.AddWithValue("@address", user.UserRole);
                    cmd.Parameters.AddWithValue("@userrole", user.UserRole);
                    cmd.Parameters.AddWithValue("@gender", user.Gender);
                    cmd.ExecuteNonQuery();
                }
            }
        }