C# tutorial CRUD tutorials with example

-

 

C# Tutorial: CRUD Operations with Example

In this tutorial, we'll cover the basics of CRUD operations (Create, Read, Update, Delete) in C#. We'll go through the key concepts and implement them using C# and a SQL Server database to perform the database operations.

1. Prerequisites

  • C# and .NET Framework: Basic knowledge of C# and .NET framework.
  • SQL Server: We’ll use SQL Server for the database.
  • Visual Studio: A popular IDE to write and test C# code.
  • SQL Server Management Studio (SSMS): To interact with SQL Server.

2. Setting Up the Environment

Before we dive into the code, make sure that:

  • You have Visual Studio installed.
  • You have SQL Server running, either locally or remotely, and can connect to it.

3. SQL Database Setup

We will start by creating a simple database called SampleDB and a table called Users for storing user information.

SQL Script to Create the Database and Table:

sql
CREATE DATABASE SampleDB;
GO

USE SampleDB;
GO

CREATE TABLE Users (
    Id INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);
GO

The Users table will store the following fields:

  • Id: A unique identifier for each user (Primary Key).
  • FirstName: The user's first name.
  • LastName: The user's last name.
  • Email: The user's email address.

4. C# Code Setup

We will now create a C# application to perform CRUD operations. Below are the steps.

Step 1: Create a Console Application in Visual Studio

  1. Open Visual Studio and create a new Console App project.
  2. Name the project something like CRUDApp.

Step 2: Install ADO.NET for Database Connection

To interact with the SQL Server, we need to use ADO.NET.

In your Visual Studio project:

  1. Right-click on References > Manage NuGet Packages.
  2. Search for System.Data.SqlClient and install it.

5. C# CRUD Code

We will create methods for Create, Read, Update, and Delete operations.

Step 3: Create a Class for User Entity

Let's start by creating a User class to represent the user entity:

csharp
public class User
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

This class will represent the user data that we'll be working with.

Step 4: Create the CRUD Operations

Next, we'll create a DatabaseHelper class to interact with the SQL Server database.

csharp
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

public class DatabaseHelper
{
    private string connectionString = @"Server=localhost;Database=SampleDB;Trusted_Connection=True;";

    // Create
    public void CreateUser(User user)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Users (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@FirstName", user.FirstName);
            cmd.Parameters.AddWithValue("@LastName", user.LastName);
            cmd.Parameters.AddWithValue("@Email", user.Email);

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    // Read (Get all users)
    public List<User> GetUsers()
    {
        List<User> users = new List<User>();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Users";
            SqlCommand cmd = new SqlCommand(query, conn);

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                User user = new User
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    FirstName = reader["FirstName"].ToString(),
                    LastName = reader["LastName"].ToString(),
                    Email = reader["Email"].ToString()
                };
                users.Add(user);
            }
        }

        return users;
    }

    // Update
    public void UpdateUser(User user)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "UPDATE Users SET FirstName = @FirstName, LastName = @LastName, Email = @Email WHERE Id = @Id";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@FirstName", user.FirstName);
            cmd.Parameters.AddWithValue("@LastName", user.LastName);
            cmd.Parameters.AddWithValue("@Email", user.Email);
            cmd.Parameters.AddWithValue("@Id", user.Id);

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    // Delete
    public void DeleteUser(int userId)
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "DELETE FROM Users WHERE Id = @Id";
            SqlCommand cmd = new SqlCommand(query, conn);

            cmd.Parameters.AddWithValue("@Id", userId);

            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Explanation of CRUD Methods:

  • CreateUser(User user): This method inserts a new record into the Users table.
  • GetUsers(): This method retrieves all the users from the Users table and returns them as a list of User objects.
  • UpdateUser(User user): This method updates an existing user’s details based on the Id.
  • DeleteUser(int userId): This method deletes a user record from the Users table based on the Id.

Step 5: Implement CRUD Operations in the Main Program

Now, let’s use the DatabaseHelper class in our Main method to test the CRUD operations.

