|
|
Imports System
|
|
|
Imports System.Data
|
|
|
Imports System.Data.SqlTypes
|
|
|
Imports System.Data.SqlClient
|
|
|
|
|
|
Namespace TKB.VV.Sysadmin
|
|
|
|
|
|
Public Class DomainTable
|
|
|
|
|
|
#Region "Deklarationen"
|
|
|
|
|
|
Dim UpdateCommand As New SqlCommand
|
|
|
Dim InsertCommand As New SqlCommand
|
|
|
Dim selectcommand As New SqlCommand
|
|
|
Dim BaseData As New DataSet
|
|
|
Dim connection As New SqlConnection()
|
|
|
Dim da As New SqlDataAdapter("", connection)
|
|
|
|
|
|
Dim m_tablename As String
|
|
|
Property Tablename() As String
|
|
|
Get
|
|
|
Return m_tablename
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_tablename = value
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
Dim m_selectproc As String
|
|
|
Property Select_Proc() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_selectall"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_selectall"
|
|
|
End Set
|
|
|
End Property
|
|
|
Property Update_Proc() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_Update"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_Update"
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
Property Insert_Proc() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_Insert"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_Update"
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
Property Select_Proc_Bottomtable() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_selectall_bottomtable"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_selectall_bottomtable"
|
|
|
End Set
|
|
|
End Property
|
|
|
Property Update_Proc_Bottomtable() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_Update_bottomtable"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_Update_bottomtable"
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
Property Insert_Proc_Bottomtable() As String
|
|
|
Get
|
|
|
Return "pr_" & Tablename & "_Insert_bottomtable"
|
|
|
End Get
|
|
|
Set(ByVal value As String)
|
|
|
m_selectproc = "pr_" & Tablename & "_Update_bottomtable"
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
Dim m_Tabledata As New DataSet
|
|
|
Property Tabledata() As DataSet
|
|
|
Get
|
|
|
Return m_Tabledata
|
|
|
End Get
|
|
|
Set(ByVal value As DataSet)
|
|
|
m_Tabledata = value
|
|
|
End Set
|
|
|
End Property
|
|
|
|
|
|
#End Region
|
|
|
|
|
|
''' <summary>
|
|
|
''' Tabellenname <20>bernehmen und Daten ab DB laden
|
|
|
''' </summary>
|
|
|
''' <param name="tablename"></param>
|
|
|
''' <remarks></remarks>
|
|
|
Sub New(ByVal tablename As String, Optional ByVal Fokus As Integer = 0, Optional ByVal Keyvalue As String = "")
|
|
|
Me.Tablename = tablename
|
|
|
If Keyvalue = "" Then
|
|
|
Load_Data()
|
|
|
Else
|
|
|
' Load_Bootom_Table(tablename, Fokus, Keyvalue)
|
|
|
End If
|
|
|
End Sub
|
|
|
|
|
|
Sub New(ByVal tablename As String, ByVal Fokus As Integer, ByVal Keyvalue As String, ByVal mitarbeiternr As Integer)
|
|
|
Me.Tablename = tablename
|
|
|
If Keyvalue = "" Then
|
|
|
Load_Data()
|
|
|
Else
|
|
|
Load_Data_MA_Fokus(tablename, Fokus, Keyvalue, mitarbeiternr)
|
|
|
End If
|
|
|
End Sub
|
|
|
|
|
|
|
|
|
Sub New(ByVal tablename As String, ByVal keyvalue As String, ByVal mitarbeiternr As Integer)
|
|
|
Me.Tablename = tablename
|
|
|
Load_Data_MA(tablename, keyvalue, mitarbeiternr)
|
|
|
End Sub
|
|
|
|
|
|
Public Sub Load_Data_MA(ByVal tablename As String, ByVal KeyValue As String, ByVal mitarbeiternr As Integer)
|
|
|
Tabledata.Tables.Clear()
|
|
|
Dim sqlcmd As New SqlCommand
|
|
|
sqlcmd.CommandText = Me.Select_Proc_Bottomtable
|
|
|
sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
|
|
|
sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
|
|
|
sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
|
|
|
sqlcmd.Parameters.Add("@mitarbeiternr", SqlDbType.VarChar, 255)
|
|
|
sqlcmd.Parameters(0).Value = 0
|
|
|
sqlcmd.Parameters(1).Value = 0
|
|
|
sqlcmd.Parameters(2).Value = KeyValue
|
|
|
sqlcmd.Parameters(3).Value = 1
|
|
|
|
|
|
sqlcmd.CommandType = CommandType.StoredProcedure
|
|
|
sqlcmd.Connection = connection
|
|
|
Try
|
|
|
connection.ConnectionString = globals.connstr
|
|
|
connection.Open()
|
|
|
da.SelectCommand = sqlcmd
|
|
|
da.Fill(Tabledata, "Domaintable")
|
|
|
Catch ex As Exception
|
|
|
Finally
|
|
|
connection.Close()
|
|
|
da.Dispose()
|
|
|
sqlcmd.Dispose()
|
|
|
End Try
|
|
|
End Sub
|
|
|
|
|
|
Public Sub Load_Data_MA_Fokus(ByVal tablename As String, ByVal Fokus As Integer, ByVal KeyValue As String, ByVal mitarbeiternr As Integer)
|
|
|
Tabledata.Tables.Clear()
|
|
|
Dim sqlcmd As New SqlCommand
|
|
|
sqlcmd.CommandText = Me.Select_Proc_Bottomtable
|
|
|
sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
|
|
|
sqlcmd.Parameters.Add("@Fokus", SqlDbType.Int, 4)
|
|
|
sqlcmd.Parameters.Add("@KeyValue", SqlDbType.VarChar, 255)
|
|
|
sqlcmd.Parameters.Add("@mitarbeiternr", SqlDbType.VarChar, 255)
|
|
|
sqlcmd.Parameters(0).Value = 0
|
|
|
sqlcmd.Parameters(1).Value = Fokus
|
|
|
sqlcmd.Parameters(2).Value = KeyValue
|
|
|
sqlcmd.Parameters(3).Value = 1
|
|
|
|
|
|
sqlcmd.CommandType = CommandType.StoredProcedure
|
|
|
sqlcmd.Connection = connection
|
|
|
Try
|
|
|
connection.ConnectionString = globals.connstr
|
|
|
connection.Open()
|
|
|
da.SelectCommand = sqlcmd
|
|
|
da.Fill(Tabledata, "Domaintable")
|
|
|
Catch ex As Exception
|
|
|
Finally
|
|
|
connection.Close()
|
|
|
da.Dispose()
|
|
|
sqlcmd.Dispose()
|
|
|
End Try
|
|
|
End Sub
|
|
|
|
|
|
'Sub New()
|
|
|
'End Sub
|
|
|
''' <summary>
|
|
|
''' Daten ab Datenbank laden
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Public Overridable Sub Load_Data()
|
|
|
Tabledata.Tables.Clear()
|
|
|
|
|
|
selectcommand.CommandText = Me.Select_Proc
|
|
|
selectcommand.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
|
|
|
selectcommand.Parameters(0).Value = 0
|
|
|
|
|
|
selectcommand.CommandType = CommandType.StoredProcedure
|
|
|
selectcommand.Connection = connection
|
|
|
Try
|
|
|
connection.ConnectionString = globals.connstr
|
|
|
connection.Open()
|
|
|
da.SelectCommand = selectcommand
|
|
|
da.Fill(Tabledata, "Domaintable")
|
|
|
Catch ex As Exception
|
|
|
Finally
|
|
|
connection.Close()
|
|
|
da.Dispose()
|
|
|
selectcommand.Dispose()
|
|
|
End Try
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Basis-Datentabelle laden. Diese wird f<>r die dynamische Generierung der Insert- und Update-Statements ben<65>tigt
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Load_BaseData()
|
|
|
BaseData.Tables.Clear()
|
|
|
Dim sqlcmd As New SqlCommand
|
|
|
|
|
|
sqlcmd.CommandText = Me.Select_Proc
|
|
|
sqlcmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4)
|
|
|
sqlcmd.Parameters(0).Value = 0
|
|
|
|
|
|
sqlcmd.CommandType = CommandType.StoredProcedure
|
|
|
sqlcmd.Connection = connection
|
|
|
Try
|
|
|
connection.ConnectionString = globals.connstr
|
|
|
connection.Open()
|
|
|
da.SelectCommand = sqlcmd
|
|
|
da.Fill(BaseData, "Basedata")
|
|
|
Catch ex As Exception
|
|
|
Finally
|
|
|
connection.Close()
|
|
|
da.Dispose()
|
|
|
sqlcmd.Dispose()
|
|
|
End Try
|
|
|
End Sub
|
|
|
|
|
|
''' <summary>
|
|
|
''' Update-Statement dynamisch f<>r das UpdateCommand generieren
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Generate_Update_Statement()
|
|
|
Dim col As DataColumn
|
|
|
Dim col1 As DataColumn
|
|
|
Dim UseCol As Boolean = False
|
|
|
|
|
|
UpdateCommand.CommandText = Me.Update_Proc
|
|
|
UpdateCommand.CommandType = System.Data.CommandType.StoredProcedure
|
|
|
UpdateCommand.Connection = connection
|
|
|
|
|
|
UpdateCommand.Parameters.Clear()
|
|
|
For Each col In Me.Tabledata.Tables(0).Columns
|
|
|
UseCol = False
|
|
|
For Each col1 In Me.BaseData.Tables(0).Columns
|
|
|
If col.ColumnName = col1.ColumnName Then
|
|
|
UseCol = True
|
|
|
Exit For
|
|
|
End If
|
|
|
Next
|
|
|
If UseCol Then UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter(Get_Prefix(col) & col.ColumnName, Get_SqlDBType(col), Get_Data_Fieldlen(col), col.ColumnName))
|
|
|
Next
|
|
|
UpdateCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iErrorcode", SqlDbType.Int, 4))
|
|
|
UpdateCommand.Parameters("@iErrorcode").Value = 0
|
|
|
da.UpdateCommand = UpdateCommand
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Insert-Statement dynamisch f<>r das InsertCommand generieren
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Private Sub Generate_Insert_Statement()
|
|
|
Dim col As DataColumn
|
|
|
Dim col1 As DataColumn
|
|
|
Dim UseCol As Boolean = False
|
|
|
|
|
|
InsertCommand.CommandText = Me.Insert_Proc
|
|
|
InsertCommand.CommandType = System.Data.CommandType.StoredProcedure
|
|
|
InsertCommand.Connection = connection
|
|
|
|
|
|
InsertCommand.Parameters.Clear()
|
|
|
For Each col In Me.Tabledata.Tables(0).Columns
|
|
|
UseCol = False
|
|
|
For Each col1 In Me.BaseData.Tables(0).Columns
|
|
|
If col.ColumnName = col1.ColumnName Then
|
|
|
UseCol = True
|
|
|
Exit For
|
|
|
End If
|
|
|
Next
|
|
|
If UseCol Then InsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter(Get_Prefix(col) & col.ColumnName, Get_SqlDBType(col), Get_Data_Fieldlen(col), col.ColumnName))
|
|
|
Next
|
|
|
InsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@iErrorcode", SqlDbType.Int, 4))
|
|
|
InsertCommand.Parameters("@iErrorcode").Value = 0
|
|
|
da.InsertCommand = InsertCommand
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Prefixt f<>r den SP-<2D>bergabeparameter generieren
|
|
|
''' </summary>
|
|
|
''' <param name="col">Aktuelle Columnt</param>
|
|
|
''' <returns>Prefis f<>r SP-<2D>bergabeparameter</returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function Get_Prefix(ByVal col As DataColumn) As String
|
|
|
If col.DataType.Name = "DateTime" Then Return "@da"
|
|
|
If col.DataType.Name = "Double" Then Return "@f"
|
|
|
Return "@" & col.DataType.Name.Substring(0, 1)
|
|
|
End Function
|
|
|
''' <summary>
|
|
|
''' SQL-DB-Type f<>r den SP-<2D>bergabeparameter festlegen
|
|
|
''' </summary>
|
|
|
''' <param name="col">Aktuelle Column</param>
|
|
|
''' <returns>SQLDBType</returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function Get_SqlDBType(ByVal col As DataColumn) As SqlDbType
|
|
|
If col.DataType.Name = "Integer" Then Return SqlDbType.Int
|
|
|
If col.DataType.Name = "Int32" Then Return SqlDbType.Int
|
|
|
If col.DataType.Name = "String" Then Return SqlDbType.VarChar
|
|
|
If col.DataType.Name = "Boolean" Then Return SqlDbType.Bit
|
|
|
If col.DataType.Name = "DateTime" Then Return SqlDbType.DateTime
|
|
|
If col.DataType.Name = "Double" Then Return SqlDbType.Float
|
|
|
MsgBox(col.DataType.Name)
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Feldl<64>nge f<>r den SP-<2D>bergabeparemter festlegen
|
|
|
''' </summary>
|
|
|
''' <param name="col">Aktulle Column</param>
|
|
|
''' <returns>Feldl<64>nge</returns>
|
|
|
''' <remarks></remarks>
|
|
|
Private Function Get_Data_Fieldlen(ByVal col As DataColumn) As Integer
|
|
|
Return col.MaxLength
|
|
|
End Function
|
|
|
|
|
|
''' <summary>
|
|
|
''' Datesichern. Dabei wird das Update- sowie das Insert-Statement dynamisch generiert
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Public Sub Save_Data()
|
|
|
Load_BaseData()
|
|
|
Generate_Update_Statement()
|
|
|
Generate_Insert_Statement()
|
|
|
Try
|
|
|
da.Update(Me.Tabledata, Me.Tabledata.Tables(0).TableName)
|
|
|
|
|
|
Catch ex As Exception
|
|
|
MsgBox(ex.Message)
|
|
|
End Try
|
|
|
End Sub
|
|
|
''' <summary>
|
|
|
''' Dispose von Tabledata
|
|
|
''' </summary>
|
|
|
''' <remarks></remarks>
|
|
|
Public Sub dispose()
|
|
|
Me.Tabledata.Dispose()
|
|
|
End Sub
|
|
|
|
|
|
End Class
|
|
|
|
|
|
End Namespace
|
