asked on
Public Class DoctorsData
Sub New(ByRef Conn As SqlClient.SqlConnection)
Dim cmdselect As String = "SELECT * FROM [RiskEx].[dbo].[Doctors]"
Dim cmddelete As String = "DELETE FROM [RiskEx].[dbo].[Doctors] WHERE LicenseID = ?"
Dim cmdupdate As String = "UPDATE [RiskEx].[dbo].[Doctors] SET LastName = ?, FirstName = ?, City = ?, Street = ?, ZipCode = ?, Comments = ? WHERE LicenseID = ?"
Dim cmdinsert As String = "INSERT INTO [RiskEx].[dbo].[Doctors] (LicenseID, LastName, FirstName, City, Street, ZipCode, Comments) VALUES (?, ?, ?, ?, ?, ? , ?) "
ds = New DataSet
selectcommand = New SqlClient.SqlCommand(cmdselect, Conn)
deletecommand = New SqlClient.SqlCommand(cmddelete, Conn)
With deletecommand.Parameters.Add("@p1", SqlDbType.NVarChar, 12)
.SourceColumn = "LicenseID"
.SourceVersion = DataRowVersion.Original
End With
updatecommand = New SqlClient.SqlCommand(cmdupdate, Conn)
'add arguments for SET clause
updatecommand.Parameters.Add("@p1", SqlDbType.NVarChar, 20, "LastName")
updatecommand.Parameters.Add("@p2", SqlDbType.NVarChar, 20, "FirstName")
updatecommand.Parameters.Add("@p3", SqlDbType.NVarChar, 30, "City")
updatecommand.Parameters.Add("@p4", SqlDbType.NVarChar, 30, "Street")
updatecommand.Parameters.Add("@p5", SqlDbType.NVarChar, 10, "ZipCode")
updatecommand.Parameters.Add("@p6", SqlDbType.NVarChar, 250, "Comments")
'add arguments for WHERE clause
updatecommand.Parameters.Add("@p4", SqlDbType.NVarChar, 12, "LicenseID").SourceVersion = DataRowVersion.Original
insertcommand = New SqlClient.SqlCommand(cmdinsert, Conn)
'add arguments for VALUES clause
insertcommand.Parameters.Add("@p1", SqlDbType.NVarChar, 12, "LicenseID")
insertcommand.Parameters.Add("@p2", SqlDbType.NVarChar, 20, "LastName")
insertcommand.Parameters.Add("@p3", SqlDbType.NVarChar, 20, "FirstName")
insertcommand.Parameters.Add("@p4", SqlDbType.NVarChar, 30, "City")
insertcommand.Parameters.Add("@p5", SqlDbType.NVarChar, 30, "Street")
insertcommand.Parameters.Add("@p6", SqlDbType.NVarChar, 10, "ZipCode")
insertcommand.Parameters.Add("@p7", SqlDbType.NVarChar, 250, "Comments")
da = New SqlClient.SqlDataAdapter()
cmdBuilder = New SqlClient.SqlCommandBuilder(da)
da.SelectCommand = selectcommand
da.DeleteCommand = deletecommand
da.UpdateCommand = updatecommand
da.InsertCommand = insertcommand
da.FillSchema(ds, SchemaType.Source, "Doctors")
da.Fill(ds, "Doctors")
End Sub
Public Sub Save()
da.Update(ds, "Doctors")
End Sub
'dataset
Public ds As DataSet
'current record
Public dr As DataRow
'data adapter
Public da As SqlClient.SqlDataAdapter
'sql commands
Public selectcommand As SqlClient.SqlCommand
Public deletecommand As SqlClient.SqlCommand
Public updatecommand As SqlClient.SqlCommand
Public insertcommand As SqlClient.SqlCommand
'command builder
Public cmdBuilder As SqlClient.SqlCommandBuilder
End Class