Context Connection in Stored Procedures

Question

Hallo,

ich habe eine .NET Assembly geschrieben die die context connection für SQL Commands verwendet.

Das Aufrufen / Ausführen einzelner CLR Prozeduren funktioniert auch einwandfrei, allerdings wenn ich innnerhalb einer CRL-Prozedur eine andere aufrufe, die auch die Context Connection verwenden soll, bekomme ich die Meldung das die Context Connection bereits verwendet wird.

Gibt es eine Möglichkeit die bereits geöffnete Connection der zweiten SP bereitzustellen oder eine zweite Transaktion innerhalb des gleichen Context zu öffnen? Also keine eigene neue Connection zum SQL-Server aufzubauen? Habe leider in meiner 3 stündigen Recherche immernoch keine Lösung gefunden.

Bin für jeden Tip dankbar!

 

MfG

Dominik Scholz

Answer 1

Hallo Dominik,

in Technet: Restrictions on Regular and Context Connections findet man den Hinweis, das innerhalb einer Connection nur einmalig "Context Connection" verwendet werden kann.

Ich habe das bisher auch noch nicht ausprobiert, ich versuche möglichst alles mit T-SQL zu erschlagen. Wenn man aber wegen dieser Einschränkung aus einer CLR-SP keine ander CLR-SP aufrufen kann, die eine Connection verwendet, wäre das echt fatal. Ich werde da auch noch recherchieren und experimentieren.

---

Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de

Answer 2

Hallo Dominik,

auf die Einschränkungen einer Context Connection hat Olaf bereits hingewiesen.
Den gleichen Einschränkungen unterliegt letztendlich auch eine SQL Server Prozedur,
wenn man genau hinsieht. Denn auch dort gibt es keine Möglichkeit innerhalb eines
Resultsets (DataReader) eine Prozedur auszuführen, ein SELECT EXEC ...  ist nie erlaubt.

Auch gibt es nur eine Transaktionskontext, geschachtelte (eigenständige) Transaktionen
kennt der SQL Server nicht. Weitere BEGIN TRANSACTION würden nur @@TRANCOUNT erhöhen

Das heißt aber nicht, dass Du nicht weitere (SQL oder CLR) Prozeduren aufrufen kannst.
Nur mußt Du Dich dabei an die gleichen Richtlinien halten wie in TRANSACT SQL auch -
was im übrigen in den meisten Fällen bei der SQL Verarbeitung überlegen ist.

Eine Variante, wie man zwei Prozeduren kombinieren kann für die Northwind Customer->Orders:

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetCustomersOrderClr()
  {
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
      connection.Open();

      // Abrufen der Customer (nur ID)
      var customersCommand = new SqlCommand("GetCustomersClr", connection);
      customersCommand.CommandType = CommandType.StoredProcedure;

      var customersList = new List<SqlString>();
      using (var customersReader = customersCommand.ExecuteReader())
      {
        while (customersReader.Read())
        {
          customersList.Add(customersReader.GetSqlString(0));
        }
        customersReader.Close();
      }

      // Abrufen der Order (ID) eines Customers
      var ordersCommand = new SqlCommand("GetOrdersClr", connection);
      ordersCommand.CommandType = CommandType.StoredProcedure;
      var customerIDParameter = ordersCommand.Parameters.Add("@customerID", SqlDbType.NChar, 5);

      // Zusammengesetztes Ergebnis für CustomerID, OrderID
      var customerOrderRecord = new SqlDataRecord(
        new SqlMetaData("CustomerID", SqlDbType.NChar, 5),
        new SqlMetaData("OrderID", SqlDbType.Int));

      // Ruft die OrderID eines Customers ab
      SqlContext.Pipe.SendResultsStart(customerOrderRecord);
      foreach (var customerID in customersList)
      {
        customerIDParameter.Value = customerID;

        using (var ordersReader = ordersCommand.ExecuteReader())
        {
          while (ordersReader.Read())
          {
            customerOrderRecord.SetSqlString(0, customerID);
            customerOrderRecord.SetSqlInt32(1, ordersReader.GetSqlInt32(0));
            SqlContext.Pipe.SendResultsRow(customerOrderRecord);
          }
          ordersReader.Close();
        }
      }
      SqlContext.Pipe.SendResultsEnd();
    }
  }

