C# SQL Server Connection: 3 Easy Methods

Interacting with database servers is a key skill for any C# developer. Whether you are maintaining enterprise applications or building smaller-scale solutions, connecting your .NET code to databases like SQL Server is fundamental.

However, opening and managing connections from C# code to SQL Server can trip up new developers. Handling connections properly using best practices is important for security, performance and reliability reasons.

In this post, we will explore three methods to connect C# SQL Server along with the fundamentals of Database Connectivity and help you establish a C# SQL Server connection.

Introduction to Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS) that helps store and retrieve information as per the demand. Developed by Microsoft, it is one of the best designs to compete with Oracle Database and MySQL. Also, the Software Product supports different Business Intelligence Operations, Transaction Processing, Data Management, and Analytical Operations. The server has numerous versions managed and administered using the Structured Query Language (SQL) language.

Nowadays, various businesses carry sensitive customer information and share files through a network. In this case, using the Microsoft SQL Server will add increased security, speed, and reliability. Furthermore, it helps in maintaining a backup system. There are numerous benefits of using SQL Server in the business world. It also supports different database types.

Fundamentals of Database Connectivity

Various Programming Languages like C# and .Net are compatible with Oracle and Microsoft SQL Server. Also, they follow the same logic with each database in most cases. Here are a few concepts common for all Databases.

• Creating a Connection: The first and foremost step is to establish a connection. In order to work with Database information, establishing a connection is a must. Follow the below-listed parameters to create a connection.
  • Database Name or Data Source: Database Name refers to the name of the Database to which the Developer needs to create a connection. These are case-sensitive with a maximum length of 64 bytes. It comprises letters, numbers, underscores, and symbols. Also, every connection is allowed to work with one Database at a time.
  • Credentials: Secondly, to establish a secure connection to the Database, make sure to generate a username and strong password. This parameter allows only the privileged users to connect and use the database.
  • Optional Parameters: There are a few optional parameters to establish a better connection, such as details on how .net must handle connections or how long the connections will stay active. These parameters can inform users if no action takes place. Also, it helps determine if the connection to the Database needs closure.

• Fetch data from the Database: As you create a connection to the database, the next step is to select information from the source. C# executes ‘SQL’ statements that can help fetch information from the database tables. Hence, to select specific information from the Database, it is recommended to execute the ‘SQL’ select command.

• Insert Information into the Database: C# Programming Language is a great option to insert data into Databases. All you require is to add or specify values in C# for each row that you want to insert.

• Update the Existing Data: C# Programming Language is also used for updating the previous data records in the Database. Again, you need to add or specify values in C# for each row that you want to update.

• Remove Data: Use C# for deleting any previous or new data records from the Database. Select or specify commands for each row in C# that you want to delete.

Methods for C# SQL Server Connection

• Method 1: C# SQL Server Connection using Entity Framework
• Method 2: C# SQL Server Connection using Dapper
• Method 3: Establishing a C# SQL Server Database Connection Using a Windows Forms Application

Method 1: C# SQL Server Connection using Entity Framework

Entity Framework is an Object Relational Mapping(ORM) framework that lets the user build a clean, portable, and high-level data access layer with C# across SQL Databases. It is the easiest and most preferred method for C# SQL Server Connection since it does not require the user to write any SQL queries.

Follow the steps below for an easy C# SQL Server Database connection:

• Step 1: Install the Entity Framework NuGet Package
• Step 2: Define your Data Model
• Step 3: Create DbContext Class
• Step 4: Configure Connection String
• Step 5: Initialize Database
• Step 6: Use DbContext in Your Code

Step 1: Install the Entity Framework NuGet Package

Install-Package EntityFramework

Step 2: Define your Data Model

Create your data model by creating C# classes that represent entities. Entities are typically equivalent to database tables.

// YourEntity.cs
public class YourEntity
{
    public int Id { get; set; }
    public string Property { get; set; }
}

Step 3: Create DbContext Class

Create a class that inherits from DbContext to represent your database context. This class will be responsible for interacting with the database.

// YourDbContext.cs
public class YourDbContext : DbContext
{
    public DbSet<YourEntity> YourEntities { get; set; }
}