csharp
class Program
{
    static void Main(string[] args)
    {
        DatabaseHelper db = new DatabaseHelper();

        // Create a new user
        User newUser = new User { FirstName = "John", LastName = "Doe", Email = "john.doe@example.com" };
        db.CreateUser(newUser);
        Console.WriteLine("User created successfully.");

        // Read all users
        List<User> users = db.GetUsers();
        Console.WriteLine("Users in the database:");
        foreach (var user in users)
        {
            Console.WriteLine($"{user.Id}: {user.FirstName} {user.LastName}, Email: {user.Email}");
        }

        // Update a user (assume we know the Id of the user we want to update)
        User userToUpdate = users[0]; // Update the first user in the list
        userToUpdate.FirstName = "Jane";
        userToUpdate.LastName = "Doe";
        userToUpdate.Email = "jane.doe@example.com";
        db.UpdateUser(userToUpdate);
        Console.WriteLine("User updated successfully.");

        // Delete a user (assume we know the Id of the user we want to delete)
        db.DeleteUser(users[0].Id); // Delete the first user
        Console.WriteLine("User deleted successfully.");
    }
}

Explanation of the Main Program:

  1. Create a User: We create a new User object and pass it to the CreateUser method to insert it into the database.
  2. Read Users: We retrieve all users from the database using the GetUsers method and display them.
  3. Update User: We update the first user’s details and call the UpdateUser method.
  4. Delete User: We delete the first user by calling the DeleteUser method.

6. Testing the Application

  • After running the application, the following operations will be executed in sequence:
    1. A new user will be created and added to the database.
    2. All users will be displayed in the console.
    3. The first user’s details will be updated.
    4. The first user will be deleted from the database.

You should see the output similar to the following in your console:

sql
User created successfully.
Users in the database:
1: John Doe, Email: john.doe@example.com
User updated successfully.
User deleted successfully.

7. Conclusion

In this tutorial, we learned how to perform CRUD operations (Create, Read, Update, Delete) using C# and SQL Server. The key concepts we covered include:

  • Setting up a database and creating a table.
  • Using ADO.NET to interact with the database in C#.
  • Creating methods for each CRUD operation.
  • Testing the operations with sample data.

This pattern is the foundation for building data-driven applications using C# and SQL Server.

Comments

Post a Comment

Popular posts from this blog

PrimeNG tutorial with examples using frequently used classes

-
  PrimeNG Tutorial: Frequently Used Classes and Components PrimeNG is a comprehensive and flexible UI component library for Angular applications. It provides a wide range of ready-to-use components like forms, data tables, dialogs, buttons, and many others, all styled using Material Design principles. In this tutorial, we will explore some of the frequently used components and classes in PrimeNG , along with examples for creating interactive, modern, and responsive UIs. 1. Setting Up PrimeNG Before using PrimeNG components, you need to install PrimeNG in your Angular project. If you haven’t done so, follow these steps: Step 1: Install PrimeNG and PrimeIcons Run the following commands to install PrimeNG and PrimeIcons: bash Copy code npm install primeng npm install primeicons Step 2: Import Required Modules After installation, you need to import the relevant PrimeNG modules in your app.module.ts . Example of importing commonly used modules: typescript Copy code import { NgModule ...
Read more

Docker and Kubernetes Tutorials and QnA

-
 Docker and Kubernetes Tutorials: Detailed Guide Let's dive into detailed tutorials for Docker and Kubernetes, starting with their core concepts and progressing to hands-on examples. These tutorials are designed for both beginners and intermediate learners. Part 1: Docker Tutorial 1. Introduction to Docker Docker is a platform used to develop, ship, and run applications inside containers. Containers are lightweight, portable, and ensure consistent environments across different stages of the application lifecycle. They help solve the "works on my machine" problem. Key Concepts of Docker: Images : A blueprint for containers, containing everything needed to run an application (code, runtime, libraries, etc.). Containers : A runtime instance of an image, running isolated from the host system. Dockerfile : A script with a set of instructions to build Docker images. Docker Hub : A public repository of Docker images. Docker Compose : A tool to define and manage multi-container a...
Read more

oAuth in angular

-
 Implementing OAuth in an Angular application is a common requirement when you want to integrate with third-party services that require user authentication (e.g., Google, Facebook, GitHub) or enable Single Sign-On (SSO) across applications. OAuth allows users to authorize your app to access their data without sharing their credentials directly. In an Angular app, OAuth 2.0 is typically implemented using libraries like OAuth.js or Auth0 (for simpler management), or you can build the OAuth flow yourself. Below is a detailed guide on how OAuth 2.0 can be integrated into an Angular application using OAuth 2.0 with an identity provider and token handling. 1. Understand OAuth Flow OAuth 2.0 typically follows this flow: Authorization Code Flow : This is the most commonly used flow, which is secure and recommended for web applications. Implicit Flow : Used for client-side (JavaScript) applications, but not recommended anymore due to security concerns (e.g., exposing tokens in the URL)...
Read more