Access SQL Server Database In .NET Core Console Application

In this blog, we will see how to access the SQL Server database using a .NET Core console application.

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.

 

 

Create table TB_COUNTRY 

CREATE TABLE [dbo].[tb_country](
   [id] [int] IDENTITY(1,1) NOT NULL,
   country] [varchar](100) NOT NULL,
   [active] [bit] NOT NULL,
   CONSTRAINT [PK_tb_country] PRIMARY KEY CLUSTERED
   (
   [id] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Let’s add some rows in the table TB_COUNTRY:

INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'The United States', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Germany', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'England', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Netherlands', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Italy', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Brazil', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'South Africa', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Japan', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'South Korea', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'North Korea', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'India', 1)
INSERT [dbo].[tb_country] ([country], [active]) VALUES (N'Russia', 1)

Let’s create a Stored Procedure to get a list with countries.

CREATE PROCEDURE [dbo].[SP_COUNTRY_GET_LIST]
AS
   BEGIN
   SELECT id
                  ,country
                 ,active
   FROM tb_country
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).

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.

• Microsoft.Extensions.Configuration;
• Microsoft.Extensions.Configuration.FileExtensions;
• Microsoft.Extensions.Configuration.Json;
• System.Data.SqlClient.

We are going to install these packages one by one.

 

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

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

{
"ConnectionStrings": {
   "Default": "Server=YOUR_SERVER;Database=mydatabase;User    Id=YOUR_USER;Password=YOUR_PASSWORD;MultipleActiveResultSets=true"
   }
}

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.

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.

CountryDAL.cs

using ConsoleApp.Model;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApp.DAL
{
public class CountryDAL
{
   private string _connectionString;
   public CountryDAL(IConfiguration iconfiguration)
   {
      _connectionString = iconfiguration.GetConnectionString("Default");
   }
   public List<CountryModel> GetList()
   {
      var listCountryModel = new List<CountryModel>();
      try
      {
         using (SqlConnection con = new SqlConnection(_connectionString))
         {
               SqlCommand cmd = new SqlCommand("SP_COUNTRY_GET_LIST", con);
               cmd.CommandType = CommandType.StoredProcedure;
               con.Open();
               SqlDataReader rdr = cmd.ExecuteReader();
               while (rdr.Read())
            {
                  listCountryModel.Add(new CountryModel
                   {
                           Id = Convert.ToInt32(rdr[0]),
                           Country = rdr[1].ToString(),
                           Active = Convert.ToBoolean(rdr[2])
                     });
               }
            }
         }
         catch (Exception ex)
         {
               throw ex;
         }
         return listCountryModel;
      }
   }
}

CountryModel.cs

namespace ConsoleApp.Model
{
public class CountryModel
{
   public int Id { get; set; }
   public string Country { get; set; }
   public bool Active { get; set; }
}
}

Program.cs

using ConsoleApp.DAL;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;
namespace ConsoleApp
{
class Program
{
private static IConfiguration _iconfiguration;
static void Main(string[] args)
{
   GetAppSettingsFile();
   PrintCountries();
}
static void GetAppSettingsFile()
{
   var builder = new ConfigurationBuilder()
                        .SetBasePath(Directory.GetCurrentDirectory())
                        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
                        _iconfiguration = builder.Build();
}
static void PrintCountries()
{
   var countryDAL = new CountryDAL(_iconfiguration);
   var listCountryModel = countryDAL.GetList();
   listCountryModel.ForEach(item =>
   {
         Console.WriteLine(item.Country);
   });
   Console.WriteLine("Press any key to stop.");
   Console.ReadKey();
}
}
}

Now let’s start the Console Application.

Result

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.