EF combined with SqlBulkCopy in the project

This is my first time to write a blog, because the level of limited, write no good things, but also hope forgive me.

I am now involved in this project using the EF Framework, which facilitates access to the database. In practice, however, it takes too long to find the data in the project to be imported into the city, so take this time to specialize in the import of big data.

Excel data

The project database already has the provincial and municipal data, the task is to import County data. There is a foreign key relationship between the provinces and counties. The project already has the method to read the Excel table, and the efficiency is not low, the time is mainly in the import into SQL Server2008

public void Importcounties (list<county> counties)

{

List<city> cities = counties. Select (d = d.city). ToList ();

Mytestentities db = new mytestentities ();

foreach (Var city in cities)

{

Province province =

Db. Provinces.where (d = d.name = = City. Province.name). First ();

City. province = Province;

}

foreach (Var county in counties)

{

Db. Counties.addobject (county);

}

Db. SaveChanges ();

}

This is the source code in the project, which takes about 40 seconds. After I tested it, I found it was not only inefficient, but also a bug. Testing the first data entry is not a problem, if you delete the data in the Counties table and then import data is problematic, I do not know why to assign a value for province and the first time can be successful, if the great god see, please help me. I can only try to do it my way first.

public void Importcountiessecond (list<county> counties)

{

Mytestentities db = new mytestentities ();

List<city> cities = db. Cities.tolist ();

foreach (Var county in counties)

{

County. City = cities. Where (d = = D.name = = County. City.name). FirstOrDefault ();

Db. Counties.addobject (county);

}

Db. SaveChanges ();

}

The speed is probably reduced to 20 seconds, but that's not what I can endure. So the internet and check the information, understand the sqlbulkcopy.

The code is as follows:

public void Importcountiesthird (list<county> counties)

{

Mytestentities db = new mytestentities ();

String constr = configurationmanager.connectionstrings["Dbconnstr"]. ConnectionString;

List<city> cities = db. Cities.tolist ();

DataTable table = new DataTable ();

Table. Columns.Add ("Cityid", typeof (int));

Table. Columns.Add ("Name");

Table. Columns.Add ("Ordernum", typeof (int));

Table. Columns.Add ("Iscity", typeof (BOOL));

for (int i = 0; i < counties. Count; i++)

{

County line = Counties[i];

int Cityid = cities. Where (d = = D.name = = Counties[i]. City.name). FirstOrDefault (). Cityid;

String name = line. Name;

int ordernum = line. Ordernum;

BOOL iscity = line. iscity;

DataRow row = table. NewRow ();

row["Cityid"] = Cityid;

row["name"] = name;

row["Ordernum"] = Ordernum;

row["iscity"] = iscity;

Table. Rows.Add (row);

}

using (SqlBulkCopy bulkcopy = new SqlBulkCopy (CONSTR))

{

Bulkcopy.destinationtablename = "counties";

BULKCOPY.COLUMNMAPPINGS.ADD ("Cityid", "Cityid");

BULKCOPY.COLUMNMAPPINGS.ADD ("name", "name");

BULKCOPY.COLUMNMAPPINGS.ADD ("Ordernum", "Ordernum");

BULKCOPY.COLUMNMAPPINGS.ADD ("Iscity", "iscity");

Bulkcopy.writetoserver (table);

}

}

The time is reduced to about 4 seconds, mostly when the EF queries the city table and assigns a value to each counties. But this optimization I think temporarily can not be done, first of all.

EF combined with SqlBulkCopy in the project