C# Command: Operation Database

In this tutorial, we'll demonstrate how to perform basic CRUD (Create, Read, Update, and Delete) operations on a database using ADO.NET SqlCommand in C#. We'll use a simple example of an Employee table in a SQL Server database.

Set up the database

Create a new database and a table called 'Employees':

CREATE DATABASE EmployeeDB;

USE EmployeeDB;

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

Create a C# Console Application

Create a new C# Console Application project in Visual Studio and add the following namespaces:

using System;
using System.Data;
using System.Data.SqlClient;

Set up the connection string

Create a connection string with the necessary credentials to connect to your SQL Server instance:

string connectionString = "Server=localhost;Database=EmployeeDB;User Id=myUser;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);

CRUD Operations

Now, let's see how to perform CRUD operations using SqlCommand:

Create (INSERT):

static void InsertEmployee(SqlConnection connection, string firstName, string lastName)
{
    string insertQuery = "INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)";
    SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
    insertCommand.Parameters.AddWithValue("@FirstName", firstName);
    insertCommand.Parameters.AddWithValue("@LastName", lastName);

    connection.Open();
    insertCommand.ExecuteNonQuery();
    connection.Close();
}

Read (SELECT):

static void ReadEmployees(SqlConnection connection)
{
    string selectQuery = "SELECT * FROM Employees";
    SqlCommand selectCommand = new SqlCommand(selectQuery, connection);

    connection.Open();
    SqlDataReader reader = selectCommand.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["EmployeeID"]}, Name: {reader["FirstName"]} {reader["LastName"]}");
    }
    reader.Close();
    connection.Close();
}

Update (UPDATE):

static void UpdateEmployee(SqlConnection connection, int employeeId, string newFirstName, string newLastName)
{
    string updateQuery = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName WHERE EmployeeID = @EmployeeID";
    SqlCommand updateCommand = new SqlCommand(updateQuery, connection);
    updateCommand.Parameters.AddWithValue("@FirstName", newFirstName);
    updateCommand.Parameters.AddWithValue("@LastName", newLastName);
    updateCommand.Parameters.AddWithValue("@EmployeeID", employeeId);

    connection.Open();
    updateCommand.ExecuteNonQuery();
    connection.Close();
}

Delete (DELETE):

static void DeleteEmployee(SqlConnection connection, int employeeId)
{
    string deleteQuery = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
    SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection);
    deleteCommand.Parameters.AddWithValue("@EmployeeID", employeeId);

    connection.Open();
    deleteCommand.ExecuteNonQuery();
    connection.Close();
}

Test the CRUD operations

In the Main method, call the CRUD operation methods to test them:

static void Main(string[] args)
{
    SqlConnection connection = new SqlConnection(connectionString);

    InsertEmployee(connection, "John", "Doe");
    InsertEmployee(connection, "Jane", "Smith");

    Console.WriteLine("Employees before update:");
    ReadEmployees(connection);

    UpdateEmployee(connection, 1, "Johnathan", "Doe");

    Console.WriteLine("\nEmployees after update:");
    ReadEmployees(connection);

    DeleteEmployee(connection, 2);

    Console.WriteLine("\nEmployees after delete:");
    ReadEmployees(connection);
}

C# Database Operations Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Perform database operations here

            connection.Close();
        }
    }
}

Executing SQL Commands in C#:

using (SqlCommand command = new SqlCommand("SELECT * FROM TableName", connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Access data using reader
        }
    }
}

CRUD Operations in C# with Database:

CRUD stands for Create, Read, Update, and Delete.

// Create
using (SqlCommand command = new SqlCommand("INSERT INTO TableName (Column1, Column2) VALUES (@Value1, @Value2)", connection))
{
    command.Parameters.AddWithValue("@Value1", "NewValue1");
    command.Parameters.AddWithValue("@Value2", "NewValue2");
    command.ExecuteNonQuery();
}

// Read
using (SqlCommand command = new SqlCommand("SELECT * FROM TableName", connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Access data using reader
        }
    }
}

// Update
using (SqlCommand command = new SqlCommand("UPDATE TableName SET Column1 = @NewValue WHERE ID = @ID", connection))
{
    command.Parameters.AddWithValue("@NewValue", "UpdatedValue");
    command.Parameters.AddWithValue("@ID", 1);
    command.ExecuteNonQuery();
}

// Delete
using (SqlCommand command = new SqlCommand("DELETE FROM TableName WHERE ID = @ID", connection))
{
    command.Parameters.AddWithValue("@ID", 1);
    command.ExecuteNonQuery();
}

C# SqlCommand for Database Manipulation:

SqlCommand is used to execute SQL commands.

using (SqlCommand command = new SqlCommand("INSERT INTO TableName (Column1, Column2) VALUES (@Value1, @Value2)", connection))
{
    command.Parameters.AddWithValue("@Value1", "NewValue1");
    command.Parameters.AddWithValue("@Value2", "NewValue2");
    command.ExecuteNonQuery();
}

Executing Stored Procedures in C# with SqlCommand:

using (SqlCommand command = new SqlCommand("StoredProcedureName", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    // Add parameters if needed
    command.Parameters.AddWithValue("@Parameter1", value1);

    // Execute stored procedure
    command.ExecuteNonQuery();
}

Handling Transactions in C# Database Operations:

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        using (SqlCommand command1 = new SqlCommand("FirstCommand", connection, transaction))
        {
            // Execute command1
            command1.ExecuteNonQuery();
        }

        using (SqlCommand command2 = new SqlCommand("SecondCommand", connection, transaction))
        {
            // Execute command2
            command2.ExecuteNonQuery();
        }

        // Commit the transaction if everything is successful
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // Rollback the transaction in case of an exception
        transaction.Rollback();
        Console.WriteLine("Transaction rolled back: " + ex.Message);
    }
}