/*
  // So geht es nicht, da nur ein Resultset (aka SqlDataReader) offen sein darf
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetCustomersOrderWontWork()
  {
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
      connection.Open();

      // Abrufen der Customer (nur ID)
      var customersCommand = new SqlCommand("GetCustomersClr", connection);
      customersCommand.CommandType = CommandType.StoredProcedure;

      // Abrufen der Order (ID) eines Customers
      var ordersCommand = new SqlCommand("GetOrdersClr", connection);
      ordersCommand.CommandType = CommandType.StoredProcedure;
      var customerIDParameter = ordersCommand.Parameters.Add("@customerID", SqlDbType.NChar, 5);

      // Zusammengesetztes Ergebnis für CustomerID, OrderID
      var customerOrderRecord = new SqlDataRecord(
        new SqlMetaData("CustomerID", SqlDbType.NChar, 5),
        new SqlMetaData("OrderID", SqlDbType.Int));

      using (var customersReader = customersCommand.ExecuteReader())
      {
        SqlContext.Pipe.SendResultsStart(customerOrderRecord);

        while (customersReader.Read())
        {
          SqlString customerID = customersReader.GetSqlString(0);
          customerIDParameter.Value = customerID;

          using (var ordersReader = ordersCommand.ExecuteReader())
          {
            while (ordersReader.Read())
            {
              customerOrderRecord.SetSqlString(0, customerID);
              customerOrderRecord.SetSqlInt32(1, ordersReader.GetSqlInt32(0));
              SqlContext.Pipe.SendResultsRow(customerOrderRecord);
            }
            ordersReader.Close();
          }
        }
        SqlContext.Pipe.SendResultsEnd();
      }
    }
  }
*/

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetCustomersClr()
  {
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
      connection.Open();
      
      using (var command = new SqlCommand("SELECT CustomerID FROM dbo.Customers", connection))
      {
        SqlContext.Pipe.ExecuteAndSend(command); 
      }
    }
  }

  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void GetOrdersClr(SqlString customerID)
  {
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
      connection.Open();

      using (var command = new SqlCommand("SELECT OrderID FROM dbo.Orders WHERE CustomerID = @CustomerID", connection))
      {
        command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = customerID;
        SqlContext.Pipe.ExecuteAndSend(command);
      }
    }
  }
};

Im ausgekommentierten Teil habe ich gezeigt, was nicht geht.

Gruß Elmar

 

Answer 3

Hallo,

entschuldigt die späte Reaktion, zum Monatswechsel gibt es immer sehr viel zu tun.

Das Problem mit geschachtelten DataReadern habe ich umgangen indem ich Datesets benutze und meine temporäre Lösung um geschachtelte Prozeduren zum laufen zu bringen ist, vor dem Aufruf die Connection zu schliessen und dann wieder zu öffnen (klingt recht banal),das Ergebnis wird mittels OUTPUT Parameter wieder zurückgegeben. Nur ist es möglich das sich andere Probleme mit diesem Weg in den anderen Prozeduren ergeben.

Ich bin dabei ein PL/SQL Package auf T/SQL zu migrieren bzw. es in eine VB.NET Assembly umzuwandeln, da leider T/SQL allein nicht genug Funktionalitäten bietet.

Ich werde mal Elmars Ansatz ausprobieren, vielen Dank für die Antworten!

MfG Dominik

Answer 4

Hallo Dominik,

wenn Du ein klein wenig über Dein PL/SQL Package verrätst, kann man Dir vielleicht weitere Tipps geben.
(Lesen kann ich PL/SQL noch ansatzweise, wenn ich auch seit langem kein Oracle mehr anfasse ;-)

Denn z. B. die erwähnten DataSets sind ein magerer Ersatz für eine temporäre Tabelle
(meine List<> im Beispiel ist auch nicht wesentlich besser)
Da fehlen bei schnell die Statistiken, die ein SQL Server braucht,
um effiziente Abfragen zu generieren.

SQL-CLR sollte man im allgmeinen nur bei rechenintensiven Operationen einsetzen,
bei allen tabellenorientierten Operationen ist T-SQL trotz des vermeintlich geringen
Umfangs um ein mehrfaches überlegen.

Gruß Elmar

Answer 5

Hallo Elmar,

in dem Package werden hauptsächlich Berechnungen vorgenommen desssen Queries dynamisch erzeugt werden was einer der Hauptgründe war eine Assembly zu erstellen.

Nur eine Prozedur aus dem Package weil das Gesamte doch jeden Rahmen eines Posts sprengen würde.

