129 lines
4.1 KiB
VB.net
129 lines
4.1 KiB
VB.net
Imports System.Data.SqlClient
|
|
|
|
Public Class cSQL
|
|
|
|
Public Shared Sub SQL2DS(ByRef selector As String, ByRef ds As DataSet, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = My.Resources.connStringDISPO
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect 'My.Resources.connStringDISPO
|
|
cmd.Connection = con
|
|
|
|
Dim dataadapter As New SqlDataAdapter(selector, con)
|
|
con.Open()
|
|
dataadapter.Fill(ds)
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub UpdateSQL(ByRef table As String, ByRef values As String, ByRef where As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = My.Resources.connStringDISPO
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' My.Resources.connStringDISPO
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "UPDATE " & table & " SET " & values & " WHERE " & where & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub InsertSQL(ByRef table As String, ByRef insert As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = My.Resources.connStringDISPO
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' My.Resources.connStringDISPO
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "INSERT INTO " & table & " " & insert & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub DeleteSQL(ByRef table As String, ByRef where As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = My.Resources.connStringDISPO
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' My.Resources.connStringDISPO
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = "DELETE FROM " & table & " WHERE " & where & ""
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Sub SQLCommand(command As String, Optional ByRef DBConnect As String = "")
|
|
If DBConnect = "" Then DBConnect = My.Resources.connStringDISPO
|
|
Dim con As New SqlConnection
|
|
Dim cmd As New SqlCommand
|
|
con.ConnectionString = DBConnect ' My.Resources.connStringDISPO
|
|
cmd.Connection = con
|
|
|
|
con.Open()
|
|
cmd.CommandText = command
|
|
cmd.ExecuteNonQuery()
|
|
con.Close()
|
|
End Sub
|
|
|
|
Public Shared Function getUpdateCmd(ByVal list As List(Of SQLVariable), ByVal WhereObject As String, ByRef update As String, ByRef where As String)
|
|
|
|
' Dim list As List(Of SQLVariable) = getparameterlist()
|
|
'Dim str As String
|
|
|
|
For Each i In list
|
|
If Not i.SQLText = WhereObject Then
|
|
update &= "[" & i.SQLText & "]='" & i.SQLValue & "',"
|
|
Else
|
|
where &= WhereObject & " = '" & i.SQLValue & "'"
|
|
End If
|
|
Next
|
|
update = update.Substring(0, update.Length - 1) 'wg. ','
|
|
' Return str
|
|
|
|
End Function
|
|
|
|
Public Shared Function getInsertCmd(ByVal list As List(Of SQLVariable), ByRef insert As String)
|
|
|
|
' Dim list As List(Of SQLVariable) = getparameterlist()
|
|
Dim text, value As String
|
|
|
|
For Each i In list
|
|
text &= i.SQLText & ", "
|
|
value &= "'" & i.SQLValue & "', "
|
|
Next
|
|
text = text.Substring(0, text.Length - 2)
|
|
value = value.Substring(0, value.Length - 2)
|
|
|
|
insert = "(" & text & ") VALUES (" & value & ")"
|
|
|
|
End Function
|
|
|
|
End Class
|
|
|
|
'Public Class SQLVariable
|
|
' Private Text, Value As String
|
|
' Private prim As Boolean
|
|
|
|
' Public Sub New(ByVal btext As String, ByVal bvalue As String, Optional bprim As Boolean = False)
|
|
' Me.Value = bvalue
|
|
' Me.Text = btext
|
|
|
|
' End Sub
|
|
|
|
' Public ReadOnly Property SQLText() As String
|
|
' Get
|
|
' Return Text
|
|
' End Get
|
|
' End Property
|
|
|
|
' Public ReadOnly Property SQLValue() As Object
|
|
' Get
|
|
' Return Value
|
|
' End Get
|
|
' End Property
|
|
|
|
'End Class
|
|
|