Hilfsfunktionen zum Lesen von Daten aus einem SQL Server Datenbank
Wenn ich mit vb.Net auf einen SQL Server zugreife, dann verwende ich in der Regel eine public Zusatzklasse, in der ich alle Funktionen zum Zugriff
Auf den SQL Server gespeichert habe. Dadurch muß ich in der Codeseite nur noch per einfachem SQL-Befehl ein Recordset erstellen, ohne mich um die komplexe
Aufruf-Methodik des .Net Frameworks zu kümmern.
Hier der Beispiel-Code zur freien Verwendung.
Imports System.Data.SqlClient
Public Class clsDB
#Region "Connection"
'> aktueller Connectionstring >
Public Shared cnString As String = ConfigurationManager.ConnectionStrings(sys.DomainID).ConnectionString
'Public Shared cnString As String = System.Configuration.ConfigurationManager.AppSettings("connection")
'< aktueller Connectionstring <
#End Region
#Region "/Datenausgabe"
'===================================</ Datenausgabe >===================================
Public Shared Function getTableAdapter(ByVal sSQL As String) As SqlClient.SqlDataAdapter
Try
'---------< getTableAdapter >--------
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
Dim cmd As New SqlClient.SqlCommand(sSQL, cn)
Dim ta As New SqlClient.SqlDataAdapter(cmd)
Return ta
Catch ex As Exception
Return Nothing
End Try
'---------</ getTableAdapter >--------
End Function
|
Public Function getReader(ByVal sql As String) As SqlDataReader
'---------< getReader >--------
Try
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
'SELECT
Dim cmd As New SqlCommand(sql, cn)
cmd.CommandTimeout = 100000
Dim reader As SqlDataReader
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Return reader
Catch ex As Exception
Return Nothing
End Try
'---------</ getReader >--------
End Function
|
Public Shared Function executeSQL(ByVal sSQL As String) As Integer
'---------< executeSQL >--------
Try
' Ausfuehren eines update,delete,insert commands
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
Dim cmd As New SqlClient.SqlCommand(sSQL, cn)
Dim nRecords As Integer = cmd.ExecuteNonQuery
Return nRecords
Catch ex As Exception
Return Nothing
End Try
'---------</ executeSQL >--------
End Function
|
Public Shared Function getCount(ByVal sFeld As String, ByVal sTabelle As String, Optional ByVal sWhere As String = "") As Integer
'---------< getCount >--------
Try
' Ausfuehren eine Abfrage die Integer zurueckgibt
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
'SQL erstellen
Dim sSQL As String = "SELECT COUNT(" & sFeld & ") AS nCount FROM " & sTabelle
'Where
If Not sWhere Like "" Then
If Not sWhere Like "*WHERE*" Then sWhere = " WHERE " & sWhere
sSQL &= sWhere
End If
Dim cmd As New SqlClient.SqlCommand(sSQL, cn)
Dim nRecords As Integer = cmd.ExecuteScalar
Return nRecords
Catch ex As Exception
Return Nothing
End Try
'---------</ getCount >--------
End Function
|
Public Shared Function getValue(ByVal sFeld As String, ByVal sTabelle As String, Optional ByVal sWhere As String = "", Optional ByVal sOrder As String = "") As String
'---------< getValue >--------
Try
' Ersten Wert zurueckgeben
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
'SQL erstellen
Dim sSQL As String = "SELECT TOP 1 " & sFeld & " FROM " & sTabelle
'Where
If Not sWhere Like "" Then
If Not sWhere Like "*WHERE*" Then sWhere = " WHERE " & sWhere
sSQL &= sWhere
End If
'Order
If Not sOrder Like "" Then
If Not sOrder Like "*ORDER BY*" Then sOrder = " ORDER BY " & sOrder
sSQL &= sOrder
End If
Dim cmd As New SqlClient.SqlCommand(sSQL, cn)
cmd.CommandType = CommandType.Text
'< Rueckgabe als String >
Dim sReturn As String = dbText(cmd.ExecuteScalar)
'</ Rueckgabe als String >
Return sReturn
Catch ex As Exception
Return Nothing
End Try
'---------</ getValue >--------
End Function
|
Public Shared Function getDataset(ByVal sql As String) As DataSet
'---------< getDataset >--------
Try
' ein Dataset zurueckgeben
Dim cn As New SqlClient.SqlConnection(cnString)
If Not cn.State = ConnectionState.Open Then
cn.Open()
End If
'record oeffnen
Dim cmd As New SqlCommand(sql, cn)
cmd.CommandTimeout = 3600
Dim Adapter As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
'nur Tabellen oder Abfragen ohne parameter
Adapter.Fill(ds, sql) 'lade ds
'Rueckgabe
Return ds
'ABSCHLUSS
ds.Dispose()
Catch ex As Exception
Return Nothing
End Try
'---------</ getDataset >--------
End Function
|
Public Shared Function getDataTable(ByVal sql As String) As DataTable
'---------< getDataTable >--------
Try
' ein Dataset zurueckgeben
Dim ds As DataSet = getDataset(sql)
Dim dt As DataTable
dt = ds.Tables(0)
'Rueckgabe
Return dt
'ABSCHLUSS
ds.Dispose()
Catch ex As Exception
Return Nothing
End Try
'---------</ getDataTable >--------
End Function
|
Public Shared Function getDataRow(ByVal sql As String) As DataRow
'---------< getDataRow >--------
Try
Dim dt As DataTable = getDataTable(sql)
Dim r As DataRow = Nothing
If Not dt Is Nothing Then
If dt.Rows.Count > 0 Then
r = dt.Rows(0)
End If
End If
'Rueckgabe
Return r
'ABSCHLUSS
dt.Dispose()
Catch ex As Exception
Return Nothing
End Try
'---------</ getDataRow >--------
End Function
|
'Public Shared Function getAdoRecordset(ByVal sSQL As String) As ADODB.Recordset
' '---------< getAdoRecordset >--------
' Try
' ' einen Ado.Recordset zurueckgeben
' '< connection oeffnen >
' Dim cn As New ADODB.Connection
' cn.Provider = "SQLOLEDB"
' cn.ConnectionString = cnString
' cn.ConnectionTimeout = 1000
' cn.CursorLocation = ADODB.CursorLocationEnum.adUseServer
' cn.Mode = ADODB.ConnectModeEnum.adModeShareDenyNone
' cn.Open()
' '</ connection oeffnen >
' '< record oeffnen >
' Dim r As ADODB.Recordset = New ADODB.Recordset
' r.Open(sSQL, cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
' '</ record oeffnen >
' Return r
' Catch ex As Exception
' Return Nothing
' End Try
' '---------</ getAdoRecordset >--------
'End Function
|
'===================================</ Datenausgabe >===================================
#End Region
#Region "Execute"
Public Shared Function execute(ByVal sSQL As String) As Integer
Return executeSQL(sSQL)
End Function
#End Region
|
End Class
|