Example #1
0
        /// <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);
            }
        }
Example #6
0
        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));
            }
        }