locked
How to use SqlBulkCopy with Entity Framework RRS feed

  • Question

  • User-1664485818 posted

    Hi everyone, how do I use  Entity Framework with SqlBulkCopy,

    Any help much appreciated, my problem is with the second method, not sure how to use Entity Framework with SqlBulkCopy...

    private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
            {
                DataTable csvData = new DataTable();
    
                try
                {
                    using (TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                    {
                        csvReader.SetDelimiters(new string[] { ";" });
                        csvReader.HasFieldsEnclosedInQuotes = true;
                        string[] colFields = csvReader.ReadFields();
    
                        foreach (string column in colFields)
                        {
                            DataColumn datecolumn = new DataColumn(column);
                            datecolumn.AllowDBNull = true;
                            csvData.Columns.Add(datecolumn);
                        }
                        while (!csvReader.EndOfData)
                        {
                            string[] fieldData = csvReader.ReadFields();
    
                            //Making empty value as null
                            for (int i = 0; i < fieldData.Length; i++)
                            {
                                if (fieldData[i] == "")
                                {
                                    fieldData[i] = null;
                                }
                            }
    
                            csvData.Rows.Add(fieldData);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
    
                return csvData;
            }
    
            static void InsertDataIntoSQLServerUsingSQLBulkCopy(DataTable csvFileData)
            {
                using (Entities dbcontext = new Entities())
                {
                    SqlConnection sqlCon = (SqlConnection)dbcontext
    
                    using (SqlBulkCopy s = new SqlBulkCopy(dbcontext))
                    {
                        s.DestinationTableName = dbcontext.tablename;
    
                        foreach (var column in csvFileData.Columns)
    
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
    
                        s.WriteToServer(csvFileData);
                    }
                }
    
            }


     

    Saturday, January 5, 2019 6:40 PM

Answers

  • User283571144 posted

    Hi brucey,

    As far as I know, we couldn't directly convert the dbcontext to sql connection when we want to use SqlBulkCopy with Entity Framework.

    We could only get the connection string from the DbContext's Database.Connection.ConnectionString property and use it works with the SqlBulkCopy.

    More details, you could refer to below codes:

                using (ApplicationDbContext dbcontext = new ApplicationDbContext())
                {
    
    
                    SqlConnection sqlCon = new SqlConnection(dbcontext.Database.Connection.ConnectionString);
                    sqlCon.Open();
                    using (SqlBulkCopy s = new SqlBulkCopy(sqlCon))
                    {
                        //set the table name
                        s.DestinationTableName = "AspNetUsers";
    
                        foreach (var column in d.Columns)
    
                            s.ColumnMappings.Add(column.ToString(), column.ToString());
    
                        s.WriteToServer(d);
                    }
    
                    sqlCon.Close();
    
                }


    But, this also using ado.net concept not EF, if you want to directly using EF to bulk copy, you could try to use below library:

    https://github.com/MikaelEliasson/EntityFramework.Utilities 

    It works well for simple bulk inserts and updates.

    You should also look at the following post if you want to find out about other options to achieve bulk insert:

    https://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework 

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 7, 2019 3:25 AM