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.

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
{   
}

SqlCommand in Ado.Net C#

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();
}

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

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();
}

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);
}

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.

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 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