| ...
|
| // NOTE: This is the middle of the script,
|
| // which is why this is indented and doesn't
|
| // have a starting bracket, etc.
|
|
|
|
|
| //sql init for insert, purge old tables
|
| InitSqlForInsert(conn);
|
|
|
| //iterate list from SDK
|
| int iteratedCount = 0;
|
| DateTime OutputStart = DateTime.Now;
|
| foreach (DataRow dr in areasFromAPI.Data.Rows)
|
| {
|
| Area area = Sdk.GetEntity((Guid)dr[0]) as Area;
|
| out_p_AreaGUID.Value = area.Guid;
|
| out_p_AreaName.Value = area.Name;
|
| out_p2_AreaGUID.Value = area.Guid;
|
|
|
| out_cmd.ExecuteNonQuery(); //insert area
|
|
|
| //iterate area-door pairs
|
| foreach (Guid doorGuid in area.AllDoors)
|
| {
|
| out_p2_DoorGUID.Value = doorGuid;
|
| out_cmd2.ExecuteNonQuery(); //insert door-area pair
|
| }
|
| Trace($"{Math.Round((double)iteratedCount / areasFromAPI.Data.Rows.Count * 100, 0)}% Wrote {area.AllDoors.Count} DoorAreaPairs");
|
| iteratedCount++;
|
| }
|
| out_trn.Commit();
|
| TimeSpan QueryOutputTime = DateTime.Now - OutputStart;
|
| Trace($"Area table refresh completed, {areasFromAPI.Data.Rows.Count} areas inserted in {QueryOutputTime.TotalSeconds} seconds.");
|
| }
|
| catch (Exception e)
|
| {
|
| out_trn?.Rollback();
|
| Trace($"Exception: {e}");
|
| }
|
| CleanupSqlForInsert();
|
| conn.Close();
|
| }
|
|
|
| //initialize the shared sql variables for data insertion
|
| private void InitSqlForInsert(System.Data.SqlClient.SqlConnection conn)
|
| {
|
| if (out_cmd != null) out_cmd.Dispose();
|
| out_cmd = conn.CreateCommand();
|
| out_trn = conn.BeginTransaction();
|
|
|
| if (out_cmd2 != null) out_cmd2.Dispose();
|
| out_cmd2 = conn.CreateCommand();
|
|
|
| // Must assign both transaction object and connection
|
| // to Command object for a pending local transaction
|
| out_cmd.Connection = conn;
|
| out_cmd.Transaction = out_trn;
|
| out_cmd2.Connection = conn;
|
| out_cmd2.Transaction = out_trn;
|
|
|
| //purge tables
|
| out_cmd.CommandText = $"TRUNCATE TABLE {AreaExportTableName};";
|
| out_cmd.ExecuteNonQuery();
|
| out_cmd2.CommandText = $"TRUNCATE TABLE {AreaDoorsExportTableName};";
|
| out_cmd2.ExecuteNonQuery();
|
|
|
| out_p_AreaGUID = new System.Data.SqlClient.SqlParameter("@AreaGUID", System.Data.SqlDbType.UniqueIdentifier);
|
| out_cmd.Parameters.Add(out_p_AreaGUID);
|
| out_p_AreaName = new System.Data.SqlClient.SqlParameter("@AreaName", System.Data.SqlDbType.NVarChar, 100);
|
| out_cmd.Parameters.Add(out_p_AreaName);
|
|
|
| out_p2_DoorGUID = new System.Data.SqlClient.SqlParameter("@DoorGUID", System.Data.SqlDbType.UniqueIdentifier);
|
| out_cmd2.Parameters.Add(out_p2_DoorGUID);
|
| out_p2_AreaGUID = new System.Data.SqlClient.SqlParameter("@AreaGUID", System.Data.SqlDbType.UniqueIdentifier);
|
| out_cmd2.Parameters.Add(out_p2_AreaGUID);
|
|
|
| out_cmd.CommandText =
|
| $"INSERT INTO {AreaExportTableName} (AreaGUID, AreaName) " +
|
| "VALUES (@AreaGUID, @AreaName);";
|
| out_cmd.Prepare();
|
|
|
| out_cmd2.CommandText =
|
| $"INSERT INTO {AreaDoorsExportTableName} (AreaGUID, DoorGUID) " +
|
| "VALUES (@AreaGUID, @DoorGUID);";
|
| out_cmd2.Prepare();
|
| }
|