Module module_roosclass_System
Class roosclass_System_LockTabel
Public id As Integer
Public lockkey As String
Public lockuser As String
Public lockvalue As Integer
Public lockdate As Date
Public Sub LoadLockRecord(ByVal s_lockkey As String)
' --- Try to load the requested record
' return id=-1 if the key could not be found
Dim db_connection As SqlConnection
Dim db_command As SqlCommand
Dim datareader As SqlDataReader
db_connection = New SqlConnection(My.Settings.KToolsConnectionString)
db_connection.Open()
db_command = New SqlCommand("SELECT * FROM __Global_Locking WHERE lockkey='" & s_lockkey & "'", db_connection)
datareader = db_command.ExecuteReader
If datareader.HasRows = True Then
While datareader.Read
id = datareader("id").ToString
lockkey = datareader("lockkey").ToString
lockuser = datareader("lockuser").ToString
lockvalue = datareader("lockvalue").ToString
lockdate = datareader("lockdate").ToString
End While
Else
id = -1
End If
datareader.Close()
db_connection.Close()
End Sub
Public Sub SaveAdd()
' --- Add a new record to the table
Dim myCon As New OleDbConnection("Provider=SQLOLEDB;" & My.Settings.KToolsConnectionString)
Dim myCmd As New OleDbCommand()
With myCmd
.Connection = myCon
.CommandText = "INSERT INTO __Global_Locking (lockkey, lockuser, lockvalue, lockdate) " _
& "VALUES (?,?,?,?)"
.Parameters.Add("lockkey", OleDbType.VarWChar)
.Parameters.Add("lockuser", OleDbType.VarWChar)
.Parameters.Add("lockvalue", OleDbType.Integer)
.Parameters.Add("lockdate", OleDbType.Date)
.Parameters(0).Value = lockkey
.Parameters(1).Value = lockuser
.Parameters(2).Value = lockvalue
.Parameters(3).Value = lockdate
End With
myCon.Open()
myCmd.ExecuteNonQuery()
myCon.Close()
End Sub
Public Sub SaveUpdate()
' --- Update an existing record in the table
Dim myCon As New OleDbConnection("Provider=SQLOLEDB;" & My.Settings.KToolsConnectionString)
Dim myCmd As New OleDbCommand()
With myCmd
.Connection = myCon
.CommandText = "UPDATE __Global_Locking SET lockkey=?, lockuser=?, lockvalue=?, lockdate=? " _
& "WHERE id=" & id
.Parameters.Add("lockkey", OleDbType.VarWChar)
.Parameters.Add("lockuser", OleDbType.VarWChar)
.Parameters.Add("lockvalue", OleDbType.Integer)
.Parameters.Add("lockdate", OleDbType.Date)
.Parameters(0).Value = lockkey
.Parameters(1).Value = lockuser
.Parameters(2).Value = lockvalue
.Parameters(3).Value = lockdate
End With
myCon.Open()
myCmd.ExecuteNonQuery()
myCon.Close()
End Sub
End Class
End Module
Example of a minimal class capable of reading, adding and updating records
This code shows a minimal implementation of a class file that can be used to read, add and update records in an sql server table. All sql access is done with parameters to prevent problems with special characters and quotes.