Pass parameter to stored procedure using SqlDataAdapter and Code for login in asp.net

Introduction: 

As we know Stored procedures are always better than inline queries. Stored procedures are less prone to SQL Injection.  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.

First of all create a Table in SQL server database: 

Table Name: Admin_Login

Cloumns Name: USERNAME and PASSWORD

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

Add Connection String In webconfig File Of Your Application: 

<connectionStrings>
    <add name="EmpCon" connectionString="Data Source=localhost;Initial Catalog=test; 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  login in asp.net

• Now in code behind file(.aspx.cs) write the code on button click event:

    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();         

        }

    }

VB.Net Code to pass parameter to stored procedure using SqlDataAdapter and check login in asp.net

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

Protected Sub btnLogin_Click(sender As Object, e As EventArgs)

               Dim dt As New DataTable()

               Try

                               Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("EmpCon").ConnectionString)

                               Dim adp As 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 Then

                                              Response.Write("Login Successfull.")

                              Else

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

                               End If

               Catch ex As Exception

                               Response.Write("Error occured : " & ex.ToString())

               Finally

                               dt.Clear()

                               dt.Dispose()

               End Try

End Sub