Datenmanipulation in CLR Triggern

02/03/2009 - 16:28 von Lutz Uhlmann | Report spam
Hallo NG !

Irgendwie komm ich nicht weiter!
Ich habe hier Austauschtabellen, in die mir eine fremde Anwendung Datensàtze
reinschreibt (auch gern mehrere pro Insert).
Auf diesen Insert (spàter auch Update) reagier ich mit einem AFTER INSERT
Trigger den ich des Umfangs wegen im Visual Studio 2008 per VB schreibe.

Den Trigger bekomme ich prinzipiell angelegt:
'<Microsoft.SqlServer.Server.SqlTrigger(Name:="TR_AIU_DB_AT_INVENTAR",
Target:="schTrans.DB_AT_INVENTAR", Event:="AFTER INSERT, UPDATE")> _
Public Shared Sub TR_AIU_DB_AT_INVENTAR()
...
End Sub
Und dann per Postdeploy registriert weil in einem anderen Schema:
CREATE TRIGGER [schTrans].[TR_AIU_DB_AT_INVENTAR] ON
[schTrans].[DB_AT_INVENTAR]
AFTER INSERT, UPDATE AS
EXTERNAL NAME [Proj].[Proj.clsSchnittstellen].[TR_AIU_DB_AT_INVENTAR]
GO

Soweit so gut. Allerdings weiß ich jetzt nicht wie ich innerhalb der
Funktion richtig vorgehen soll und welche Klassen etc ich verwenden soll.
gerade was die Connection, DataReader etc. angeht ...
Aufgabe des Triggers ist es.
- durch alle Datensàtze gehen
- für jeden Datensatz bestimmte Unter-Funktionen aufrufen, die Werte prüfen
und evtl entsprechende Datensàtze in anderen Tabellen anlegen
- jeden Datensatz als bearbeitet markiert (Status-Feld)

Probleme hab ich z.B. Wenn ich ein zweites DataReader-Objekt auf dem
Connection-Objekt öffne
(siehe: CheckMandantKst() Using rs1 As SqlDataReader =
sqlCmdRead.ExecuteReader)

Dann bekomme ich z.B. den Fehler
"There is already an open DataReader associated with this Command which must
be closed first."
Aber wie kann ich dann mehrere Recordsets auf meiner Conenction öffnen?
Andere Klassen ?
Bin in dem Bereich noch neu - vielleicht hat ja jemand dazu schon ne kleine
Sammlung an Code oder weiß wo ich sowas im Netz finde ...

' -
Public Shared Sub TR_AIU_DB_AT_INVENTAR()
...
Using conn As New SqlConnection("context connection=true")
conn.Open()

bRet = False

If triggContext.TriggerAction = TriggerAction.Insert Then
bRet = InventarInsert(conn)
ElseIf triggContext.TriggerAction = TriggerAction.Update Then
bRet = InventarUpdate(conn)
End If

End Using

End Sub
' -
Public Shared Function InventarInsert(ByRef conn As SqlConnection) As
Boolean
On Error GoTo Err_
...
InventarInsert = False

sqlP.Send("DB_AT_INVENTAR Insert check")

sqlComm = New SqlCommand("SELECT * from INSERTED WHERE BEA_STATUS=0",
conn)

Using re As SqlDataReader = sqlComm.ExecuteReader
While re.Read

sMANDANT = re.GetString(re.GetOrdinal("MANDANT"))
If Not re.IsDBNull(re.GetOrdinal("KST")) Then
nKST = re.GetInt32(re.GetOrdinal("KST"))
Else
nKST = 0
End If
sMandKST = CheckMandantKst(conn, sMANDANT, nKST)

If sMandKST = "" Then GoTo Exit_
End While
End Using

InventarInsert = True

Exit_:
Exit Function
Err_:
sqlPipe.Send(Err.Number & " - " & Err.Description)
Resume Exit_
End Function
' -
Public Shared Function CheckMandantKst(ByRef conn As SqlConnection, ByVal
sChkMan As String, ByVal sChkKST As String) As String
On Error GoTo Err_

CheckMandantKst = ""

