protected override void RunActualProcess() { var arr = ExcelHelper.ExtractDataFromExcel(@"U:\SimZukunft\RawDataForMerging\GWRData.xlsx", 1, "A1", "AW4000"); var headerToColumnDict = new Dictionary <string, int>(); for (var i = 0; i < arr.GetLength(1) - 1; i++) { var o = arr[2, i + 1]; if (o == null) { throw new Exception("was null"); } headerToColumnDict.Add(o.ToString(), i + 1); } SqlConnection.RecreateTable <GwrData>(Stage.Raw, Constants.PresentSlice); var db = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice).Database; db.BeginTransaction(); for (var row = 3; row < arr.GetLength(0); row++) { var a = new GwrData(); if (arr[row, headerToColumnDict["EGID"]] == null) { continue; } TransferFields(arr, headerToColumnDict, row, a); db.Save(a); } db.CompleteTransaction(); }
// ReSharper disable once FunctionComplexityOverflow private static void TransferFields([NotNull][ItemNotNull] object[,] arr, [NotNull] Dictionary <string, int> hdict, int row, [NotNull] GwrData a) { a.EidgGebaeudeidentifikator_EGID = Convert.ToInt32(arr[row, hdict["EGID"]]); a.EidgBauprojektidentifikator_EPROID = Helpers.GetInt(arr[row, hdict["EPROID"]]); a.ErhebungsstelleBaustatistik_GESTNR = Helpers.GetInt(arr[row, hdict["GESTNR"]]); a.BauprojektIdLiefersystem_GBABID = (string)arr[row, hdict["GBABID"]]; a.GebaeudeIDLiefersystem_GBAGID = (string)arr[row, hdict["GBAGID"]]; a.BFSGemeindenummer_GDENR = Helpers.GetInt(arr[row, hdict["GDENR"]]); a.EidgGrundstuecksidentifikator_GEGRID = (string)arr[row, hdict["GEGRID"]]; a.Grundbuchkreisnummer_GGBKR = Helpers.GetInt(arr[row, hdict["GGBKR"]]); a.Parzellennummer_GPARZ = Helpers.GetInt(arr[row, hdict["GPARZ"]]); a.AmtlicheGebaeudenummer_GEBNR = Helpers.GetInt(arr[row, hdict["GEBNR"]]); a.NamedesGebaeudes_GBEZ = (string)arr[row, hdict["GBEZ"]]; a.AnzahlGebaeudeeingaenge_GANZDOM = Helpers.GetInt(arr[row, hdict["GANZDOM"]]); a.EKoordinate_GKODE = Helpers.GetDouble(arr[row, hdict["GKODE"]]); a.NKoordinate_GKODN = Helpers.GetDouble(arr[row, hdict["GKODN"]]); a.XKoordinate_GKODX = Helpers.GetDouble(arr[row, hdict["GKODX"]]); a.YKoordinate_GKODY = Helpers.GetDouble(arr[row, hdict["GKODY"]]); a.Koordinatenherkunft_GKSCE = Helpers.GetInt(arr[row, hdict["GKSCE*"]]); a.Lokalcode1_GLOC1 = Helpers.GetInt(arr[row, hdict["GLOC1"]]); a.Lokalcode2_GLOC2 = Helpers.GetInt(arr[row, hdict["GLOC2"]]); a.Lokalcode3_GLOC3 = Helpers.GetInt(arr[row, hdict["GLOC3"]]); a.Lokalcode4_GLOC4 = Helpers.GetInt(arr[row, hdict["GLOC4"]]); a.Gebaeudestatus_GSTAT = Helpers.GetInt(arr[row, hdict["GSTAT*"]]); a.Gebaeudekategorie_GKAT = Helpers.GetInt(arr[row, hdict["GKAT*"]]); a.Gebaeudeklasse_GKLAS = Helpers.GetInt(arr[row, hdict["GKLAS*"]]); a.Baujahr_GBAUJ = Helpers.GetInt(arr[row, hdict["GBAUJ"]]); a.Bauperiode_GBAUP = Helpers.GetInt(arr[row, hdict["GBAUP*"]]); a.Renovationsjahr_GRENJ = Helpers.GetInt(arr[row, hdict["GRENJ"]]); a.Renovationsperiode_GRENP = Helpers.GetInt(arr[row, hdict["GRENP*"]]); a.Abbruchjahr_GABBJ = Helpers.GetInt(arr[row, hdict["GABBJ"]]); a.Gebaeudeflaeche_GAREA = Helpers.GetInt(arr[row, hdict["GAREA"]]); a.AnzahlGeschosse_GASTW = Helpers.GetInt(arr[row, hdict["GASTW"]]); a.AnzahlseparateWohnraeume_GAZZI = Helpers.GetInt(arr[row, hdict["GAZZI"]]); a.AnzahlWohnungen_GANZWHG = Helpers.GetInt(arr[row, hdict["GANZWHG"]]); a.Heizungsart_GHEIZ = Helpers.GetInt(arr[row, hdict["GHEIZ*"]]); a.EnergietraegerderHeizung_GENHZ = Helpers.GetInt(arr[row, hdict["GENHZ*"]]); a.Warmwasserversorgung_GWWV = Helpers.GetInt(arr[row, hdict["GWWV**"]]); a.EnergietraegerfuerWarmwasser_GENWW = Helpers.GetInt(arr[row, hdict["GENWW*"]]); a.AnzahlEingangsrecords_GADOM = Helpers.GetInt(arr[row, hdict["GADOM"]]); a.AnzahlWohnungsrecords_GAWHG = Helpers.GetInt(arr[row, hdict["GAWHG"]]); a.PlausibilitaetsstatusderKoordinaten_GKPLAUS = Helpers.GetInt(arr[row, hdict["GKPLAUS"]]); a.StatusWohnungsbestandes_GWHGSTD = Helpers.GetInt(arr[row, hdict["GWHGSTD*"]]); a.VerifikationWohnungsbestand_GWHGVER = Helpers.GetInt(arr[row, hdict["GWHGVER**"]]); a.PlausibilitaetsstatusGebaeude_GPLAUS = Helpers.GetInt(arr[row, hdict["GPLAUS*"]]); a.Baumonat_GBAUM = (string)arr[row, hdict["GBAUM"]]; a.Renovationsmonat_GRENM = Helpers.GetInt(arr[row, hdict["GRENM"]]); a.Abbruchmonat_GABBM = Helpers.GetInt(arr[row, hdict["GABBM"]]); }
protected override void RunActualProcess() { string fn = CombineForRaw("GWRData.xlsx"); ExcelHelper eh = new ExcelHelper(Services.Logger, MyStage); var arr = eh.ExtractDataFromExcel2(fn, 1, "A1", "AW4000", out var _); var headerToColumnDict = new Dictionary <string, int>(); for (var i = 0; i < arr.GetLength(1) - 1; i++) { var o = arr[1, i]; if (o == null) { throw new Exception("was null"); } headerToColumnDict.Add(o.ToString(), i); } var db = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice); db.RecreateTable <GwrData>(); db.BeginTransaction(); for (var row = 2; row < arr.GetLength(0); row++) { var a = new GwrData(); if (arr[row, headerToColumnDict["EGID"]] == null) { continue; } TransferFields(arr, headerToColumnDict, row, a); db.Save(a); } db.CompleteTransaction(); }