ASP.NET Core 5.0 and PostgreSQL (edit)

Building REST APIs with .NET 5, ASP.NET Core, and PostgreSQL | End Point

.NET Core, ASP.NET Core logging with NLog and PostgreSQL | Software Engineering (damienbod.com)

ASP.NET Core with PostgreSQL and Dapper - CRUD Operations Example - TechBrij

Store images in SQL Server using EF Core and ASP.NET Core | BinaryIntellect Knowledge Base

megakevin/end-point-blog-dotnet-5-web-api: Demo application for blog post about developing Web APIs with .NET 5 and ASP.NET Core. (github.com)

ASP.NET Code Generator tool

dotnet tool install --global dotnet-aspnet-codegenerator

Entity Framework command line tool

dotnet tool install --global dotnet-ef

EF Core driver for PostgreSQL

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

dotnet add package EFCore.NamingConventions

dotnet ef migrations add AddLookupTables

dotnet ef database update

dotnet ef migrations add AddSeedDataForSizesAndBodyTypes

dotnet ef database update

Building REST APIs with .NET 5, ASP.NET Core, and PostgreSQL | End Point

PostgreSQL (edit)

• xUnit
• AutoFixture
  • AutoFixture.XUnit2
  • AutoFixture.NUnit3
• Respawn
  • Respawn.Postgres

Respawn: Intelligent database cleaner for integration tests

Respawn supported the following databases:

• SQL Server
• PostgreSQL
• MySQL/MariaDB
• Oracle

Bulk Inserts Using Dapper (edit)

• Đọc tệp Excel bằng thư viện Open XML
• Đưa vào List<Dto>
• Build câu Insert bằng StringBuilder
• Dùng Dapper để thực hiện Bulk Insert

Read Excel using Open XML

https://alberton.info/postgresql_meta_info.html (PostgreSQL)

https://stackoverflow.com/questions/10689779/bulk-inserts-taking-longer-than-expected-using-dapper/ (Bulk Inserts)

https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/ (Read/Write excel file .NET Core using OpemXML SDK)

https://www.thecodebuzz.com/read-excel-as-json-using-open-xml-sdk/ (OpenXML SDK)

https://www.aspsnippets.com/Articles/Read-and-Import-Excel-data-to-DataTable-using-OpenXml-in-ASPNet-with-C-and-VBNet.aspx (OpenXML SDK)

https://dzone.com/articles/import-and-export-excel-file-in-asp-net-core-31-ra (Import & Export)

• ASP.NET Core 2.1
• EF Core 2.1
• PostgreSQL (Install-Package Npgsql)
• Dapper (Install-Package Dapper)
• DocumentFormat.OpenXml or OpenXML (Install-Package Open-XML-SDK | Install-Package DocumentFormat.OpenXml)
• Upload files
• Read Excel files
• Import and Export to CSV files

Open XML

https://www.c-sharpcorner.com/article/creating-excel-file-using-openxml/

https://bettersolutions.com/csharp/xml/open-xml-sdk.htm

https://www.codingame.com/playgrounds/11047/edit-word-documents-using-openxml-and-c-without-automationinterop

Code Samples

https://www.michalbialecki.com/2019/05/21/bulk-insert-in-dapper/

https://github.com/mikuam/Blog/tree/master/ServiceBusExamples/MichalBialecki.com.NetCore.Web/Users

[HttpGet]
[Route("[action]")]
public ActionResult ImportData()
{
 var listUsers = new List<Users>();
 Users users;

 var excelFilePath = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");

 if (!System.IO.File.Exists(excelFilePath)) return Ok("File excel not found.");

 try
 {
 // Lets open the existing excel file and read through its content. Open the excel using openxml sdk
 using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelFilePath, false))
 {
 // Create the object for workbook part
 WorkbookPart workbookPart = doc.WorkbookPart;
 Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();

 // Using for each loop to get the sheet from the sheetcollection
 foreach (Sheet thesheet in thesheetcollection)
 {
 // Statement to get the worksheet object by using the sheet id
 Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;

 SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();

 foreach (Row thecurrentrow in thesheetdata)
 {
 if (thecurrentrow.RowIndex == 1)
 {
 continue;
 }

 string[] arr = new string[3];
 var idx = 0;
 foreach (Cell thecurrentcell in thecurrentrow)
 {
 // Statement to take the integer value
 string currentcellvalue = string.Empty;
 if (thecurrentcell.DataType != null)
 {
 if (thecurrentcell.DataType == CellValues.SharedString)
 {
 int id;
 if (Int32.TryParse(thecurrentcell.InnerText, out id))
 {
 SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
 if (item.Text != null)
 {
 currentcellvalue = item.Text.Text;
 }
 else if (item.InnerText != null)
 {
 currentcellvalue = item.InnerText;
 }
 else if (item.InnerXml != null)
 {
 currentcellvalue = item.InnerXml;
 }
 }
 }
 }
 else
 {
 currentcellvalue = thecurrentcell.InnerText;
 }
 arr[idx++] = currentcellvalue;
 if (idx == 3)
 {
 users = new Users();
 var canInsert = true;
 try
 {
 users.ColumnIndex = NumberUtil.TryGetInt32(arr[0]);
 users.Name = arr[1];
 users.Salary = arr[2];
 }
 catch (Exception ex)
 {
 canInsert = false;
 Debug.WriteLine(ex.ToString());
 }

 if (canInsert)
 {
 listUsers.Add(users);
 users = null;
 }

 idx = 0;
 }
 }
 }
 }
 }
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 }

 var allItems = (from x in listUsers
 where x.ColumnIndex > 0
 select new
 {
 ColumnIndex = x.ColumnIndex,
 Name = x.Name,
 Salary = x.Salary
 }).ToList();

 using (var connection = new NpgsqlConnection(_dbContext.Connection.ConnectionString))
 {
 connection.Open();
 using (var transaction = connection.BeginTransaction())
 {
 try
 {
 connection.Execute("INSERT INTO public.\"Users\" (\"ColumnIndex\", \"Name\", \"Salary\") VALUES (@ColumnIndex, @Name, @Salary)", allItems, transaction);

 transaction.Commit();
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 transaction.Rollback();
 throw;
 }
 finally
 {
 connection.Close();
 }
 }
 }

 var allUsers = _dbContext.Users.ToList();

 return Ok(allUsers.Count);
}

Migration Data from Database-Dev to Database-Test

