สร้างโปรเจ็กส์ 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
> dotnet add package System.Data.SqlClient --version 4.8.2
ติดตั้ง Microsoft.Extensions.Configuration
> dotnet add package Microsoft.Extensions.Configuration --version 5.0.0
ติดตั้ง Microsoft.Extensions.Configuration.Json
> dotnet add package Microsoft.Extensions.Configuration.Json --version 5.0.0
ติดตั้ง 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();
}
}
}