Thursday, May 28, 2009

Select using SQLParameter in ADO.NET

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 SQLCommand Objects



SqlCommand cmd = new SqlCommand("Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password", conn);



4. 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]);


5. 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("Select UserId From UserDetail WHERE UserId = @UserId AND Password = @Password", conn);
     
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();
     }
}




 


Read this article to implement above scenario using stored procedure.
Select using SQLParameter
I always recommend to use stored procedure instead of inline query in .NET.

Happy Coding :)

No comments:

Site Meter