SQL Insert not considering blank values for the insert in my C# code-CodePudding

I have a nice piece of C# code which allows me to import data into a table with less columns than in the SQL table (as the file format is consistently bad).

My problem comes when I have a blank entry in a column. The values statement does not pickup an empty column from the csv. And so I receive the error

You have more insert columns than values

Here is the query printed to a message box...

As you can see there is nothing for Crew members 4 to 11, below is the file...

Please see my code:

SqlConnection ADO_DB_Connection = new SqlConnection();
ADO_DB_Connection = (SqlConnection)
(Dts.Connections["ADO_DB_Connection"].AcquireConnection(Dts.Transaction) as SqlConnection);

// Inserting data of file into table
int counter = 0;
string line;
string ColumnList = "";

// MessageBox.Show(fileName);

System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);

while ((line = SourceFile.ReadLine()) != null)
{
    if (counter == 0)
    {
        ColumnList = "["   line.Replace(FileDelimiter, "],[")   "]";
    }
    else
    {
        string query = "Insert into "   TableName   " ("   ColumnList   ") ";
        query  = "VALUES('"   line.Replace(FileDelimiter, "','")   "')";

        // MessageBox.Show(query.ToString());

        SqlCommand myCommand1 = new SqlCommand(query, ADO_DB_Connection);
        myCommand1.ExecuteNonQuery();
    }

    counter  ;
}

If you could advise how to include those fields in the insert that would be great.

Here is the same file but opened with a text editor and not given in picture format...

Date,Flight_Number,Origin,Destination,STD_Local,STA_Local,STD_UTC,STA_UTC,BLOC,AC_Reg,AC_Type,AdultsPAX,ChildrenPAX,InfantsPAX,TotalPAX,AOC,Crew 1,Crew 2,Crew 3,Crew 4,Crew 5,Crew 6,Crew 7,Crew 8,Crew 9,Crew 10,Crew 11
05/11/2022,241,BOG,SCL,15:34,22:47,20:34,02:47,06:13,N726AV,"AIRBUS A-319                  ",0,0,0,36,AV,100612,161910,323227

CodePudding user response：

Not touching the potential for sql injection as I'm free handing this code. If this a system generated file (Mainframe extract, dump from Dynamics or LoB app) the probability for sql injection is awfully low.

// Char required
char FileDelimiterChar = FileDelimiter.ToChar()[0];
int columnCount = 0;
while ((line = SourceFile.ReadLine()) != null)
{
    if (counter == 0)
    {
        ColumnList = "["   line.Replace(FileDelimiterChar, "],[")   "]";
        // How many columns in line 1. Assumes no embedded commas
        // The following assumes FileDelimiter is of type char
        // Add 1 as we will have one fewer delimiters than columns
        columnCount = line.Count(x => x == FileDelimiterChar)  1;
    }
    else
    {
        string query = "Insert into "   TableName   " ("   ColumnList   ") ";
        // HACK: this fails if there are embedded delimiters
        int foundDelimiters = line.Count(x => x == FileDelimiter)  1;
        // at this point, we know how many delimiters we have
        // and how many we should have.
        string csv = line.Replace(FileDelimiterChar, "','");

        // Pad out the current line with empty strings aka ','
        // Note: I may be off by one here
        // Probably a classier linq way of doing this or string.Concat approach
        for (int index = foundDelimiters; index <= columnCount; index  )
        {
             csv  = "','";
        }

        query  = "VALUES('"   csv   "')";

        // MessageBox.Show(query.ToString());

        SqlCommand myCommand1 = new SqlCommand(query, ADO_DB_Connection);
        myCommand1.ExecuteNonQuery();
    }

    counter  ;
}

Something like that should get you a solid shove in the right direction. The concept is that you need to inspect the first line and see how many columns you should have. Then for each line of data, how many columns do you actually have and then stub in the empty string.

If you change this up to use SqlCommand objects and parameters, the approximate logic is still the same. You'll add all the expected parameters by figuring out columns in the first line and then for each line you will add your values and if you have a short row, you just send the empty string (or dbnull or whatever your system expects).

The big take away IMO is that CSV parsing libraries exist for a reason and there are so many cases not addressed in the above psuedocode that you'll likely want to trash the current approach in favor of a standard parsing library and then while you're at it, address the potential security flaws.

I see your updated comment that you'll take the formatting concerns back to the source party. If they can't address them, I would envision your SSIS package being

Script Task -> Data Flow task.

Script Task is going to wrangle the unruly data into a strict CSV dialect that a Data Flow task can handle. Preprocessing the data into a new file instead of trying to modify the existing in place.

The Data Flow then becomes a chip shot of Flat File Source -> OLE DB Destination

CodePudding user response：

Here's how you can process this file... I would still ask for Json or XML though.

You need two outputs set up. Flight Info (the 1st 16 columns) and Flight Crew (a business key [flight number and date maybe] and CrewID).

Seems to me the problem is how the crew is handled in the CSV.

So basic steps are Read the file, use regex to split it, write out first 16 col to output1 and the rest (with key) to flight crew. And skip the header row on your read.

var lines = System.File.IO.ReadAllLines("filepath");
for(int i =1; i<lines.length; i  )
{
    var = new System.Text.RegularExpressions.Regex("new Regex("(?:^|,)(?=[^\"]|(\")?)\"?((?(1)(?:[^\"]|\"\")*|[^,\"]*))\"?(?=,|$)"); //Some code I stole to split quoted CSVs
    var m = r.Matches(line[i]); //Gives you all matches in a MatchCollection
    //first 16 columns are always correct
    OutputBuffer0.AddRow();
    OutputBuffer0.Date = m[0].Groups[2].Value;
    OutputBuffer0.FlightNumber = m[1].Groups[2].Value;
    [And so on until m[15]]
    for(int j=16; j<m.Length; j  )
    {
         OutputBuffer1.AddRow(); //This is a new output that you need to set up
         OutputBuffer1.FlightNumber = m[1].Groups[2].Value;
         [Keep adding to make a business key here]
         OutputBuffer1.CrewID = m[j].Groups[2].Value;
    }
}

Be careful as I just typed all this out to give you a general plan without any testing. For example m[0] might actually be m[0].Value and all of the data types will be strings that will need to be converted.

To check out how regex processes your rows, please visit https://regex101.com/r/y8Ayag/1 for explanation. You can even paste in your row data.

UPDATE: I just tested this and it works now. Needed to escape the regex function. And specify that you wanted the value of group 2. Also needed to hit IO in the File.ReadAllLines.