public void ImportProducts()
{
using (var stream = new FileStream("Products.xlsx", FileMode.Open, FileAccess.Read)) {
var helper = new DefaultExcelBulkEditHelper();
var categories = helper.Read <CategroyExcelBulkRow>(stream);
var products = helper.Read <ProductExcelBulkRow>(stream);
var queryString = @"
SET IDENTITY_INSERT [dbo].[Category] ON
MERGE INTO [dbo].[Category] [target]
USING(
VALUES
{0}
)[source]([Id],[MerchantId],[Name],[Enabled],[Description],[JsonString],[CreatedDateTime],[LastUpdatedDateTime],[CreatedBy],[LastUpdatedBy])
ON [target].[Id] = [source].[Id] AND [target].[MerchantId] = [source].[MerchantId]
WHEN MATCHED THEN UPDATE SET
[target].[Name] = [source].[Name],
[target].[Enabled] = [source].[Enabled],
[target].[Description] = [source].[Description],
[target].[JsonString] = [source].[JsonString],
[target].[LastUpdatedDateTime] = [source].[LastUpdatedDateTime],
[target].[LastUpdatedBy] = [source].[LastUpdatedBy]
WHEN NOT MATCHED THEN INSERT ([Id],[MerchantId],[Name],[Enabled],[Description],[JsonString],[CreatedDateTime],[CreatedBy])
VALUES([source].[Id],[source].[MerchantId],[source].[Name],[source].[Enabled],[source].[Description],[source].[JsonString],[source].[CreatedDateTime],[source].[CreatedBy]);
SET IDENTITY_INSERT [dbo].[Category] OFF
";
using (var database = factory.GenerateDatabase()) {
queryString = string.Format(queryString, string.Join(",", categories.Data.Where(x => x.Id != 0).Select(ctx => {
return($"\r\n({ctx.Id},{ctx.MerchantId},'{ctx.Name}',1,'','[]',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),'Initialization','Initialization')");
})));
database.Execute(queryString);
}
queryString = @"SET IDENTITY_INSERT [dbo].[Product] ON
MERGE INTO [dbo].[Product] AS [target]
USING(
VALUES
{0}
) AS[source]([Id],[MerchantId],[CategoryId],[Name],[Price],[SalesVol],[SortNo],[Enabled],[Description],[ImageUrl],[Settings],[CreatedBy],[CreatedDateTime],[LastUpdatedBy],[LastUpdatedDateTime])
ON[target].[Id] = [source].[Id] AND[target].[MerchantId] = [source].[MerchantId]
WHEN MATCHED THEN UPDATE SET
[target].[CategoryId] = [source].[CategoryId]
,[target].[Name] = [source].[Name]
,[target].[Price] = [source].[Price]
,[target].[SalesVol] = [source].[SalesVol]
,[target].[SortNo] = [source].[SortNo]
,[target].[Enabled] = [source].[Enabled]
,[target].[Description] = [source].[Description]
,[target].[ImageUrl] = [source].[ImageUrl]
,[target].[Settings] = [source].[Settings]
,[target].[LastUpdatedBy]=[source].[LastUpdatedBy]
,[target].[LastUpdatedDateTime] =[source].[LastUpdatedDateTime]
WHEN NOT MATCHED THEN INSERT
([Id], [MerchantId], [CategoryId], [Name], [Price], [SalesVol], [SortNo], [Enabled], [Description], [ImageUrl], [CreatedBy], [CreatedDateTime])
VALUES([source].[Id] , [source].[MerchantId] , [source].[CategoryId] , [source].[Name] , [source].[Price] , [source].[SalesVol] , [source].[SortNo] , [source].[Enabled] , [source].[Description] , [source].[ImageUrl] , [source].[CreatedBy] , [source].[CreatedDateTime] );
SET IDENTITY_INSERT[dbo].[Product] OFF
";
using (var database = factory.GenerateDatabase()) {
queryString = string.Format(queryString, string.Join(",", products.Data.Where(x => x.Id != 0).Select(ctx => {
var settings = new ProductSettings();
settings.Banners = ctx.Banners.Split(",")
?.Select(x => $"https://www.yourc.club/images/products/{x}")
.ToArray();
settings.Specifications = new List <Specification>();
var size = ctx.Size.DeserializeToObject <Specification>();
if (size != null)
{
settings.Specifications.Add(size);
}
var tempet = ctx.Temperature.DeserializeToObject <Specification>();
if (tempet != null)
{
settings.Specifications.Add(tempet);
}
ctx.ImageUrl = string.IsNullOrEmpty(ctx.ImageUrl) ? string.Empty : $"https://www.yourc.club/images/products/{ctx.ImageUrl}";
return($"\r\n({ctx.Id},{ctx.MerchantId},{ctx.CategoryId},'{ctx.Name}',{ctx.Price},{ctx.SalesVol},{ctx.SortNo},1,'{ctx.Description}','{ctx.ImageUrl}','{settings.SerializeToJson()}','Initialization',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()),'Initialization',DATEDIFF(S,'1970-01-01',SYSUTCDATETIME()))");
})));
database.Execute(queryString);
}
}
}
