I’m currently using the ASP.NET framework to build a website that can send information to a MySQL database hosted on PHPMyAdmin. The data being read into the database comes from a CSV file that the user uploads to the website. So far, the following code has worked successfully on smaller sample files (variable names changed and sensitive information hidden):
string connectionString = "Server=****;Database=****;Uid=****;Pwd=****";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
using (MySqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
// clear the table before inserting new data:
command.CommandText = "DELETE FROM CustomerData";
command.ExecuteNonQuery();
// read the CSV file and insert data into the table:
using (StreamReader reader = new StreamReader(fileName))
{
string line;
bool isFirstLine = true;
while ((line = reader.ReadLine()) != null)
{
if (isFirstLine)
{
isFirstLine = false;
continue; // skip the first line
}
string[] values = line.Split(',');
// prepare the SQL insert statement:
// prepare the SQL insert statement:
command.CommandText = "INSERT INTO CustomerData (Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8, Data9, Data10, Data11, Data12, Data13, Data14, Data15, Data16, Data17, Data18, Data19, Data20, Data21, Data22, Data23, Data24, Data25, Data26) " +
"VALUES (@Data1, @Data2, @Data3, @Data4, @Data5, @Data6, @Data7, @Data8, @Data9, @Data10, @Data11, @Data12, @Data13, @Data14, @Data15, @Data16, @Data17, @Data18, @Data19, @Data20, @Data21, @Data22, @Data23, @Data24, @Data25, @Data26)";
// set parameter values, accounting for possible nulls:
command.Parameters.Clear();
command.Parameters.AddWithValue("@Data1", string.IsNullOrEmpty(values[0]) ? DBNull.Value : values[0]);
command.Parameters.AddWithValue("@Data2", string.IsNullOrEmpty(values[1]) ? DBNull.Value : (object)values[1]);
command.Parameters.AddWithValue("@Data3", string.IsNullOrEmpty(values[2]) ? DBNull.Value : (object)values[2]);
command.Parameters.AddWithValue("@Data4", string.IsNullOrEmpty(values[3]) ? DBNull.Value : (object)values[3]);
command.Parameters.AddWithValue("@Data5", string.IsNullOrEmpty(values[4]) ? DBNull.Value : (object)values[4]);
command.Parameters.AddWithValue("@Data6", string.IsNullOrEmpty(values[5]) ? DBNull.Value : (object)values[5]);
command.Parameters.AddWithValue("@Data7", string.IsNullOrEmpty(values[6]) ? DBNull.Value : (object)values[6]);
command.Parameters.AddWithValue("@Data8", string.IsNullOrEmpty(values[7]) ? DBNull.Value : (object)values[7]);
command.Parameters.AddWithValue("@Data9", string.IsNullOrEmpty(values[8]) ? DBNull.Value : (object)values[8]);
command.Parameters.AddWithValue("@Data10", string.IsNullOrEmpty(values[9]) ? DBNull.Value : (object)values[9]);
command.Parameters.AddWithValue("@Data11", string.IsNullOrEmpty(values[10]) ? DBNull.Value : (object)values[10]);
command.Parameters.AddWithValue("@Data12", string.IsNullOrEmpty(values[11]) ? DBNull.Value : (object)values[11]);
command.Parameters.AddWithValue("@Data13", string.IsNullOrEmpty(values[12]) ? DBNull.Value : (object)values[12]);
command.Parameters.AddWithValue("@Data14", string.IsNullOrEmpty(values[13]) ? DBNull.Value : (object)values[13]);
command.Parameters.AddWithValue("@Data15", string.IsNullOrEmpty(values[14]) ? DBNull.Value : (object)values[14]);
command.Parameters.AddWithValue("@Data16", string.IsNullOrEmpty(values[15]) ? DBNull.Value : (object)values[15]);
command.Parameters.AddWithValue("@Data17", string.IsNullOrEmpty(values[16]) ? DBNull.Value : (object)values[16]);
command.Parameters.AddWithValue("@Data18", string.IsNullOrEmpty(values[17]) ? DBNull.Value : (object)values[17]);
command.Parameters.AddWithValue("@Data19", string.IsNullOrEmpty(values[18]) ? DBNull.Value : (object)values[18]);
command.Parameters.AddWithValue("@Data20", string.IsNullOrEmpty(values[19]) ? DBNull.Value : (object)values[19]);
command.Parameters.AddWithValue("@Data21", string.IsNullOrEmpty(values[20]) ? DBNull.Value : (object)values[20]);
command.Parameters.AddWithValue("@Data22", string.IsNullOrEmpty(values[21]) ? DBNull.Value : (object)values[21]);
command.Parameters.AddWithValue("@Data23", string.IsNullOrEmpty(values[22]) ? DBNull.Value : (object)values[22]);
command.Parameters.AddWithValue("@Data24", string.IsNullOrEmpty(values[23]) ? DBNull.Value : (object)values[23]);
command.Parameters.AddWithValue("@Data25", string.IsNullOrEmpty(values[24]) ? DBNull.Value : (object)values[24]);
command.Parameters.AddWithValue("@Data26", string.IsNullOrEmpty(values[25]) ? DBNull.Value : (object)values[25]);
// execute the insert statement:
command.ExecuteNonQuery();
}
}
}
}
As stated, this code 100% worked when I was trying to upload smaller CSV files to the MySQL database. However, when I began trying to upload CSV files that have upwards of 25,000 lines, I would receive the following error:
System.IndexOutOfRangeException: Index was outside the bounds of the
array.
And it was occurring on the line with my connection string:
string connectionString = "Server=****;Database=****;Uid=****;Pwd=****";
Suffice it to say that this error has left me quite baffled. I’m not even sure how that specific error could be occurring on the line in question. I realize it must have something to do with the way the MySQL database is receiving the information from the CSV file, but I don’t know how to go about resolving this. Any help/advice is greatly appreciated!
2
Answers
I believe the error happened because one of your lines contains less than 26 elements:
That’s why somewhere around
values[25]
you encounter anIndexOutOfRangeException
.It is recommended to check the size of an array before accessing its elements by index.
I suggest that the best way to detect the error is debugging the application. For example if you are in Windows is usual that CSV files have an ENTER in the last line.
For example:
CSV:
when you read your CSV = SPLIT(‘,’)
And when you will read the second row C# throw the error:
System.IndexOutOfRangeException: Index was outside the bounds of the array.