|
Databank bewerkingen
Posted: 28 Oct 2009 02:35 PM |
Omdat ik vanuit verschillende pagina's nogal wat databank bewerkingen moet uitvoeren heb ik de meest voorkomende zaken even in een Class gezet. Op zich is dit niks nieuws, maar bij veelvuldig gebruik van de databank scheelt het wel een hoop typwerk.
Je kan de code als volgt aanroepen:
Dim ds as dataset = AppTools.SQLInstr.GetDataSet("mijndb", "SELECT * FROM MyTable WHERE ...")
Voor het uitvoeren van een actiequery declareer je bijkomend een dictionary voor de queryparameters:
vb:
Dim parameters as new dictionary(string, string)
parameters.add("@text", "hallo allemaal")
Apptools.sqlinstr.runactionquery("mijndb", "DELETE * FROM MyTable WHERE [Text]=@text;", parameters)
Vergeet niet om de connstring aan te passen naar je eigen behoefte...
Veel plezier ermee
de class:
-----------
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Namespace AppTools
Public Class SQLInstr
Public Shared conn As OleDbConnection
Const connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\projecten\websites\limos-vzw.be\utilities\db\FILENAME.mdb;Persist Security Info=False"
Public Shared Function GetDataSet(ByVal db As String, ByVal sqlcommand As String) As DataSet
Dim ds As New DataSet()
Dim cmd As OleDbCommand
Dim adapter As OleDbDataAdapter
Try
conn = New OleDbConnection(Replace(connstring, "FILENAME", db))
cmd = New OleDbCommand(sqlcommand, conn)
conn.Open()
adapter = New OleDbDataAdapter(cmd)
adapter.Fill(ds)
Catch ex As Exception
WriteExeptionsToEventLog(ex.Message, "AppTools.SQLInstr.GetDataSet")
Finally
conn.Close()
End Try
Return ds
End Function
Public Shared Function GetString(ByVal db As String, ByVal sqlcommand As String) As String
Dim str As String = ""
Dim conn As OleDbConnection = Nothing
Dim cmd As OleDbCommand
Try
conn = New OleDbConnection(Replace(connstring, "FILENAME", db))
cmd = New OleDbCommand(sqlcommand, conn)
conn.Open()
str = IIf(DBNull.Value.Equals(cmd.ExecuteScalar), String.Empty, cmd.ExecuteScalar)
Catch ex As Exception
WriteExeptionsToEventLog(ex.Message, "AppTools.SQLInstr.GetString")
Finally
conn.Close()
End Try
Return str
End Function
Public Shared Function CheckIfDataExists(ByVal db As String, ByVal sqlcommand As String) As Boolean
Dim mybool As Boolean = False
Dim conn As OleDbConnection = Nothing
Dim cmd As OleDbCommand
Try
conn = New OleDbConnection(Replace(connstring, "FILENAME", db))
cmd = New OleDbCommand(sqlcommand, conn)
conn.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
If reader.HasRows Then mybool = True
reader.Close()
Catch ex As Exception
WriteExeptionsToEventLog(ex.Message, "AppTools.SQLInstr.CheckIfDataExists")
Finally
conn.Close()
End Try
Return mybool
End Function
Public Shared Sub RunActionQuery(ByVal db As String, ByVal sqlcommand As String, ByVal Parameters As Dictionary(Of String, String))
Dim conn As OleDbConnection = Nothing
Dim cmd As OleDbCommand
Dim kvp As KeyValuePair(Of String, String)
Try
conn = New OleDbConnection(Replace(connstring, "FILENAME", db))
cmd = New OleDbCommand(sqlcommand, conn)
For Each kvp In Parameters
cmd.Parameters.AddWithValue(kvp.Key, kvp.Value)
Next
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
WriteExeptionsToEventLog(ex.Message & ": " & cmd.CommandText.ToString, "AppTools.SQLInstr.RunActionQuery")
Finally
conn.Close()
End Try
End Sub
Public Shared Sub WriteExeptionsToEventLog(ByVal exMsg As String, ByVal routine As String)
Dim conn As OleDbConnection = Nothing
Dim cmd As OleDbCommand
Dim sqlcommand As String = "INSERT INTO tblScriptErrors ([datum], [url], [module], [foutmelding], [ipadres], [userid], [browserinfo]) VALUES (@datum, @url, @module, @foutmelding, @ipadres, @userid, @browserinfo);"
Dim ipaddress As String
ipaddress = HttpContext.Current.Request.ServerVariables("HTTP_X_FORWARDED_FOR")
If ipaddress = "" Or ipaddress Is Nothing Then
ipaddress = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
End If
Dim s As String = ""
With HttpContext.Current.Request.Browser
s &= "Type = " & .Type & "<br />"
s &= "Name = " & .Browser & "<br />"
s &= "Version = " & .Version & "<br />"
s &= "Major Version = " & .MajorVersion & "<br />"
s &= "Minor Version = " & .MinorVersion & "<br />"
s &= "Platform = " & .Platform & "<br />"
s &= "Is Beta = " & .Beta & "<br />"
s &= "Is Crawler = " & .Crawler & "<br />"
s &= "Is AOL = " & .AOL & "<br />"
s &= "Is Win16 = " & .Win16 & "<br />"
s &= "Is Win32 = " & .Win32 & "<br />"
s &= "Supports Frames = " & .Frames & "<br />"
s &= "Supports Tables = " & .Tables & "<br />"
s &= "Supports Cookies = " & .Cookies & "<br />"
s &= "Supports VBScript = " & .VBScript & "<br />"
s &= "Supports JavaScript = " & .EcmaScriptVersion.ToString() & "<br />"
s &= "Supports Java Applets = " & .JavaApplets & "<br />"
s &= "Supports ActiveX Controls = " & .ActiveXControls
End With
Try
conn = New OleDbConnection(Replace(connstring, "FILENAME", "dbfouten"))
cmd = New OleDbCommand(sqlcommand, conn)
cmd.Parameters.AddWithValue("@datum", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"))
cmd.Parameters.AddWithValue("@url", HttpContext.Current.Request.RawUrl)
cmd.Parameters.AddWithValue("@module", routine)
cmd.Parameters.AddWithValue("@foutmelding", exMsg)
cmd.Parameters.AddWithValue("@ipadres", ipaddress)
cmd.Parameters.AddWithValue("@userid", IIf(HttpContext.Current.Session("userid") Is Nothing, DBNull.Value, HttpContext.Current.Session("userid")))
cmd.Parameters.AddWithValue("@browserinfo", s)
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Finally
conn.Close()
End Try
End Sub
End Class
|
't is niet omdat jij het antwoord kent dat het daarom voor jezelf moet houden... |
|
 |
|