ASP.NET ExecuteNonQuery
The ExecuteNonQuery() method in ADO.NET is used to execute a Transact-SQL statement against a connection and returns the number of rows affected by the operation. This method is capable of performing both Data Definition tasks and Data Manipulation tasks.
ExecuteNonQuery() method
Data Definition tasks involve actions such as creating stored procedures, views, or other database objects. These tasks can be accomplished using the ExecuteNonQuery() method. When executing a Data Definition statement, the method does not return any rows, but it can still affect the structure or schema of the database.
VB.Net
command.ExecuteNonQuery()
C#
command.ExecuteNonQuery();
Similarly, Data Manipulation tasks, such as inserting, updating, or deleting records, can also be performed using the ExecuteNonQuery() method. These tasks modify the data stored in the database, and the method returns the number of affected rows.
It's important to note that even though the ExecuteNonQuery() method does not return any rows, it can still populate output parameters or return values that are mapped to parameters. This allows for the retrieval of specific data or information related to the executed operation.
The following ASP.NET program insert a new row in Discount (Pubs database) table using ExecuteNonQuery();
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</form>
</body>
</html>
Full Source | C#
using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
string sql = "insert into discounts values('New Discont',8042,1000,1000,5.25)";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.ExecuteNonQuery();
connection.Close();
Label1.Text = "Successfully Inserted !!";
}
catch (Exception ex)
{
Label1.Text = "Error inserting data" + ex.ToString();
}
}
}
Full Source | VB.NET
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim connection As SqlConnection
connectionString = ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim sql As String = "insert into discounts values('New Discont',8042,1000,1000,5.25)"
Try
connection.Open()
Dim cmd As New SqlCommand(sql, connection)
cmd.ExecuteNonQuery()
connection.Close()
Label1.Text = "Successfully Inserted !!"
Catch ex As Exception
Label1.Text = "Error inserting data" & ex.ToString
End Try
End Sub
End Class
Conclusion
The ExecuteNonQuery() method in ADO.NET is versatile and can be used to execute both Data Definition and Data Manipulation tasks. It returns the number of rows affected by the executed operation, and it can also populate output parameters or return values associated with the operation. This method is useful for performing various database operations that do not require retrieving a result set.