**Course Title:** Mastering C#: From Fundamentals to Advanced Programming **Section Title:** Database Connectivity with ADO.NET and Entity Framework **Topic:** Build a C# application that connects to a database and performs CRUD operations.(Lab topic) **Introduction** In this lab, you will learn how to build a C# application that connects to a database and performs CRUD (Create, Read, Update, Delete) operations using ADO.NET and Entity Framework. This hands-on exercise will help you apply the concepts you have learned so far and gain practical experience in working with databases. **Prerequisites** * You should have a basic understanding of C# programming and the .NET Framework. * You should have completed the previous topics in this course, including ADO.NET and Entity Framework basics. **Step 1: Create a new C# project** * Open Visual Studio and create a new C# project. Choose the "Console App (.NET Core)" template. * Name your project, for example, "CrudApplication". **Step 2: Install the required packages** * In the Solution Explorer, right-click on your project and select "Manage NuGet Packages". * Install the following packages: + "Microsoft.EntityFrameworkCore.SqlServer" (for SQL Server) + "Microsoft.EntityFrameworkCore.Tools" (for migration) + "System.Data.SqlClient" (for ADO.NET) **Step 3: Create a database connection string** * Go to the "appsettings.json" file and add the following code: ```json { "ConnectionStrings": { "CrudDatabase": "Server=your_server_name;Database=CrudDatabase;User ID=your_username;Password=your_password;" } } ``` Replace the placeholders with your actual database connection details. **Step 4: Create a database using ADO.NET** * Create a new class file, for example, "DatabaseHelper.cs". * Add the following code to create a database using ADO.NET: ```csharp using System.Data.SqlClient; public class DatabaseHelper { public static void CreateDatabase() { string connectionString = "Data Source=your_server_name;Initial Catalog=master;Integrated Security=True"; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string sql = "CREATE DATABASE CrudDatabase"; using (SqlCommand command = new SqlCommand(sql, connection)) { command.ExecuteNonQuery(); } } } } ``` Replace the placeholder with your actual server name. **Step 5: Create a data model using Entity Framework** * Create a new class file, for example, "User.cs". * Add the following code to define a simple data model: ```csharp using System; public class User { public int Id { get; set; } public string Name { get; set; } public string Email { get; set; } } ``` **Step 6: Create a DbContext class using Entity Framework** * Create a new class file, for example, "CrudContext.cs". * Add the following code to define a DbContext class: ```csharp using Microsoft.EntityFrameworkCore; public class CrudContext : DbContext { public CrudContext(DbContextOptions<CrudContext> options) : base(options) { } public DbSet<User> Users { get; set; } } ``` **Step 7: Configure the DbContext class in the Startup.cs file** * Open the "Startup.cs" file and add the following code to configure the DbContext class: ```csharp using Microsoft.EntityFrameworkCore; public class Startup { public void ConfigureServices(IServiceCollection services) { services.AddDbContext<CrudContext>(options => options.UseSqlServer(Configuration.GetConnectionString("CrudDatabase"))); } // ... } ``` **Step 8: Perform CRUD operations** * Create a new class file, for example, "CrudOperations.cs". * Add the following code to perform CRUD operations: ```csharp using System; using Microsoft.EntityFrameworkCore; public class CrudOperations { private readonly CrudContext _context; public CrudOperations(CrudContext context) { _context = context; } public async Task CreateUserAsync(User user) { _context.Users.Add(user); await _context.SaveChangesAsync(); } public async Task<User> GetUserAsync(int id) { return await _context.Users.FindAsync(id); } public async Task UpdateUserAsync(User user) { _context.Users.Update(user); await _context.SaveChangesAsync(); } public async Task DeleteUserAsync(int id) { var user = await _context.Users.FindAsync(id); if (user != null) { _context.Users.Remove(user); await _context.SaveChangesAsync(); } } } ``` **Step 9: Call the CRUD operations in the Console App** * Open the "Program.cs" file and add the following code to call the CRUD operations: ```csharp class Program { static async Task Main(string[] args) { // Create a new instance of the CrudOperations class var crudOperations = new CrudOperations(); // Create a new user var user = new User { Name = "John Doe", Email = "john.doe@example.com" }; await crudOperations.CreateUserAsync(user); // Get the user by ID var getUser = await crudOperations.GetUserAsync(1); // Update the user getUser.Name = "Jane Doe"; await crudOperations.UpdateUserAsync(getUser); // Delete the user await crudOperations.DeleteUserAsync(1); } } ``` **Conclusion** In this lab, you have learned how to build a C# application that connects to a database and performs CRUD operations using ADO.NET and Entity Framework. You have applied the concepts you have learned so far and gained practical experience in working with databases. **External Resources** * [Entity Framework Core documentation](https://docs.microsoft.com/en-us/ef/core/) * [ADO.NET documentation](https://docs.microsoft.com/en-us/dotnet/framework/data/adonet-ado-net-overview) **What's Next** In the next topic, we will learn about Windows Forms for desktop application development. If you have any questions or need help with this lab, please leave a comment below.