**Course Title:** Mastering C#: From Fundamentals to Advanced Programming **Section Title:** Database Connectivity with ADO.NET and Entity Framework **Topic:** CRUD operations (Create, Read, Update, Delete) with SQL databases ## Introduction to CRUD Operations CRUD (Create, Read, Update, Delete) operations are fundamental to any database management system. In this topic, we'll explore how to perform CRUD operations with SQL databases using ADO.NET. You'll learn how to create, read, update, and delete data in a SQL database, and we'll discuss best practices for optimizing your database interactions. ### What are CRUD Operations? * **Create**: Adding new data to a database. * **Read**: Retrieving existing data from a database. * **Update**: Modifying existing data in a database. * **Delete**: Deleting existing data from a database. ## Setting Up the Sample Database Before we dive into CRUD operations, let's set up a sample database that we'll use throughout this topic. We'll use the Microsoft SQL Server Express edition, which you can download from the [Microsoft website](https://www.microsoft.com/en-us/sql-server/sql-server-downloads). Create a new database called `CRUD_Database` and add a table called `Employees` with the following columns: | Column Name | Data Type | | --- | --- | | EmployeeID | int (primary key) | | FirstName | nvarchar(50) | | LastName | nvarchar(50) | | Email | nvarchar(100) | ## ADO.NET Fundamentals Before we perform CRUD operations, let's review some ADO.NET fundamentals: * **Connection Strings**: Used to connect to the database. Ensure you have the correct connection string for your database. * **SqlCommand**: Used to execute SQL commands against the database. * **SqlDataReader**: Used to retrieve data from the database. ## CRUD Operations ### Create To create a new employee record, we'll use the `SqlCommand` object to execute an `INSERT INTO` statement. ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string query = "INSERT INTO Employees (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@FirstName", "John"); command.Parameters.AddWithValue("@LastName", "Doe"); command.Parameters.AddWithValue("@Email", "john.doe@example.com"); command.ExecuteNonQuery(); } ``` ### Read To retrieve employee records, we'll use the `SqlCommand` object to execute a `SELECT *` statement and the `SqlDataReader` object to retrieve the data. ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string query = "SELECT * FROM Employees"; SqlCommand command = new SqlCommand(query, connection); SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine($"{reader["EmployeeID"]}: {reader["FirstName"]} {reader["LastName"]}"); } } ``` ### Update To update an existing employee record, we'll use the `SqlCommand` object to execute an `UPDATE` statement. ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string query = "UPDATE Employees SET FirstName = @FirstName WHERE EmployeeID = @EmployeeID"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@FirstName", "Jane"); command.Parameters.AddWithValue("@EmployeeID", 1); command.ExecuteNonQuery(); } ``` ### Delete To delete an existing employee record, we'll use the `SqlCommand` object to execute a `DELETE` statement. ```csharp using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); string query = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID"; SqlCommand command = new SqlCommand(query, connection); command.Parameters.AddWithValue("@EmployeeID", 1); command.ExecuteNonQuery(); } ``` ## Best Practices * Always use parameterized queries to prevent SQL injection attacks. * Use `using` statements to ensure connections are closed and disposed of properly. * Optimize your database interactions by batching operations and minimizing round-trips to the database. ## Conclusion In this topic, we explored CRUD operations with SQL databases using ADO.NET. You learned how to create, read, update, and delete data in a SQL database, and we discussed best practices for optimizing your database interactions. ## Assignment Create a console application that performs CRUD operations on the `Employees` table. Implement the following functionality: * Create a new employee record * Retrieve all employee records * Update an existing employee record * Delete an existing employee record **Please post any questions or seek help in the comments section below if you need assistance or clarification on any of the concepts covered in this topic.** Next topic: **Entity Framework Basics and ORM (Object-Relational Mapping)**