ExecuteReader in Asp.Net with Example

ExecuteReader is forward only retrieval of records and it is used to read the table values from first to last. It sends the CommandText to the Connection and builds a SqlDataReader.

Example :

Consider you have a " Employees " table in your database.

Now , you want to fetch all the record from your Employee table and show them in a GridView.

Add this code to youe aspx Page(Here i am using a GridView control to show Employee records).

<form id="form1" runat="server">
<asp:GridView ID="EmployeesList" runat="server">
</asp:GridView>
</form>

Write this code in your code behind file(Name.aspx.cs) :

//Add Following NameSpaces

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

//Add some code to your Page_Load Event

protected void Page_Load(object sender, EventArgs e)
{
if (! IsPostBack) //When you are posting the page for the first time then IsPostBack is false and !IsPostBack it becomes true
{
BindGridview();
}
}

// This method is used to bind gridview from database
protected void BindGridview()
{
using (SqlConnection connection = new SqlConnection("Write Your Connection string here.."))
{
connection.Open();
SqlCommand cmd = new SqlCommand("Select FirstName,LastName FROM Employees", connection);

SqlDataReader reader = cmd.ExecuteReader();
EmployeesList.DataSource = reader; //Adding datasource to our GridView
Employees.DataBind();  //Binding data to GridView
connection.Close();
}
}

In this way you can fetch(retrieve) records from your DataBase table using ExecuteReader  

Read more...

ExecuteNonQuery() in Asp.Net With Example

ExecuteNonQuery executes a SQL command(Insert,Update or Delete commands) against the connection and returns the number of rows affected.

Example :

Consider you have a " Employee " table in your database.

Now , you want to insert a record into your Employee table.

Consider you have to enter two values i.e., FirstName and LastName into your table. For that , write this code into your aspx(Name.aspx) page..

 <form id="form1" runat="server">
        <table>
            <tr>
                <td>FirstName</td>
                <td><asp:TextBox runat="server" ID="txtFirstName"> </asp:TextBox> </td>
            </tr>
            <tr>
                <td>LastName</td>
                <td><asp:TextBox runat="server" ID="txtLastName"> </asp:TextBox></td>
            </tr>
            <tr>
                <td><asp:Button runat="server" ID="btnSubmit" Text="SUBMIT" OnClick="btnSubmit_Click"/>                </td>
                <td></td>
            </tr>
        </table>
 </form>

The above form contains two textboxes to enter FirstName and LastName and a Button to Submit the data.

Note: Note the onClick attribute of Button. The event btnSubmit_Click is called when you click on the button.

In your code behind file(Name.aspx.cs) write this code...

//Add these two namespaces
using System;
using System.Data.SqlClient;

// Add btnSubmit_Click event(which is fired when you click on button) below Page_Load() event..

protected void btnSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection connection=new SqlConnection("Write your connection string here.."))
{
connection.Open();
SqlCommand cmd = new SqlCommand("insert into Employee(FirstName,LastName) values(@FName,@LName)", connection);

//then add parameters to your command

cmd.Parameters.AddWithValue("@FName", txtFirstName.Text());
cmd.Parameters.AddWithValue("@LName", txtLastName.Text());

int result= cmd.ExecuteNonQuery();  //here you will get the no.of rows effected 

if(result > 0)  // It means atleast one row has been effected 
{
response.write("Employee added");
}
else
{
response.write("Error in adding Employee");
}
connection.Close();  //Don't forget to close the connection when you are done with the connection.
}
}

In this way, we can add records to our DataBase table(Using ExecuteNonQuery()..)

Read more...