How To Convert List Into DataTable For SQlBulkCopy

c# sql sqlbulkcopy

Question

I have an object class which I cobvert into a List. I would like to convert the class into a list, so I can do a SQLBulkCopy, as my current method takes long to write data intop SQL database table.

class ExtractedInfo
{
    public string Date { get; set; }
    public string Client { get; set; }
    public string Path { get; set; }
}

List<ExtractedInfo> extractedList = new List<ExtractedInfo>(); 

try
            {

            Console.WriteLine("Writing to DB");

            using (SqlConnection conn = new SqlConnection(connectionStringPMT))
            {
                conn.Open();

                SqlCommand cmd =
       new SqlCommand(
       "IF NOT EXISTS (SELECT 1 FROM [FileTrckT] WHERE Path = @Path) " +
       "INSERT INTO [FileTrckT] (Date, Client, Path, DateAddedToDb, FileName) " +  // dont forget to add "DateAddedToDb" on live code
       "VALUES (@Date, @Client, @Path, @DateAddedToDb, @FileName)");// dont forget to add " @DateAddedToDb" on live code
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                cmd.Parameters.Add("@Date", DbType.DateTime);
                cmd.Parameters.Add("@Client", DbType.String);
                cmd.Parameters.Add("@Path", DbType.String);
                cmd.Parameters.Add(@"DateAddedToDb",DbType.DateTime); //uncomment on live code 
                cmd.Parameters.Add("@FileName", DbType.String);

                foreach (var extractedRecord in extractedList)
                {   
                    cmd.Parameters[0].Value = extractedRecord.Date;
                    cmd.Parameters[1].Value = extractedRecord.Client;
                    cmd.Parameters[2].Value = extractedRecord.Path;
                    cmd.Parameters[3].Value = DateTime.Now;  //uncomment on live code 
                    cmd.Parameters[4].Value = extractedRecord.Path.Substring(extractedRecord.Path.LastIndexOf("/")+1);


                    cmd.ExecuteNonQuery();
                }

                conn.Close();
            }
        } catch(Exception ex)
        {   
            Console.WriteLine(ex.Message.ToString());
            Console.WriteLine("Error occured whilst inserting data into sql table FiletrckT");
            log.Info("Error occured whilst inserting data into sql table FiletrckT");
                log.Error(DateTime.Now + ": " + ex.Message.ToString());
        }

        }

        catch(Exception ex)
        {
            log.Error(DateTime.Now.ToString() + " " + ex.Message.ToString());
            Console.WriteLine(ex.Message);
        }
    }

Popular Answer

A better option is to use FastMember's ObjectReader to create an IDataReader on top of the collection or IEnumerable. This way you avoid doubling memory usage by copying everything into a DataTable.

From the repo's samples:

using(var bcp = new SqlBulkCopy(connection)) 
using(var reader = ObjectReader.Create(myList, "Id", "Name", "Description")) 
{ 
   bcp.DestinationTableName = "SomeTable"; 
   bcp.WriteToServer(reader); 
}

You don't need to pass a field list if you want to export all fields.

The library is available through NuGet

If you want to create a DataTable for other reasons, you can use MoreLINQ's ToDataTable. It works the same as ToList or ToArray but generates a DataTable. The full MoreLINQ library is available through NuGet. Individual extensions are available as code packages, eg ToDataTable's source is available here

UPDATE

The ExtractedInfo class doesn't seem to match the fields of the FileTrckT table. This can be fixed by adding a Select call that converts the ExtractedInfo objects to the form expected by the table, eg:

var itemsToAdd= from it in extractedList
                select new { Date= DateTime.Parse(it.Date), //Or ParseExact
                             it.Client,
                             it.Path,
                             DateAddedToDb = DateTime.Now,
                             FileName = it.Path.Substring(it.Path.LastIndexOf("/")+1)
                            };
var fields=new []{"Date", "Client", "Path","DateAddedToDb","FileName"};

using(var bcp = new SqlBulkCopy(connection)) 
using(var reader = ObjectReader.Create(itemsToAdd, fields)) 
{ 
   bcp.DestinationTableName = "FileTrckT"; 
   bcp.WriteToServer(reader); 
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why