public void TestBulkCopyData_DEV_2_PROD()
{
//string dest = @"Data Source=FX023179M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
//SQLCopy.SQLUtils s = new SQLCopy.SQLUtils("ConnectionAdmin", dest);
string dev = @"Data Source=FX027471M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
string stage = @"Data Source=MEPAPP042_R;Initial Catalog=E2DBFGA01;Persist Security Info=True;User ID=E2FGATP;Password=E2FGATP25";
string preprod = @"Data Source=VWI1BDD002;Initial Catalog=E1DBFGA01;Persist Security Info=True;User ID=e1fgatp;Password=e1fgatp02";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(stage, preprod);
s.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
// "DATA_FACTSET"
//"SECTOR",
new DatabaseTable("IDENTIFICATION"),
new DatabaseTable("ASSET")
//"SECTOR_TRANSCO",
//"ASSET_TO_SECTOR"
//"ACT_COEF_CRITERE",
//"ACT_COEF_SECTEUR",
//"ACT_PTF",
//"ISR_NOTE"
//"ACT_AGR_FORMAT"
});
}
public void TestBulkCopyDataCriteria()
{
string dest = @"Data Source=FX026132M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("ConnectionAdmin", dest);
s.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
new DatabaseTable("PTF_RAPPORT")
}, ColumnCriteria: "Date", CriteriaValue: "MAX");
}
public void TestBulkCopyDataBottomLimit()
{
string dest = @"Data Source=FX026132M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("ConnectionAdmin", dest);
s.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
new DatabaseTable(
"PTF_TRANSPARISE")
}, 1000, "dateinventaire");
}
public void TestBulkCopyDataSourceSQLFile()
{
string dest = @"Data Source=FX026132M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("OMEGA", dest);
// Read the file as one string.
System.IO.StreamReader myFile =
new System.IO.StreamReader("SourceRequestOmega.sql");
string request = myFile.ReadToEnd();
myFile.Close();
s.bulkcopySourceRequest(request, new DatabaseTable("STRAT_VALEUR_INDICE"));
}
public void OpenXMLReadValues_PortefeuilleModele()
{
DataSet ds = new DataSet("PTF_MODELE");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
/* using (var reader = new OpenXMLDataReader(new StreamReader(@"Portefeuille modèle.xlsm"), null, true, "modele_€", "J7", "R94",null))
* {
*
* OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(reader);
*/
string model_path1 = @"G:\,FGA Front Office\02_Gestion_Actions\01_MODELES\MODELE GARP\Portefeuille modèle.xlsm";
string model_path2 = @"C:\DATA\Portefeuille modèle_20131125.xlsm";
string model_path3 = @"C:\DATA\Portefeuille modèle.xlsm";
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(model_path1, "modele_€", "J7", "R94", true);
//ITableMapping mapping = adapter.TableMappings.Add("Table", "PTF_FGA");
//mapping.ColumnMappings.Add("CLOSE", "close");
//mapping.ColumnMappings.Add("INDEX", "ponderationIndice");
adapter.AddColumnMapping("PTF_FGA", "COMPANY", "Libelle_Titre");
adapter.AddColumnMapping("PTF_FGA", "QUANTITY", "quantite", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("PTF_FGA", "€CLOSE", "coursclose", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("PTF_FGA", "CRNCY", "Devise_Titre");
adapter.FillColumnWithCell("Dateinventaire", "Q5", System.Type.GetType("System.DateTime"));
adapter.FillColumnWithCell("Libelle_PtfSource", "G3");
adapter.FillColumnWithValue("Groupe", "MODEL_ACT");
adapter.FillColumnWithValue("Compte", "ACT001");
adapter.FillColumnWithValue("ISIN_Ptf", "MODEL_ACT001");
adapter.FillColumnWithValue("Coupon_Couru", 0d);
adapter.FillColumnWithValue("Dateintegration", DateTime.Now);
adapter.Fill(ds);
DataTable dt = ds.Tables["PTF_FGA"];
// conversion de colonne pour mettre dans la BDD
// DataColumn.Expression pour remplir des colonnes
//dt.Columns.Add("quantite", System.Type.GetType("System.Double"), "CONVERT( quantiteSource, System.Double )");
//dt.Columns.Add("coursclose", System.Type.GetType("System.Double"), "CONVERT( courscloseSource, System.Double )");
dt.Columns.Add("Libelle_Ptf", System.Type.GetType("System.String"), "TRIM(Libelle_PtfSource)");
dt.Columns.Add("Valeur_Boursiere", System.Type.GetType("System.Double"), "quantite*coursclose");
dt.Columns.Add("code_Titre", System.Type.GetType("System.String"), "TICKER + ' EQUITY'");
//dt.Columns.Add("Dateinventaire", System.Type.GetType("System.DateTime"), "CONVERT(DateinventaireSource,System.DateTime)");
s.bulkcopyData(dt);
}
public void TestBulkCopyData()
{
//string dest = @"Data Source=FX023179M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
//string dest = @"Data Source=FX007119m\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
string dest = @"Server=tcp:FX026896M,1433; Database=PreProLocal; User Id=super; Password=Password; Connection Timeout = 60";
//SQLCopy.SQLUtils s = new SQLCopy.SQLUtils("ConnectionAdmin", dest);
//string dest = @"Data Source=FX027471M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("FGA_RW", dest);
//s.bulkcopyData(ListeMode.Exclude, new List<DatabaseTable>() {
// new DatabaseTable("PTF_TRANSPARISE"),
// new DatabaseTable("PTF_RAPPORT"),
// new DatabaseTable("PTF_RAPPORT_NIV2"),
// new DatabaseTable("ACT_DATA_FACTSET_AGR"),
// new DatabaseTable("ACT_DATA_FACTSET"),
// new DatabaseTable("TX_IBOXX"),
// new DatabaseTable("TX_IBOXX_RAPPORT_EMETTEUR"),
// new DatabaseTable("TX_IBOXX_RAPPORT_EMETTEUR2"),
// new DatabaseTable("TX_IBOXX_RAPPORT"),
// new DatabaseTable("TX_IBOXX_RAPPORT_PRIME")
// });
//s.bulkcopyData(ListeMode.Include, new List<DatabaseTable>() {
// new DatabaseTable("ACT_RECO_COMMENT"),
// new DatabaseTable("ACT_RECO_SECTOR"),
// new DatabaseTable("ACT_RECO_VALEUR"),
// new DatabaseTable("ACT_FGA_SECTOR_RECOMMANDATION"),
// new DatabaseTable("ACT_ICB_SECTOR_RECOMMANDATION"),
// new DatabaseTable("ACT_RECOMMANDATION"),
// });
//s.bulkcopyData(ListeMode.Include, new List<DatabaseTable>() {
// new DatabaseTable("ACT_AGR_FORMAT"),
// new DatabaseTable("ISR_NOTE"),
// new DatabaseTable("SECTOR"),
// new DatabaseTable("SECTOR_TRANSCO"),
// new DatabaseTable("UTILISATEUR")
s.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
new DatabaseTable("ACT_COEF_SECTEUR"),
new DatabaseTable("ACT_COEF_CRITERE")
});
//s.bulkcopyData(ListeMode.Include, new List<DatabaseTable>() { new DatabaseTable("DATA_FACTSET")
// }, "SELECT * FROM {0}.{1} where date >= '01/11/2014'");
}
public void TestBulkCopyDataSourceRequestSQLFile_OMEGA()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("OMEGA", "FGA_JMOINS1");
string filePath = @"C:\FGA_SOFT\DEVELOPPEMENT\PROJET\FGA_Soft_Front\Front\SQL_SCRIPTS\OMEGA\PTF_FGA.sql";
// Read the file as one string.
System.IO.StreamReader myFile =
new System.IO.StreamReader(filePath);
string request = myFile.ReadToEnd();
myFile.Close();
request = request.Replace("'***'", "'09/01/2014'");
s.bulkcopySourceRequest(request, new DatabaseTable("PTF_FGA"));
}
public void TestBulkCopyData_4DataModel()
{
string source = @"Data Source=FX026132M\SQLExpress;Initial Catalog=FGA_DATAMODEL;Integrated Security=True;Connection Timeout=60";
DBConnectionDelegate s = new MSSQL2005_DBConnection(source);
DBConnectionDelegate d = new MSSQL2005_DBConnection("FGA_JMOINS1");
//DataSet ds = s.GetMetatDataDBScripts();
//// sauvegarde des fichiers de scripts
//TextFile.WriteTo(ds, "C:", "fgadatamodel_db", "sql");
//// on execute les scripts sur la base destination
//DataTable schema = ds.Tables["SCHEMA"];
//d.ExecuteScripts(schema);
//DataTable table = ds.Tables["TABLE"];
//d.ExecuteScripts(table);
//DataTable fk = ds.Tables["FK"];
//d.ExecuteScripts(fk);
//DataTable procstock = ds.Tables["PROCSTOC"];
//d.ExecuteScripts(procstock, useDBcmd: false);
//SQLCopy.SQLUtils s = new SQLCopy.SQLUtils("ConnectionAdmin", dest);
SQLCopy.MSDBIntegration util = new SQLCopy.MSDBIntegration(s, d);
util.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
new DatabaseTable("ASSET_HOLDING"),
new DatabaseTable("COMPONENT"),
new DatabaseTable("INDEX"),
new DatabaseTable("ref_holding", "PORTFOLIO"),
new DatabaseTable("VALUATION"),
new DatabaseTable("ROLE"),
new DatabaseTable("RATING"),
new DatabaseTable("ASSET"),
new DatabaseTable("ASSET_CLASSIFICATION"),
new DatabaseTable("ASSET_PORTFOLIO"),
new DatabaseTable("DEBT"),
new DatabaseTable("EQUITY"),
new DatabaseTable("FUND"),
new DatabaseTable("PRICE"),
new DatabaseTable("SECURITIES_ISSUANCE")
// "IDENTIFICATION"
});
}
public void ACTION_PROCESS_BulkCopy_Import_ACT_PTF_BaseTitreDirects_4()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration("OMEGA", "FGA_RW");
string date = "10/02/2014";
string filePath = @"C:\FGA_SOFT\DEVELOPPEMENT\PROJET\FGA_Soft_Front\Front\SQL_SCRIPTS\AUTOMATE\GESTION_ACTION\FCP_Action_BaseTitresDirects.sql";
// la requete
string request;
if (date != null)
{
request = parameterRequest(filePath, new string[] { "@dateDemandee" }, new string[] { "'" + date + "'" });
}
else
{
request = parameterRequest(filePath, new string[] { }, new string[] { });
}
s.bulkcopySourceRequest(request, new DatabaseTable("ACT_PTF"));
}
public void ACTION_PROCESS_BulkCopy_IMPORT_ISR_3()
{
string filepath2 = @"G:\,FGA ISR\Notation Fédéris\NotationISRbase.xlsx";
string filepath1 = @"G:\,FGA Soft\INPUT\ACTION\ISR\NotationISRbase.xlsx";
DataSet ds = new DataSet("ISR_NOTE");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "ISR", "A2", "F1782", true);
adapter.AddColumnMapping("ISR_NOTE", "Note Actions", "Note Actions", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "Note Credit", "Note Credit", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("ISR_NOTE", "date ", "DATE", System.Type.GetType("System.DateTime"));
adapter.AddColumnMapping("ISR_NOTE", "Name", "NAME");
adapter.Fill(ds);
DataTable dt = ds.Tables["ISR_NOTE"];
s.bulkcopyData(dt);
}
public void LumenCSVReadValues()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_JMOINS1");
List <string> columns = new List <string>();
using (var reader = new CsvReader(new StreamReader(@"PortfolioHolding_PROXY1.csv"), true, ';'))
{
int fieldCount = reader.FieldCount;
string[] headers = reader.GetFieldHeaders();
while (reader.ReadNextRecord())
{
for (int i = 0; i < fieldCount; i++)
{
Console.Write(string.Format("{0} = {1};",
headers[i], reader[i]));
}
Console.WriteLine();
}
}
}
public void ACTION_PROCESS_BulkCopy_FACTSET_MODELE_CLASSIFICATION_2()
{
string date = "10/02/2014";
//string filepath1 = @"C:\FGA_SOFT\DEVELOPPEMENT\PROJET\FGA_Soft_Front\Front\INPUT\ACTION\FACTSET\Modele_Classification.xlsx";
string filepath2 = @"G:\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\Modele_Classification.xlsx";
DataSet ds = new DataSet("DATA_FACTSET");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "Modele Classification", "B1", "V926", true);
adapter.AddColumnMapping("DATA_FACTSET", "MXEU", "MXEU", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXUSLC", "MXUSLC", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEM", "MXEM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXEUM", "MXEUM", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "MXFR", "MXFR", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100001", "6100001", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100004", "6100004", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100030", "6100030", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100033", "6100033", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100063", "6100063", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEURO", "AVEURO", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "AVEUROPE", "AVEUROPE", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100026", "6100026", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100062", "6100062", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100002", "6100002", System.Type.GetType("System.Double"));
adapter.AddColumnMapping("DATA_FACTSET", "6100024", "6100024", System.Type.GetType("System.Double"));
adapter.FillColumnWithValue("DATE", date);
adapter.Fill(ds);
DataTable dt = ds.Tables["DATA_FACTSET"];
s.bulkcopyData(dt);
}
public void TestBulkCopyData_4FACTSET()
{
//string dest = @"Data Source=FX023179M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
//SQLCopy.SQLUtils s = new SQLCopy.SQLUtils("ConnectionAdmin", dest);
string source = @"Data Source=FX027471M\SQLExpress;Initial Catalog=FGA_JMOINS1;Integrated Security=True;Connection Timeout=60";
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(source, "FGA_RW");
//s.bulkcopyData(ListeMode.Include, new List<string>() {
// "ACT_RECO_COMMENT",
// "ACT_RECO_SECTOR",
// "ACT_RECO_VALEUR",
// "ACT_FGA_SECTOR_RECOMMANDATION",
// "ACT_ICB_SECTOR_RECOMMANDATION",
// "ACT_RECOMMANDATION",
// });
s.bulkcopyData(ListeMode.Include, new List <DatabaseTable>()
{
new DatabaseTable("ACT_DATA_FACTSET")
}, "SELECT * FROM {0}.{1} where date = '27/12/2013'");
}
public void ACTION_PROCESS_BulkCopy_FACTSET_TICKER_CONV()
{
string date = "17/01/2014";
string filepath1 = @"G:\,FGA Front Office\02_Gestion_Actions\00_BASE\Base 2.0\TickerConversion.xlsx";
string filepath2 = @"C:\TickerConversion.xlsx";
DataSet ds = new DataSet("ACT_TICKER_CONVERSION");
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_PREPROD_RW");
OpenXMLDataAdapter adapter = new OpenXMLDataAdapter(filepath2, "Feuil1", "B3", "F51", true);
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "ISIN", "ISIN");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "TICKER", "TICKER");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "BBG", "BBG");
adapter.AddColumnMapping("ACT_TICKER_CONVERSION", "EXCH_F", "EXCH_B");
adapter.Fill(ds);
DataTable dt = ds.Tables["ACT_TICKER_CONVERSION"];
s.bulkcopyData(dt);
}
public void LumenCSVReadValuesAndMergeIntoDest()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_JMOINS1");
s.BulkUpsertCsv(@"PortfolioHolding_PROXY1.csv", new DatabaseTable("dbo", "PTF_PROXY"));
}
public void OpenXMLReadValues_PortefeuilleModele2()
{
SQLCopy.MSDBIntegration s = new SQLCopy.MSDBIntegration(destinationConnection: "FGA_JMOINS1");
DataTable excelDataTable = null;
List <string> columns = new List <string>();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(@"C:\DATA\Portefeuille modèle.xlsm", false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetPartByName(spreadsheetDocument, "modele_€");
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
string text;
string rowNum;
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
// SheetData sd = (SheetData)reader.LoadCurrentElement();
reader.ReadFirstChild();
do// while : Skip to the next row
{
if (reader.HasAttributes)
{
rowNum = reader.Attributes.First(a => a.LocalName == "r").Value;
Console.WriteLine("rowNum: " + rowNum);
}
if (reader.ElementType == typeof(Row))
{
reader.ReadFirstChild();
do// while: next Cell
{
if (reader.ElementType == typeof(Cell))
{
Cell c = (Cell)reader.LoadCurrentElement();
string cellValue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
{
SharedStringItem ssi = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(int.Parse(c.CellValue.InnerText));
cellValue = ssi.Text.Text;
}
else if (c.CellValue != null)
{
cellValue = c.CellValue.InnerText;
}
else
{
cellValue = "Empty";
}
Console.WriteLine("{0}: {1} ", c.CellReference, cellValue);
}
} while (reader.ReadNextSibling());// while: next Cell
}
Console.WriteLine("END ROW");
} while (reader.ReadNextSibling());// while : Skip to the next row
Console.WriteLine("END ROW");
}
if (reader.ElementType != typeof(Worksheet))
{
reader.Skip();
}
}
reader.Close();
}
// s.bulkcopyData(dt: excelDataTable, nomTableDestination : "PTF_FGA");
}