Giả sử bạn phải chuyển hết dữ liệu từ DB Dev sang DB Test hoặc sang DB Staging

PostgreSQL, Dapper, NpgSql, CsvHelper, Open-XML-SDK with .NET Framework 4.7.2

using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace ConsoleApp1
{
 class Program
 {
 static string mConnectionStringSource = "server=localhost:5432;database=test;uid=postgres;pwd=postgres;";
 static string mSchemaSource = "public";

 static string mConnectionStringDestination = "server=localhost:5432;database=test;uid=postgres;pwd=postgres;";
 static string mSchemaDestination = "public";



 static void Main(string[] args)
 {
 Dictionary<string, List<object>> keyValuePairs = new Dictionary<string, List<object>>();

 Console.WriteLine("Hello World!");

 //Step 1: Lấy danh sách tên bảng + tên các column (tên các column join bằng dấu chẩm phẩy)
 var listTableDto = new List<TableDto>()
 {
 new TableDto("Users", "Id,Name,Salary,DoB,Gender,IsActive,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate"),
 new TableDto("Roles", "Id,Name"),
 };

 //Step 2: Lấy dữ liệu từ DB Source (mConnectionStringSource)
 using (var connection = new NpgsqlConnection(mConnectionStringSource))
 {
 connection.Open();
 for (int i = 0, n = listTableDto.Count; i < n; i++)
 {
 var tableDto = listTableDto[i];
 if (tableDto == null || string.IsNullOrWhiteSpace(tableDto.TableName)) continue;
 var tableName = listTableDto[i].TableName;
 var list = connection.Query<object>($"SELECT * FROM \"{mSchemaSource}\".\"{tableName}\"").ToList();
 keyValuePairs.Add(tableName, list);
 }
 }

 var sb = new StringBuilder();
 //Step 3: Build câu insert hàng loạt (Bulk Insert)

 using (var connection = new NpgsqlConnection(mConnectionStringDestination))
 {
 connection.Open();
 using (var transaction = connection.BeginTransaction())
 {
 for (int i = 0, n = listTableDto.Count; i < n; i++)
 {
 var tableDto = listTableDto[i];
 if (tableDto == null || string.IsNullOrWhiteSpace(tableDto.TableName)) continue;
 var tableName = listTableDto[i].TableName;
 var listColumnNames = listTableDto[i].ListColumnNames;
 var sqlInsert = $"INSERT INTO \"{mSchemaDestination}\".\"{tableName}\" ({BuildColumnInsert(listColumnNames)}) VALUES ({BuildParamInsert(listColumnNames)})";
 try
 {
 connection.Execute(sqlInsert, keyValuePairs.ElementAt(i).Value, transaction);

 transaction.Commit();
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 transaction.Rollback();
 throw;
 }
 finally
 {
 connection.Close();
 }
 }
 }
 }
 }

 /// <summary>
 /// Input: Id,Name,DoB,Desc
 /// Output: \"Id\", \"Name\", \"DoB\", \"Desc\"
 /// </summary>
 /// <param name="listColumnNames"></param>
 /// <returns></returns>
 private static string BuildColumnInsert(string listColumnNames)
 {
 var sb = new StringBuilder();
 sb.Append("\"");
 var arr = listColumnNames.Split(new char[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
 sb.Append(string.Join("\", \"", arr));
 sb.Append("\"");
 var s = sb.ToString();
 return s;
 }

 /// <summary>
 /// Input: Id,Name,DoB,Desc
 /// Output: @Id, @Name, @DoB, @Desc
 /// </summary>
 /// <param name="table"></param>
 /// <returns></returns>
 private static string BuildParamInsert(string listColumnNames)
 {
 var sb = new StringBuilder();
 sb.Append("@");
 var arr = listColumnNames.Split(new char[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
 sb.Append(string.Join(", @", arr));
 var s = sb.ToString();
 return s;
 }

 }

 class Users
 {
 public int Id { get; set; }
 public string Name { get; set; }
 public decimal? Salary { get; set; }
 public DateTime? DoB { get; set; }
 public int? Gender { get; set; }
 public bool IsActive { get; set; }
 public int CreatedBy { get; set; }
 public DateTime CreatedDate { get; set; }
 public int? ModifiedBy { get; set; }
 public DateTime? ModifiedDate { get; set; }
 }

 class TableDto
 {
 public string TableName { get; set; }
 public string ListColumnNames { get; set; }
 public TableDto()
 {

 }

 public TableDto(string tableName, string listColumnNames)
 {
 TableName = tableName;
 ListColumnNames = listColumnNames;
 }
 }
}

PostgreSQL (edit)

https://alberton.info/postgresql_meta_info.html

CREATE A TRIGGER FOR EACH TABLE TO MONITOR

http://www.alberton.info/postgresql_table_audit.html

CREATE A STORED PROCEDURE

https://carto.com/help/working-with-data/sql-stored-procedures/

https://www.enterprisedb.com/postgres-tutorials/10-examples-postgresql-stored-procedures

OTHERS:

• Extracting META information from Interbase/Firebird SQL (INFORMATION_SCHEMA)
• Extracting META information from Oracle (INFORMATION_SCHEMA)
• Extracting META information from SQL Server (INFORMATION_SCHEMA)
• Updated articles to extract META informations from databases

EF Core Update Model to Database PostgreSQL (edit)

ASP.NET Core Entity Framework Core with PostgreSQL Code First

https://github.com/hidayatarg/EntityFramework-.netCore-PostgresSQLconnection/

ASP.NET Core Entity Framework Core with PostgreSQL Code First

https://medium.com/faun/asp-net-core-entity-framework-core-with-postgresql-code-first-d99b909796d7

ASP.NET Core Entity Framework Core Power Tools

https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools

https://docs.microsoft.com/en-us/ef/core/extensions/

Swagger

https://medium.com/@salmanlone89/add-swagger-to-asp-net-core-2-1-web-api-f5ef0d170d4f

EF Core tools reference (Package Manager Console) - EF Core | Microsoft Docs

EF Core Update Model on Database First Project - DEV Community

Migrations

Migration in Entity Framework Core (entityframeworktutorial.net)

add-migration MyFirstMigration

dotnet ef migrations add MyFirstMigration

update-database

dotnet ef database update

remove-migration

dotnet ef migrations remove

update-database MyFirstMigration

dotnet ef database update MyFirstMigration

script-migration

dotnet ef migrations script

add-migration Init

update-database

Script-Migration -From <PreviousMigration> -To <LastMigration>

Script-Migration -From <PreviousMigration>

dotnet ef migrations add Init

dotnet ef database update

dotnet ef database update Init

dotnet ef migrations remove

dotnet ef migrations remove Init

dotnet ef migrations script

dotnet ef migrations script --output migrations.sql

Running the following in the Package Manager Console works as expected:

Update-Database -Script -SourceMigration:0

PostgreSQL

How to Add a Column in PostgreSQL - PopSQL

PostgreSQL ADD COLUMN: Add One Or More Columns To a Table (postgresqltutorial.com)

(EF Core) Tạo migration trong EntityFramework với C# CSharp

(EF Core) Tạo migration trong EntityFramework với C# CSharp (xuanthulab.net)

Giới thiệu migration

Migration là kỹ thuật trong việc tương tác với cơ sở dữ liệu, theo đó việc thay đổi về cấu trúc CSDL ở code sẽ được cập nhật lên CSDL đảm bảo dữ liệu đang tồn tại không bị mất, lịch sử (phiên bản) cập nhật được lưu lại sau mỗi lần cập nhật.

Thường khi sử dụng EF làm việc với DB, có hai cách đó là làm việc với một CSDL đang tồn tại (gọi là database first) - việc cập nhật database thực hiện khá độc lập với ứng dụng - tình huống này Migration ít hữu ích, tuy nhiên trường hợp bạn tạo database từ code, thay đổi cấu trúc database ... bằng code thì migration rất hữu ích. Tất nhiên ta vẫn có cách để sử dụng EF Migration trên database đã tồn tại.

Với migration khi bạn cập nhật Model, yêu cầu database cập nhật thì nó sẽ lưu thông tin phiên bản hiện tại của cấu trúc Model (database) ở Server DB - ví dụ phiên bản a, sau đó thay đổi các Model, lại yêu cầu cập nhật thì nó sẽ đọc thông tin phiên bản cuối trên DB, so sánh sự khác biệt và cập nhật sự khác biệt đó để lên phiên bản mới, phiên bản b.

Tạo dự án để thực hành EF Migration

Tạo một dự án kiểu console, trong thư mục EFMigration, có cài đặt các package để làm việc được với EF

dotnet add package System.Data.SqlClient
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.Extensions.DependencyInjection
dotnet add package Microsoft.Extensions.Logging.Console
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools.DotNet

# .NET 3.0 trở đi cài lệnh dotnet ef bằng
dotnet tool install --global dotnet-ef

Tạo ra hai Model đơn giản sau:

Models/Article.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFMigration.Models
{
    [Table("article")]
    public class Article
    {
        [Key]
        public int ArticleId {set; get;}

        [StringLength(100)]
        public string Title {set;  get;}

    }
}

Models/Tag.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFMigration.Models
{
    public class Tag
    {
        [Key]
        [StringLength(20)]
        public string TagId {set; get;}
        [Column(TypeName="ntext")]
        public string Content {set; get;}
    }
}

Triển khai một DbContext (WebContext) sử dụng 2 Model trên

Models/WebContext.cs

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;

namespace EFMigration.Models
{
    public class WebContext : DbContext
    {
        public DbSet<Article> articles {set; get;}        // bảng article
        public DbSet<Tag> tags {set; get;}                // bảng tag

        // chuỗi kết nối với tên db sẽ làm  việc đặt là webdb
        public const string ConnectStrring  =  @"Data Source=localhost,1433;Initial Catalog=webdb;User ID=SA;Password=Password123";

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
             optionsBuilder.UseSqlServer(ConnectStrring);
             optionsBuilder.UseLoggerFactory(GetLoggerFactory());       // bật logger
        }
        
        private ILoggerFactory GetLoggerFactory()
        {
            IServiceCollection serviceCollection = new ServiceCollection();
            serviceCollection.AddLogging(builder =>
                    builder.AddConsole()
                           .AddFilter(DbLoggerCategory.Database.Command.Name,
                                    LogLevel.Information));
            return serviceCollection.BuildServiceProvider()
                    .GetService<ILoggerFactory>();
        }

    }

}

Do sử dụng kỹ thuật migration để tạo và thay đổi database nên đừng sử dựng EnsureCreatedAsync như các ví dụ trước (nếu làm vậy cần xử lý như là database first - xem phần sau).

Tạo Migration

Lệnh tạo ra một Migration, giả sử đặt tên cho nó là NameMigration sử dụng lệnh sau:

dotnet ef migrations add NameMigration

Thay NameMigration bằng tên do bạn đặt, nó mang ý nghĩa như là phiên bản, nó cũng được dùng để đặt tên những lớp phát sinh.

Bản đầu tiên áp dụng cho ví dụ sẽ đặt tên là InitWebDB

dotnet ef migrations add InitWebDB

Sau lệnh này, nó tạo ra 3 file trong thư mục Migrations các file có tên dạng:

20190925193123_InitWebDB.cs
20190925193123_InitWebDB.Designer.cs
WebContextModelSnapshot.cs

Số 20190925193123 sinh ra theo thời điểm chạy lệnh. 3 file này chứa thông tin để có thể cập nhật (hoặc tạo) database đúng cấu trúc Model ở thời điểm mà bạ tạo Migration.

WebContextModelSnapshot.cs là snapshot (ảnh chụp) để tạo được cấu trúc database theo các Model hiện tại. Mở xem thử nội dung xem:

// <auto-generated />
using EFMigration.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

namespace EFMigration.Migrations
{
    [DbContext(typeof(WebContext))]
    [Migration("20190925193123_InitWebDB")]
    partial class InitWebDB
    {
        protected override void BuildTargetModel(ModelBuilder modelBuilder)
        {
#pragma warning disable 612, 618
            modelBuilder
                .HasAnnotation("ProductVersion", "3.0.0")
                .HasAnnotation("Relational:MaxIdentifierLength", 128)
                .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

            modelBuilder.Entity("EFMigration.Models.Article", b =>
                {
                    b.Property<int>("ArticleId")
                        .ValueGeneratedOnAdd()
                        .HasColumnType("int")
                        .HasAnnotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn);

                    b.Property<string>("Title")
                        .HasColumnType("nvarchar(100)")
                        .HasMaxLength(100);

                    b.HasKey("ArticleId");

                    b.ToTable("article");
                });

            modelBuilder.Entity("EFMigration.Models.Tag", b =>
                {
                    b.Property<string>("TagId")
                        .HasColumnType("nvarchar(20)")
                        .HasMaxLength(20);

                    b.Property<string>("Content")
                        .HasColumnType("ntext");

                    b.HasKey("TagId");

                    b.ToTable("tags");
                });
#pragma warning restore 612, 618
        }
    }
}

Mỗi một Migration có một lớp kế thừa từ lớp Migration được tạo ra, trong nó có hai phương thức là Up và Down - để thực hiện chuyển từ phiên bản thấp đến phiên bản này (Up) hoặc đang từ phiên bản này lùi về phiên bản trước (Down). Lớp này được định nghĩa trong 2 file mã nguồn còn lại, ví dụ trong file: 20190925193123_InitWebDB.cs

using Microsoft.EntityFrameworkCore.Migrations;

namespace EFMigration.Migrations
{
    public partial class InitWebDB : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "article",
                columns: table => new
                {
                    ArticleId = table.Column<int>(nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Title = table.Column<string>(maxLength: 100, nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_article", x => x.ArticleId);
                });

            migrationBuilder.CreateTable(
                name: "tags",
                columns: table => new
                {
                    TagId = table.Column<string>(maxLength: 20, nullable: false),
                    Content = table.Column<string>(type: "ntext", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_tags", x => x.TagId);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "article");

            migrationBuilder.DropTable(
                name: "tags");
        }
    }
}

Thực hiện Migration

Bạn có thể thực hiện migrate (tạo nếu chưa có, cập nhật nếu cần) từ code, như:

using System;
using System.Threading.Tasks;
using EFMigration.Models;
using Microsoft.EntityFrameworkCore;

namespace EFMigration
{
    class Program
    {
        static async Task Main(string[] args)
        {
            using (var webcontext =  new WebContext())
            {
                // Thực hiện Migrate - tạo db đúng cấu trúc Migration cuối cùng nếu chưa có
                // Nếu DB đã có từ các Migration trước, sẽ cập nhật
                await webcontext.Database.MigrateAsync();
            }
        }
    }
}

Tuy nhiên thường thực hiện Migrate bằng gõ lệnh sau (nên làm cách này) để cập nhật DB như migration cuối cùng:

dotnet ef database update

Trong trường hợp muốn chuyển DB về cấu trúc ở bản Migration nào đó (khi đang có nhiều Migration) thì chỉ rõ tên migration trong lệnh. Ví dụ - tên phiên bản đầu tiên InitWebDB thì gõ:

dotnet ef database update InitWebDB

Bạn chú ý là nếu muốn xóa DB (cẩn thận) để thực hiện lại thì có thể gõ lệnh:

dotnet ef database drop -f

Sau khi thực hiện Migration, do chưa có DB nó đã tạo ra DB đúng theo cấu trúc Model

Ngoài các bảng theo Model, có thêm bảng __EFMigrationsHistory chứa thông tin lịch sử cập nhật bởi Migration. Từ bảng này, EF Migration biết được DB đang ở phiên bản nào

Tạo Migration thứ 2

Để tìm hiểu kỹ hơn, tiến hành sửa đổi cập nhật Model như sau: cho vào Model Article một trường mới

/..
    public class Article
    {
        /..
        // Cột thêm vào khi cập nhật lần 2
        [Column(TypeName="ntext")]
        public string Content {set; get;}

    }
}

Sau khi thực hiện thay đổi các Model như vậy, tiến hành tạo ra một Migration mới đặt tên là InitWebDB-V1

dotnet ef migrations add InitWebDB_V1

Nó đã tạo ra Migration tiếp theo Migrations/20190925204118_InitWebDB_V1.cs

using Microsoft.EntityFrameworkCore.Migrations;

namespace EFMigration.Migrations
{
    public partial class InitWebDB_V1 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "Content",
                table: "article",
                type: "ntext",
                nullable: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "Content",
                table: "article");
        }
    }
}

