Сортировка данных FileHelpers в различные столбцы в SQl sever с помощью C#

Я создал код в C#, который читает в файлах с разделителями txt из нескольких папок, анализирует данные и затем обновляет таблицу Microsoft SQL server с правильными значениями. Код показан ниже:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FileHelpers;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace prototype_using_filehelpers
{


    class ManagerReport
    {

        [DelimitedRecord("|")]
        [IgnoreEmptyLines()]
        [IgnoreFirst()]


        public class ManagerReportNames
        {


            public int? MASTER_VALUE_ORDER;
            public int? MASTER_VALUE;
            public string RESORT;
            public int? CS_HEADING_COUNT_MASTER;
            public int? CS_FS_ARR_ROOMS_MASTER;
            public int? CS_FS_DEP_ROOMS_MASTER;
            public int? CS_FS_NO_ROOMS_MASTER;
            public int? CS_FS_GUESTS_MASTER;
            public int? CS_FS_TOTAL_REVENUE_MASTER;
            public int? CS_FS_ROOM_REVENUE_MASTER;
            public int? CS_FS_INVENTORY_ROOMS_MASTER;
            public int? CF_FS_PERC_OCC_ROOMS_MASTER;
            public int? CF_FS_AVG_ROOM_RATE_MASTER;
            public int? LAST_YEAR_01;
            public int? SUB_GRP_1_ORDER;
            public string SUB_GRP_1;
            public string DESCRIPTION;
            public string AMOUNT_FORMAT_TYPE;
            public string PRINT_LINE_AFTER_YN;
            public int? HEADING_1_ORDER;
            public int? HEADING_1;
            [FieldOptional]
            public string HEADING_2;
            [FieldOptional]
            [FieldConverter(ConverterKind.Decimal, ".")]
            public decimal? SUM_AMOUNT;
            [FieldOptional]
            public decimal? FORMATTED_AMOUNT;


        }


        static void ProcessFilesCSVFiles(string copyPath, string destinationPath)
        {


            // first check if path exists
            if (!Directory.Exists(copyPath))
                // doesn't exist then exit, can't copy from something that doesn't exist
                return;
            var copyPathDirectory = new DirectoryInfo(copyPath);
            // using the SearchOption.AllDirectories will search sub directories
            var copyPathCSVFiles = copyPathDirectory.GetFiles("*.txt", SearchOption.AllDirectories);
            // loops through directory looking for txt files
            for (var i = 0; i < copyPathCSVFiles.Length; i++)
            {
                // get the file
                var csvFile = copyPathCSVFiles[i];
                //sets lines to the files full extention so it can then be called at a later time
                string lines = csvFile.FullName;

                // read the csv file line by line
                FileHelperEngine engine = new FileHelperEngine(typeof(ManagerReportNames));
                var records = engine.ReadFile(lines) as ManagerReportNames[];


                foreach (var record in records)
                {
                    Console.WriteLine(record.RESORT);
                    Console.WriteLine(record.FORMATTED_AMOUNT);



              // SQL connection. Creates connection and command and inserts the values taken from the File Helper engine into the SQL table
                    SqlCommand cmd;
                    SqlConnection conn;


                    conn = new SqlConnection("Data Source=hureports01;Initial Catalog=hureports;Integrated Security=True");
                    conn.Open();
                    cmd = new SqlCommand("INSERT INTO test2 (property_id,val1) VALUES (@property_id,@val1)", conn);
                    cmd.Parameters.AddWithValue("@property_id", record.RESORT);
                    cmd.Parameters.AddWithValue("@val1", record.FORMATTED_AMOUNT);
                    cmd.ExecuteNonQuery();
                }



                // creates a variable that combines the the directory of the new folder with the file name
                var destinationFilePath = Path.Combine(destinationPath, csvFile.Name);
                // This loop prevents duplicates. If a file is already in the folder, it will delete the file already in there and move this one in.
                // Shouldn't be an issue since each file will have a different name
                if (File.Exists(destinationFilePath))
                {
                    File.Delete(destinationFilePath);
                }
                // moves it to the new folder
                csvFile.MoveTo(destinationFilePath);


            }
        }


        static void Main(string[] args)
        {

            ProcessFilesCSVFiles(@"C:UsersDocuments", @"C:UsersDocumentsComplete");


        }
    }
}

