• Access SQL Server Database In .NET Core Console Application

สร้างโปรเจ็กส์ Console

> dotnet new console -o ConsoleApp

เพิ่มไฟล์ .gitignore (optional)

*.swp
*.*~
project.lock.json
.DS_Store
*.pyc
nupkg/

# Visual Studio Code
.vscode

# Rider
.idea

# User-specific files
*.suo
*.user
*.userosscache
*.sln.docstates

# Build results
[Dd]ebug/
[Dd]ebugPublic/
[Rr]elease/
[Rr]eleases/
x64/
x86/
build/
bld/
[Bb]in/
[Oo]bj/
[Oo]ut/
msbuild.log
msbuild.err
msbuild.wrn

# Visual Studio 2015
.vs/

รันโปรเจ็กส์ด้วยคำสั่ง dotnet run

> cd ConsoleApp
> dotnet run

ติดตั้ง System.Data.SqlClient

• System.Data.SqlClient

> dotnet add package System.Data.SqlClient --version 4.8.2

ติดตั้ง Microsoft.Extensions.Configuration

• Microsoft.Extensions.Configuration

> dotnet add package Microsoft.Extensions.Configuration --version 5.0.0

ติดตั้ง Microsoft.Extensions.Configuration.Json

• Microsoft.Extensions.Configuration.Json

> dotnet add package Microsoft.Extensions.Configuration.Json --version 5.0.0

ติดตั้ง Microsoft.Extensions.Configuration.Binder

• Microsoft.Extensions.Configuration.Binder

> dotnet add package Microsoft.Extensions.Configuration.Binder --version 5.0.0

สร้างไฟล์ appsettings.json

{
    "DbConfig": {
        "ServerName": "localhost",
        "DatabaseName": "myDatabase",
        "UserName": "myUsername",
        "Password": "myPassword"
    }
}

เพิ่มไฟล์ Model.Student.cs

using System;

namespace ConsoleApp.Model
{
    public class Student
    {
        public int id { get; set; }
        public string firstname { get; set; }
        public string lastname { get; set; }
        public string email { get; set; }
        public string mobile { get; set; }
    }
}

เพิ่มไฟล์ Data.StudentDAL.cs

using ConsoleApp.Model;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp.Data
{
    public class StudentDAL
    {
        private string _connectionString;

        public StudentDAL(IConfiguration config)
        {
            string _server = config.GetValue<string>("DbConfig:ServerName");
            string _database = config.GetValue<string>("DbConfig:DatabaseName");
            string _username = config.GetValue<string>("DbConfig:UserName");
            string _password = config.GetValue<string>("DbConfig:Password");
            _connectionString = ($"Server={_server};Database={_database};User ID={_username};Password={_password};Trusted_Connection=False;MultipleActiveResultSets=true;");
        }
        public List<Student> GetList()
        {
            List<Student> students = new List<Student>();
            try
            {
                using (SqlConnection con = new SqlConnection(_connectionString))
                {
                    DataTable dt = new DataTable();
                    string sql = @"
SELECT * 
FROM Student
ORDER BY id";

                    SqlDataAdapter da = new SqlDataAdapter(sql, con);
                    da.Fill(dt);

                    foreach (DataRow row in dt.Rows)
                    {
                        Student std = new Student();
                        std.id = Convert.ToInt32(row["id"]);
                        std.firstname = row["Firstname"] as string;
                        std.lastname = row["Lastname"] as string;
                        std.email = row["Email"] as string;
                        std.mobile = row["mobile"] as string;
                        students.Add(std);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return students;
        }
    }
}

แก้ไขไฟล์ Program.cs

using ConsoleApp.Data;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;
using System.Text.Json;

namespace ConsoleApp
{
    class Program
    {
        private static IConfiguration _iconfiguration;
        static void Main(string[] args)
        {
            GetAppSettingsFile();

            var studentDAL = new StudentDAL(_iconfiguration);
            var students = studentDAL.GetList();
            students.ForEach(item =>
            {
                string jsonString = JsonSerializer.Serialize(item);
                Console.WriteLine(jsonString);
            });
        }

        static void GetAppSettingsFile()
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
            _iconfiguration = builder.Build();
        }
    }
}