Đồng thời Snapshot có thêm:

/..
b.Property<string>("Content")
 .HasColumnType("ntext");
/..

Thực hiện Migrate

dotnet ef database update InitWebDB_V1

Kết quả như hình, với database có cấu trúc mới

Tạo Migration thứ 3

Tạo mới model tên ArticleTag là bảng chứa thông tin về các Tag của bài viết Article

Tạo Model mới Models/ArticleTag.cs

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace EFMigration.Models
{
    [Table("articletag")]
    public class ArticleTag
    {
        [Key]
        public int ArticleTagId {set;  get;}

        public int ArticleId {set; get;}
        [ForeignKey("ArticleId")]
        public Article article {set; get;}

        [StringLength(20)]
        public string TagId {set; get;}
        [ForeignKey("TagId")]
        public Tag tag {set; get;}
    }
}

Thêm thuộc tính vào WebContext

public DbSet<ArticleTag> articleTags {set; get;}

Cũng thêm WebContext phương thức OnModelCreating trong đó thiết lập Index cho bảng mới.

    /..
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ArticleTag>(entity => {
            // Tạo Index Unique trên 2 cột
            entity.HasIndex(p => new {p.ArticleId,  p.TagId})
                  .IsUnique();
        });
    }
    /..

Tương tự như trên tạo ra bản Migration tiếp theo:

dotnet ef migrations add InitWebDB_V2

Cập nhật vào DB

dotnet ef database update InitWebDB_V2

Xóa Migration cuối với lệnh

dotnet ef migrations remove

Liệt kê các Migration

dotnet ef migrations list

Nếu muốn tạo SQL Script cho Migration thì gõ

dotnet ef migrations script --output migrations.sql

Kết quả xuất ra migrations.sql

Bạn có thể quay về một phiên bản bất kỳ trong danh sách bằng cách thực hiện lệnh dotnet ef database update tên_quay_về

Mã nguồn hoặc tải về ex046

Tạo Migration với Db đã có

Nếu dự án đã có DB trước rồi (có cả dữ liệu), giờ mới bắt đầu sử dụng Migration, thì trước tiên tạo ra các Model, DbContext từ DB có sẵn theo hướng dẫn - dbcontext scaffold

Tiếp theo cần tạo Migration đầu tiên như hướng dẫn trên, ví dụ migration đầu tiên đặt tên là Init

dotnet ef migrations add Init

Tuy nhiên nếu thực hiện update sẽ lỗi vì DB đã có và trong lịch sử không có lưu thông tin gì về Migration

Để thiết lập Migration này đã thực hiện và lưu trong lịch sử DB thì gõ lệnh tạo ra SQL Migration