Код работает, но я пытаюсь реализовать новую функцию, и я не уверен, как. В настоящее время он обновляет SQL server с двумя значениями: RESORT и FORMATTED_AMOUNT. RESORT входит в столбец таблицы «property_id», а FORMATTED_AMOUNT-в val1. В моей таблице я также добавил val2, val3 и val4. HEADING_2 может иметь три различных значения или быть null. Как бы я исправил мой код так, что когда HEADING_2 является первым значением, соответствующий FORMATTED_AMOUNT перейдет в val1 и когда HEADING_2 является вторым значением, соответствующий FORMATTED_AMOUNT перейдет в v2 и когда HEADING_2 является третьим значением, соответствующий FORMATTED_AMOUNT перейдет в val3 и, наконец, когда HEADING_2 равен null, соответствующий FORMATTED_AMOUNT перейдет в val4

текущий код:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FileHelpers;
using System.Data.SqlClient;
using System.IO;
using System.Data;

namespace prototype_using_filehelpers
{

    class ManagerReport
    {

        [DelimitedRecord("|")]
        [IgnoreEmptyLines()]
        [IgnoreFirst()]
        [IgnoreLast(2)]


        public class ManagerReportNames
        {


            public int? MASTER_VALUE_ORDER;
            public int? MASTER_VALUE;
            public string RESORT;
            public int? CS_HEADING_COUNT_MASTER;
            public int? CS_FS_ARR_ROOMS_MASTER;
            public int? CS_FS_DEP_ROOMS_MASTER;
            public int? CS_FS_NO_ROOMS_MASTER;
            public int? CS_FS_GUESTS_MASTER;
            public int? CS_FS_TOTAL_REVENUE_MASTER;
            public int? CS_FS_ROOM_REVENUE_MASTER;
            public int? CS_FS_INVENTORY_ROOMS_MASTER;
            public int? CF_FS_PERC_OCC_ROOMS_MASTER;
            public int? CF_FS_AVG_ROOM_RATE_MASTER;
            public int? LAST_YEAR_01;
            public int? SUB_GRP_1_ORDER;
            public string SUB_GRP_1;
            public string DESCRIPTION;
            public string AMOUNT_FORMAT_TYPE;
            public string PRINT_LINE_AFTER_YN;
            public int? HEADING_1_ORDER;
            public int? HEADING_1;
            [FieldOptional]
            public string HEADING_2;
            [FieldOptional]
            [FieldConverter(ConverterKind.Decimal, ".")]
            public decimal? SUM_AMOUNT;
            [FieldOptional]
            public decimal? FORMATTED_AMOUNT;


        }

