Setting Up Your First ADO.NET Project

Creating a Basic ADO.NET application

ADO.NET is a set of classes that expose data access services for .NET Framework programmers. It provides a rich set of components for creating distributed, data-sharing applications. This guide will walk you through setting up your first ADO.NET project, connecting to a database, and executing simple queries.

Prerequisites

Before you start, ensure you have the following.

Visual Studio installed
SQL Server or any other database management system (DBMS)
Basic knowledge of C# programming

Step 1. Create a New Project

Open Visual Studio.
Click on Create a new project.
Select Console App (.NET Core) and click Next.
Name your project (e.g., ADO.NETDemo) and click Create.

Step 2. Install ADO.NET package

Although ADO.NET is part of the .NET Framework, for .NET Core projects, you might need to install the necessary packages.

Open the NuGet Package Manager by right-clicking on your project in the Solution Explorer and selecting Manage NuGet Packages.
Search for System.Data.SqlClient.
Install the package.

Step 3. Set up your Database

For this guide, we'll assume you are using SQL Server. Create a database and a table to work with.

Open SQL Server Management Studio (SSMS).
Create a new database named DemoDB.

Create a new table named Employees with the following schema.

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(50),
    Position NVARCHAR(50),
    Salary DECIMAL(18, 2)
);

Insert some sample data into the Employees table.

INSERT INTO Employees (Name, Position, Salary) VALUES 
('John Doe', 'Software Engineer', 60000),
('Jane Smith', 'Project Manager', 75000),
('Sam Brown', 'HR Specialist', 50000);

Step 4. Connect to the Database

Now, let's write some C# code to connect to the database and execute simple queries.

Open Program. cs in your project.
Add the following using directives at the top of the file.
```
using System;
using System.Data.SqlClient;
```

Write the code to connect to the database and fetch data.

namespace ADO.NETDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "Server=your_server_name;Database=DemoDB;User Id=your_username;Password=your_password;";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    Console.WriteLine("Connection to database established successfully.");

                    string query = "SELECT * FROM Employees";
                    SqlCommand command = new SqlCommand(query, connection);

                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}

Step 5. Run the Application

Press F5 or click on Run to execute the application.
If everything is set up correctly, you should see the output displaying the employee data from the database.

Summary

Congratulations! You have successfully set up your first ADO.NET project, connected to a database, and executed a simple query. This is just the beginning—ADO.NET offers many more features and capabilities for data access and manipulation. Experiment with different queries, commands, and data operations to further enhance your understanding and skills in ADO.NET.