sSQL = "SELECT Mandant FROM schAnw.DB_MANDANT WHERE Mandant='" & sChkMan &
"'"
sqlCmdRead = New SqlCommand(sSQL, conn)
Using rs1 As SqlDataReader = sqlCmdRead.ExecuteReader
If rs1.HasRows Then
rs1.Read()
sMandant = rs1.GetString(0)
If sMandant.Length < 1 Then GoTo Exit_
Else
sMandant = sChkMan
nMandID = GetAnwID(conn, "ID", "DB_MANDANT")
If nMandID > 0 Then
sSQL = "INSERT INTO schAnw.DB_MANDANT (ID,MANDANT,MBEZ,KEN) " & _
"VALUES (" & nMandID & ",'" & sMandant & "','Mandant " &
sMandant & "',0)"
sqlCmdUpd = New SqlCommand(sSQL, conn)
If sqlCmdUpd.ExecuteNonQuery() <> 1 Then
GoTo Exit_
End If
Else
GoTo Exit_
End If
End If
End Using

sSQL = "SELECT KBEZ FROM schAnw.DB_RKST WHERE KBEZ='" & sChkKST & "'"
sqlCmdRead = New SqlCommand(sSQL, conn)
Using rs2 As SqlDataReader = sqlCmdRead.ExecuteReader

End Using

sSQL = "SELECT MANDKST FROM schAnw.DB_MANDKST WHERE MANDANT='" & sMandant
& "' AND RKST='" & sRKST & "'"
sqlCmdRead = New SqlCommand(sSQL, conn)
Using rs3 As SqlDataReader = sqlCmdRead.ExecuteReader
If rs3.HasRows Then
rs3.Read()
sMandKst = rs3.GetString(0)
If sMandKst.Length < 1 Then GoTo Exit_
Else
nMandKstID = GetAnwID(conn, "ID", "DB_MANDKST")
If nMandKstID > 0 Then
sSQL = "INSERT INTO schAnw.DB_MANDKST
(ID,MANDANT,RKST,MKBEZ,VERANTW,OE,KEN) " & _
"VALUES (" & nMandKstID & ",'" & sMandant & "','" & sRKST &
"','*','*','*',0)"
sqlCmdUpd = New SqlCommand(sSQL, conn)
If sqlCmdUpd.ExecuteNonQuery() <> 1 Then
GoTo Exit_
End If

sSQL = "SELECT MANDKST FROM schAnw.DB_MANDKST WHERE MANDANT='" &
sMandant & "' AND RKST='" & sRKST & "'"
sqlCmdRead2 = New SqlCommand(sSQL, conn)
Using rs4 As SqlDataReader = sqlCmdRead2.ExecuteReader
If rs4.HasRows Then
rs4.Read()
sMandKst = rs4.GetString(0)
If sMandKst.Length < 1 Then GoTo Exit_
Else
GoTo Exit_
End If
End Using
Else
GoTo Exit_
End If
End If
End Using

CheckMandantKst = sMandKst

Exit_:
Exit Function
Err_:
Resume Exit_
End Function
 

Lesen sie die antworten

#1 Elmar Boye
02/03/2009 - 17:03 | Warnen spam
Hallo Lutz,

"Lutz Uhlmann" schrieb ...
Irgendwie komm ich nicht weiter!
Ich habe hier Austauschtabellen, in die mir eine fremde Anwendung Datensàtze reinschreibt (auch gern mehrere pro Insert).
Auf diesen Insert (spàter auch Update) reagier ich mit einem AFTER INSERT Trigger den ich des Umfangs wegen im Visual Studio 2008
per VB schreibe.



Tue Dir einen Gefallen und verwende soweit möglich T-SQL dafür.
Nur wenn Du wirklich .NET funktionalitàt abseits von ADO.NET
benötigst, wàre CLR eine adàquate Wahl.

Soweit so gut. Allerdings weiß ich jetzt nicht wie ich innerhalb der Funktion richtig vorgehen soll und welche Klassen etc ich
verwenden soll. gerade was die Connection, DataReader etc. angeht ...
Aufgabe des Triggers ist es.
- durch alle Datensàtze gehen
- für jeden Datensatz bestimmte Unter-Funktionen aufrufen, die Werte prüfen und evtl entsprechende Datensàtze in anderen Tabellen
anlegen
- jeden Datensatz als bearbeitet markiert (Status-Feld)

Probleme hab ich z.B. Wenn ich ein zweites DataReader-Objekt auf dem Connection-Objekt öffne

"There is already an open DataReader associated with this Command which must be closed first."



Geht nicht. Denn MARS, was die einzige Alternative wàre,
ist für "context connection = true" nicht erlaubt.
Und mit weiteren Verbindungen bekommst Du Probleme,
was die Transaktion angeht.

