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"); }