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.

Table of Contents

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
  Simplify Data Integration with Hevo!

  Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

  Start for free now!

  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 DbSetYourEntities < get; set; >> 

  Step 4: Configure Connection String

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

  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("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 < Property = "Updated Data" >; connection.Execute("UPDATE YourTable SET Property = @Property WHERE entityToUpdate); > // Delete data using (var connection = new SqlConnection(connectionString)) < connection.Open(); var entityIdToDelete = 2; connection.Execute("DELETE FROM YourTable WHERE new < >); > 

  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 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.

      Simplify SQL Server ETL with Hevo’s No-code Data Pipeline

      Hevo Data is a No-code Data Pipeline that offers a fully managed solution to set up data integration from SQL Server and 150+ Data Sources (including 40+ Free Data Sources)and will let you directly load data to a Data Warehouse or the destination of your choice. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data.

      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.

      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

      How do I connect SQL Server to C#?

      To connect SQL Server to C#, use the SqlConnection class from the System.Data.SqlClient namespace. Create a connection string with your SQL Server details and pass it to an instance of SqlConnection .

      How to use SqlConnection in C#?

      To use SqlConnection in C#, instantiate it with a connection string and open the connection using Open() .

      How to connect .NET and SQL?

      To connect .NET and SQL Server, use ADO.NET. Install the System.Data.SqlClient or Microsoft.Data.SqlClient package, configure your connection string, and use classes like SqlConnection , SqlCommand , and SqlDataReader to interact with the database.

      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.

      Hitesh Jethva Technical Content Writer, Hevo Data

      Hitesh is a skilled freelance writer in the data industry, known for his engaging content on data analytics, machine learning, AI, big data, and business intelligence. With a robust Linux and Cloud Computing background, he combines analytical thinking and problem-solving prowess to deliver cutting-edge insights. Hitesh leverages his Docker, Kubernetes, AWS, and Azure expertise to architect scalable data solutions that drive business growth.