How to import values from csv to SqlServer using c#

I have come across this problem.I am trying to import data into a database from a csv file.Normally the insert part works,but now I keep getting an error:

Arithmetic overflow error converting int to data type numeric

. The statement has been terminated.It is probably because I have id as the first property,but if I “shift” the array by omitting the id I get the error again,but different:, “Index was outside the bounds of the array.“

Student class is simple id,firstName,lastName,salary values from csv file:

public void Import()
{
    var lineNumber = 0;           //method for connection
    using (SqlConnection conn = DatabaseSingleton.GetInstance())
    {
        //conn.Open();  //In my project there is my string path to csv file
        using (StreamReader reader = new StreamReader(myStringPath))
        {
            while (!reader.EndOfStream)
            {
                var line = reader.ReadLine();
                if (lineNumber != 0)
                {
                var values = line.Split(',');
                            using (command = new SqlCommand("INSERT INTO uzivatel_Barabas VALUES (@id ,@first_Name, @last_Name,@salary) ", conn)) {
                                command.Parameters.Add(new SqlParameter("@id", Convert.ToInt32(values[0].ToString())));
                                command.Parameters.Add(new SqlParameter("@first_Name", values[1].ToString()));
                                command.Parameters.Add(new SqlParameter("@last_Name",values[2].ToString()));
                                command.Parameters.Add(new SqlParameter("@salary", Convert.ToDouble(values[3].ToString())));
                                //var sql = "INSERT INTO uzivatel_Barabas VALUES (@first_Name,@last_Name,@salary)";
                                command.Connection = conn;
                            command.ExecuteNonQuery();
                }
                lineNumber++;
            }
        }
        conn.Close();
    }
    Console.WriteLine("Products Import Complete");
    Console.ReadLine();
}

Answer

As was mentioned in comments, you should use a library to parse your CSV. String.Split doesn’t handle the edge cases that can exist in a CSV file: such as quoted fields with delimiters and new lines.

I maintain a library, Sylvan.Data.Csv, that makes it quite easy to bulk load CSV data into a SQL database. The key is that it allows using SqlBulkCopy instead of using an insert SqlCommand in a loop, which ends up being quite a bit faster. SqlBulkCopy requires that the incoming column types match the types of the target table. My library allows applying a schema to the CSV data to enable this scenario.

Assuming the columns of your CSV file map directly onto the target SQL table it can be done in very few lines of code.

using SqlConnection conn = ...;

// Read the schema for the target table
using var cmd = conn.CreateCommand();
cmd.CommandText = "select top 0 * from uzivatel_Barabas";
using var reader = cmd.ExecuteReader();
var tableSchema = reader.GetColumnSchema();

// apply the schema of the target SQL table to the CSV data reader
// this is require for the SqlBulkCopy to process the data as the
// correct type without manual conversions.
var options = 
    new CsvDataReaderOptions { 
        Schema = new CsvSchema(tableSchema)
    };

using var csv = CsvDataReader.Create("uzivatel_Barabas.csv", options);

using var bcp = new SqlBulkCopy(conn);
bcp.DestinationTableName = "uzivatel_Barabas";
bcp.WriteToServer(csv);

Sylvan.Data.Csv is also the fastest CSV parser for .NET, so this should be the absolute fastest way to import your data into SqlServer.