3 Easy Ways to Connect a Database to C#

Connecting a database to a C# application is a fundamental step in building data-driven software. Depending on your project's architecture, performance requirements, and development approach, you can connect your database using Entity Framework, ADO.NET, or Visual Studio Server Explorer.

In this guide, Viettel IDC walks you through three of the most common and effective methods for connecting a database to C#, complete with step-by-step instructions.

Method 1: Connect a Database Using Visual Studio Server Explorer

Visual Studio's Server Explorer allows you to create database connections and browse database objects directly within the IDE without writing any code.

Follow these steps to establish a connection.

Step 1: Open Server Explorer

In Visual Studio, navigate to:

View → Server Explorer

or press:

Ctrl + W, L

Once Server Explorer opens, expand the Data Connections node.

Step 2: Create a New Database Connection

Click the Connect to Database icon on the Server Explorer toolbar, or right-click Data Connections and select Add Connection...

This opens the Add Connection dialog box.

Step 3: Select the Data Source and Configure the Connection

In the Add Connection window, choose the appropriate data source.

For example:

- Select Microsoft SQL Server to connect to a SQL Server instance.

- Select Microsoft SQL Server Database File when connecting to a local .mdf database file.

Next, provide the required connection information:

- Server Name

- Database Name

- Authentication Method

If SQL Server LocalDB is not installed on your computer, you can add it through the Visual Studio Installer.

Step 4: Test the Connection

Click Test Connection to verify the settings.

If everything is configured correctly, Visual Studio displays the following message:

Test connection succeeded.

If the test fails, review the server name, authentication credentials, and database information, or consult your database provider's documentation.

Step 5: Complete the Connection

Click OK to save the connection.

The database will now appear under Data Connections in Server Explorer, allowing you to browse tables, views, stored procedures, and other database objects directly within Visual Studio.

Method 2: Connect a Database to C# Using Entity Framework

Entity Framework (EF) is Microsoft's Object-Relational Mapping (ORM) framework for .NET. It allows developers to work with databases using C# objects instead of writing SQL statements manually.

Entity Framework significantly simplifies database development while improving code maintainability and productivity.

Step 1: Install Entity Framework

Before connecting your database, install the required Entity Framework packages.

Depending on your .NET version:

- .NET Framework (EF6)

Install the EntityFramework NuGet package.

- .NET 5 / .NET 6 / .NET 7 / .NET Core (EF Core)

Install:

- Microsoft.EntityFrameworkCore

- Microsoft.EntityFrameworkCore.SqlServer

or another provider appropriate for your database.

Step 2: Create Entity Models

Next, create C# classes that represent your database tables.

Each class corresponds to a table, while each property represents a database column.

For example:

A Product class may contain:

- ProductId

- ProductName

- Price

By convention, properties named Id or [ClassName]Id are automatically recognized as primary keys.

Step 3: Create a DbContext Class

Create a class that inherits from DbContext.

The DbContext is responsible for:

- Managing database connections

- Tracking entity changes

- Executing queries

- Saving data

Within the DbContext, define a DbSet<TEntity> for each entity.

Example:

public DbSet<Product> Products { get; set; }

You'll also configure the database connection string within the DbContext or through your application's configuration file.

Step 4: Configure the Connection String

Store your connection string in:

- App.config or Web.config (.NET Framework)

- appsettings.json (.NET Core / .NET 5+)

Example:

<connectionStrings>

    <add name="MyConnectionString"

         connectionString="Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True"

         providerName="System.Data.SqlClient"/>

</connectionStrings>

In this example:

- Data Source=. refers to the local SQL Server instance.

- Initial Catalog specifies the target database.

- Integrated Security=True uses Windows Authentication.

If you're using SQL Server Authentication, replace it with:

User ID=your_username;

Password=your_password;

Step 5: Create and Use the DbContext

Once the configuration is complete, instantiate your DbContext wherever database access is required—for example, inside the application's Main() method or within a service class.

Entity Framework supports LINQ, allowing developers to query data using strongly typed C# syntax.

Common operations include:

- Querying with LINQ

- Add()

- Remove()

- Update()

- SaveChanges()

