string.Split ignores Null values between delimiter

Question

I'm trying to convert some data into sql statements with the use of Streamreader and Streamwriter.

My problem is, when i split lines which in which between 2 delimiters is nothing, not even a space, they get ignored and i get a IndexOutOfRange error

because my temparray only goes till temparray[3] , but it should go to like temparray[6] ..

How can i split and use Null values or replace those null values with a simple char, so that i dont get an IndexOutOfRange error when i want to create my sql statements ?

foreach (string a in values)
{
    int temp = 1;
    String[] temparray = a.Split(';');
    streamWriter.WriteLine("Insert into table Firma values({0},'{1}','{2}')", temp, temparray[1], temparray[4]);
    temp++;
}

Answer 1

Perhaps use the ?? operator:

streamWriter.WriteLine(
    "Insert into table Firma values({0},'{1}','{2}')",
    temp,
    temparray[1] ?? 'x',
    temparray[4] ?? 'x');

This still is only safe, though, if you know for sure that your input has at least 5 tokens after splitting. If you can't guarantee this you'll need to wrap it in a conditional:

if (temparray.Length < 5)
{
    // handle invalid input
}

Answer 2

Why don't you iterate over your temparray to build up a string of param values.

This is by no means perfect, but should point you in the direction

    foreach (string a in values)
    {
        int temp = 1;
        String[] temparray = a.Split(';');

        var stringBuilder = new StringBuilder();

        foreach (var s in temparray)
            stringBuilder.Append("'" + s + "',");

        var insertStatement = string.Format("Insert into table Firma values({0}, {1})", temp, stringBuilder.ToString().TrimEnd(','));

        temp++;
    }

Answer 3

If you print out your string, I'm pretty sure it will not be what you expect it to be.

static void Main(string[] args)
{
  var result = "Überarbeitung der SAV Seite;b.i.b.;;;;PB;".Split(';');

  foreach (var part in result)
  {
    Console.WriteLine(" --> " + part);
  }

  Console.ReadLine();
}

This works great. It will not ignore the empty values. It will print

 --> Überarbeitung der SAV Seite
 --> b.i.b.
 -->
 -->
 -->
 --> PB
 -->

including the empty values.

Greetings to bib Paderborn :)

Answer 4

I've tried to reproduce with your example of string "Überarbeitung der SAV Seite;b.i.b.;;;;PB;" but everything was fine. I've got 7 items in array. You can try to use

string s = "Überarbeitung der SAV Seite;b.i.b.;;;;PB;";
var result = s.Split(new[] { ';' }, StringSplitOptions.None);

To be sure that StringSplitOptions.RemoveEmptyEntries is not enabled.

Answer 5

If you're using SQL Server, you can return empty strings instead of null by using the ISNULL operator in your query.

For example:

SELECT ISNULL(PR_Arbeitstitel, '') FROM Table

Answer 6

Why would you have 2 delimiters in a row with nothing inbetween them? Do you not control the input?

In that case you could control it by inserting a so-called sentinel value, such as "IGNOREMEPLEASE":

String[] temparray = a.Replace(";;", ";IGNOREMEPLEASE;").Split(';');

Then the rest of your code knows that IGNOREMEPLEASE means there was an empty line, and it should be ignored.

That being said, be very careful about what you send to a database, and scrub incoming data that you use to build SQL statements with, to get rid of anything dangerous.