I am having a datatable and i want to insert data to database(SSMS) using Entity Framework. What is the Feasible solution for this ?
Add a comment
|
2 Answers
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;
}
3 Comments
Mogli
I will choose this -> 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...Can you please provide any sample code or link for this ?
Ken
I know this post is old but something that might be of value is: exceptionnotfound.net/…
marc_s
@Ken: thanks for the pointer - good blog, have seen interesting posts for various topics there already
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();
}
2 Comments
devlin carnate
Your method relies on a string value for each column name in the datatable (instead of an object property, for example). If a column name changes, you'll have to update this code segment.
boj
What a nice playground for SQL injections