This article explains how to connect to SQL Server database from a C# .NET Core Console App using ADO.NET SqlConnection objects. Besides, we will learn how to execute a SQL SELECT query and display the result in the console.

Suppose we have a database named DbName in a local SQL Server instance called MSSQL2019. The DbName database contains a table named Product with the following data:

ID Name Description Price
1 Product 1 Description 1 2.35
2 Product 2 Description 2 4.22

The following examples show how to connect to the database using a basic connection code. We will also display the result using SqlCommand and DataTable objects.

Basic C# connect to SQL database

  1. Create a new .NET Core Console App

  2. Install System.Data.SqlClient using the following command in the Package Manager Console.

    Install-Package System.Data.SqlClient
    
  3. Add a reference to the System.Data.SqlClient. Here is the code in the Usings section.

    using System;
    using System.Data.SqlClient;
    
  4. Write the following code inside the Main method in Program.cs and modify the connection string.

    static void Main(string[] args)
    {
    	try
    	{
    		var connectionString = @"Data Source=.\MSSQL2019;Initial Catalog=DbName;User ID=sa;Password=password";
    
    		using (var conn = new SqlConnection(connectionString))
    		{
    			conn.Open();
    			
    			Console.WriteLine("Connection succeeded.");
    		}
    	}
    	catch (Exception exception)
    	{
    		Console.WriteLine("Connection failed. " + exception.Message);
    	}
    
    	Console.ReadLine();
    }
    
  5. Execute the console app by pressing F5 or clicking the Run button.

  6. Check the result. If the connection is successful, the console displays a “Connection succeeded” message.

Load connection string from the configuration file

  1. Right-click on the project, select Add > New Item.

  2. Choose Application Configuration File, name it App.config.

  3. Add a new connectionStrings element under configuration element, then move the connection string value from the previous example into it. Here’s the complete XML structure:

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
    <connectionStrings>
    <add name="DbNameConnectionString" 
        connectionString="Data Source=.\MSSQL2019;Initial Catalog=DbName;User ID=sa;Password=password" 
        providerName="System.Data.SqlClient"/>
    </connectionStrings>
    </configuration>
  4. Install System.Configuration.ConfigurationManager using the following command in Package Manager Console.

    Install-Package System.Configuration.ConfigurationManager
    
  5. Add a reference to the System.Configuration.ConfigurationManager to our Usings.

    using System;
    using System.Configuration;
    using System.Data.SqlClient;
    
  6. In the Main method, modify the line that assigns a value to the connectionString variable.

    static void Main(string[] args)
    {
    	try
    	{
    		var connectionString = ConfigurationManager.ConnectionStrings["DbNameConnectionString"].ToString();
    
    		using (var conn = new SqlConnection(connectionString))
    		{
    			conn.Open();
    			
    			Console.WriteLine("Connection succeeded.");
    		}
    	}
    	catch (Exception exception)
    	{
    		Console.WriteLine("Connection failed. " + exception.Message);
    	}
    
    	Console.ReadLine();
    }
    
  7. Try to run the code again to see the result.

C# SQL SELECT

Now we will query the Product table into a DataTable object and display the result in the console.

  1. Add a reference to System.Data in the Usings.

    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    
  2. Modify our Main method to use the following code:

    static void Main(string[] args)
    {
    	try
    	{
    		var connectionString = ConfigurationManager.ConnectionStrings["DbNameConnectionString"].ToString();
    
    		DataTable dataTable = new DataTable();
    
    		using (var conn = new SqlConnection(connectionString))
    		{
    			conn.Open();
    
    			using (var cmd = new SqlCommand("SELECT * FROM Product", conn))
    			{
    				cmd.CommandType = CommandType.Text;
    
    				var dataAdapter = new SqlDataAdapter(cmd);
    				dataAdapter.Fill(dataTable);
    			}
    		}
    
    		// Displaying the result
    		Console.WriteLine("ID \tName \t\tDescription \tPrice");
    		foreach (DataRow row in dataTable.Rows)
    		{
    			Console.WriteLine(row["ID"].ToString() + "\t" 
    				+ row["Name"].ToString() + "\t"
    				+ row["Description"].ToString() + "\t"
    				+ row["Price"].ToString());
    		}
    	}
    	catch (Exception exception)
    	{
    		Console.WriteLine("Error: " + exception.Message);
    	}
    
    	Console.ReadLine();
    }
    
  3. Click the Run button.

  4. Check the result. If succeeded, the console will display the Product data like the screenshot below:

    Figure 1. Sample result of C# query SQL database