Visual Basic .NET - ADO .Net - SqlCommand Class

The SqlCommand class in VB.NET is part of the ADO.NET framework and is used to execute SQL commands or stored procedures against a SQL Server database. It represents a command to be executed and provides methods and properties for setting the command text, parameters, and executing the command.

CommandText: The CommandText property is used to set the SQL command or stored procedure to be executed. It can be a SQL query, an INSERT, UPDATE, or DELETE statement, or the name of a stored procedure.

CommandType: The CommandType property specifies the type of command being executed. It can be set to CommandType.Text for SQL queries or CommandType.StoredProcedure for stored procedures.

Parameters: The Parameters property allows you to specify parameters for the command. Parameters are used to pass values to the SQL command or stored procedure and can help prevent SQL injection attacks. You can add parameters to the Parameters collection of the SqlCommand object using the Add() method.

ExecuteNonQuery: The ExecuteNonQuery method is used to execute a command that does not return any result set, such as an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected by the command.

ExecuteScalar: The ExecuteScalar method is used to execute a command that returns a single value, such as a SELECT statement with an aggregate function. It returns the first column of the first row in the result set as an object.

ExecuteReader: The ExecuteReader method is used to execute a command that returns a result set, such as a SELECT statement. It returns a SqlDataReader object that can be used to retrieve the rows from the result set.

Transaction Support: The SqlCommand class supports transactions, allowing you to group multiple database operations into a single transaction. You can assign a SqlTransaction object to the Transaction property of the SqlCommand to execute commands within a transaction.

Here's an example that demonstrates the usage of the SqlCommand class:

Imports System.Data.SqlClient
Module Program
    Sub Main()
        ' Connection string
        Dim connectionString As String = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password"
        ' SQL command
        Dim commandText As String = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)"
        ' Create a new SqlConnection object
        Dim connection As New SqlConnection(connectionString)
        Try
            ' Open the connection
            connection.Open()
            ' Create a new SqlCommand object
            Dim command As New SqlCommand(commandText, connection)
            ' Add parameters to the command
            command.Parameters.AddWithValue("@Name", "John Doe")
            command.Parameters.AddWithValue("@Email", "[email protected]")
            ' Execute the command
            Dim rowsAffected As Integer = command.ExecuteNonQuery()
            Console.WriteLine("Rows Affected: " & rowsAffected)
        Catch ex As Exception
            ' Handle any exceptions
            Console.WriteLine("Error: " & ex.Message)
        Finally
            ' Ensure the connection is always closed
            If connection.State = ConnectionState.Open Then
                connection.Close()
            End If
        End Try
    End Sub
End Module

In the example, replace "ServerName", "DatabaseName", "Username", and "Password" with the appropriate values for your SQL Server database. The SqlCommand object is created with the command text and the SqlConnection object. Parameters are added to the command using the AddWithValue() method. The ExecuteNonQuery method is then called to execute the command and return the number of rows affected.

The SqlCommand class provides a powerful and flexible way to execute SQL commands and stored procedures against a SQL Server database in VB.NET applications. It allows you to interact with the database and perform various operations, such as inserting, updating,