ExecuteNonQuery() in ado.net using asp.net with Example

Whenever we use data operation in asp.net we have to use ADO.NET or LINQ or EntityFramework. ADO.NET is very old yet powerful to perform operation with database. ADO.NET have very useful class and methods using them we can easily perform Database operation. In this article we take small part of ADO.NET and elaborate it with example code.

ExecuteNonQuery()
ExecuteNonQuery() is use when we use T-SQL Statement in our system like insert, update and delete. ExecuteNonQuery() will return no of affected rows in database. To get proper result we must declare it after T-SQL Statement execution complete.
For better understand we take basic example of ExecuteNonQuery. We use User table with four columns. We are performing Insert operation in this example. User can insert one employee at a time that why we will get 1 row affected during insertion of data. ExecuteNonQuery() return result must be greater than zero.

ExecuteNonQuery() Example in Ado.net


Database Structure for our example

Column NameData Type
IDint(Primary Key)
First Namevarchar(50)
Last Namevarchar(50)
Cityvarchar(30)
EmailIDvarchar(100)

CREATE TABLE Sample(
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](50) NULL,
 [LastName] [varchar](50) NULL,
 [City] [varchar](30) NULL,
 [EmailID] [varchar](100) NULL)
Default.aspx
<table>
  <tr>
    <td>First Name :</td>
    <td>
        <asp:TextBox runat="server" ID="txtFirstName" ></asp:TextBox>
    </td>
  </tr>
  <tr>
   <td>Last Name :</td>
   <td>
       <asp:TextBox runat="server" ID="txtLastName" ></asp:TextBox>
   </td>
  </tr>
  <tr>
   <td>City :</td>
   <td>
        <asp:TextBox runat="server" ID="txtCity" ></asp:TextBox>
   </td>
  </tr>
  <tr>
   <td>EmailID :</td>
   <td>
        <asp:TextBox runat="server" ID="txtEmailID" ></asp:TextBox>
   </td>
  </tr>
  <tr>
   <td colspan="2">
        <asp:Button ID="btnInsert" runat="server" OnClick="btnInsert_Click" Text="Insert Data"></asp:Button>
  </td>
  </tr>
</table>
Default.aspx.cs 
protected void btnInsert_Click(object sender, EventArgs e)
    {
        string ConnectionString = "Enter your ConnectionString Here";
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            string Query = "insert into Sample(FirstName, LastName, City, EmailID) values(@FirstName,@LastName,@City,@EmailID)";
            using (SqlCommand cmd = new SqlCommand(Query, con))
            {
                con.Open();
                cmd.Parameters.Add("@FirstName", txtFirstName.Text);
                cmd.Parameters.Add("@LastName", txtLastName.Text);
                cmd.Parameters.Add("@City", txtCity.Text);
                cmd.Parameters.Add("@EmailID", txtEmailID.Text);

                int AffectedRow = cmd.ExecuteNonQuery(); // use of ExecueteNonQuery()
                if (AffectedRow == 1)
                {
                    Response.Write("User Added Successfully");
                }
                else
                {
                    Response.Write("Error occur while processing your request");

                }
            }
        }
    }

Download Sample Code