In this post we will discuss about ExecuteNonQuery(), ExecuteReader() and ExecuteScalar() methods used in executing commands in Ado.Net in Asp.Net.
Also check out:
- Bind dropdownlist from enum in Asp.Net
- Common Language Runtime in C#.Net
- RangeValidator example in Asp.Net
ExecuteNonQuery():
ExecuteNonQuery() method used for UPDATE, INSERT, or DELETE statements. And it returns an integer value for the number of rows affected.
Example:
string select = "Delete from UserMaster where UserID >100";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
int rowsReturned = cmd.ExecuteNonQuery();
lblResult.Text = "Number of rows deleted " + rowsReturned;
conn.Close();
It will show the number of rows deleted in the above query.
ExecuteReader():
The ExecuteReader method executes the command and returns a typed data reader object. Then you can use that data reader object to iterate through the records returned.
Example:
string select = "SELECT * from UserMaster";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//Get the values
}
ExecuteScalar():
When we try to return a single result from a sql statement, then we can use ExecuteScalar() method. ExecuteScalar() method returns an object and you can type cast according to your requirement.
Example:
string select = "SELECT COUNT(*) FROM Customers";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
object o = cmd.ExecuteScalar();
int number = Convert.ToInt32(o);
ExecuteXmlReader():
This is provided only with SqlClient Provider. ExecuteXmlReader method executes a SQL statement and returns an XmlReader object to the caller.
SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause.
This clause can include one of three options:
FOR XML AUTO — Builds a tree based on the tables in the FROM clause.
FOR XML RAW — Maps result set rows to elements with columns mapped to attributes.
FOR XML EXPLICIT — Requires that you specify the shape of the XML tree to be returned.
Example:
string select = "SELECT * from UserMaster FOR XML AUTO";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
XmlReader xr = cmd.ExecuteXmlReader();
xr.Read();
string data;
do
{
data = xr.ReadOuterXml();
if (!string.IsNullOrEmpty(data))
//You can write data.
} while (!string.IsNullOrEmpty(data));
conn.Close();
Also check out:
- Bind dropdownlist from enum in Asp.Net
- Common Language Runtime in C#.Net
- RangeValidator example in Asp.Net
ExecuteNonQuery():
ExecuteNonQuery() method used for UPDATE, INSERT, or DELETE statements. And it returns an integer value for the number of rows affected.
Example:
string select = "Delete from UserMaster where UserID >100";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
int rowsReturned = cmd.ExecuteNonQuery();
lblResult.Text = "Number of rows deleted " + rowsReturned;
conn.Close();
It will show the number of rows deleted in the above query.
ExecuteReader():
The ExecuteReader method executes the command and returns a typed data reader object. Then you can use that data reader object to iterate through the records returned.
Example:
string select = "SELECT * from UserMaster";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//Get the values
}
ExecuteScalar():
When we try to return a single result from a sql statement, then we can use ExecuteScalar() method. ExecuteScalar() method returns an object and you can type cast according to your requirement.
Example:
string select = "SELECT COUNT(*) FROM Customers";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
object o = cmd.ExecuteScalar();
int number = Convert.ToInt32(o);
ExecuteXmlReader():
This is provided only with SqlClient Provider. ExecuteXmlReader method executes a SQL statement and returns an XmlReader object to the caller.
SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause.
This clause can include one of three options:
FOR XML AUTO — Builds a tree based on the tables in the FROM clause.
FOR XML RAW — Maps result set rows to elements with columns mapped to attributes.
FOR XML EXPLICIT — Requires that you specify the shape of the XML tree to be returned.
Example:
string select = "SELECT * from UserMaster FOR XML AUTO";
SqlConnection conn = new SqlConnection("Your connection string");
conn.Open();
SqlCommand cmd = new SqlCommand(select, conn);
XmlReader xr = cmd.ExecuteXmlReader();
xr.Read();
string data;
do
{
data = xr.ReadOuterXml();
if (!string.IsNullOrEmpty(data))
//You can write data.
} while (!string.IsNullOrEmpty(data));
conn.Close();