        static void ProcessFilesCSVFiles(string originalPath, string destinationPath)
        {


            // first check if path exists
            if (!Directory.Exists(originalPath))
                // doesn't exist then exit, can't copy from something that doesn't exist
                return;
            var copyPathDirectory = new DirectoryInfo(originalPath);
            // using the SearchOption.AllDirectories will search sub directories
            var copyPathCSVFiles = copyPathDirectory.GetFiles("*.txt", SearchOption.AllDirectories);
            // loops through directory looking for txt files
            for (var i = 0; i < copyPathCSVFiles.Length; i++)
            {
                // get the file
                var csvFile = copyPathCSVFiles[i];
                //sets lines to the files full extention so it can then be called at a later time
                string lines = csvFile.FullName;

                // read the csv file line by line
                FileHelperEngine engine = new FileHelperEngine(typeof(ManagerReportNames));
                var records = engine.ReadFile(lines) as ManagerReportNames[];


                foreach (var record in records)
                {
                    Console.WriteLine(record.RESORT);
                    Console.WriteLine(record.FORMATTED_AMOUNT);
                    // This allows us to split what column a row goes into based on whether it is a day month or year
                    string Heading = record.HEADING_2;
                    string Group = record.SUB_GRP_1;
                    string column;
                    if (Heading == "DAY" && Group == "OCC_PERC")
                    {
                        column = "Percent_Rooms_Occupied";
                    }
                    else if (Heading == "DAY" && Group == "OCC_PERC_WO_CH")
                    {
                        column = "Percent_Rooms_Occupied_minus_Comp_and_House";
                    }
                    else if (Heading == "DAY" && Group == "ADR_ROOM")
                    {
                        column = "ADR";
                    }
                    else if (Heading == "DAY" && Group == "ADR_ROOM_WO_CH")
                    {
                        column = "ADR_minus_Comp_and_House";
                    }
                    else if (Heading == "DAY" && Group == "ROOMREV_AVL_ROOMS_MINUS_OOO")
                    {
                        column = "Revenue_per_Available_Room_minus_OOO";
                    }
                    else if (Heading == "DAY" && Group == "TOTAL_REVENUE")
                    {
                        column = "Total_Revenue";
                    }
                    else if (Heading == "DAY" && Group == "ROOM_REVENUE")
                    {
                        column = "Room_Revenue";
                    }
                    else if (Heading == "DAY" && Group == "FOOD_BEV_REVENUE")
                    {
                        column = "Food_And_Beverage_Revenue";
                    }
                    else if (Heading == "DAY" && Group == "OTHER_REVENUE")
                    {
                        column = "Other_Revenue";

                    }
                    else if (Heading == "DAY" && Group == "PHYSICAL_ROOMS")
                    {
                        column = "Total_Rooms_in_Hotel";

                    }
                    else if (Heading == "DAY" && Group == "OCC_ROOMS")
                    {
                        column = "Rooms_Occupied";

                    }
                    else if (Heading == "DAY" && Group == "OCC_MINUS_COMP_HU")
                    {
                        column = "Rooms_Occupied_minus_Comp_and_House_Use";

                    }
                    else if (Heading == "DAY" && Group == "COMP_ROOMS")
                    {
                        column = "Complimentary_Rooms";

                    }
                    else
                    {
                        continue;
                    }



                    // SQL connection. Creates connection and command and inserts the values taken from the File Helper engine into the SQL table
                    SqlCommand cmd;
                    SqlConnection conn;


                    conn = new SqlConnection("Data Source=hureports01;Initial Catalog=hureports;Integrated Security=True");
                    conn.Open();

                    //var sqlCommand2 = string.Format(@"IF EXISTS (SELECT * FROM[HEWreport] WHERE Property_ID = @Property_ID) INSERT INTO[HEWreport] (Property_ID, < ColumnName >) VALUES(@Property_ID, @val");
                    var sqlCommand = string.Format(@"MERGE [HEWreport] AS target USING (select @Property_ID as Property_ID, @val as {0}) AS source ON (target.Property_ID = source.Property_ID) WHEN MATCHED THEN UPDATE SET {0}= source.{0}
                                                    WHEN NOT MATCHED THEN INSERT (Property_ID, {0}) VALUES (source.Property_ID, source.{0});", column);
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.AddWithValue("@Property_ID", record.RESORT);
                    cmd.Parameters.AddWithValue("@val", record.FORMATTED_AMOUNT);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }




                // creates a variable that combines the the directory of the new folder with the file name
                var destinationFilePath = Path.Combine(destinationPath, csvFile.Name);
                // This loop prevents duplicates. If a file is already in the folder, it will delete the file already in there and move this one in.
                // Shouldn't be an issue since each file will have a different name
                if (File.Exists(destinationFilePath))
                {
                    File.Delete(destinationFilePath);
                }
                // moves it to the new folder
                csvFile.MoveTo(destinationFilePath);


            }
        }


        static void Main(string[] args)
        {

            ProcessFilesCSVFiles(@"C:UsersDocumentsHotels", @"C:UsersDocumentsCompleted Hotels");


        }
    }
}

Как вы можете видеть, я изменил именно то, что я хочу сделать, но это та же самая концепция. Вместо столбцов val1 или val2 они определены, мне также нужно было больше, чем было в HEADING_2, мне также нужно было SUB_GRP_1. Я также попытался использовать параметры, так как мне также сказали, что AddWithValue() может вызвать проблемы, но это все еще не работает

1 ответ

  1. Предположим, у вас есть функция, которая сопоставляет HEADING_2 с именем столбца, как это

    var columnName = GetColumnNameFromHEADING_2(HEADING_2_value);

    код можно изменить следующим образом:

    var sqlCommand = string.Format("INSERT INTO test2 (property_id, {0}) VALUES (@property_id,@val)", columnName);
    cmd = new SqlCommand(sqlCommand, conn);
    cmd.Parameters.AddWithValue("@property_id", record.RESORT);
    cmd.Parameters.AddWithValue("@val", record.FORMATTED_AMOUNT);
    

    Если вы хотите объединить строки, как описано в комментарии, используйте инструкцию merge:

        var sqlCommand = string.Format(@"
      MERGE [test2] AS target
                    USING (select @property_id as property_id, @val as {0}) AS source
                    ON (target.property_id = source.property_id)
                    WHEN MATCHED THEN
                    UPDATE
                    SET {0}= source.{0}
                    WHEN NOT MATCHED THEN
                    INSERT (property_id, {0}) 
                    VALUES (source.property_id, source.{0});
    ", columnName);