dotnet ef migrations script --output migrations.sql

Mở migrations.sql lấy và thực hiện trực tiếp câu lệnh SQL liên quan đến bảng __EFMigrationsHistory gồm các SQL tạo bảng __EFMigrationsHistory, Insert vào bảng __EFMigrationsHistory, tạo bảng đó bằng cách chạy trực tiếp SQL

CREATE TABLE [__EFMigrationsHistory] (
    [MigrationId] nvarchar(150) NOT NULL,
    [ProductVersion] nvarchar(32) NOT NULL,
    CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);

Sau đó lấy MigrationID bằng cách gõ lệnh:

dotnet ef migrations list

Nó hiện thị thông tin:

Build started...
Build succeeded.
20200826095315_Init

Qua đó biết được MigrationID đầu tiên là: 20200826095315_Init

Thực hiện tiếp lấy Version của công cụ:

dotnet ef --version

Nó hiện thị thông tin:

Entity Framework Core .NET Command-line Tools
3.1.7

Vậy phiên bản là 3.1.7, thực lệnh câu lệnh SQL chèn một dòng vào bảng:

INSERT INTO [__EFMigrationsHistory](MigrationId, ProductVersion)
VALUES ('20200826095315_Init', '3.1.7')

Từ đây, các Migration tiếp theo (không phải Migration Init) sẽ thực hiện bình thường

Tùy biến Migration

Trong các phiên bản Migration, code của nó có hai phương thức là Up và Down, tại đây bạn có thể thi hành các lệnh SQL, nếu thi hành trong Up thì là khi cập nhật, thi hành trong Down là khi revert về phiên ban cũ.

Để thi hành các câu lệnh SQL bạn thực hiên

migrationBuilder.Sql("câu-lệnh-sql")

Tóm tắt các lệnh với Migration

Lệnh	Ý nghĩa
dotnet tool install --global dotnet-ef	Cài đặt công cụ dotnet ef
dotnet tool update --global dotnet-ef	Cập nhật công cụ dotnet ef
dotnet ef migrations add NameMigration	Tạo một Migration có tên NameMigration
dotnet ef migrations list	Danh sách các Migration
dotnet ef database update	Cập nhật Database đến Migration cuối
dotnet ef database update NameMigration	Cập nhật Database đến Migration có tên NameMigration
dotnet ef migrations remove	Xóa migration cuối
dotnet ef migrations script --output migrations.sql	Xuất lệnh SQL khi thực hiện Migration
dotnet ef database drop -f	Xóa database

 

Dapper + Generic Repository + PostgreSQL (edit)

.NET Core

https://www.codeproject.com/Articles/1186566/Dapper-Generic-Repository

https://techbrij.com/asp-net-core-postgresql-dapper-crud

.NET Web API

http://www.mukeshkumar.net/articles/web-api/dapper-and-repository-pattern-in-web-api

DI in .NET Core Console App

https://andrewlock.net/using-dependency-injection-in-a-net-core-console-application/

https://long2know.com/2018/02/net-core-console-app-dependency-injection-and-user-secrets/

https://auth0.com/blog/dependency-injection-in-dotnet-core/

EF Core + MySQL + Repository (ASP.NET Core Series)

https://code-maze.com/net-core-series/

https://code-maze.com/net-core-web-development-part4/

https://code-maze.com/net-core-web-development-part5/

Ways to consume Restful API

https://code-maze.com/different-ways-consume-restful-api-csharp/

• HttpWebRequest
• WebClient
• HttpClient
• RestSharp
• ServiceStack
• cUrl (command-line tool)

Repository Pattern

https://www.codeproject.com/Articles/1119652/Repository-Pattern-For-Net

Windows Service in .NET Core

https://www.stevejgordon.co.uk/running-net-core-generic-host-applications-as-a-windows-service

Work with EFCore Dapper together in the PostgreSQL database (edit)

Install-Package Dapper
Install-Package Npgsql

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Relational
Install-Package Dapper
Install-Package Npgsql
Install-Package System.Data.SqlClient

https://codewithmukesh.com/blog/using-entity-framework-core-and-dapper/ (HAY)

https://github.com/iammukeshm/EFCoreAndDapper

https://dotnetcoretutorials.com/2020/07/11/dapper-with-mysql-postgresql-on-net-core/

https://www.c-sharpcorner.com/article/getting-started-with-postgresql-using-dapper-in-net-core/

https://techbrij.com/asp-net-core-postgresql-dapper-crud/ (HAY)

https://dotnetcorecentral.com/blog/postgresql-and-dapper-in-net-core/

SQL (edit)

SQL CookBook

SQL Cookbook: query solutions and techniques for all sql users | Anthony Molinaro, Robert de Graaf | download (vn1lib.org)

SQL Cheat Sheet

https://www.sisense.com/blog/sql-symbol-cheatsheet/

SQL Cheat Sheet: Retrieving Column Description in SQL Server

https://www.sisense.com/blog/sql-cheat-sheet-retrieving-column-description-sql-server/

In SQL Server, details regarding a specific table column (e.g., column name, column id, column data type, column constraints) can be retrieved by joining system tables such as sys.tables, sys.columns, and sys.types.

PRINT 1..100

;WITH CTE AS (
  SELECT COUNT=1
  UNION ALL
  SELECT COUNT=COUNT+1
  FROM CTE WHERE COUNT<100
)
SELECT COUNT FROM CTE

;WITH Numbers(Number) AS (
  SELECT 1
  UNION ALL
  SELECT Number + 1
  FROM Numbers
  WHERE Number <= 999999
)
SELECT * FROM Numbers OPTION (MAXRECURSION 0)

select number from master..spt_values 
where type = 'p' 
and number between 1 and 100 
order by number

STUFF

SELECT TABLE_NAME ,
STUFF(( SELECT ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS Columns
WHERE Tables.TABLE_NAME = Columns.TABLE_NAME
ORDER BY COLUMN_NAME
FOR
XML PATH('')
), 1, 1, '') ConcatColumnNames
FROM INFORMATION_SCHEMA.COLUMNS Tables
GROUP BY TABLE_NAME;

COALESCE

SET @str=COALESCE(@str+','+Name,Name) FROM dbo.Customer

PRINT @str;

DENSE_RANK