Step 4: Configure Connection String

Make sure your App.config or Web.config file includes the connection string for your database.

<connectionStrings>
    <add name="YourDbContext" connectionString="Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Step 5: Initialize Database

In your application (e.g., in the Main method or Application_Start in a web application), add code to initialize the database. This can be done using Entity Framework migrations or by simply calling Database.Initialize in your code.

// Main method or Application_Start
Database.SetInitializer(new YourDbInitializer());

Step 6: Use DbContext in Your Code

Now, you can use your DbContext in your application to perform database operations.

using (var context = new YourDbContext())
{
    // Query data
    var data = context.YourEntities.ToList();

    // Insert data
    var newEntity = new YourEntity { Property = "New Data" };
    context.YourEntities.Add(newEntity);
    context.SaveChanges();

    // Update data
    var entityToUpdate = context.YourEntities.Find(1);
    if (entityToUpdate != null)
    {
        entityToUpdate.Property = "Updated Data";
        context.SaveChanges();
    }

    // Delete data
    var entityToDelete = context.YourEntities.Find(2);
    if (entityToDelete != null)
    {
        context.YourEntities.Remove(entityToDelete);
        context.SaveChanges();
    }
}

By following these steps, you can integrate C# with SQL Server using Entity Framework in a Code-First approach. This method provides a convenient way to work with databases using C# classes, and it automatically handles the translation between the object-oriented world of C# and the relational world of SQL Server.

Advantages of Using Entity Framework

• It supports the querying of data with C# instead of SQL.
• It works with the data as objects.
• It has more advanced powers and features for the end user.

Method 2: C# SQL Server Connection using Dapper

Dapper is another open-source object-relational mapping (ORM) library for .NET and .NET Core applications. The library supports easy access to data from databases without the need to write endless code. Dapper is preferred for C# SQL Server connection because it facilitates the user in running raw SQL queries and mapping results to objects.

Follow the steps below for an easy C# SQL Server Database connection:

• Step 1: Install Dapper NuGet Package
• Step 2: Create Your Data Model
• Step 3: Use Dapper in Your Code

Step 1: Install Dapper NuGet Package

Install-Package Dapper

Step 2: Create Your Data Model

Define your data model by creating a C# class representing the entity. This class will map to a database table.

// YourEntity.cs
public class YourEntity
{
    public int Id { get; set; }
    public string Property { get; set; }
}

Note: Make sure your application has access to the connection string for your database.

Step 3: Use Dapper in Your Code

Use Dapper to perform database operations in your application.

using System.Data.SqlClient;
using Dapper;

// ...

string connectionString = "YourConnectionString";

// Query data
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var data = connection.Query<YourEntity>("SELECT * FROM YourTable").ToList();
}

// Insert data
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var newEntity = new YourEntity { Property = "New Data" };
    connection.Execute("INSERT INTO YourTable (Property) VALUES (@Property)", newEntity);
}

// Update data
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var entityToUpdate = new YourEntity { Id = 1, Property = "Updated Data" };
    connection.Execute("UPDATE YourTable SET Property = @Property WHERE Id = @Id", entityToUpdate);
}

// Delete data
using (var connection = new SqlConnection(connectionString))
{
    connection.Open();

    var entityIdToDelete = 2;
    connection.Execute("DELETE FROM YourTable WHERE Id = @Id", new { Id = entityIdToDelete });
}

Dapper provides extension methods for the SqlConnection class, making it easy to execute queries and commands. The Query method is used for SELECT queries, and the Execute method is used for INSERT, UPDATE, and DELETE operations.

Advantages of Using Dapper for C# SQL Server Connection:

• It has a better speed and performance.
• It treats data as objects.

Method 3: Establishing a C# SQL Server Database Connection Using a Windows Forms Application

Let’s say you want to create a connection to a Database named Demodb. Username (sa) and Password (demo123) are credentials that will make all the privileged users connect to a Database.

Follow the steps below to establish a C# SQL Server Database connection.

• Step 1: Create a New Project
• Step 2: Connect
• Step 3: Add Event Handler
• Code
• Code Explanation
• Output

