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

Popular posts from this blog

PrimeNG tutorial with examples using frequently used classes

Docker and Kubernetes Tutorials and QnA

oAuth in angular