ADO.NET Select Stored Procedure using SQLParameter

This artilce explains how to use parameters in the command. I have created a database called Test in SQL which contains a UserDetail table having UserId and Password as column.

1.Import below namespace

using System.Data;
using System.Data.SqlClient;

2. Create and open SQLConnection

string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();

3. Create stored procedure

CREATE PROCEDURE GetUserDetail
@UserId VARCHAR(50),
@Password VARCHAR(50)
AS
BEGIN
 Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password
END

4. Create SQLCommand Objects

SqlCommand cmd = new SqlCommand("GetUserDetail", conn);
cmd.CommandType = CommandType.StoredProcedure;

5. Create SqlParameter Objects and bind it to SQLCommand objects

SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@UserId", strUserId);
param[1] = new SqlParameter("@Password", strPassword);
//add parameters to command object
cmd.Parameters.Add(param[0]);
cmd.Parameters.Add(param[1]);

6. Get Data from DB

//get data stream
SqlDataReader reader = cmd.ExecuteReader();
//Check whether record exists or not
if (reader.HasRows)
{
     // Loop through each record
     while (reader.Read())
     {
          strName = reader["UserId"].ToString();
     }
}
else
{
     Response.Write("Record not found.");
}

 

Let's Put all together

string connectionString = "Data Source=localhost;Initial Catalog=Test;Integrated Security=True";
SqlConnection conn = null;
SqlDataReader reader = null;
string strUserId = "myName";
string strPassword = "myPassword";
string strName = string.Empty;
try
{
     conn = new SqlConnection(connectionString);
     conn.Open();
     SqlCommand cmd = new SqlCommand("GetUserDetail", conn);
     cmd.CommandType = CommandType.StoredProcedure;
     SqlParameter[] param = new SqlParameter[2];
     param[0] = new SqlParameter("@UserId", strUserId);
     param[1] = new SqlParameter("@Password", strPassword);
     //add new parameter to command object
     cmd.Parameters.Add(param[0]);
     cmd.Parameters.Add(param[1]);
     //get data stream
     reader = cmd.ExecuteReader();
     //Check whether record exists or not
     if (reader.HasRows)
     {
          // Loop through each record
          while (reader.Read())
          {
               strName = reader["UserId"].ToString();
          }
     }
     else
     {
          Response.Write("Record not found.");
     }
}
catch (Exception ex)
{
}
finally
{
     //Closing reader and connection in finally block in case  of any exception reader and conn should close.
     //Code in finally block executes in normal scenario as well as in case of exception thrown.
     if (reader != null)
     {
          reader.Close();
     }
     // close connection
     if (conn != null)
     {
          conn.Close();
     }
}

 

Happy Coding :)