Using a simple connect button, users can establish a C# SQL Server connection to the Database. Look at the Windows forms application to learn how to work with Databases.

Step 1: Create a New Project

• To get started with C# SQL Server, open Visual Studio to create a new project. Once the visual studio is active, go to the menu option at the top. Select “New” and click on “Project“.

• Mention your project name and choose a location from the browser. Now, press “OK“. Also, make sure to select your project type as a “Windows Forms” application.

• Before you move on to the next step, make a note of these:
  • You can create various types of projects from the Project dialog box in Visual Studio. Go to the left-hand side and click on the “Windows” option.
  • Now, select “Windows Forms Application” from the list. Name the application and provide a location where you want to save the project. In this example, we have saved the application as “DemoApplication”.
  • Lastly, press the “OK” button to create a project in the Visual Studio.

Step 2: Connect

• Go to the toolbox and add a button to the Windows form. Mention the button’s text property as “Connect“. Here is how the button will appear on your screen.

Step 3: Add Event Handler

• To add the Event Handler to the code, make sure to double-click on the form. The Event Handler is used for the Button Click event. Copy the below-listed code and paste it into the Event Handler.

Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace DemoApplication1
{
 public partial class Form1 : Form
 {
  public Form1()
  {
   InitializeComponent();
  }

  private void button1_Click(object sender, EventArgs e)
  {
   string connetionString;
   SqlConnection cnn;
   connetionString = @"Data Source=WIN-50GP30FGO75;Initial Catalog=Demodb;User ID=sa;Password=demol23";
   cnn = new SqlConnection(connetionString);
   cnn.Open();
   MessageBox.Show("Connection Open  !");
   cnn.Close();
  }
 }
}

Code Explanation

• In the first stage, you create variables. These variables help generate the connection string. Further, it aids in establishing a C# SQL Server Database connectivity.

• Now, in the next step, you will create a connection string. Make sure to place and specify it in the right manner for C#. It will help in a better understanding of the connection string. A connection string comprises:
  • Data Source: It refers to the server name where a Database is maintained. In this example, the machine on which the Database resides is WIN- 50GP30FGO75.
  • To specify the Database name, you require the initial catalog.
  • To establish a connection with the Database, create credentials – userID and password.

• In the third step, the connection string is assigned to the variable cnn. It is a SqlConnection type mostly used to help create a connection with the Database.

• Now, the Open method of the cnn variable is exercised to establish a connection. As the connection is established, a message will display on your screen confirming the status.

• As the operation is successful, add a closure to the connection. It is best to close the connection if no other changes are necessary.

• Execute the project using Visual Studio and place the above code correctly in C#. We assure if done properly, you will get the right output. Also, click the “Connect” button as the form displays on your screen.

Output

You will notice that the connection to the Database is created as you press the connection button on the screen from the output. Here is how the output will appear on the screen.

That’s it, you’ve successfully established the C# SQL Server Database connection.

Conclusion

Microsoft SQL Server is a Relational Database Management System (RDBMS) that helps businesses work efficiently at all times. The Relational Database is generally used to store, retrieve information, Business Intelligence operations, manage data, and perform analysis.

Using Microsoft SQL Server adds more security, speed, and reliability. In this post, we have shown how to establish a C# SQL Server connection. Follow the above-listed fundamentals of Database connectivity and steps to build a C# SQL Server Database connectivity easily.

Dive into a deeper knowledge of SQL Server with these essential reads:

• Running SQL Server on Raspberry Pi using Docker | A 101 Guide
• MySQL vs SQL Server: 10 Critical Differences
• Connect Kafka to SQL Server: 2 Easy Methods

To get a complete overview of your business performance, it is important to consolidate data from SQL Server and other Data Sources into a Cloud Data Warehouse or a destination of your choice for further Business Analytics. This is where Hevo comes in.

visit our website to explore hevo

Hevo Data with its strong integration with BI tools such as SQL Server, allows you to not only export data from sources & load data to the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools.

FAQ

Give Hevo Data a try and sign up for a 14-day free trial today. Hevo offers plans & pricing for different use cases and business needs, check them out!

Share your experience of working with C# SQL Server connection in the comments section below.