How to Create and Execute SqlCommand in ADO.NET
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 methodIf 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))