Deswegen ist ein CLR Trigger an der Stelle nur mühsam realisierbar.
Wenn es nicht direkt mit INSERT/UPDATE/DELETE Anweisungen geht,
musst Du die benötigten Daten in eine Auflistung oder ein DataSet
übertragen. Danach die Änderungen Zeile für Zeile durchführen.

Was in der Summe den Trigger langsamer als ein T-SQL Pendant werden làßt.
Und hàufig einen deutlich höheren Programmieraufwand gegenüber
T-SQL bedeuten dürfte.

Unten noch mal einige kleinere Hinweise.

Public Shared Function InventarInsert(ByRef conn As SqlConnection) As Boolean
On Error GoTo Err_
...
InventarInsert = False

sqlP.Send("DB_AT_INVENTAR Insert check")

sqlComm = New SqlCommand("SELECT * from INSERTED WHERE BEA_STATUS=0", conn)



Hier könntest Du die Daten in eine DataTable lesen und
einen DataTableReader verwenden.
"*" sollte man wie immer vermeiden, hier kàme der
zusàtzliche Speicherbedarf hinzu, d. h. nur die Spalten
angeben, die in der Folge verwendet werden.

Using re As SqlDataReader = sqlComm.ExecuteReader
While re.Read

sMANDANT = re.GetString(re.GetOrdinal("MANDANT"))
If Not re.IsDBNull(re.GetOrdinal("KST")) Then
nKST = re.GetInt32(re.GetOrdinal("KST"))
Else
nKST = 0
End If



Verwende besser die SqlTypes Datentypen, oben SqlInt32.
Da muß weniger konvertiert werden und da diese INullable
implementieren, erhàlst ein Pendant zur SQL NULL. Mit
http://msdn.microsoft.com/de-de/lib...types.aspx
wird das auch vom SqlDataAdapter (für oben) unterstützt.

Public Shared Function CheckMandantKst(ByRef conn As SqlConnection, ByVal sChkMan As String, ByVal sChkKST As String) As String
On Error GoTo Err_



Bitte verwende Try Catch... das On Error hat allgemein nichts
in .NET verloren und Visual Basic trickst da im Hintergrund
nur unnötig rum (was den Trigger nicht schneller macht).



CheckMandantKst = ""

sSQL = "SELECT Mandant FROM schAnw.DB_MANDANT WHERE Mandant='" & sChkMan & "'"
sqlCmdRead = New SqlCommand(sSQL, conn)
Using rs1 As SqlDataReader = sqlCmdRead.ExecuteReader
If rs1.HasRows Then
rs1.Read()



Kürzer (und lesbarer ist
If rs1.Read() Then

denn die HasRow Prüfung ist im Prinzip überflüssig
und nur mal für ADO Classic Programmierer nachgereicht worden ;-)

sMandant = rs1.GetString(0)
If sMandant.Length < 1 Then GoTo Exit_




Mit Try Catch ... Finally (und/oder Using) braucht Du keine
Goto's mehr, da reicht "Return".
http://msdn.microsoft.com/de-de/lib...t46tz.aspx

Else
sMandant = sChkMan
nMandID = GetAnwID(conn, "ID", "DB_MANDANT")
If nMandID > 0 Then
sSQL = "INSERT INTO schAnw.DB_MANDANT (ID,MANDANT,MBEZ,KEN) " & _
"VALUES (" & nMandID & ",'" & sMandant & "','Mandant " & sMandant & "',0)"



Verwende generell Parameterisierte Befehle (mit exakten Làngenangaben
für Strings). So ist der Code nicht nur für SQL Injection anfàllig,
sondern blàht auch den Query Cache auf.

Wenn Du die Command Objekte vorab deklarierst, können sie wiederverwendet
werden, was den Ressourcen-Bedarf senkt und den Garbage Collector weniger
arbeiten làßt. (Was sich hàufig am besten in einer eigenen Klasse realisieren
làsst).

sSQL = "SELECT KBEZ FROM schAnw.DB_RKST WHERE KBEZ='" & sChkKST & "'"
sqlCmdRead = New SqlCommand(sSQL, conn)
Using rs2 As SqlDataReader = sqlCmdRead.ExecuteReader

End Using



Das oben gesagte wiederholt sich in der Folge.

Und beim Lesen habe ich nichts gesehen, was nicht mit T-SQL
ebensogut realisierbar wàre.

Gruß Elmar

Ähnliche fragen