C# - With SQL Server

Performing CRUD (Create, Read, Update, Delete) operations with SQL Server in C# involves connecting to a SQL Server database, executing SQL commands, and handling the results. Here’s a guide to doing CRUD operations using C# and SQL Server:

1. Set Up SQL Server

Ensure you have SQL Server installed and running, and that you have a database and table(s) set up. Use SQL Server Management Studio (SSMS) or another client to manage your database.

2. Install SQL Server Data Provider

You need the System.Data.SqlClient or Microsoft.Data.SqlClient library to interact with SQL Server from C#. For .NET Core or later, you should use Microsoft.Data.SqlClient.

Using Visual Studio:

Right-click on your project in Solution Explorer.
Choose "Manage NuGet Packages."
Search for Microsoft.Data.SqlClient and install it.

Using .NET CLI:

bash
dotnet add package Microsoft.Data.SqlClient

3. Perform CRUD Operations

Below are examples of how to perform CRUD operations using C# and SQL Server.

Create (Insert Data)

csharp
using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

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

            string insertQuery = "INSERT INTO YourTable (Column1, Column2) VALUES (@Value1, @Value2)";
            using (SqlCommand command = new SqlCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@Value1", "SampleValue1");
                command.Parameters.AddWithValue("@Value2", "SampleValue2");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read (Select Data)

csharp
using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

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

            string selectQuery = "SELECT * FROM YourTable";
            using (SqlCommand command = new SqlCommand(selectQuery, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string column1Value = reader["Column1"].ToString();
                        string column2Value = reader["Column2"].ToString();
                        Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
                    }
                }
            }
        }
    }
}

Update (Modify Data)

csharp
using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

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

            string updateQuery = "UPDATE YourTable SET Column1 = @NewValue WHERE Column2 = @Condition";
            using (SqlCommand command = new SqlCommand(updateQuery, connection))
            {
                command.Parameters.AddWithValue("@NewValue", "UpdatedValue");
                command.Parameters.AddWithValue("@Condition", "ConditionValue");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete (Remove Data)

csharp
using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

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

            string deleteQuery = "DELETE FROM YourTable WHERE Column1 = @Condition";
            using (SqlCommand command = new SqlCommand(deleteQuery, connection))
            {
                command.Parameters.AddWithValue("@Condition", "ValueToDelete");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

4. Handling Exceptions

Proper exception handling ensures that your application can gracefully manage errors. Use try-catch blocks to handle exceptions.

csharp
using Microsoft.Data.SqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

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

                // Perform CRUD operations here

                Console.WriteLine("Operation completed successfully.");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"SQL Error: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

5. Parameterization

Always use parameterized queries to prevent SQL injection and handle user input safely.

Summary

Create: Use INSERT INTO to add new records.
Read: Use SELECT to retrieve records.
Update: Use UPDATE to modify existing records.
Delete: Use DELETE to remove records.

This guide provides a foundation for performing CRUD operations with SQL Server in C#. Adjust the connection string, SQL queries, and parameter values as needed for your specific use case. If you have any questions or need further clarification, feel free to ask!

techmore.in