sql 批量插入_.NET_编程开发_程序员俱乐部

? private void btnC_Click(object sender, EventArgs e)
??????? {???? //得到每个楼层下面的房号
??????????? List<List<string>> floorandroom = rbgStepThreeWindow.FloorAndRoom;
??????????? //得到所有的楼层
??????????? string[] allfloors = rbgStepThreeWindow.AllFloors;
??????????? //得到楼层和房号是否绑定
?????????? bool _checked=rbgStepThreeWindow.CheckBoxValue;

?

?????????? if (_checked)
?????????? {
?????????????? for (int count= 0; count < allfloors.Length; count++)
?????????????? {
?????????????????? SqlConnection conn = DBConnection.DBOpen();
?????????????????? string floorid = Guid.NewGuid().ToString();
?????????????????? int bind =1;
?????????????????
?????????????????? try
?????????????????? {
?????????????????????? string sql = "insert into dbo.floor(floorID,buildingID,floor_num,floor_bind_to_room)values('" + floorid + "','" + buildingID + "'," + allfloors[count] + "," + bind + ");";
?????????????????????? SqlCommand cmd = new SqlCommand(sql, conn);
?????????????????????? cmd.ExecuteNonQuery();
?????????????????? }
?????????????????? catch
?????????????????? {

?????????????????????? MessageBox.Show("发生异常");
?????????????????????? return;
?????????????????? }
?????????????????? finally
?????????????????? {
?????????????????????? if (conn.State == ConnectionState.Open)
?????????????????????? {
?????????????????????????? conn.Close();
?????????????????????? }
?????????????????? }
?????????????????? DataTable dt = GetroomTableSchema();
?????????????????? foreach (string ss in floorandroom[count])
?????????????????? {
?????????????????????? DataRow r = dt.NewRow();
?????????????????????? r["roomID"] = Guid.NewGuid().ToString();
?????????????????????? r["buildingID"] = buildingID;
?????????????????????? r["floorID"] =floorid;
?????????????????????? r["room_name"] = ss;
?????????????????????? r["ruleid"] = rule_id;
?????????????????????? dt.Rows.Add(r);
?????????????????? }
?????????????????? BulkToDB(dt);
?????????????? }
?????????? }
?????????? else
?????????? {
?????????????? for (int i = 0; i < floorandroom.Count; i++)
?????????????? {
?????????????????? DataTable dt = GetroomTableSchema();
?????????????????? foreach (string ss in floorandroom[i])
?????????????????? {
?????????????????????? DataRow r = dt.NewRow();
?????????????????????????? r["roomID"] = Guid.NewGuid().ToString();
????????????????????????? r["buildingID"] = buildingID;
?????????????????????????? r["room_name"] = ss;
????????????????????????? r["ruleid"] = rule_id;
????????????????????????? dt.Rows.Add(r);
?????????????????? }
?????????????????? BulkToDB(dt);
?????????????? }
????????????
????????????
?????????? }
??????????? MessageBox.Show("保存成功");
??????? }
??????? public static void BulkToDB(DataTable dt)
??????? {

??????????? SqlConnection sqlConn = DBConnection.DBOpen();
??????????? SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
??????????? bulkCopy.DestinationTableName = "dbo.room";
??????????? bulkCopy.BatchSize = dt.Rows.Count;

??????????? try
??????????? {
??????????????? // sqlConn.Open();
??????????????? if (dt != null && dt.Rows.Count != 0)
??????????????????? //将源表dt中的所有数据插入到数据库中的目标表中
??????????????????? bulkCopy.WriteToServer(dt);
??????????? }
??????????? catch (Exception ex)
??????????? {
??????????????
??????????????? throw ex;
???????????????
??????????? }
??????????? finally
??????????? {
??????????????? sqlConn.Close();
??????????????? if (bulkCopy != null)
??????????????????? bulkCopy.Close();
??????????? }
??????? }
??????????????? //build room table
??????? public static DataTable GetroomTableSchema()
??????? {
??????????? DataTable dt = new DataTable("dbo.room");
??????????? dt.Columns.AddRange(new DataColumn[]
??????????? {
new DataColumn("roomID",typeof(Guid)),
new DataColumn("buildingID",typeof(Guid)),
new DataColumn("floorID",typeof(Guid)),
new DataColumn("room_name",typeof(string)),
new DataColumn("room_state",typeof(bool)),
new DataColumn("ruleid",typeof(Guid))

??????????? }
??????????? );

??????????? return dt;
??????? }