HOW TO PASS PARAMETER TO STORED PROCEDURE USING SQLDATAADAPTER AND CHECK LOGIN IN ASP.NET

As we know Stored procedures are always better than inline queries. Here in this article i have explained an example of login form where user has to enter username and password and his authentication is checked by passing his username and password to stored procedure.Lets create this application:

• First Create a table in Sql server Database having USERNAME and PASSWORD column and name it "ADMIN_LOGIN" then Create a stored procedure in sql server as:

CREATE PROCEDURE CHECK_ADMIN_LOGIN   

                        @USERNAME VARCHAR(50),

                        @PASSWORD VARCHAR(50)

AS

BEGIN

 SELECT * FROM ADMIN_LOGIN WHERE USERNAME COLLATE Latin1_general_CS_AS =@USERNAME AND [PASSWORD] COLLATE Latin1_general_CS_AS=@PASSWORD           

END

• Now in web.config file add the connection string under <configuration> element tag :

<connectionStrings>
    <add name="EmpCon" connectionString="Data Source=LocalServer;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>

 

• Now in design page(.aspx) the place two TextBox for entering username and password and a Button control for submitting.

        <table>

            <tr>

                <td>

                    UserName</td>

                <td>

                    <asp:TextBox ID="txtUserName" runat="server"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    Password</td>

                <td>

                    <asp:TextBox ID="txtPwd" runat="server"></asp:TextBox>

                </td>

            </tr>

            <tr>

                <td>

                    &nbsp;</td>

                <td>

                    <asp:Button ID="btnLogin" runat="server" onclick="btnLogin_Click"

                        Text="Login" />

                </td>

            </tr>

        </table

C#.Net code to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net

• Now in code behind file(.aspx.cs) write the code:

    protected void btnLogin_Click(object sender, EventArgs e)

    {      

        DataTable dt=new DataTable();

        try

        {        

            SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["EmpCon"].ConnectionString);

            SqlDataAdapter adp=new SqlDataAdapter("CHECK_ADMIN_LOGIN",con);

            adp.SelectCommand.CommandType=CommandType.StoredProcedure;

            adp.SelectCommand.Parameters.Add("@USERNAME",SqlDbType.VarChar,50).Value=txtUserName.Text.Trim();

            adp.SelectCommand.Parameters.Add("@PASSWORD",SqlDbType.VarChar,50).Value=txtPwd.Text.Trim();

            adp.Fill(dt);

            if (dt.Rows.Count>0)

            {

                Response.Write("Login Successfull.");

            }

            else

            {

                Response.Write("Invalid username or passwrod.");

            }

        }

        catch(Exception ex)

        {

            Response.Write("Error occured : " + ex.ToString() );

        }

        finally

        {

            dt.Clear();

            dt.Dispose();         

        }

    }