這是我第一次寫博客，由于水平有限，寫不出什么好東西，還望見諒。

我現在參與的這個項目采用的是EF框架，方便了數據庫的訪問。但在實際中，發現項目中導入市縣Excel數據耗時太長，于是趁這段時間專門研究了一下大數據的導入。

Excel數據如圖：

項目數據庫里已經有了省市數據，任務是導入縣數據。其中省市縣之間有外鍵關系。項目里已經有了讀取Excel表的方法，而且效率不低，耗時主要是在導入到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();

        }

這是項目里的源代碼，耗時40秒左右。我測試之后發現不僅效率低，而且是有bug的。測試時第一次錄入數據沒問題，如果刪除Counties表里的數據再導入數據是有問題的，我不知道為什么要為Province賦值而且第一次可以成功，如果有大神看見，請幫我解惑。我只能先嘗試以我的方法去做。

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();

        }

速度大概縮減到20秒，但這也不是我所能忍受的。于是上網又查了資料，了解到SqlBulkCopy。

代碼如下：

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);

            }

        }

時間縮減到4秒左右，其中主要是在EF查詢City表并為每一個Counties賦值時耗時占了大半。但這個的優化我覺得暫時做不到了，先這樣吧。