SQL Server CRUD Tutorials in C#: A Step-by-Step Guide

SQL Server is a powerful, enterprise-grade relational database management system developed by Microsoft, and C# is a versatile programming language. In this tutorial, we will explore the basics of performing CRUD (Create, Read, Update, Delete) operations in SQL Server using C#. We’ll cover the following steps:

Setting Up Your Environment:
- Installing SQL Server.
- Setting up your C# development environment.
Connecting to SQL Server:
- Creating a connection to your SQL Server database.
Creating a Table:
- Writing C# code to create a table in your SQL Server database.
Inserting Data:
- Demonstrating how to insert data into the table.
Querying Data:
- Retrieving data from the table.
Updating Data:
- Modifying existing records in the table.
Deleting Data:
- Deleting records from the table.

1. Setting Up Your Environment

Installing SQL Server

Download and install SQL Server from the official Microsoft website.

Setting Up Your C# Development Environment

Install Visual Studio or Visual Studio Code, and ensure you have the .NET SDK installed.

2. Connecting to SQL Server

To connect to your SQL Server database from a C# application, you can use the System.Data.SqlClient library. This library is included with .NET by default.

Now, let’s create a connection to your SQL Server database:

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

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=mydatabase;User=myuser;Password=mypassword;";
        SqlConnection connection = new SqlConnection(connectionString);

        try
        {
            connection.Open();
            Console.WriteLine("Connected to SQL Server Database!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace localhost, mydatabase, myuser, and mypassword with your SQL Server details.

3. Creating a Table

Let’s create a simple users table in your SQL Server database:

string createTableSql = "CREATE TABLE IF NOT EXISTS users (" +
    "id INT IDENTITY(1,1) PRIMARY KEY," +
    "name VARCHAR(255) NOT NULL," +
    "email VARCHAR(255) NOT NULL)";
SqlCommand createTableCommand = new SqlCommand(createTableSql, connection);

try
{
    connection.Open();
    createTableCommand.ExecuteNonQuery();
    Console.WriteLine("Table created!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code creates a table named users with columns id, name, and email.

4. Inserting Data

Now, let’s insert a user into the users table:

string insertSql = "INSERT INTO users (name, email) VALUES (@name, @email)";
SqlCommand insertCommand = new SqlCommand(insertSql, connection);

// Parameters
insertCommand.Parameters.AddWithValue("@name", "John Doe");
insertCommand.Parameters.AddWithValue("@email", "[email protected]");

try
{
    connection.Open();
    int rowsAffected = insertCommand.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code inserts a user with the name “John Doe” and email “[email protected]” into the users table.

5. Querying Data

Let’s retrieve data from the users table:

string query = "SELECT * FROM users";
SqlCommand queryCommand = new SqlCommand(query, connection);

try
{
    connection.Open();
    SqlDataReader reader = queryCommand.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code queries and displays all records in the users table.

6. Updating Data

Let’s update a user’s email address:

string updateSql = "UPDATE users SET email = @newEmail WHERE name = @name";
SqlCommand updateCommand = new SqlCommand(updateSql, connection);

// Parameters
updateCommand.Parameters.AddWithValue("@newEmail", "[email protected]");
updateCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = updateCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code updates the email address of the user with the name “John Doe” in the users table.

7. Deleting Data

Let’s delete a user from the users table:

string deleteSql = "DELETE FROM users WHERE name = @name";
SqlCommand deleteCommand =

 new SqlCommand(deleteSql, connection);

// Parameter
deleteCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = deleteCommand.ExecuteNonQuery();
    Console.WriteLine($"Deleted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code deletes the user with the name “John Doe” from the users table.

With these CRUD operations, you have a solid foundation for working with SQL Server in your C# applications. SQL Server’s scalability and robust features make it a great choice for enterprise-level applications. Feel free to extend and adapt these examples to meet the requirements of your specific project.