How to Execute Parameterized SQL Statements: ADO.NET

As we know, SQL statements can be of simple type or may be parameterized. The parameterized query contains some parameters, which may be accepted through object of SqlParameter class. SqlParameter class is used to create the parameters used by the command object to execute the Sql queries.
Write the following code to select a record from the table groups which has a given name. The name is passed by the parameter i.e. Sql Parameter.

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source= (LocalDb)\\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups where code=@code", connection);
command.Parameters.AddWithValue("@code", 04);
SqlDataReader dr = command.ExecuteReader();

Look out the command object which have a variable name @code. It is the syntax of parameter used. In the next line, the parameter is added by using the function AddWithValue(). This method takes two parameter i.e. one for variable name (same as used in command object) and the second one is its value.

Now when we execute this code, a single record having code = 04 will be returned and can be accessed by data reader object dr.

We can check the rows as the same procedure as in previous post.

How to Execute SQL Statements: ADO.NET

We have learnt about ADO.NET object model. In that article we have created a SQL connection, open that connection and close that connection. To execute the SQL statements, we have to write them in between the open and close method of the above connection.

So to execute the SQL statement we have to, first create the connection, open that, write some code which will be executed and at the last execute it with ExecuteReader() method of SQLCommand class.

SqlCommand class is used to specify the Sql statement to be executed and the connection that need to be used. It can also be used to execute stored procedures. Following code will used to execute Sql statement in a command object:

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Data Source=(LocalDb)\v11.0; Initial Catalog=StockDb; Integrated Security=True";
connection.Open();
SqlCommand command = new SqlCommand("select * from Groups", connection);
SqlDataReader dr = command.ExecuteReader();

In the above code, when SqlCommand object will create, an object of SqlDataReader have been also created. And it is initialized with the function ExecuteReader(). Now all the data can be accessed one by one through dr object, one by one.

When we check the dataReader object through the breakpoint then there are some rows, as shown in following image:

See also: Parameterized Sql Queries

How to use HasRows property of SqlDataReader class

Use HasRows property of SqlDataReader class 
Gets a value that indicates whether the SqlDataReader contains one or more rows (msdn.microsoft.com)



Step-1 : Create a database table with empty data

Step-2 : Check data in database using HasRows property of  SqlDataReader class

Step-3 :  Write code on page_load method



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

public partial class duplicatevalue : System.Web.UI.Page
{
    SqlDataReader rd;

    protected void Page_Load(object sender, EventArgs e)
    {

        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString =ConfigurationManager .ConnectionStrings ["ConnectionString"].ToString ();
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "checkdata";
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                   result.Text="Row Exisit";  //here result is the id of the label control
                }
                else
                {
                    result.Text = "no row found";

                  
                }
                                
            }

       
        }

    }

OutPut Screen

what is the use of SqlDataReader in ASP.NET

SqlDataReader 
Provides a way of reading a forward-only stream of rows from a SQL Server database ( source from msdn.microsoft.com)

Example How to bind gridview using SqlDataReader class

Step-1 : Take a Gridview onto design window. 
Step-2 :  Develop code for binding gridview using SqlDataReader.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class datareadercl : System.Web.UI.Page
{
    SqlDataReader rd;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            using (SqlConnection con = new SqlConnection())
            {
                con.ConnectionString =ConfigurationManager.ConnectionStrings ["ConnectionString"].ToString ();
                con.Open ();
                using(SqlCommand cmd=new SqlCommand ())
{
                    cmd.CommandText ="select * from [Table]";
                    cmd.Connection =con;
                    rd=cmd.ExecuteReader();
                    GridView1 .DataSource =rd;
                    GridView1 .DataBind ();                  
 
}

            }
        }
    }
}

OutPut of the program

Read() method of SqlDataReader class  : read next record

• Here Example of Read method which is read table row one by one.