/* OTBValDayCalcNew
	* Neuberechnung aller Werte
	*****************************************************************************************/
	FUNCTION 	OTBValDayCalcNew(    	sKstStelle				IN VARCHAR2		,
														sKstStelleSub			IN VARCHAR2		,
														sKeyTyp					IN VARCHAR2		,
														sKeyTypSub				IN VARCHAR2		,
														dDayFrom     		IN DATE			,
														dDayUntil				IN DATE			) RETURN NUMBER AS
   sLogPosProc							CONSTANT VARCHAR2(100) 	:= 'OTBValDayCalcNew';
		sLogPosSeq							VARCHAR2(100)				:= '-';
 		nErrNum       				NUMBER;
   sErrMsg          		VARCHAR2(100);

		TYPE RecLizFak_t IS RECORD (
			DAY						DATE			 ,
			PERSNR					VARCHAR2	(5) ,
			KEY						VARCHAR2	(30),
			KEY_SUB					VARCHAR2	(30),
			FAKTOR					NUMBER
		);
		TYPE TabLizFak_t IS TABLE OF 	RecLizFak_t;
		tabLizFak							TabLizFak_t;
		iSeqStart							INTEGER;
		iSeqPos								INTEGER;
		nSeqSum								NUMBER;
		bEndOfSeq							BOOLEAN;
		iColPos								INTEGER;
		nLiz									NUMBER;
		nLizBrutto							NUMBER;
		nResult								NUMBER;
   sQuery								VARCHAR2(4000);
   sKeyVal								VARCHAR2(500);
   sKeyValSub							VARCHAR2(500);
	BEGIN
   
		CheckDate(sKstStelleSub, dDayFrom, dDayUntil);
		
   	-- Löschen der alten Werte
  	DELETE FROM OTB_KEY_VAL_DAY 		WHERE KST_STELLE=sKstStelle AND KST_STELLE_SUB=sKstStelleSub AND KEY_TYP=sKeyTyp AND KEY_TYP_SUB=sKeyTypSub AND DAY>=TRUNC(dDayFrom) AND DAY<=TRUNC(dDayUntil);
   DELETE FROM OTB_KEY_VAL_DAY_ART 	WHERE KST_STELLE=sKstStelle AND KST_STELLE_SUB=sKstStelleSub AND KEY_TYP=sKeyTyp AND KEY_TYP_SUB=sKeyTypSub AND DAY>=TRUNC(dDayFrom) AND DAY<=TRUNC(dDayUntil);

		-- Faktoren für Liz - Zeiten für Key's und SubKey's bestimmen
		IF NOT (sKeyTyp = 'PRS' AND sKeyTypSub = 'ALL') THEN
			-- eine Loop, wenn Datensatz vorhanden
 			FOR rowLizPartCmd IN (
				SELECT
					QUERY_CMD,
        PAR_KEY,
        QUERY_KEY_TYP
				FROM OTB_VAL_LIZ_PART_DEF
				WHERE			KST_STELLE			= sKstStelle
						AND	KST_STELLE_SUB		= sKstStelleSub
						AND	KEY_TYP				= sKeyTyp
   					AND	KEY_TYP_SUB			= sKeyTypSub)
			LOOP
     	sQuery := rowLizPartCmd.QUERY_CMD;
     	-- Key und Key-Sub Elemente eventuell dynamisch einbauen
      IF INSTR(sQuery, '<KEY>') > 0 OR INSTR(sQuery, '<KEY_SUB>') > 0 THEN
      	GetKeyDef(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, rowLizPartCmd.QUERY_KEY_TYP, sKeyVal, sKeyValSub);
        sQuery := REPLACE(sQuery, '<KEY>', sKeyVal);
        sQuery := REPLACE(sQuery, '<KEY_SUB>', sKeyValSub);
      END IF;
      
				-- Daten laden
      IF rowLizPartCmd.PAR_KEY = 'DD_KKS' THEN
					EXECUTE IMMEDIATE sQuery BULK COLLECT INTO tabLizFak USING dDayFrom, dDayUntil, sKstStelle, sKstStelleSub;
      ELSIF rowLizPartCmd.PAR_KEY = 'DDK' THEN
      	EXECUTE IMMEDIATE sQuery BULK COLLECT INTO tabLizFak USING dDayFrom, dDayUntil, sKstStelleSub;
      ELSIF rowLizPartCmd.PAR_KEY = 'DD' THEN
					EXECUTE IMMEDIATE sQuery BULK COLLECT INTO tabLizFak USING dDayFrom, dDayUntil;
      END IF;

				-- und berechnen
				-- Loop über alle Werte-Sequenzen
				iSeqStart := tabLizFak.FIRST;
				WHILE iSeqStart IS NOT NULL LOOP
					nSeqSum		:= 0;

					-- Loop innerhalb der aktuellen Werte-Sequenz zur Summenbestimmung
					iSeqPos	 	:= iSeqStart;
					bEndOfSeq	:= FALSE;
					WHILE iSeqPos IS NOT NULL AND NOT bEndOfSeq LOOP
						nSeqSum := tabLizFak(iSeqPos).FAKTOR + nSeqSum;

						iSeqPos := tabLizFak.NEXT(iSeqPos);
						IF iSeqPos IS NOT NULL THEN
							bEndOfSeq := NOT(tabLizFak(iSeqPos).PERSNR = tabLizFak(iSeqStart).PERSNR AND tabLizFak(iSeqPos).DAY = tabLizFak(iSeqStart).DAY);
						END IF;
					END LOOP;

					-- Loop innerhalb der aktuellen Werte-Sequenz zur Faktorenberechnung
					iSeqPos	 	:= iSeqStart;
					bEndOfSeq	:= FALSE;
					WHILE iSeqPos IS NOT NULL AND NOT bEndOfSeq LOOP
						tabLizFak(iSeqPos).FAKTOR := tabLizFak(iSeqPos).FAKTOR / nSeqSum;

						iSeqPos := tabLizFak.NEXT(iSeqPos);
						IF iSeqPos IS NOT NULL THEN
							bEndOfSeq := NOT(tabLizFak(iSeqPos).PERSNR = tabLizFak(iSeqStart).PERSNR AND tabLizFak(iSeqPos).DAY = tabLizFak(iSeqStart).DAY);
						END IF;
					END LOOP;

					-- nächste Werte - Sequenz
					iSeqStart := iSeqPos;
				END LOOP;
			END LOOP;
		END IF;

   -- Loop über LIZ in allen Servicen zur KstStelle
   sLogPosSeq := 'curLiz - ' || sKstStelle;
  	FOR rowLIZ IN (
				SELECT
  				TRUNC(TSK.TSSTART)														AS DAY			,
  				TSK.PERSNR 																	AS PERSNR		,
       	SUM(TSK.TSLASTALIVE - TSK.TSSTART) * (60 * 60 * 24) 			AS LIZ_BRUTTO
      	FROM 	CICSYS.CIC_TASK_SERVICE TSK
     	WHERE 		TSK.TSSTART 	>= TRUNC(dDayFrom)
  					AND	TSK.TSSTART 	< 	TRUNC(dDayUntil + 1)
  					AND	TSK.SERVICEID 	IN(SELECT SERVICEID FROM CICSYS.CIC_SERVICE WHERE KST_STELLE=sKstStelle AND KST_STELLE_SUB=sKstStelleSub)
  			GROUP BY TRUNC(TSK.TSSTART), TSK.PERSNR
				ORDER BY TRUNC(TSK.TSSTART), TSK.PERSNR)
		LOOP
			nLizBrutto 	:= rowLIZ.LIZ_BRUTTO;
  		nLiz 			:= CICSYS.GetLIZNettoFromKstStelle(rowLiz.PERSNR, rowLiz.DAY, sKstStelle, sKstStelleSub);

			-- wenn nicht normale PersNr - Agregation
			IF NOT (sKeyTyp = 'PRS' AND sKeyTypSub = 'ALL') THEN
   		iColPos 		:= tabLizFak.FIRST;
      -- dann mit Loop und Faktoren PersNr - Werte auf Key - Wert aufteilen
      WHILE iColPos IS NOT NULL LOOP
        IF (tabLizFak(iColPos).PERSNR = rowLIZ.PERSNR AND tabLizFak(iColPos).DAY = rowLiz.DAY) THEN
         -- Liz mit Faktor multiplizieren
         -- dann erst Updateversuch, da Liz je Key über mehrere PersNr aufaddiert werden muss
         UPDATE  OTB_KEY_VAL_DAY SET
              LIZ_BRUTTO			= LIZ_BRUTTO + (nLizBrutto * tabLizFak(iColPos).FAKTOR),
              LIZ 					= LIZ + (nLiz * tabLizFak(iColPos).FAKTOR)
         WHERE 		KST_STELLE			= sKstStelle
            AND	KST_STELLE_SUB		= sKstStelleSub
            AND	KEY_TYP				= sKeyTyp
            AND	KEY_TYP_SUB			= sKeyTypSub
            AND	DAY 		     = rowLiz.DAY
            AND 	KEY 	     	= tabLizFak(iColPos).KEY
            AND 	KEY_SUB     	= tabLizFak(iColPos).KEY_SUB;
         IF SQL%ROWCOUNT = 0 THEN
           INSERT INTO OTB_KEY_VAL_DAY	 (KST_STELLE	,KST_STELLE_SUB	,KEY_TYP		,KEY_TYP_SUB,DAY
           										 ,KEY		            				  ,KEY_SUB
           										 ,LIZ_BRUTTO											,LIZ)
                       VALUES (sKstStelle	,sKstStelleSub		,sKeyTyp		,sKeyTypSub	,rowLiz.DAY
                       		 ,tabLizFak(iColPos).KEY						  ,tabLizFak(iColPos).KEY_SUB
                       		 ,(nLizBrutto * tabLizFak(iColPos).FAKTOR)	,(nLiz * tabLizFak(iColPos).FAKTOR));
         END IF;
        END IF;
        iColPos := tabLizFak.NEXT(iColPos);
      END LOOP;
  		ELSE
      -- sonst mit PersNr als Key Wertesatz einfügen
      -- Einfügen erfolgt je PersNr, Day nur für einen Datensatz, daher keine Notwendigkeit für Update
      INSERT INTO OTB_KEY_VAL_DAY	 (KST_STELLE	,KST_STELLE_SUB	,KEY_TYP		,KEY_TYP_SUB,DAY
      										 ,KEY														,KEY_SUB
                      ,LIZ_BRUTTO											,LIZ)
                  VALUES (sKstStelle	,sKstStelleSub		,sKeyTyp		,sKeyTypSub	,rowLiz.DAY
                  		 ,rowLiz.PERSNR	           		,'ALL'
                      ,nLizBrutto											,nLiz);
			END IF;
  	END LOOP;

   -- Query - Definitionen zur Berechnungs ausführen
		nResult := OTBValDayCalc(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil, 'NEW');
		IF nResult = 0 THEN
	 		nResult := OTBValDayCalc(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil, 'UPD');
		END IF;
		IF nResult = 0 THEN
			nResult := OTBValDayVKN( sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil);
		END IF;
		IF nResult = 0 THEN
			COMMIT;
		END IF;
		RETURN nResult;
	EXCEPTION
  	WHEN Invalid_Date_Exception THEN
			nErrNum := -1;
     sErrMsg := SUBSTR('Zeitrahmen für die Neuberechnung überschritten: ' || sKstStelle || ', ' || sKstStelleSub || ', ' || TO_CHAR(dDayFrom, 'DD.MM.YYYY'), 1, 100);
     INSERT INTO CICSYS.CIC_ERRORLOG (TS, ERR_NUM, ERR_MSG, USERSCHEMA, POSITION) VALUES (SYSDATE, nErrNum, sErrMsg, c_sLogPosSchema, c_sLogPosPkg || '.' || sLogPosProc || '->' || sLogPosSeq);
			RETURN nErrNum;
		WHEN OTHERS THEN
			nErrNum := SQLCODE;
     sErrMsg := SUBSTR(SQLERRM, 1, 100);
     INSERT INTO CICSYS.CIC_ERRORLOG (TS, ERR_NUM, ERR_MSG, USERSCHEMA, POSITION, DUMP_INFO) VALUES (SYSDATE, nErrNum, sErrMsg, c_sLogPosSchema, c_sLogPosPkg || '.' || sLogPosProc || '->' || sLogPosSeq, sQuery);
			RETURN nErrNum;
	END OTBValDayCalcNew;

