62 lines
1.5 KiB
C#
62 lines
1.5 KiB
C#
using System.Data.SqlClient;
|
|
using System.Linq;
|
|
using Dapper;
|
|
using NUnit.Framework;
|
|
|
|
namespace PriorBankParser
|
|
{
|
|
public class DbLoader
|
|
{
|
|
private const string Sql = @"
|
|
DROP TABLE IF EXISTS [dbo].[VPSK_IMPORT]
|
|
GO
|
|
|
|
CREATE TABLE [dbo].[VPSK_IMPORT]
|
|
(
|
|
[Contract] NVARCHAR(255),
|
|
[TransactionName] NVARCHAR(2048),
|
|
[Category] NVARCHAR(2048),
|
|
[Currency] VARCHAR(5),
|
|
[IsIncome] VARCHAR(5),
|
|
[TransactionDate] DATETIME,
|
|
[OperationDate] DATETIME,
|
|
[Amount] DECIMAL(19, 6),
|
|
[Commission] DECIMAL(19, 6),
|
|
[AccountTurnover] DECIMAL(19, 6)
|
|
)
|
|
GO
|
|
|
|
BULK INSERT [dbo].[VPSK_IMPORT]
|
|
FROM '<SOURCE_FILE_PATH>'
|
|
WITH (
|
|
TABLOCK
|
|
,FORMAT = 'CSV'
|
|
,FIRSTROW = 2
|
|
,FIELDTERMINATOR = ','
|
|
,FIELDQUOTE = '0x22'
|
|
--,ROWTERMINATOR = '0x0a'
|
|
,CODEPAGE=65001
|
|
);
|
|
|
|
";
|
|
|
|
[Test]
|
|
public void Load()
|
|
{
|
|
const string sourceFile = @"D:\Cloud-storage\Dropbox\Home\Finance\Raw Source\ConvertedResult.csv";
|
|
var script = Sql.Replace("<SOURCE_FILE_PATH>", sourceFile);
|
|
|
|
var connectionString = new SqlConnectionStringBuilder
|
|
{ DataSource = "(local)", InitialCatalog = "MY_FINANCE", IntegratedSecurity = true, }
|
|
.ConnectionString;
|
|
using var sqlConnection = new SqlConnection(connectionString);
|
|
sqlConnection.Open();
|
|
|
|
foreach (var scriptPart in script.Split("GO").Where(s => !string.IsNullOrEmpty(s)))
|
|
{
|
|
sqlConnection.Execute(scriptPart, commandTimeout: 60 * 60);
|
|
}
|
|
}
|
|
}
|
|
}
|