Inserting datatable data to database using Entity Framework?

Question

I am having a datatable and i want to insert data to database(SSMS) using Entity Framework. What is the Feasible solution for this ?

Answer 1

A DataTable is raw rows and columns - Entity Framework works with .NET objects, which is fundamentally something else.

So you cannot just easily insert the rows and columns from a DataTable using EF.

You either need to iterate through your DataTable and build up objects from those rows & columns, stick those into a List<YourObject> and then persist those objects to the database using EF....

or you just skip EF and persist the "raw" DataTable to the database using raw ADO.NET (SqlDataAdapter or SqlCommand with an INSERT statement).

Update:

OK, so you want to convert your DataTable to objects. You need to have a class that represents your entity in the database - since you provided no information, I'm just going to call it MyObject

public class MyObject
{
   // define some properties
   public int ID { get; set; }
   public string Name { get; set; }
   // whatever else this object has
}

You most likely already have such an object - an entity class - that exists in your database.

Then you need to define a method to convert the data table to a list of objects:

public List<MyObject> ConvertDataTable(DataTable tbl)
{
     List<MyObject> results = new List<MyObject>();

     // iterate over your data table
     foreach(DataRow row in tbl.Rows)
     {
         MyObject convertedObject = ConvertRowToMyObject(row);
         results.Add(convertedObject);
     }

     return results;
} 

and now you need a last method to convert a single row to your object type:

public MyObject ConvertRowToMyObject(DataRow row)
{
     MyObject result = new MyObject();

     // assign the properties of MyObject from the DataRow
     result.ID = row.GetInt32(0);
     result.Name = row.GetString(1);
     // and so on .... convert the column values in the DataRow into the
     // properties of your object type

     return result;
}

Answer 2

I found another way that is same but with less code and much easier i think. I thought I should share it here, might help others.

using (var productDB = new studentDBEntities()) //JFEntity object
      {
      for (int i = 0; i < DataExcelTable.Rows.Count; i++)
          {
                DataRow dr = DataExcelTable.Rows[i];
                productDB.Products.Add(new Product() 
                { //add data to class objects variable
                ProductName = dr["ProductNumber"].ToString(),
                ProductNumber=Convert.ToInt32 (dr["ProductNumber"]),
                Size=dr["size"].ToString(),
                Weight=Convert.ToInt32( dr["weight"]),
                Price=Convert.ToInt32(dr["price"]),
                SaleStatus=Convert.ToBoolean(dr["salestatus"])
                });
           } productDB.SaveChanges();
       }