Index was outside the bounds of the array exception

Question

Here is my code to get data from a flat file and insert into SQL Server. It is generating an exception (Index was outside the bounds of the array).

string path = string.Concat(Server.MapPath("~/TempFiles/"), Fileupload1.FileName);                       
string text = System.IO.File.ReadAllText(path);               
string[] lines = text.Split(' ');                                  
con.Open();                 
SqlCommand cmd = new SqlCommand();                 
string[] Values = new string[3];                                 
foreach (string line1 in lines)                 
{                     
    Values = line1.Split(';');                                           
    string query = "INSERT INTO demooo VALUES ('" + Values[0] + "','" + Values[1] + "','" + Values[2] + "')";                     
    cmd = new SqlCommand(query,con);                     
    cmd.ExecuteNonQuery();                  
} 

Answer 1

The exception happens because one of your lines has less than three elements separated with a semicolon. Even though you declare Values as a String array of three elements, affecting the variable to the result of String.Split() function makes that irrelevant: your array will have whatever length the returned array has. If it's less, your code will definitely fail.

If it's not supposed to happen I suggest you do an assertion in your code to help you debugging:

// ...
Values = line1.Split(';');
// the following will make the debugger stop execution if line.Length is smaller than 3
Debug.Assert(line1.Length >= 3);
// ...

As a side note, I should mention making a batch INSERT would be far more efficient. Also your way of declaring and reaffecting cmd variable isn't quite correct. And finally you should call String.Replace on your values to make sure any apostrophes is doubled. Otherwise your code will be open for SQL injection attacks.

Answer 2

Some details on how your code is behaving at run-time:

// This line declares a variable named Values and sets its value to 
// a new array of strings. However, this new array is never used 
// because the loop overwrites Values with a new array before doing 
// anything else with it.
string[] Values = new string[3];                                 
foreach (string line1 in lines)                 
{                     
    Values = line1.Split(';');        
// At this point in the code, whatever was previously stored in Values has been
// tossed on the garbage heap, and Values now contains a brand new array containing
// the results of splitting line1 on semicolons.
// That means that it is no longer safe to assume how many elements the Values array has.
// For example, if line1 is blank (which often happens at the end of a text file), then
// Values will be an empty array, and trying to get anything out of it will throw an
// exception                                   
    string query = "INSERT INTO demooo VALUES ('" + Values[0] + "','" + Values[1] + "','" + Values[2] + "')";                     
    cmd = new SqlCommand(query,con);                     
    cmd.ExecuteNonQuery();                  
} 

Similar to how Values keeps getting overwritten, that SqlCommand that's created outside the loop will also never get used. It's safe to put both of these declarations inside the loop instead. The following code does that, and also adds some error checking to make sure that a usable number of values was retrieved from the line. It will simply skip any lines that aren't long enough - if that's not OK then you might need to create some more complex error-handling code of your own.

foreach(string line in lines) 
{
    string[] values = line.split[';'];
    if(values.Length >= 3)
    {
        string query = "INSERT INTO demooo VALUES ('" + Values[0] + "','" + Values[1] + "','" + Values[2] + "')";      
        using (SqlCommand command = new SqlCommand(query, con))
        {
            cmd.ExecuteNonQuery();
        }
    }
}

As one final note, the above code might be vulnerable to hackers if you were using it in something like a Web application. Think about what command might get sent to the server if you were processing a file that looked like this:

1;2;3
4;5;6
7;8;9') DROP TABLE demooo SELECT DATALENGTH('1    

A safer option is to use parameterized queries, which will help protect against this kind of attack. They do this by separating the command from its arguments, which helps protect you against passing in values for arguments that look like SQL code. An example of how to set things up that way would look more like this:

string query = "INSERT INTO demooo VALUES (@val1, @val2, @val3);
using (var command = new SqlCommand(query, con))
{
    command.Parameters.AddWithValue("@val1", Values[0]);
    command.Parameters.AddWithValue("@val2", Values[1]);
    command.Parameters.AddWithValue("@val3", Values[2]);
    command.ExecuteNonQuery();
}