DENSE_RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary) AS drank

Tối ưu hóa câu lệnh truy vấn SQL

+ Dùng index để tìm kiếm nhanh hơn

+ Dùng join thay vì sub-query

+ Chỉ chọn những trường cần thiết

+ Dùng exists thay vì count

+ Tránh dùng CURSOR

13 câu lệnh SQL quan trọng Programmer nào cũng cần biết

Cơ sở dữ liệu là một phần không thể thiếu của những trang web hiện đại. Trang web lớn hoặc web động đều sử dụng database theo một cách nào đó và khi được kết hợp với Structured Query Language (SQL) thì khả năng thao tác dữ liệu thực sự là vô tận. Nếu đã biết SQL mà lại còn là lập trình viên thì bạn hãy chắc chắn rằng mình đã nắm chắc 13 câu lệnh SQL quan trọng mà chúng tôi đề cập đến trong bài viết này nhé!

Có rất nhiều tên dữ liệu được trả về từ bảng dữ liệu. Dữ liệu thường được gọi là Rows (hàng), Records (bản ghi) hoặc Tuples. Những thuật ngữ vừa liệt kê sẽ được sử dụng thay thế cho nhau trong suốt bài viết này.

Lời nói đầu

Tất cả các ví dụ ngày hôm nay sẽ được dựa trên bốn bảng giả định. Bảng customers có tên và tuổi của khách hàng: 

Bảng heights có chứa tên và chiều cao của bất kỳ người nào:

Bảng staff có tên và tuổi của nhân viên - chính xác như customers:

Bảng cuối cùng được gọi là people có tên và tuổi của người, giống như bảng customersvà staff:

1. SELECT

Câu lệnh SELECT là đơn giản nhất, và bạn cần phải hiểu nó vì nó làm cơ sở cho khá nhiều lệnh khác. Hãy cân nhắc việc luyện tập viết các lệnh SQL bằng chữ hoa, vì nó làm cho câu lệnh dễ đọc và dễ hiểu hơn.

Như tên của nó ngụ ý, SELECT được sử dụng để chọn dữ liệu từ cơ sở dữ liệu. Đây là cách sử dụng đơn giản nhất:

SELECT * FROM table;

Câu lệnh trên có hai phần:

• SELECT *: xác định cột bạn muốn chọn, dấu * ở đây hiểu là bạn muốn chọn tất cả các cột trong bảng.
• FROM table: phần này nói với công cụ cơ sở dữ liệu nơi bạn muốn trích xuất dữ liệu, thay thế "table" bằng tên của bảng cơ sở dữ liệu cần lấy.

Câu lệnh SELECT này được gọi là "select star", sử dụng dấu * là một phương pháp khá hay giúp tìm, tính toán dữ liệu trong bảng, nhưng không phải lúc nào cũng dùng câu lệnh này. Khi sử dụng select star, việc trình bày dữ liệu trả về như thế nào hoàn toàn phụ thuộc vào engine của database, bạn không thể kiểm soát thứ tự dữ liệu được trả về, vì vậy, nếu có ai đó thêm cột mới vào bảng, bạn thấy các biến trong ngôn ngữ lập trình của mình không hiển thị dữ liệu đúng. May mắn là có một giải pháp khác cho vấn đề này.

Bạn có thể nói rõ các cột muốn truy xuất, như sau:

SELECT age, name FROM people;

Truy vấn này sẽ trích xuất cột name và age từ bảng people. Việc này có vẻ hơi nhàm chán nếu bạn có quá nhiều dữ liệu, nhưng làm vậy sẽ giúp giảm nhiều vấn đề có thể xảy ra trong tương lai, cũng như làm cho SQL dễ hiểu hơn với các lập trình viên mới sau này.

Nếu bạn muốn chọn thêm dữ liệu bổ sung, nhưng nó không được lưu trữ trong bất kỳ bảng nào, thì có thể làm như sau: 

SELECT age, '1234' FROM people;

Bất kỳ chuỗi nào bên trong dấu nháy đơn sẽ được trả về thay vì tên cột phù hợp.

2. WHERE

Câu lệnh SELECT là lựa chọn tuyệt vời để lấy dữ liệu, nhưng nếu bạn muốn lọc kết quả kỹ hơn chút nữa, ví như, chỉ muốn trích xuất ra những người có màu mắt xanh, người sinh tháng 1 và làm thợ cơ khí thì phải làm sao? Đây chính là lúc sử dụng câu lệnh WHERE. WHERE cho phép áp dụng thêm các điều kiện vào SELECT, bạn chỉ cần nối nó vào cuối cùng của câu lệnh là được:

SELECT age, name FROM people WHERE age > 10;

Truy vấn này được giới hạn cho những người có tuổi lớn hơn 10. Bạn có thể kết hợp nhiều điều kiện bằng cách sử dụng toán tử AND:

SELECT age, name FROM people WHERE age > 10 AND age < 20;

Lệnh AND làm việc chính xác như nghĩa của nó trong tiếng Anh: Nó áp dụng những điều kiện khác nhau cho câu lệnh. Trong ví dụ trên, dữ liệu được trả về sẽ là bất kỳ bản ghi nào có tuổi nằm giữa 10 và 20. Do không có kết quả nào phù hợp nên không có dữ liệu nào được trả lại.

Một lệnh khác có thể được sử dụng để kết hợp điều kiện là OR. Đây là ví dụ:

SELECT age, name FROM people WHERE age > 10 OR name = 'Joe';

Truy vấn này yêu cầu trả về những bản ghi có tuổi lớn hơn 10 hoặc tên là Joe. Chú ý, ở đây chỉ có một dấu "=", nhưng nhiều ngôn ngữ lập trình sử dụng 2 dấu bằng (==) để kiểm tra sự tương đương, điều này không cần thiết cho phần lớn các engine của database, xong bạn vẫn nên kiểm tra kỹ trên môi trường làm việc của cơ sở dữ liệu.

3. ORDER

Lệnh ORDER được sử dụng để sắp xếp kết quả trả về, sử dụng ORDER khá đơn giản, chỉ cần thêm ORDER vào cuối câu lệnh như ví dụ dưới đây:

SELECT name, age FROM people ORDER BY age DESC;

Nếu cần chọn cột và thứ tự cụ thể, bạn có thể làm như sau (ASC là tăng dần, DESC là giảm dần): 

SELECT name, age FROM people ORDER BY name ASC, age DESC;

