locked
How to code my database with my webpage RRS feed

  • Question

  • User1250730359 posted
    Have spend more than a month on my web database, have create and the database, but it remain the coding that will connect my register and login user page to input data to the database... Please am a statistics student..
    Sunday, July 29, 2018 2:12 PM

All replies

  • User-893317190 posted

    Hi Raoty,

     I have made a sample of registering and login using database.

     Below is my code.

     I have four common methods .

     The first.

     public  string GetMd5(string source)
            {
                MD5 md5 = MD5.Create();
                Byte[] bys = md5.ComputeHash(Encoding.Default.GetBytes(source));
                StringBuilder builder = new StringBuilder();
                foreach (var item in bys)
                {
                    builder.Append(item.ToString("x2"));
                }
                md5.Clear();
                return builder.ToString();
    
            }
          

    The second

     public  int ExcuteNunQuery(string sql, params SqlParameter[] sqlParameters)
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        com.Parameters.AddRange(sqlParameters);
                        con.Open();
                        return com.ExecuteNonQuery();
                    }
                }
            }

    The third

     public Object ExcuteScalar(string sql, params SqlParameter[] sqlParameters)
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        com.Parameters.AddRange(sqlParameters);
                        con.Open();
                        return com.ExecuteScalar();
                    }
    
                }
            }

    The fourth

     public  SqlDataReader GetSqlDataReader(string sql, params SqlParameter[] sqlParameters)
            {
    
    
                SqlConnection con = new SqlConnection(constr);
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    try
                    {
                        con.Open();
    
                        com.Parameters.AddRange(sqlParameters);
    
                        return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
                    }
                    catch (Exception)
                    {
                        con.Close();
                        con.Dispose();
    
                        throw;
                    }
    
                }
    
            }

    Page for registering .

    <form id="form1" runat="server">
            <div>
                <asp:Label ID="Label1" runat="server" Text="username"></asp:Label><br />
                 <asp:TextBox ID="userName" runat="server"></asp:TextBox>
                 <asp:Label ID="errorMsg" runat="server" Text="" Visible="false" ForeColor="Red"></asp:Label>
                  <asp:RequiredFieldValidator ID="requireUsername" runat="server" ErrorMessage="userName is required" ControlToValidate="userName" ForeColor="red"></asp:RequiredFieldValidator>
               
            </div>
                
              <div>
                <asp:Label ID="Label2" runat="server" Text="password"></asp:Label><br />
                 <asp:TextBox ID="password" runat="server" TextMode="Password"></asp:TextBox>
                  <asp:RequiredFieldValidator ID="requirePassword" runat="server" ErrorMessage="password is required" ControlToValidate="password" ForeColor="Red"></asp:RequiredFieldValidator>
            </div>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
           
        </form>



    And code behind.

     protected void Button1_Click(object sender, EventArgs e)
            {
                //  confirm whether the username has been registered
                string sql = "select count(*) from users where username=@username";
            
              int count= (int)  ExcuteScalar(sql, new SqlParameter("username", System.Data.SqlDbType.NVarChar, 50) { Value = userName.Text });
                if (count == 1)
                {
                    // the username has been registered ,show error message
    
                    errorMsg.Visible = true;
                    errorMsg.Text = "the username has been registered";
                }
                else
                {
                    // the username hasn't been registered , save the username and password into database
                    errorMsg.Visible = false;
                    //encrypt the password to save it in database, you could use your own way( I use MD5) 
                    string dbPass = GetMd5(password.Text);
                    string regist = "insert into users (username,password) values(@username,@password)";
                    SqlParameter[] sqlParameters = new SqlParameter[] {
                        new SqlParameter("username",System.Data.SqlDbType.NVarChar,50){Value=userName.Text},
                        new SqlParameter("password",System.Data.SqlDbType.NVarChar,200){Value=dbPass}
                    };
    
                    try
                    {
                        // after registering the new user , redirect to login page.
                        ExcuteNunQuery(regist, sqlParameters);
                        Response.Redirect("~/Identity/Login.aspx");
                    }
                    catch (Exception)
                    {
    
                        throw;
                    }
    
                }
            }

    Page for login.

     <form id="form1" runat="server">
           <h1>
               login
           </h1>
                <div>
                <asp:Label ID="Label1" runat="server" Text="username"></asp:Label><br />
                 <asp:TextBox ID="userName" runat="server"></asp:TextBox>
                  <asp:RequiredFieldValidator ID="requireUsername" runat="server" ErrorMessage="userName is required" ControlToValidate="userName" ForeColor="red"></asp:RequiredFieldValidator>
               
            </div>
                
              <div>
                <asp:Label ID="Label2" runat="server" Text="password"></asp:Label><br />
                 <asp:TextBox ID="passWord" runat="server" TextMode="Password"></asp:TextBox>
                  <asp:RequiredFieldValidator ID="requirePassword" runat="server" ErrorMessage="password is required" ControlToValidate="password" ForeColor="Red"></asp:RequiredFieldValidator>
            </div>
            <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
    
            <asp:Label ID="errorMsg" runat="server" Text="username or password is wrong" ForeColor="red" Visible="false"></asp:Label>
        </form>

    Code behind.

     protected void Button1_Click(object sender, EventArgs e)
            {
                string username = userName.Text;
               string password=GetMd5( passWord.Text);
                //confirm whether the username or the password is wrong
                string sql = "select * from users where username=@username and password=@password";
                SqlParameter[] sqlParameters = new SqlParameter[] {
                    new SqlParameter("username",System.Data.SqlDbType.NVarChar,50){Value=username},
                    new SqlParameter("password",System.Data.SqlDbType.NVarChar,200){Value=password}
                };
    
                                                //the fourth common method
                using (SqlDataReader reader= GetSqlDataReader(sql, sqlParameters))
                {
                   
                    if (reader.HasRows)
                    {
                        // the user has been found , save it into session to keep the user state and redirect the user to index.aspx
                        Models.User user = new Models.User();
                        reader.Read();
                       user.Username= reader.GetString(1);
                       user.Password= reader.GetString(2);
                        Session["user"] = user;
                        Response.Redirect("~/Identity/Index.aspx");
                    }
                    else
                    {
                        // if you don't find the user ,show error message.
                        errorMsg.Visible = true;
                    }
              
                } 
               
            }

    And the index page.

     <form id="form1" runat="server">
            <div>
                 <asp:Label ID="userMsg" runat="server" Text=""></asp:Label>
            </div>
        </form>

    And code behind.

     protected void Page_Load(object sender, EventArgs e)
            {
                if (Session["user"] != null)
                {
                    // In index.aspx , if there is user data in session ,please get the data and show user message.
                    Models.User user = Session["user"] as Models.User;
                    userMsg.Text = "welcome " + user.Username;
                }
            }

    This is a basic way ,you could also use Identity for convenience.

    Best regards,

    Ackerly Xu

    Tuesday, July 31, 2018 8:13 AM