INSERT values from a list to database

Question

i want to parse a CSV file, store it in a list then insert values from that list to a database . Here is the sample of my code. I'm still learning so if it is not clear then i will explain further. But the idea is to parse the csv file, row by row, and then insert each row in my database. Thank you in advance.

public class SKU : List<string[]>
{
    public string SKU_ID { get; set; }
    public string SKU_Name { get; set; }
    public string Code { get; set; }
    public string Product_Name { get; set; }
    public string DistributionCenter_Name { get; set; }

    internal static SKU ParseRow(string row)
    {
        var columns = row.Split(';');
        return new SKU()
        {
            SKU_ID = columns[0],
            SKU_Name = columns[1],
            Code = columns[2],
            Product_Name = columns[3],
            DistributionCenter_Name = columns[4],
        };
    }
}

In the script i named each column like in the csv file and in my database.

My main is as following

class Programm
{
      static void Main(string[] args)
      {
          var sku_info = ProcessCSV("skutest1.csv");                
          SqlConnection conn = new SqlConnection();    
          conn.ConnectionString = @"...";
          foreach (var information in sku_info)
          {    
              using SqlConnection connection = new SqlConnection(conn.ConnectionString);

              string commandString = ("INSERT INTO SKU VALUES ('" + information.SKU_ID + " "+information.SKU_Name+" "+information.Code+" "+information.Product_Name+" "+information.DistributionCenter_Name+"')");

              conn.Open();
              SqlTransaction transaction = conn.BeginTransaction();    
              SqlCommand cmd = new SqlCommand(commandString, conn, transaction);
              cmd.ExecuteNonQuery();   
              transaction.Commit();
          }

          Console.ReadKey();
      }

      private static List<SKU> ProcessCSV(string path)
      {
          return File.ReadAllLines("C:/.../skutest1.csv").Where(row => row.Length > 0).Select(SKU.ParseRow).ToList();
      }
}

Your `commandString` building is **very dangerous**. Please read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . — mjwills, Jul 22 '20 at 13:08
So what's your question? If I'd had to guess it's about the error you get from the `INSERT` statement because you're using string concatenation (improperly), rather than parameters, but I shouldn't have to guess. — Jeroen Mostert, Jul 22 '20 at 13:08
I'd recommend reading https://stackoverflow.com/a/17150843/34092 . — mjwills, Jul 22 '20 at 13:08
Your insert would look like this: `INSERT INTO SKU VALUES ('blahblahblahblah')` — LarsTech, Jul 22 '20 at 13:13
**Always use parameterized sql and avoid string concatenation** to add values to sql statements. This mitigates SQL Injection vulnerabilities and ensures values are passed to the statement correctly. See [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204), and [Exploits of a Mom](https://xkcd.com/327/). — Igor, Jul 22 '20 at 13:16
[Back to basics: SQL Injection](https://zoharpeled.wordpress.com/2020/07/16/back-to-basics-sql-injection/) — Zohar Peled, Jul 22 '20 at 13:21
@JeroenMostert sorry for the unclear explanation, once again i'm new..sorry for the trouble. From what i see when we do insert queries, we refer the column name of the database table and my question is : how my database can understand the query i try to sent to her through the script? Thank you for your answer, i will investigate the parameters. — Hugo Volpi, Jul 22 '20 at 13:24
@Igor thank you a lot ! i still lack on the syntax knowledge and the good ways of coding, your link will be of a great help ! — Hugo Volpi, Jul 22 '20 at 13:32
@HugoVolpi you could use CsvHelper and SqlBulkCopy in combination to insert the data in bulk, in the fastest way possible (short of using SQL Server's `bcp` or `BULK INSERT`) — Panagiotis Kanavos, Jul 22 '20 at 13:47
@PanagiotisKanavos thank you the information ! i will check it — Hugo Volpi, Jul 22 '20 at 13:51

score 1 · Answer 1 · answered Jul 22 '20 at 13:58

The fastest way to load a file on the server would be to use BULK INSERT, eg :

BULK INSERT someTable
FROM 'pathtofile`
WITH ( FORMAT = 'CSV' )

You can do something similar by using ADO.NET's SqlBulkCopy class to execute a BULK INSERT operation with data sent from the client. SqlBulkCopy expects either a DataTable or IDbDataReader parameter. You can use CsvHelper' CsvDataReader for this. The CsvDataReader parses a CSV file and produces the IDbDataReader interface needed by SqlBulkCopy.

The code could be as simple as this :

using var txtReader = File.OpenText(pathToCSV);

var reader = new CsvReader(txtReader,CultureInfo.InvariantCulture);
using var dbReader = new CsvDataReader(reader);

var bcp = new SqlBulkCopy(cns);
bcp.ColumnMappings.Add("sku_id","sku_id");
bcp.ColumnMappings.Add("sku_name","sku_name");
...

bcp.DestinationTableName = table;
bcp.WriteToServer(dbReader);

Without the mappings SqlBulkCopy will send the columns in the order they appear in the file. If that doesn't match the order of the table columns, you'll get an error or worse, get mixed up data

Oh i see, i encoutered BULK COPY once on SQL but i didn't knew that there were a class in ADO.NET , thank you ! i'll try it — Hugo Volpi, Jul 22 '20 at 14:03

score 0 · Accepted Answer · answered Jul 22 '20 at 13:42

0

Here's a short tutorial on building a parameterized commandstring - this is a much safer way of inserting to your database.

Here's a sample of how you could parameterize your insert:

string commandString = (@"INSERT INTO SKU VALUES (@sku_id, @sku_name, @code, @product_name, @distributioncenter_name");

conn.Open();
SqlTransaction transaction = conn.BeginTransaction();

SqlCommand cmd = new SqlCommand(commandString, conn, transaction);

cmd.Parameters.Add(new SqlParameter("@sku_id", information.SKU_ID));
cmd.Parameters.Add(new SqlParameter("@sku_name", information.SKU_Name));
cmd.Parameters.Add(new SqlParameter("@code", information.Code));
cmd.Parameters.Add(new SqlParameter("@product_name", information.Product_Name));
cmd.Parameters.Add(new SqlParameter("@distributioncenter_name", information.DistributionCenter_Name));

cmd.ExecuteNonQuery();

transaction.Commit();

answered Jul 22 '20 at 13:42

TheLeb

144
1
11

@hugo-volpi Thanks. If this solution works for you please click the gray checkmark to the left of my answer. This marks the answer as "accepted" by you and rewards me with some reputation. – TheLeb Jul 22 '20 at 13:46
sure ! i will try it – Hugo Volpi Jul 22 '20 at 13:49
@HugoVolpi - This is the proper use of parameters. My only suggested changes would be to: 1) Wrap your types that implement IDisposable in `using` blocks (SqlTransaction, SqlCommand, and SqlConnection from your code not shown here). Do supply the `SqlParameter` constructor with the `size` argument where applicable and keep that constant based on the length of the column in the schema. Include the column names in the `INSERT` statement to ensure the code does not break if you re-order the columns in the schema. – Igor Jul 22 '20 at 13:59
^-- *continued* A transaction is not needed unless you want to fail all the inserts in which case move it to outside of the `foreach` loop and commit after the loop finishes executing. – Igor Jul 22 '20 at 14:00
@HugoVolpi Please click https://imgur.com/a/SrYT2MF – TheLeb Jul 22 '20 at 14:11

INSERT values from a list to database

2 Answers2