ORDER BY có lẽ là hữu ích nhất khi kết hợp với các lệnh khác. Không phải tất cả các truy vấn sẽ trả về dữ liệu một cách hợp lý hoặc có trật tự - lệnh này cho phép bạn thay đổi điều đó.

4. JOIN

Lệnh JOIN được sử dụng để kết hợp các dữ liệu liên quan được lưu trữ trong một hoặc nhiều bảng. Bạn có thể nối bảng thứ hai vào bảng đầu tiên, và chỉ định cách dữ liệu được kết nối. Dưới đây là ví dụ cơ bản:

SELECT age, name, height FROM people LEFT JOIN heights USING (name);

Có một vài chú ý ở đây. Bạn phải bắt đầu với cú pháp "LEFT JOIN", hiểu rằng bạn muốn nối một bảng bằng cách sử dụng một kiểu nối LEFT. Tiếp theo, xác định bảng mà bạn muốn nối (heights). Cú pháp USING (name) cho biết cột "name" có thể được tìm thấy trong cả hai bảng và cột này sẽ được sử dụng như một chìa khóa để kết hợp các bảng với nhau.

Đừng lo lắng nếu các cột của bạn có tên khác nhau trong mỗi bảng. Bạn có thể sử dụng "ON" thay vì "USING":

SELECT age, name, height FROM people LEFT JOIN heights ON (namea = nameb);

Lệnh ON sẽ xác định rõ cột nào là chìa khóa để nối. Có rất nhiều kiểu nối mà bạn sẽ cần chút thời gian để tìm hiểu chi tiết, đây là một bản tóm tắt nhanh:

• (INNER) JOIN: Trả về các hàng có trong cả hai bảng.
• LEFT (OUTTER) JOIN: Trả về tất cả các hàng từ bảng bên trái cùng với những bản ghi phù hợp ở bảng bên phải. Nếu không có bản ghi nào phù hợp thì những bản ghi ở bảng bên trái vẫn được trả về.
• RIGHT (OUTER) JOIN: Trái ngược với kiểu nối bên trên, tất cả các hàng của bảng bên phải sẽ được trả về cùng với những hàng phù hợp của bảng bên trái.
• FULL (OUTER) JOIN: Trả về tất cả những bản ghi phù hợp ở trong hai bảng.

Cú pháp INNER hay OUTER là tùy chọn, nó làm cho mọi thứ dễ hiểu hơn nhưng không nhất thiết lúc nào bạn cũng bắt buộc phải dùng đến chúng.

5. ALIAS

Bây giờ bạn đã biết những câu lệnh cơ bản rồi, thử tiếp với lệnh ALIAS xem sao nhé.

Câu lệnh này được sử dụng để tạm thời đổi tên một bảng, tên mới này chỉ tồn tại bên trong tiến trình xử lý (transaction) bạn đang chạy. Đây là cách sử dụng:

SELECT A.age FROM people A;

Có thể sử dụng bất kỳ tên phù hợp nào bạn muốn, trong ví dụ này tôi sử dụng các chữ cái trong bảng chữ cái. Trước mỗi tên cột, ALIAS sẽ được đặt trước. ALIAS này được gán cho bảng ngay sau khi khai báo. Tương tự:

SELECT people.age FROM people;

Thay vì phải nhập tên bảng dài, bạn chỉ cần nhập chữ cái đơn giản, dễ nhớ. Nhưng ở đây có một vấn đề nhỏ, nếu bạn chọn từ nhiều bảng, rất dễ bị nhầm lẫn giữa các cột trong bảng. Trong trường hợp các bảng đó có những cột giống tên nhau, truy vấn cơ sở dữ liệu có thể bị lỗi vì không tham chiếu chính xác được đến tên bảng hoặc ALIAS. Đây là ví dụ với hai bảng:

SELECT staff.age, staff.name, customers.age, customers.name FROM staff, customers;

Và đây là truy vấn tương tự với các ALIAS:

SELECT A.age, A.name, B.age, B.name FROM staff A, customers B;

Bảng staff được gán tên mới là A, bảng customers được gán tên mới là B. Các bảng này giúp code dễ hiểu hơn và giảm số lượng chữ cần phải gõ.

Nếu muốn đổi tên cột với ALIAS, bạn sử dụng lệnh AS:

SELECT age AS person_age FROM people;

Khi truy vấn này được thực hiện, cột sẽ được gọi là "person_age" thay vì "age".

6. UNION

UNION là một lệnh tuyệt vời. Nó cho phép bạn nối các hàng với nhau. Không giống như lệnh JOIN chỉ nối thêm các cột phù hợp, UNION có thể nối các hàng không liên quan với nhau nếu có cùng một số lượng cột và tên cột. Đây là cách bạn sử dụng nó:

SELECT age, name FROM customers
UNION 
SELECT age, name FROM staff;

Một câu lệnh UNION sẽ chỉ trả về những kết quả là hàng duy nhất giữa 2 truy vấn, bạn có thể sử dụng cú pháp UNION ALL để trả lại tất cả dữ liệu, kể cả những cái trùng nhau.

SELECT age, name FROM customers
UNION ALL
SELECT age, name FROM staff;

Dù kết quả trả về của 2 câu lệnh trên giống nhau, nhưng bạn nhận thấy thứ tự của các hàng có sự thay đổi, đúng không? UNION hoạt động theo cách hiệu quả nhất, vì vậy dữ liệu trả về có thể khác nhau theo thứ tự.

Một trường hợp nữa có thể sử dụng UNION là tính tổng số phụ (subtotal), bạn kết hợp một truy vấn của tổng số (sum total) vào truy vấn của các tổng số riêng lẻ (individual total) cho một tình huống cụ thể. Nghe lằng nhằng nhỉ!

7. INSERT

6 câu lệnh bên trên đều giúp bạn trích xuất dữ liệu từ database, nếu muốn chèn thêm dữ liệu vào database thì làm thế nào? Đây là lúc cho lệnh INSERT thể hiện:

INSERT INTO people(name, age) VALUES('Joe', 102);

Bạn phải chỉ định tên bảng (people) và cột bạn muốn sử dụng (name và age). Cú pháp VALUES sau đó được sử dụng để cung cấp các giá trị cần chèn. Thứ tự của giá trị cần chèn phải được đặt đúng như thứ tự của các cột đã được chỉ định trước đó.

