Using a .NET Core console application, it is very easy to access an existing SQL Server database. Follow the below steps for that.

First, let’s create a Database.
Access SQL Server DataBase In .NET Core Console Application
Create table TB_COUNTRY
  1. CREATE TABLE [dbo].[tb_country](
  2. [id] [int] IDENTITY(1,1) NOT NULL,
  3. country] [varchar](100) NOT NULL,
  4. [active] [bit] NOT NULL,
  5. CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED
  6. (
  7. [id] ASC
  8. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  9. ) ON [PRIMARY]

Let’s add some rows in the table TB_COUNTRY:

  1. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'The United States', 1)
  2. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Germany', 1)
  3. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'England', 1)
  4. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Netherlands', 1)
  5. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Italy', 1)
  6. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Brazil', 1)
  7. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'South Africa', 1)
  8. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Japan', 1)
  9. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'South Korea', 1)
  10. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'North Korea', 1)
  11. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'India', 1)
  12. INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Russia', 1)
Let’s create a Stored Procedure to get a list with countries.
  1. CREATE PROCEDURE [dbo].[SP_COUNTRY_GET_LIST]
  2. AS
  3. BEGIN
  4. SELECT id
  5. ,country
  6. ,active
  7. FROM tb_country
  8. END

Now, let’s create a .NET Core Console Application.

Step 1

First, click on the File tab, go to the New, and click on "New Project".

Let us select the Console App (.NET Core).

Access SQL Server DataBase In .NET Core Console Application

Step 2

We are going to use NuGet to install the packages. Right-click on Project (“ConsoleApp”) and select "Manage NuGet Packages".

Click on the "Browse" tab and search for the following packages.

We are going to install these packages one by one.

Access SQL Server DataBase In .NET Core Console Application
With these installed packages, we can access the settings file of the database.
Step 3

Now, we need to add the appsettings file, where it contains our connection string to access the SQL Server Database.

Right-click on Project (“ConsoleApp”), go to Add, and click on "New Item".

Access SQL Server DataBase In .NET Core Console Application

Now, let’s add our connection string to the database in appsetings.json.

  1. {
  2. "ConnectionStrings": {
  3. "Default": "Server=YOUR_SERVER;Database=mydatabase;User Id=YOUR_USER;Password=YOUR_PASSWORD;MultipleActiveResultSets=true"
  4. }
  5. }

Don’t forget, change Server, DataBase, Id and Password according you need.

Step 4

This step is very important. We need copy appsetings.json to Directory where the application will run.

Access SQL Server DataBase In .NET Core Console Application

Step 5

Now, we will create 2 Folders ( “MODEL”“ and DAL”). After that, we will create a class named “CountryModel “ within Model folder and a class named “CountryDAL” within DAL folder.

CountryModel.cs represents our data model and CountryDAL.cs representes our data access layer.

Access SQL Server DataBase In .NET Core Console Application

CountryDAL.cs

  1. using ConsoleApp.Model;
  2. using Microsoft.Extensions.Configuration;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Data.SqlClient;
  7. namespace ConsoleApp.DAL
  8. {
  9. public class CountryDAL
  10. {
  11. private string _connectionString;
  12. public CountryDAL(IConfiguration iconfiguration)
  13. {
  14. _connectionString = iconfiguration.GetConnectionString("Default");
  15. }
  16. public List<CountryModel> GetList()
  17. {
  18. var listCountryModel = new List<CountryModel>();
  19. try
  20. {
  21. using (SqlConnection con = new SqlConnection(_connectionString))
  22. {
  23. SqlCommand cmd = new SqlCommand("SP_COUNTRY_GET_LIST", con);
  24. cmd.CommandType = CommandType.StoredProcedure;
  25. con.Open();
  26. SqlDataReader rdr = cmd.ExecuteReader();
  27. while (rdr.Read())
  28. {
  29. listCountryModel.Add(new CountryModel
  30. {
  31. Id = Convert.ToInt32(rdr[0]),
  32. Country = rdr[1].ToString(),
  33. Active = Convert.ToBoolean(rdr[2])
  34. });
  35. }
  36. }
  37. }
  38. catch (Exception ex)
  39. {
  40. throw ex;
  41. }
  42. return listCountryModel;
  43. }
  44. }
  45. }

CountryModel.cs

  1. namespace ConsoleApp.Model
  2. {
  3. public class CountryModel
  4. {
  5. public int Id { get; set; }
  6. public string Country { get; set; }
  7. public bool Active { get; set; }
  8. }
  9. }

Program.cs

  1. using ConsoleApp.DAL;
  2. using Microsoft.Extensions.Configuration;
  3. using System;
  4. using System.IO;
  5. namespace ConsoleApp
  6. {
  7. class Program
  8. {
  9. private static IConfiguration _iconfiguration;
  10. static void Main(string[] args)
  11. {
  12. GetAppSettingsFile();
  13. PrintCountries();
  14. }
  15. static void GetAppSettingsFile()
  16. {
  17. var builder = new ConfigurationBuilder()
  18. .SetBasePath(Directory.GetCurrentDirectory())
  19. .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
  20. _iconfiguration = builder.Build();
  21. }
  22. static void PrintCountries()
  23. {
  24. var countryDAL = new CountryDAL(_iconfiguration);
  25. var listCountryModel = countryDAL.GetList();
  26. listCountryModel.ForEach(item =>
  27. {
  28. Console.WriteLine(item.Country);
  29. });
  30. Console.WriteLine("Press any key to stop.");
  31. Console.ReadKey();
  32. }
  33. }
  34. }

Now let’s start the Console Application.

Result

Access SQL Server DataBase In .NET Core Console Application

In this blog, we have explored how to access SQL Server DataBase in .NET Core Console Application using a settings file. It can be useful for creating automatic routines.

If you face a problem with this code, please comment below.