sql 批量插入

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