ADO.NET

How to Create and Execute SqlCommand in ADO.NET

Technologies ADO.NET, Data Access, .NET Development

Topics

Stored Procedures, sql commond

How to Create and Execute SqlCommand in ADO.NET

Introduction

We can create and execute different types of SqlCommand. In this applilcation, we will demonstrate how to create and execute SqlCommand:

1. Create different types of SqlCommand;
2. Execute SqlCommand in different ways;
3. Display the result.

Using the Code

1. ExecuteNonQuery method
If you need modify the data (Add, Delete, Update), you can use the method to complete it.

C#

public static Int32 ExecuteNonQuery(String connectionString, String commandText, 
    CommandType commandType, params SqlParameter[] parameters)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            cmd.CommandType = commandType;
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }
}

Visual Basic

Public Shared Function ExecuteNonQuery(ByVal connectionString As String, ByVal commandText As String,
                                       ByVal commandType As CommandType, ByVal ParamArray parameters() As SqlParameter) As Int32
    Using conn As New SqlConnection(connectionString)
        Using cmd As New SqlCommand(commandText, conn)
            ' There're three command types: StoredProcedure, Text, TableDirect. The TableDirect 
            ' type is only for OLE DB.  
            cmd.CommandType = commandType
            cmd.Parameters.AddRange(parameters)
            conn.Open()
            Return cmd.ExecuteNonQuery()
        End Using
    End Using
End Function

public static Int32 ExecuteNonQuery(String connectionString, String commandText,  
    CommandType commandType, params SqlParameter[] parameters) 
{ 
    using (SqlConnection conn = new SqlConnection(connectionString)) 
    { 
        using (SqlCommand cmd = new SqlCommand(commandText, conn)) 
        { 
            cmd.CommandType = commandType; 
            cmd.Parameters.AddRange(parameters); 
            conn.Open(); 
            return cmd.ExecuteNonQuery(); 
        } 
    } 
} 

2. ExecuteScalar method
If you only need one value (first column and first row), you can use the method to get the value, such as the statistical value.

C#

public static Object ExecuteScalar(String connectionString, String commandText,
    CommandType commandType, params SqlParameter[] parameters)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            cmd.CommandType = commandType;
            cmd.Parameters.AddRange(parameters);
            conn.Open();
            return cmd.ExecuteScalar();
        }
    }
}

Visual Basic

Public Shared Function ExecuteScalar(ByVal connectionString As String, ByVal commandText As String,
                                     ByVal commandType As CommandType, ByVal ParamArray parameters() As SqlParameter) As Object
    Using conn As New SqlConnection(connectionString)
        Using cmd As New SqlCommand(commandText, conn)
            cmd.CommandType = commandType
            cmd.Parameters.AddRange(parameters)
            conn.Open()
            Return cmd.ExecuteScalar()
        End Using
    End Using
End Function

public static Object ExecuteScalar(String connectionString, String commandText, 
    CommandType commandType, params SqlParameter[] parameters) 
{ 
    using (SqlConnection conn = new SqlConnection(connectionString)) 
    { 
        using (SqlCommand cmd = new SqlCommand(commandText, conn)) 
        { 
            cmd.CommandType = commandType; 
            cmd.Parameters.AddRange(parameters); 
            conn.Open(); 
            return cmd.ExecuteScalar(); 
        } 
    } 
} 

3. ExecuteReader method
When you need the details of the data, you can use this method to return the information.

C#

public static SqlDataReader ExecuteReader(String connectionString, String commandText, 
    CommandType commandType, params SqlParameter[] parameters)
{
    SqlConnection conn = new SqlConnection(connectionString);
    using (SqlCommand cmd = new SqlCommand(commandText, conn))
    {
        cmd.CommandType = commandType;
        cmd.Parameters.AddRange(parameters);
        conn.Open();
        // When using CommandBehavior.CloseConnection, the connection will be closed when the 
        // IDataReader is closed.
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return reader;
    }
}

Visual Basic

Public Shared Function ExecuteReader(ByVal connectionString As String, ByVal commandText As String,
                                     ByVal commandType As CommandType, ByVal ParamArray parameters() As SqlParameter) As SqlDataReader
    Dim conn As New SqlConnection(connectionString)
    Using cmd As New SqlCommand(commandText, conn)
        cmd.CommandType = commandType
        cmd.Parameters.AddRange(parameters)
        conn.Open()
        ' When using CommandBehavior.CloseConnection, the connection will be closed when the 
        ' IDataReader is closed.
        Dim reader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        Return reader
    End Using
End Function

public static SqlDataReader ExecuteReader(String connectionString, String commandText,  
    CommandType commandType, params SqlParameter[] parameters) 
{ 
    SqlConnection conn = new SqlConnection(connectionString); 
    using (SqlCommand cmd = new SqlCommand(commandText, conn)) 
    { 
        cmd.CommandType = commandType; 
        cmd.Parameters.AddRange(parameters); 
        conn.Open(); 
        // When using CommandBehavior.CloseConnection, the connection will be closed when the // IDataReader is closed. 
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
        return reader; 
    } 
} 

4. Parameter
You can add several parameters to the command and set the properties of parameter, such as the value, the type and the direction. If the direction is set as Output, you can get the value of parameter after executing the command.

C#

// Specify the year of StartDate
SqlParameter parameterYear = new SqlParameter("@Year", SqlDbType.Int);
parameterYear.Value = year;
SqlParameter parameterBudget = new SqlParameter("@BudgetSum", SqlDbType.Money);
parameterBudget.Direction = ParameterDirection.Output;

Visual Basic

' Specify the year of StartDate
Dim parameterYear As New SqlParameter("@Year", SqlDbType.Int)
parameterYear.Value = year
Dim parameterBudget As New SqlParameter("@BudgetSum", SqlDbType.Money)
parameterBudget.Direction = ParameterDirection.Output

// Specify the year of StartDate 
SqlParameter parameterYear = new SqlParameter("@Year", SqlDbType.Int); 
parameterYear.Value = year; 
SqlParameter parameterBudget = new SqlParameter("@BudgetSum", SqlDbType.Money); 
parameterBudget.Direction = ParameterDirection.Output; 

5. Command Type.
There're three command types: StoredProcedure, Text (Default), TableDirect. The TableDirect type is only for OLE DB.

C#

using (SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, commandText, 
               CommandType.StoredProcedure, parameterYear, parameterBudget))

Visual Basic

Using reader As SqlDataReader = SqlHelper.ExecuteReader(connectionString, commandText,
                                                        CommandType.StoredProcedure,
                                                        parameterYear, parameterBudget)

using (SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, commandText,  
               CommandType.StoredProcedure, parameterYear, parameterBudget))