This approach eliminates much of the boilerplate SQL code traditionally required.

Step 6: Create or Migrate the Database

If you're using the Code First approach, Entity Framework can automatically generate the database from your model classes.

For EF Core, you can call:

Database.EnsureCreated()

or use EF Core Migrations.

For Entity Framework 6, execute the following commands in the Package Manager Console:

Enable-Migrations

 

Update-Database

These commands create or update the database schema to match your entity models.

Method 3: Connect a Database in C# WinForms Using ADO.NET

For Windows Forms applications, ADO.NET remains one of the most widely used technologies for database connectivity.

Although Visual Studio provides drag-and-drop data binding tools, connecting through code offers greater flexibility and control.

Step 1: Create a Windows Forms Project

Open Visual Studio and create a new project.

Select:

Windows Forms Application

Choose a project name and storage location, then click OK.

Step 2: Import Required Namespaces

In Form1.cs, add the following namespaces:

using System.Data;

using System.Data.SqlClient;

These provide access to classes such as:

- SqlConnection

- SqlCommand

- SqlDataAdapter

- SqlDataReader

Step 3: Prepare the Connection String

Create a SQL Server connection string.

You can either store it in App.config or define it directly in code.

Windows Authentication

Data Source=SERVER_NAME;

Initial Catalog=DATABASE_NAME;

Integrated Security=True;

SQL Server Authentication

Data Source=SERVER_NAME;

Initial Catalog=DATABASE_NAME;

User ID=USERNAME;

Password=PASSWORD;

For LocalDB, use:

(localdb)\MSSQLLocalDB

Step 4: Open the Database Connection

Create a SqlConnection object using the connection string.

Call:

SqlConnection.Open()

Always wrap the connection in a try-catch block to handle errors such as:

- Invalid connection strings

- Incorrect credentials

- Missing databases

- Network connectivity issues

Step 5: Execute Queries and Retrieve Data

There are two common approaches for reading data in ADO.NET.

Option 1: SqlDataAdapter + DataTable

This method is ideal for displaying data in controls such as DataGridView.

The workflow is:

- Create a SqlDataAdapter using a SELECT statement.

- Fill a DataTable using:

Fill(DataTable)

- Assign the DataTable to:

DataGridView.DataSource

This approach is simple and well suited for desktop applications.

Option 2: SqlDataReader

SqlDataReader retrieves data one row at a time.

The typical workflow includes:

- Creating a SqlCommand

- Calling:

ExecuteReader()

- Looping through the SqlDataReader

This method is faster and more memory-efficient when processing large result sets sequentially.

Step 6: Display Data and Close the Connection

When using SqlDataAdapter, all records are loaded into memory after calling Fill(), allowing the database connection to be closed immediately.

When using SqlDataReader, keep the connection open until all rows have been processed.

After completing your database operations, always close the connection:

SqlConnection.Close()

Alternatively, use a using statement to ensure resources are automatically released.

Which Database Connection Method Should You Choose?

Each approach offers different advantages depending on your application's requirements.

There is no universally "best" method. Your choice should depend on your project's complexity, performance requirements, and development style.

Conclusion

Understanding how to connect a database to C# is an essential skill for every .NET developer.

Whether you choose Visual Studio Server Explorer, Entity Framework, or ADO.NET, each approach provides unique advantages for different application scenarios. Selecting the right technology will help improve development efficiency, simplify database management, and build more scalable applications.

Simplify Database Management with Viettel Database Service

If your business requires a professional database platform with high availability, automatic backup and recovery, enterprise-grade security, and 24/7 technical support, consider Viettel Database Service.

Designed for modern enterprises, the service provides a comprehensive database management solution that optimizes performance, enhances security, and reduces operational costs - making it ideal for organizations modernizing their IT infrastructure or accelerating digital transformation.

Learn more about Viettel Database Service at:

https://viettelidc.com.vn/viettel-database-service

Contact Viettel IDC

For expert consultation and technical support, please contact Viettel IDC through the following channels:

- Hotline: 1800 8088 (Toll-free)

- Facebook: https://www.facebook.com/viettelidc

- Website: https://viettelidc.com.vn/en/home