/// <summary>
///Remplit la table destination en mergeant avec le contenu du fichier csv fournie
///
/// Voir en MS SQL 2012 , pour utiliser la nouvelle instruction MERGE
///
/// </summary>
/// <returns></returns>
public void BulkUpsertCsv(string CsvFileName, DatabaseTable targetTableName, string csvFileEncoding = "UTF8", string dateFormat = "DMY", int connectionTimeout = -1)
{
Encoding enc = null;
switch (csvFileEncoding)
{
case "UTF8": enc = Encoding.UTF8; break;
case "ASCII": enc = Encoding.ASCII; break;
case "BigEndianUnicode": enc = Encoding.BigEndianUnicode; break;
case "Unicode": enc = Encoding.Unicode; break;
case "UTF32": enc = Encoding.UTF32; break;
case "UTF7": enc = Encoding.UTF7; break;
default: enc = Encoding.Default; break;
}
;
DatabaseTable usingTableName = new DatabaseTable(targetTableName.schema, "#" + targetTableName.table);
List <string> columns = new List <string>();
using (var reader = new CsvReader(new StreamReader(CsvFileName, enc), true, ';'))
{
try
{
DestinationConnection.Open();
CsvDataAdapter adapter = new CsvDataAdapter(reader);
int nbTobeMergedRows = adapter.Fill(DestinationConnection, usingTableName);
InfoLogger.Info(String.Format("MERGE Table: {0}, using File {1} : nb rows: {2}", targetTableName, CsvFileName, nbTobeMergedRows));
this.BulkUpsert(targetTableName, usingTableName, dateFormat, connectionTimeout);
}
catch (Exception e)
{
throw e;
}
finally
{
DestinationConnection.Close();
}
}
}
public void ACTION_PROCESS_BulkCopy_DATA_FACTSET_DATA_1()
{
string filepath = @"G:\,FGA MarketData\FACTSET\201402\base_20140210.csv";
DataSet ds = new DataSet("DATA_FACTSET");
SQLCopy.MSSQL2005_DBConnection dest = new SQLCopy.MSSQL2005_DBConnection("FGA_RW");
using (var reader = new CsvReader(new StreamReader(new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)), true, '|'))
{
CsvDataAdapter adapter = new CsvDataAdapter(reader);
// TODO : ajouter un objet de mapping sur le csvDataApdapter
int nbReadLines = adapter.Fill(dest, new DatabaseTable("dbo", "DATA_FACTSET"));
}
}
public void LumenCSVReadValuesAndBulkCopyIntoDest_PROXY()
{
string proxyPath = @"G:\,FGA Systèmes\PRODUCTION\BASES\PROXY\20131231\portfolio\PortfolioHolding_MONNET.csv";
DataSet ds = new DataSet("PTF_PROXY");
SQLCopy.MSSQL2005_DBConnection dest = new SQLCopy.MSSQL2005_DBConnection("FGA_RW");
using (var reader = new CsvReader(new StreamReader(proxyPath, Encoding.UTF8), true, ';'))
{
CsvDataAdapter adapter = new CsvDataAdapter(reader);
adapter.Fill(dest, new DatabaseTable("dbo", "#PTF_PROXY"));
dest.Insert(new DatabaseTable("dbo", "PTF_PROXY"), new DatabaseTable("dbo", "#PTF_PROXY"));
//adapter.Fill(dest, new DatabaseTable("dbo", "PTF_PROXY"));
}
}
public void LumenCSVReadValuesAndInsertIntoDest()
{
TestContext.BeginTimer("LumenCSVReadValuesAndInsertIntoDest");
DataSet ds = new DataSet("PTF_PROXY");
DBConnectionDelegate dest = new MSSQL2005_DBConnection("FGA_JMOINS1");
using (var reader = new CsvReader(new StreamReader(@"PortfolioHolding_PROXY2.csv"), true, ';'))
{
CsvDataAdapter adapter = new CsvDataAdapter(reader);
adapter.Fill(dest, new DatabaseTable("dbo", "#PTF_PROXY"));
dest.Insert(new DatabaseTable("dbo", "PTF_PROXY"), new DatabaseTable("dbo", "#PTF_PROXY"));
}
TestContext.EndTimer("LumenCSVReadValuesAndInsertIntoDest");
}
public void LumenCSVReadValuesAndDeleteIntoDest()
{
DBConnectionDelegate destinationConnection = new MSSQL2005_DBConnection("FGA_JMOINS1");
DataSet ds = new DataSet("PTF_PROXY_4DELETE");
List <string> columns = new List <string>();
using (var reader = new CsvReader(new StreamReader(@"Delete_PROXY1.csv"), true, ';'))
{
CsvDataAdapter adapter = new CsvDataAdapter(reader);
int nbReadLines = adapter.Fill(ds, "#PTF_PROXY_CLAUSE");
DataTable dt = ds.Tables["#PTF_PROXY_CLAUSE"];
destinationConnection.Delete(new DatabaseTable("dbo", "PTF_PROXY"), dt);
}
}
public override int DeleteCsvFile(string CsvFileName, DatabaseTable targetTableName, string csvFileEncoding = "UTF8", string dateFormat = "DMY", string tableCollation = "COLLATE SQL_Latin1_General_CP1_CI_AS", int connectionTimeout = -1)
{
Encoding enc = csvFileEncoding.GetEncoding();
string usingTableName = "#" + targetTableName.table;
string usingSchemaName = targetTableName.schema;
List <string> columns = new List <string>();
DataSet ds = new DataSet();
using (var reader = new CsvReader(new StreamReader(CsvFileName, enc), true, ';'))
{
Connection.Open();
CsvDataAdapter adapter = new CsvDataAdapter(reader);
adapter.Fill(ds, "TO_DELETE");
DataTable dt = ds.Tables["TO_DELETE"];
return(this.Delete(targetTableName, dt, dateFormat, tableCollation, connectionTimeout));
}
}
