(原创)C# 用数据集Dataset批量添加记录到数据库
因为给老板做个新软件，涉及到提取旧数据到新软件中．原来采取的SQL单个添加记录，结果导入一批数据约两万条记录需要半个多小时,实在受不了这个速度．经BAIDU加研究发现个高效的批量添加数据方法．
1.创建个数据集(xsd)并将数据库中表拖入数据库设计界面中如下图
利用这个数据集可以很方便的添加各种SQL语句从而实现数据库操作，非常方便用这个后我从来就没再用过ADO了．不过有个缺点就是跨表查询可能会出现诸如：未能启用约束 一行或多行中包含违反非空 的错误，至今还没有找到解决方法从VS2003中就有这问题到VS2005仍然存在，实在是遗憾．我只能采取变通的方法从一个表中取出数据后再到另一个表中去查询．也算是解决但效率上肯定会受到不少影响．
2.在程序代码中添加声明引用数据表如：
private dsNewFasoyoTableAdapters.MemberTableAdapter myNewMTA;
3.初始化数据表
myNewMTA = new dsNewFasoyoTableAdapters.MemberTableAdapter();
4.使用数据表操作数据库及利用DataTable批量添加数据
先创建个DataTable 如dtMembers
然后用xsd里的数据表myNewMTA.Fill加载dtMembers
在For循环里给MemberRow赋值并将row加载到dtMembers中
最后使用myNewMTA.Update更新数据库实现批量加入数据．最后的result就代表着成功执行的记录数．
DataTable dtOldMembers = myOldMTA.GetAllMembers(); objState = ""; dsNewFasoyo.MemberDataTable dtMembers = new dsNewFasoyo.MemberDataTable(); myNewMTA.Fill(dtMembers); for (int i = 0; i < dtOldMembers.Rows.Count; i++)
{
string state = i.ToString() + "/" + dtOldMembers.Rows.Count.ToString(); string mid = dtOldMembers.Rows[i].ItemArray.GetValue(1).ToString(); //DataTable dtMember = myNewMTA.getMemberByMID(mid); string barCode = dtOldMembers.Rows[i].ItemArray.GetValue(1).ToString(); string firstName = dtOldMembers.Rows[i].ItemArray.GetValue(3).ToString(); string lastName = dtOldMembers.Rows[i].ItemArray.GetValue(2).ToString(); string address = dtOldMembers.Rows[i].ItemArray.GetValue(4).ToString(); string mobileNo = dtOldMembers.Rows[i].ItemArray.GetValue(7).ToString(); string email = dtOldMembers.Rows[i].ItemArray.GetValue(10).ToString(); string homePhone = dtOldMembers.Rows[i].ItemArray.GetValue(6).ToString(); string workPhone = dtOldMembers.Rows[i].ItemArray.GetValue(8).ToString(); string company = dtOldMembers.Rows[i].ItemArray.GetValue(9).ToString(); string alterPhone = dtOldMembers.Rows[i].ItemArray.GetValue(11).ToString(); string alterName = dtOldMembers.Rows[i].ItemArray.GetValue(12).ToString() + " " + dtOldMembers.Rows[i].ItemArray.GetValue(13).ToString(); string driverLic = dtOldMembers.Rows[i].ItemArray.GetValue(14).ToString(); string passportNo = dtOldMembers.Rows[i].ItemArray.GetValue(15).ToString(); string studentID = dtOldMembers.Rows[i].ItemArray.GetValue(16).ToString(); string member_Others = dtOldMembers.Rows[i].ItemArray.GetValue(17).ToString(); string member_othersDetail = dtOldMembers.Rows[i].ItemArray.GetValue(18).ToString(); string cardNo = dtOldMembers.Rows[i].ItemArray.GetValue(22).ToString(); DateTime expDate = DateTime.Now; DateTime.TryParse(dtOldMembers.Rows[i].ItemArray.GetValue(21).ToString(), out expDate); DateTime joinDate = DateTime.Now; DateTime.TryParse(dtOldMembers.Rows[i].ItemArray.GetValue(26).ToString(), out joinDate); string enrolLoc = dtOldMembers.Rows[i].ItemArray.GetValue(27).ToString(); dsNewFasoyo.MemberRow row = dtMembers.NewMemberRow(); row.MID = mid; row.Member_BarCode = barCode; row.FirstName = firstName; row.LastName = lastName; row.Address = address; row.MobilePhone = mobileNo; row.Email = email; row.HomePhone = homePhone; row.WorkPhone = workPhone; row.Company = company; row.AlterPhone = alterPhone; row.AlterName = alterName; row.DriverLic = driverLic; row.PassportNo = passportNo; row.StudentID = studentID; row.Member_Others = member_Others; row.Member_OthersDetail = member_othersDetail; row.CCType = 0; row.CardNo = cardNo; row.CCExpDate = expDate; row.JoinDate = joinDate; row.EnrolLoc = enrolLoc; //if (dtMember.Rows.Count <= 0) try
{ dtMembers.Rows.Add(row);
} catch (Exception ex) { Console.WriteLine(ex); } //myNewMTA.AddMember(mid, barCode, firstName, lastName, address, mobileNo, email, homePhone, workPhone, company, alterPhone, alterName, driverLic, passportNo, studentID, member_Others, member_othersDetail, 0, cardNo, expDate, joinDate, enrolLoc); dispProgBarStatus(state);
} int result = myNewMTA.Update(dtMembers); Console.WriteLine("Add Member Detail:" + result); if (result == dtOldMembers.Rows.Count) MessageBox.Show("导入会员资料完成！", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); else MessageBox.Show("导入会员资料失败！", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
