0

Possible Duplicate:
sqlbulkcopy using sql CE

I am trying to insert a lot of data from C# into a SQL Server CE database. Currently I am working with Entity Framework, pocos and poco-configuration classes because the property names of the pocos does not match the column names in the database.

I've already read a lot of articles and blog posts about this topic but no one of them matches exactly my problem with server generated id's and foreign keys. 

public class Country
{
    public int    Id   { get; set; }
    public string Name { get; set; }
}

public class City
{
    public int    Id      { get; set; }
    public string ZipCode { get; set; }
    public string Name    { get; set; }

    //Foreign Keys
    public int CountryId { get; set; }
}

public class Address
{
    public int Id        { get; set; }
    public string Name   { get; set; }
    public string Number { get; set; }

    //Foreign Keys
    public int CityId    { get; set; }
}

public class Customer
{
    public int Id            { get; set; }
    public string FirstName  { get; set; }
    public string SecondName { get; set; }

    //Foreign Keys
    public int AddressId { get; set; }
}

public class EmailAddress
{
    public int Id       { get; set; }
    public string Email { get; set; }

    //Foreign Keys
    public int CustomerId { get; set; }
}

That are my pocos and the IDs are server generated. So what is the fastest way of inserting a lot of Customers with Addresses and EmailAddresses into a SQL Server CE database.

Community
  • 1
  • 1
Julius
  • 192
  • 1
  • 9
  • http://stackoverflow.com/questions/1606487/sqlbulkcopy-using-sql-ce – Raphaël Althaus Feb 04 '13 at 15:40
  • 1
    But one thing is not clear at all : where do your data (which you wanna put in your database) come from ? Cause "from c#" doens't tell us how they look like... – Raphaël Althaus Feb 04 '13 at 15:45
  • they look like the pocos i described in the post and are stored in a List. I´ve already read about this BulkCopyLibrary from ErikJ but i dont know how to insert Rows with Foreign Keys. e.g do i first have to insert the Country, load the Country again from the database (to know its ID) and then insert the City with the new ID from the Country? that seems not the best way in my opinion? – Julius Feb 05 '13 at 01:46
  • Well, what I (still) don't understand : did you set the Ids /and Foreign Key Ids in your current Pocos manually ? – Raphaël Althaus Feb 05 '13 at 09:30
  • server generated ID means the SQL CE server assigns the ID – Julius Feb 05 '13 at 12:09
  • But as you say you have your entity in `List` : this mean you have a `List`, a `List`, no ? Then how can you know which customer is realated to which adress, before going to db ? – Raphaël Althaus Feb 05 '13 at 12:20
  • at first my data comes from a .txt file. one row in the file contains the customer, address and emailaddresses. then i map the data to the poco classes and then it goes to the database. To make it simpler lets just assume that i have on country e.g. "Germany" and i have 3 Cities "Cologne", "Munich", and "Frankfurt". Whats the best way to insert them into the Database? Do i have to add the country first then reload it (with the assigned id) and then add the countries? – Julius Feb 06 '13 at 21:54

0 Answers0