Mein Assembly Code

 

'/* OTBValDayCalcNew
  '* Neuberechnung aller Werte
  '*****************************************************************************************/
  <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub OTBValDayCalcNew( _
        ByVal sKstStelle As String, _
        ByVal sKstStelleSub As String, _
        ByVal sKeyTyp As String, _
        ByVal sKeyTypSub As String, _
        ByVal dDayFrom As Date, _
        ByVal dDayUntil As Date)


    Const sLogPosProc As String = "OTBValDayCalcNew"
    Dim sLogPosSeq As String = "-"
    'Dim nErrNum As Double
    'Dim sErrMsg As String

    Dim iSeqStart As Integer
    Dim iSeqPos As Integer
    Dim nSeqSum As Double
    Dim bEndOfSeq As Boolean
    Dim iColPos As Integer
    Dim nLiz As Double
    Dim nLizBrutto As Double
    Dim nResult As Integer
    Dim sQuery As String
    Dim sKeyVal As String = ""
    Dim sKeyValSub As String = ""

    Dim conDB = New SqlConnection("context connection=true")
    conDB.Open()

    Try
      CheckDate(sKstStelleSub, dDayFrom, dDayUntil)

      '-- Löschen der alten Werte
      Dim CmdClear As New SqlCommand
      With CmdClear
        .Connection = conDB
        .CommandType = CommandType.Text
        .Parameters.AddWithValue("@KstStelle", sKstStelle)
        .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
        .Parameters.AddWithValue("@KeyTyp", sKeyTyp)
        .Parameters.AddWithValue("@KeyTypSub", sKeyTypSub)
        .Parameters.AddWithValue("@DayFrom", dDayFrom)
        .Parameters.AddWithValue("@DayUntil", dDayUntil)

        .CommandText = _
          "DELETE FROM OtbKeyValDay 		WHERE KstStelle = @KstStelle AND KstStelleSub = @KstStelleSub AND KeyTyp = @KeyTyp AND KeyTypSub = @KeyTypSub AND DAY>=@DayFrom AND DAY<=@DayUntil"
        .ExecuteNonQuery()

        .CommandText = _
          "DELETE FROM OtbKeyValDayArt 	WHERE KstStelle = @KstStelle AND KstStelleSub = @KstStelleSub AND KeyTyp = @KeyTyp AND KeyTypSub = @KeyTypSub AND DAY>=@DayFrom AND DAY<=@DayUntil"
        .ExecuteNonQuery()
      End With

      Dim CmdLizPartCmd As New SqlCommand
      Dim m_sqlDataAdapter As New SqlDataAdapter
      Dim DsLizPartCmd As New DataSet

      With CmdLizPartCmd
        .Connection = conDB
        .Prepare()
        .CommandType = CommandType.Text
        .CommandText = _
          "SELECT                     " + _
          "  QueryCmd      ,            " + _
          "  ParKey       ,            " + _
          "  QueryKeyTyp                 " + _
          "FROM OtbValLizPartDef             " + _
          "WHERE   KstStelle            = @KstStelle  " + _
          "	AND	KstStelleSub		    = @KstStelleSub " + _
          "	AND	KeyTyp				= @KeyTyp    " + _
          "	AND	KeyTypSub			= @KeyTypSub  "
        .Parameters.AddWithValue("@KstStelle", sKstStelle)
        .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
        .Parameters.AddWithValue("@KeyTyp", sKeyTyp)
        .Parameters.AddWithValue("@KeyTypSub", sKeyTypSub)
      End With
      m_sqlDataAdapter.SelectCommand = CmdLizPartCmd
      m_sqlDataAdapter.Fill(DsLizPartCmd)
      CmdLizPartCmd.Dispose()

      Dim dsQuery As New DataSet
      '-- Faktoren für Liz - Zeiten für Key's und SubKey's bestimmen
      If Not (sKeyTyp = "PRS" And sKeyTypSub = "ALL") Then
        '-- eine Loop, wenn Datensatz vorhanden

        Dim RowLizPartCmd As DataRow
        For Each RowLizPartCmd In DsLizPartCmd.Tables(0).Rows
          sQuery = RowLizPartCmd("QueryCmd").ToString
          '-- Key und Key-Sub Elemente eventuell dynamisch einbauen

          If InStr(sQuery, "<KEY>") > 0 Or InStr(sQuery, "<KEY_SUB>") > 0 Then
            conDB.Close()
            OtbValDay.GetKeyDef(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, RowLizPartCmd("QueryKeyTyp").ToString, sKeyVal, sKeyValSub)
            conDB.Open()
            sQuery = Replace(sQuery, "<KEY>", sKeyVal)
            sQuery = Replace(sQuery, "<KEY_SUB>", sKeyValSub)
          End If

          '-- Daten laden
          Dim CmdQuery As New SqlCommand
          With CmdQuery
            .Connection = conDB
            .CommandType = CommandType.Text
            .CommandText = sQuery
            .Parameters.AddWithValue("@DayFrom", dDayFrom)
            .Parameters.AddWithValue("@DayUsntil", dDayUntil)
          End With
          If DsLizPartCmd.Tables(0).Rows(0).Field(Of String)("Parkey") = "DD_KKS" Then
            With CmdQuery
              .Parameters.AddWithValue("@KstStelle", sKstStelle)
              .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
            End With
          ElseIf DsLizPartCmd.Tables(0).Rows(0).Field(Of String)("Parkey") = "DDK" Then
            With CmdQuery
              .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
            End With
          ElseIf DsLizPartCmd.Tables(0).Rows(0).Field(Of String)("Parkey") = "DD" Then
            '
          End If
          With m_sqlDataAdapter
            .SelectCommand = CmdQuery
            .Fill(dsQuery)
          End With
          CmdQuery.Dispose()

          '-- und berechnen
          '-- Loop über alle Werte-Sequenzen
          iSeqStart = 0 'tabLizFak.FIRST
          While iSeqStart <= dsQuery.Tables(0).Rows.Count
            nSeqSum = 0

            '-- Loop innerhalb der aktuellen Werte-Sequenz zur Summenbestimmung
            iSeqPos = iSeqStart
            bEndOfSeq = False
            While iSeqPos <= dsQuery.Tables(0).Rows.Count And Not bEndOfSeq
              nSeqSum = dsQuery.Tables(0).Rows(iSeqPos).Field(Of Double)("Faktor") + nSeqSum

              iSeqPos = iSeqPos + 1 'tabLizFak.NEXT(iSeqPos)
              If iSeqPos <= dsQuery.Tables(0).Rows.Count Then
                bEndOfSeq = Not (dsQuery.Tables(0).Rows(iSeqPos).Field(Of String)("PersNr") = dsQuery.Tables(0).Rows(iSeqStart).Field(Of String)("PersNr") And dsQuery.Tables(0).Rows(iSeqPos).Field(Of Date)("Day") = dsQuery.Tables(0).Rows(iSeqStart).Field(Of Date)("Day"))
              End If
            End While

            '-- Loop innerhalb der aktuellen Werte-Sequenz zur Faktorenberechnung
            iSeqPos = iSeqStart
            bEndOfSeq = False
            While iSeqPos <= dsQuery.Tables(0).Rows.Count And Not bEndOfSeq
              dsQuery.Tables(0).Rows(iSeqPos).BeginEdit()
              dsQuery.Tables(0).Rows(iSeqPos).Item("Faktor") = dsQuery.Tables(0).Rows(iSeqPos).Field(Of Double)("Faktor") / nSeqSum
              dsQuery.AcceptChanges()

              iSeqPos = iSeqPos + 1 'tabLizFak.NEXT(iSeqPos)
              If iSeqPos <= dsQuery.Tables(0).Rows.Count Then
                bEndOfSeq = Not (dsQuery.Tables(0).Rows(iSeqPos).Field(Of String)("PersNr") = dsQuery.Tables(0).Rows(iSeqStart).Field(Of String)("PersNr") And dsQuery.Tables(0).Rows(iSeqPos).Field(Of Date)("Day") = dsQuery.Tables(0).Rows(iSeqStart).Field(Of Date)("Day"))
              End If
            End While

            '-- nächste Werte - Sequenz
            iSeqStart = iSeqPos
          End While
        Next RowLizPartCmd
      End If

      '-- Loop über LIZ in allen Servicen zur KstStelle
      sLogPosSeq = "curLiz - " + sKstStelle
      Dim CmdRowLiz As New SqlCommand
      With CmdRowLiz
        .Connection = conDB
        .CommandType = CommandType.Text
        .CommandText = _
          "	SELECT                                                   " + _
          "		TRUNC(TSK.TsStart)							AS Day			,  " + _
          "		TSK.PersNr 								AS PersNr		,  " + _
          "   	SUM(TSK.TsLastAlive - TSK.TsStart) * (60 * 60 * 24) 			AS LizBrutto        " + _
          "  	FROM 	dbo.CicTaskService TSK                                   " + _
          "  WHERE   TSK.TsStart >= DateAdd(day, DateDiff(day, 0, @DayFrom), 0)                   " + _
          "			AND	TSK.TsStart < DateAdd(day, DateDiff(day, 0, @DayUntil + 1), 0)           " + _
          "			AND	TSK.ServiceId IN (SELECT ServiceId FROM CICSYS.CicService WHERE KstStelle = @KstStelle AND KstStelleSub = @KstStelleSub) " + _
          "	GROUP BY TRUNC(TSK.TsStart), TSK.PersNr                                   " + _
          "	ORDER BY TRUNC(TSK.TsStart), TSK.PersNr                                   "
        .Parameters.AddWithValue("@DayFrom", dDayFrom)
        .Parameters.AddWithValue("@DayUntil", dDayUntil)
        .Parameters.AddWithValue("@KstStelle", sKstStelle)
        .Parameters.AddWithValue("@KstStelleSub", sKstStelle)
      End With

      Dim dsRowLiz As New DataSet
      With m_sqlDataAdapter
        .SelectCommand = CmdRowLiz
        .Fill(dsRowLiz)
      End With
      CmdRowLiz.Dispose()

      Dim RowLiz As DataRow
      For Each RowLiz In dsRowLiz.Tables(0).Rows
        nLizBrutto = dsRowLiz.Tables(0).Rows(0).Field(Of Double)("LizBrutto")
        nLiz = CDbl(GetLIZNettoFromKstStelle(dsQuery.Tables(0).Rows(0).Field(Of String)("PersNr"), dsQuery.Tables(0).Rows(0).Field(Of Date)("Day"), sKstStelle, sKstStelleSub))

        '-- wenn nicht normale PersNr - Agregation
        If Not (sKeyTyp = "PRS" And sKeyTypSub = "ALL") Then
          iColPos = 0
          '-- dann mit Loop und Faktoren PersNr - Werte auf Key - Wert aufteilen
          While iColPos <= dsQuery.Tables(0).Rows.Count
            If dsQuery.Tables(0).Rows(iColPos).Field(Of String)("PersNr") = RowLiz.Field(Of String)("PersNr") And dsQuery.Tables(0).Rows(iColPos).Field(Of Date)("Day") = RowLiz.Field(Of Date)("Day") Then
              '-- Liz mit Faktor multiplizieren
              '-- dann erst Updateversuch, da Liz je Key über mehrere PersNr aufaddiert werden muss
              Dim CmdUpdateKeyValDay As New SqlCommand
              With CmdUpdateKeyValDay
                .Connection = conDB
                .CommandType = CommandType.Text
                .CommandText = _
                   " UPDATE  OtbKeyValDay SET                      " + _
                   "     LizBrutto			= LizBrutto + @LizBrutto , " + _
                   "     Liz 				= Liz + @Liz        " + _
                   " WHERE 		KstStelle		= @KstStelle        " + _
                   "    AND	KstStelleSub		= @KstStelleSub       " + _
                   "    AND	KeyTyp			= @KeyTyp          " + _
                   "    AND	KeyTypSub		= @KeyTypSub        " + _
                   "    AND	Day 		    = @Day           " + _
                   "    AND 	Key 	     	= @Key           " + _
                   "    AND 	KeySub     	    = @KeySub          "
                .Parameters.AddWithValue("@LizBrutto", nLizBrutto * dsQuery.Tables(0).Rows(iColPos).Field(Of Double)("Faktor"))
                .Parameters.AddWithValue("@Liz", nLiz * dsQuery.Tables(0).Rows(iColPos).Field(Of Double)("Faktor"))
                .Parameters.AddWithValue("@KstStelle", sKstStelle)
                .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
                .Parameters.AddWithValue("@KeyTyp", sKeyTyp)
                .Parameters.AddWithValue("@KeyTypSub", sKeyTypSub)
                .Parameters.AddWithValue("@Day", RowLiz.Field(Of Date)("Day"))
                .Parameters.AddWithValue("@Key", dsQuery.Tables(0).Rows(iColPos).Field(Of String)("Key"))
                .Parameters.AddWithValue("@KeySub", dsQuery.Tables(0).Rows(iColPos).Field(Of String)("KeySub"))
                If .ExecuteNonQuery() = 0 Then
                  .CommandText = _
                    "INSERT INTO OtbKeyValDay	(              " + _
                    "  LizBrutto, Liz,    KstStelle, KstStelleSub,  " + _
                    "  KeyTyp,   KeyTypSub, Day,            " + _
                    "  Key,    KeySub                 " + _
                    " ) VALUES (                      " + _
                    "  @LizBrutto, @Liz,    @KstStelle, @KstStelleSub, " + _
                    "  @KeyTyp,  @KeyTypSub, @Day,            " + _
                    "  @Key,    @KeySub    )             "
                  .ExecuteNonQuery()
                  .Dispose()
                Else
                  .Dispose()
                End If
              End With
            End If
            iColPos = iColPos + 1
          End While
        Else
          '-- sonst mit PersNr als Key Wertesatz einfügen
          '-- Einfügen erfolgt je PersNr, Day nur für einen Datensatz, daher keine Notwendigkeit für Update
          Dim CmdUpdateKeyValDay As New SqlCommand
          With CmdUpdateKeyValDay
            .Connection = conDB
            .CommandType = CommandType.Text
            .CommandText = _
              " INSERT INTO OtbKeyValDay   (                " + _
              "  LizBrutto,   Liz,      KstStelle, KstStelleSub,  " + _
              "  KeyTyp,     KeyTypSub,   Day,            " + _
              "  Key,      KeySub                   " + _
              " ) VALUES (                          " + _
              "  @LizBrutto,   @Liz,      @KstStelle, @KstStelleSub,	" + _
              "  @KeyTyp,    @KeyTypSub,   'ALL',           " + _
              "  @Day,      @Key      )              "
            .Parameters.AddWithValue("@LizBrutto", nLizBrutto)
            .Parameters.AddWithValue("@Liz", nLiz)
            .Parameters.AddWithValue("@KstStelle", sKstStelle)
            .Parameters.AddWithValue("@KstStelleSub", sKstStelleSub)
            .Parameters.AddWithValue("@KeyTyp", sKeyTyp)
            .Parameters.AddWithValue("@KeyTypSub", sKeyTypSub)
            .Parameters.AddWithValue("@Day", RowLiz.Field(Of Date)("Day"))
            .Parameters.AddWithValue("@Key", RowLiz.Field(Of String)("PersNr"))
            .ExecuteNonQuery()
            .Dispose()
          End With
        End If
      Next
      conDB.Close()

      '-- Query - Definitionen zur Berechnungs ausführen
      OTBValDayCalc(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil, "NEW", nResult)
      If nResult = 0 Then
        OTBValDayCalc(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil, "UPD", nResult)
      End If
      If nResult = 0 Then
        OTBValDayVKN(sKstStelle, sKstStelleSub, sKeyTyp, sKeyTypSub, dDayFrom, dDayUntil, nResult)
      End If
      If nResult = 0 Then
        ' Rollback nicht mehr möglich bei teilweise fehlerhafter Berechnung ?!
        'COMMIT()
      End If
      'Catch exDate As inv
    Catch ex As Exception
      Dim CmdErrorUpdate As New SqlCommand
      If conDB.State <> ConnectionState.Open Then conDB.Open()

      With CmdErrorUpdate
        .Connection = conDB
        .Prepare()
        .CommandType = CommandType.Text
        .CommandText = _
          "INSERT INTO dbo.CicErrorLog (Ts, ErrNum, ErrMsg, UserSchema, Position " + _
          " ) VALUES (                              " + _
          " GetDate(), @ErrNum, @ErrMsg, @UserSchema, @Position)         "
        .Parameters.AddWithValue("@ErrNum", -1)
        .Parameters.AddWithValue("@ErrMsg", ex.Message)
        .Parameters.AddWithValue("@UserSchema", sKstStelleSub)
        .Parameters.AddWithValue("@Position", "OtbValDay" + "." + sLogPosProc + "->" + sLogPosSeq)
        .ExecuteNonQuery()
        .Dispose()
      End With
      conDB.Close()

    End Try
    'EXCEPTION
    ' 	WHEN Invalid_Date_Exception THEN
    '		nErrNum := -1;
    '    sErrMsg := SUBSTR('Zeitrahmen für die Neuberechnung überschritten: ' || sKstStelle || ', ' || sKstStelleSub || ', ' || TO_CHAR(dDayFrom, 'DD.MM.YYYY'), 1, 100);
    '    INSERT INTO CICSYS.CIC_ERRORLOG (TS, ERR_NUM, ERR_MSG, USERSCHEMA, Position VALUES (SYSDATE, nErrNum, sErrMsg, c_sLogPosSchema, c_sLogPosPkg || '.' || sLogPosProc || '->' || sLogPosSeq);
    '		RETURN nErrNum;
    '	WHEN OTHERS THEN
    '		nErrNum := SQLCODE;
    '    sErrMsg := SUBSTR(SQLERRM, 1, 100);
    '    INSERT INTO CICSYS.CIC_ERRORLOG (TS, ERR_NUM, ERR_MSG, USERSCHEMA, POSITION, DUMP_INFO) VALUES (SYSDATE, nErrNum, sErrMsg, c_sLogPosSchema, c_sLogPosPkg || '.' || sLogPosProc || '->' || sLogPosSeq, sQuery);
    '		RETURN nErrNum;
  End Sub

Zurzeit ist mein Problem, das ich nicht weiss wie ich die Insert/Updates in dieser Prozedur und den aufgerufenen Zurückdrehen kann, sollte eine Fehlschlagen. Die Prozeduren nach dem .Close() benutzen jeweils ebenfalls die context connection und sind somit nicht mehr in derselben Transaktion.

Ich liebe Datenshredder ;)

MfG Dominik