Bạn không thể chỉ định WHERE để chèn, và cần đảm bảo rằng đã tuân thủ đúng các ràng buộc giữa các bảng.

8. UPDATE

Sau khi chèn thêm dữ liệu, bạn cần phải thay đổi các hàng cụ thể. Đây là cú pháp của lệnh UPDATE:

UPDATE people SET name = 'Joe', age = 101;

Bạn phải chỉ định bảng muốn thay đổi, sau đó sử dụng cú pháp SET để xác định các cột và các giá trị mới của chúng. Câu lệnh trong ví dụ này sẽ cập nhật tất cả bản ghi riêng lẻ.

Để cụ thể hơn, bạn có thể sử dụng WHERE giống như khi thực hiện lệnh SELECT:

UPDATE people SET name = 'Joe', age = 101 WHERE name = 'James';

Thậm chí, có thể sử dụng cả toán tử điều kiện AND, OR:

UPDATE people SET name = 'Joe', age = 101 WHERE (name = 'James' AND age = 100) OR name = 'Ryan';

Hãy chú ý cách mà dấu ngoặc đơn được sử dụng để bắt buộc tuân theo các điều kiện.

9. UPSERT

UPSERT nghe có vẻ lạ, nhưng đây lại là lệnh khá hữu ích. Giả sử có một hạn chế trên bảng dữ liệu là bạn chỉ lưu những bản ghi với tên duy nhất, bạn không muốn có hai hàng trùng tên nhau xuất hiện trong bảng. Khi đó nếu cố gắng chèn nhiều giá trị "Joe" vào thì engine của database sẽ báo lỗi và từ chối làm điều đó (gần như vậy). Lệnh UPSERT cho phép bạn cập nhật bản ghi nếu nó đã tồn tại. Nếu không có lệnh này, bạn sẽ phải viết rất nhiều logic để kiểm tra như kiểm tra xem nó đã tồn tại chưa, nếu chưa tồn tại thì chèn, nếu đã tồn tại thì trích xuất khóa chính (primary key) chính xác của nó rồi cập nhật. Thật là muốn phát điên luôn mà...

Tiếc là lệnh này được thực hiện khác nhau trên những database khác nhau. PostgreSQL gần đây đã có thêm lệnh này, trong khi MySQL đã có từ rất lâu. Đây là cú pháp lệnh UPSERT trên MySQL để bạn tham khảo:

INSERT INTO people(name, age)
VALUES('Joe', 101)
ON DUPLICATE KEY UPDATE age = 101;

Nếu tinh ý, bạn sẽ nhận thấy rằng cách này thực chất là một lệnh cập nhật kết hợp với lệnh chèn, có thể hiểu là "cập nhật nếu chèn không thành công".

10. DELETE

Lệnh DELETE được sử dụng để xóa hoàn toàn các bản ghi, nó có thể khá nguy hiểm nếu bị lạm dụng. Cú pháp của lệnh này khá đơn giản:

DELETE FROM people;

Câu lệnh trên sẽ xóa mọi thứ từ bảng people. Nếu chỉ muốn xóa những bản ghi nhất định hãy sử dụng thêm WHERE:

DELETE FROM people WHERE name = 'Joe';

Nếu bạn đang phát triển một hệ thống thì cách khôn ngoan hơn là sử dụng một lệnh "soft delete". Cụ thể, bạn không bao giờ thực sự chạy một lệnh DELETE, mà tạo một cột đã xóa (chuyển dữ liệu sang đó), kiểm tra cột một lần nữa để tránh những trường hợp xóa nhầm đáng tiếc. Cách này cũng giúp nhanh chóng lấy lại bản ghi nếu phát hiện lỗi hay vấn đề cần kiểm tra lại. Tất nhiên, đây không phải là lựa chọn sao lưu thích hợp đâu nhé. Hãy cứ thực hiện sao lưu hệ thống của bạn, bởi cẩn tắc vô áy náy mà.

11. CREATE TABLE

Vâng, đúng như tên gọi, lệnh này được sử dụng để tạo bảng, và đây là cú pháp của nó:

CREATE TABLE people (
  name TEXT,
  age, INTEGER,
  PRIMARY KEY(name)
);

Chú ý cách các tên cột, ràng buộc nằm trong ngoặc và gán kiểu dữ liệu cho cột được viết như thế nào. Khóa chính cũng cần được chỉ định, đây là yêu cầu đầu tiên của một thiết kế database chuẩn.

12. ALTER TABLE

Lệnh ALTER TABLE được sử dụng để sửa đổi cấu trúc của một bảng. Ở đây có một chút hạn chế, vì cơ sở dữ liệu của bạn sẽ không cho phép thay đổi một bảng nếu dữ liệu đang tồn tại có thể gây ra xung đột, ví dụ, thay đổi một chuỗi thành một số nguyên. Trong những trường hợp này, cần sửa dữ liệu trước, sau đó sửa đổi bảng. Đây là ví dụ:

ALTER TABLE people ADD height integer;

Ví dụ này thêm một cột được gọi là "height" với kiểu dữ liệu là số nguyên vào bảng people. Không có giới hạn về những gì bạn có thể thay đổi.

13. DROP TABLE

Lệnh cuối cùng là DROP TABLE. Lệnh này cũng gần giống với DELETE nhưng thay vì xóa một bản ghi duy nhất, nó xóa mọi bản ghi trong bảng. Đây là cách sử dụng nó:

DROP TABLE people;

Lệnh này khá nguy hiểm, vì thế nên thực hiện nó bằng tay trong phần lớn các trường hợp, đề phòng những lỗi không mong muốn có thể xảy ra.

Xong rồi, 13 lệnh tất cả, hy vọng bạn đã bỏ túi được một số thủ thuật hữu ích khi làm việc với cơ sở dữ liệu. Hãy chia sẻ với chúng tôi những câu lệnh, thủ thuật SQL khác mà bạn đã khám phá được nhé! 

SQL SERVER – Get Current TimeZone Name in SQL Server

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TimeZone OUT

SELECT @TimeZone

--SE Asia Standard Time

Handle conversion between time zones in SQL Server

https://www.mssqltips.com/sqlservertip/3173/handle-conversion-between-time-zones-in-sql-server--part-1/

https://www.mssqltips.com/sqlservertip/3174/handle-conversion-between-time-zones-in-sql-server--part-2/

https://www.mssqltips.com/sqlservertip/3175/handle-conversion-between-time-zones-in-sql-server--part-3/