Console Application - Entity Framework Code First

Package Manager Console
1) Install-Package EntityFramework -Version 6.2.0

2) Enable-Migrations

3) Enable-Migrations -Force
Checking if the context targets an existing database...
Code First Migrations enabled for project CodeFirst.ConsoleApp1.

4) Get-Migrations
Retrieving migrations that have been applied to the target database.
No migrations have been applied to the target database.

5) Add-Migration "InitialCreate"
Scaffolding migration 'InitialCreate'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration InitialCreate' again.

6) Update-Database
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Applying explicit migrations: [201803210431381_InitialCreate].
Applying explicit migration: 201803210431381_InitialCreate.
Running Seed method.

Add-Migration "InitialCreate2"
Update-Database -Force

Add-Migration "InitialCreate3"
Update-Database -Force

Add-Migration "InitialCreate4"
Update-Database -Force

Code sample for Up() & Down()
https://msdn.microsoft.com/en-us/library/jj591621(v=vs.113).aspx

Code sample for Primary Key, Max Length
https://msdn.microsoft.com/en-us/data/jj591617

Code sample for Reference Key
https://msdn.microsoft.com/en-us/data/jj591583

Code sample for using DbContext
https://msdn.microsoft.com/en-us/library/jj193542(v=vs.113).aspx

Entity Framework Core Migrations
https://www.learnentityframeworkcore.com/migrations

Retrieving data with DbContext
https://chsakell.com/2013/08/24/retrieving-data-with-dbcontext/

Add-Migration
Remove-Migration
Scaffold-DbContext
Script-Migration
Update-Database
Use-DbContext

// When the Entity Framework sees Timestamp attribute
// it configures ConcurrencyCheck and DatabaseGeneratedPattern=Computed.
[Timestamp]
public Byte[] Timestamp { get; set; }

DbContext

public class SchoolDBContext : DbContext
{
public DbSet<PaymentList> PaymentLists { get; set; }

public SchoolDBContext() : base("SchoolContext")
{
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
}

Models

public class PaymentList
{
public Guid Id { get; set; }

[MaxLength(50)]
public string IDPaymentNumber { get; set; } // ID (payment number) // ID(결제번호)

[MaxLength(50)]
public string StoreID { get; set; } // Store ID // Store ID

[MaxLength(50)]
public string ItemName { get; set; } // Item name // 아이템명

[MaxLength(50)]
public string ConsumeYesNo { get; set; } // Consume Y / N // Consume Y/N

[MaxLength(50)]
public string AppID { get; set; } // App ID // App ID

[MaxLength(50)]
public string StoreReferenceKey { get; set; } // Store Reference Key // Store Reference Key

[MaxLength(50)]
public string UserKey { get; set; } // User Key // User Key

[MaxLength(50)]
public string Condition { get; set; } // Condition // 상태

public decimal? Price { get; set; } // Price // 가격

[MaxLength(50)]
public string Currency { get; set; } // Currency // 통화 // Data example: N/A,USD,VND,AUD,MYR,GBP,RUB,TWD,PHP,NOK

[MaxLength(50)]
public string CreationDate { get; set; } // Creation date // 생성일

[MaxLength(50)]
public string RefundDate { get; set; } // RefundDate // RefundDate

// When the Entity Framework sees Timestamp attribute
// it configures ConcurrencyCheck and DatabaseGeneratedPattern=Computed.
[Timestamp]
public Byte[] Timestamp { get; set; }
}

Web.config/App.config

<connectionStrings>
   <add name="SchoolContext" connectionString="Data Source=192.168.1.60;Initial Catalog=Northwind;User Id=sa;Password=123456;Persist Security Info=False;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient" />
</connectionStrings>

Program.cs

public static void Main(string[] args)
{
using (var db = new SchoolDBContext())
{
var paymentList = db.Set<PaymentList>();

// Giả sử lấy được danh sách các [PaymentList] từ tệp Excel
var listWantToInsert = new List<PaymentList>() {
new PaymentList()
{
Id = Guid.NewGuid(),
IDPaymentNumber = "2018032121651809",
StoreID = "Google Play",
ItemName = "Tulokho_AOS_20,000K_Gold",
ConsumeYesNo = "true",
AppID = "1000365",
StoreReferenceKey = "GPA.3383-6959-8515-83550",
UserKey = "20180320141733@fb",
Condition = "Success",
Price = Convert.ToDecimal("21000"),
Currency = "VND",
CreationDate = "43180.52107",
RefundDate = ""
},
new PaymentList()
{
Id = Guid.NewGuid(),
IDPaymentNumber = "2018032121651580",
StoreID = "Google Play",
ItemName = "Tulokho_AOS_20,000K_Gold",
ConsumeYesNo = "true",
AppID = "1000365",
StoreReferenceKey = "GPA.3313-2221-9185-05818",
UserKey = "20180320141733@fb",
Condition = "Success",
Price = Convert.ToDecimal("21000"),
Currency = "VND",
CreationDate = "43180.51127",
RefundDate = ""
}
};

// 1. Giả sử lấy được danh sách các IDPaymentNumber từ tệp Excel
var listIdWantToInsert = (from x in listWantToInsert select x.IDPaymentNumber).ToList();

// 2. Kiểm tra trong Database xem đã tồn tại những IDPaymentNumber ở trên chưa
var list = (from x in db.PaymentLists.ToList()
where listIdWantToInsert.Contains(x.IDPaymentNumber)
select x.IDPaymentNumber).ToList();

// 3. INSERT into Database
foreach (var item in listWantToInsert)
{
if (list.Contains(item.IDPaymentNumber))
{
db.PaymentLists.Add(item);
}
db.SaveChanges();
}
Console.WriteLine("Done.");
Console.ReadKey();
}
}