Free Web Development Online Tutorials, Learn to Code
Learn ADO.Net SqlCommand in C#, Use ADO.Net SqlCommand ExecuteReader, ExecuteNonQuery, ExecuteScalar

Ado.net SqlCommand C# Example

Ado.net DBCommand tutorial with examples for beginners step by step, you will learn SqlCommand methods ExecuteReader, ExecuteNonQuery, ExecuteScalar with examples.

SqlCommand in Ado.Net C#

In this tutorial you learn how to use DBCommand in Ado.Net, CreateCommand from DbConnection, setting DbCommand CommandType, CommandText, Add parameters to dbCommand, using ExecuteNonQuery, ExecuteScalar, ExecuteReader etc.

DbCommand is the base class, and there different type of Ado.Net dbCommand object for different client like SqlCommand, OleDbCommand, OracleCommand.

using System.Data.Common;
public abstract class DbCommand : Component, IDbCommand, IDisposable
{   
}

Here in this tutorial we explain how to work with SQL client using SqlCommand, Let’s look at some SqlCommand ExecuteReader example with sql select query

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlCommand _sCommand = con.CreateCommand();
    _sCommand.CommandText = "Select * from tbOrder";
    _sCommand.CommandType = CommandType.Text;
    SqlDataReader _reader = _sCommand.ExecuteReader();
}

SqlCommand CreateCommand and ExecuteReader

In above example you can see how we have created a SqlCommand Object from SqlConnection and fetching data from a table then finally reading data using datareader object

Notice how SqlCommand ExecuteReader method is used with stored procedure, Learn more about SqlDataReader in Ado.Net

SqlCommand CommandType and CommandText

In above example we could have used some SQL storeprocedure to read data, where reading multiple record set would have been easier, let’s look at the CommandType.StoredProcedure example below

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlCommand _sCommand = con.CreateCommand();
    _sCommand.CommandText = "usp_SpName";
    _sCommand.CommandType = CommandType.StoredProcedure;
    SqlDataReader _reader = _sCommand.ExecuteReader();
}

SqlCommand Parameters Example

While using store procedure with DbCommand object, you may need to set parameters for the specified StoredProcedure

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlCommand _sCommand = con.CreateCommand();
    _sCommand.CommandText = "usp_SpName";
    _sCommand.CommandType = CommandType.StoredProcedure;
    _sCommand.Parameters.Add("param_name1", SqlDbType.Text);
    _sCommand.Parameters.Add("param_name2", SqlDbType.Int);
}
SqlCommand Methods in C#

Here are few very useful SqlCommand Methods

  • ExecuteReader, ExecuteReaderAsync
  • ExecuteNonQuery, ExecuteNonQueryAsync
  • ExecuteScalar
  • ExecuteXmlReader, ExecuteXmlReaderAsync
  • Cancel

In above example you have already seen how to create command object and use ExecuteReader method, same way you also can execute methods like ExecuteNonQuery, ExecuteScalar.

ExecuteNonQuery and ExecuteReader

ExecuteReader method is used when you want some data to be fetched from database, so reader object will always have some record set, ExecuteNonQuery is used when you don’t want anything in return , only want to know if the execution was successful, in operation like adding, updating or deleting data.

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlCommand _sCommand = con.CreateCommand();
    _sCommand.CommandText = "insert / update / delete sql statment";
    _sCommand.CommandType = CommandType.Text;
    int _result = _sCommand.ExecuteNonQuery();
}

ExecuteScalar and ExecuteReader

ExecuteScalar method is just like ExecuteReader , ExecuteScalar is also used for reading data, but only difference is that ExecuteScalar return only one value of first row first column

using (SqlConnection con = new SqlConnection(Util.ConnectionString))
{
    SqlCommand _sCommand = con.CreateCommand();
    _sCommand.CommandText = "usp_SpName";
    _sCommand.CommandType = CommandType.StoredProcedure;
    object _value = _sCommand.ExecuteScalar();
}

Learn more about Ado.net SqlCommand ExecuteReader

Comment
Name
Email
Website
Subscribe
 
learn ado.net
SqlCommand in Ado.Net
Ado.net Interview Questions Answers

Ado.Net C# Examples | Join Asp.Net MVC Course