'<summary>
'Default constructor creates ODBCConnection and initialize object.
'</summary>
Public Sub New()
Dim cb As OdbcCommandBuilder
getODBCConnection()
da = New OdbcDataAdapter()
init()
cb = New OdbcCommandBuilder(CType(da, OdbcDataAdapter))
cb.RefreshSchema()
End Sub 'New
'<summary>
'Constructor uses external SqlConnection to initialize object.
'</summary>
Public Sub New(extDbConn As SqlConnection)
Dim cb As SqlCommandBuilder
dbconn = extDbConn
da = New SqlDataAdapter()
init()
cb = New SqlCommandBuilder(CType(da, SqlDataAdapter))
cb.RefreshSchema()
End Sub 'New
'<summary>
'Constructor uses external OleDbConnection to initialize object.
'</summary>
Public Sub New(extDbConn As OleDbConnection)
Dim cb As OleDbCommandBuilder
dbconn = extDbConn
da = New OleDbDataAdapter()
init()
cb = New OleDbCommandBuilder(CType(da, OleDbDataAdapter))
cb.RefreshSchema()
End Sub 'New
'<summary>
'Constructor uses external ODBCConnection to initialize object.
'</summary>
Public Sub New(extDbConn As OdbcConnection)
Dim cb As OdbcCommandBuilder
dbconn = extDbConn
da = New OdbcDataAdapter()
init()
cb = New OdbcCommandBuilder(CType(da, OdbcDataAdapter))
cb.RefreshSchema()
End Sub 'New
'<summary>
'Loads object properties and DataSet by primary key.
'</summary>
Public Sub load(dPaymentDate_PK As DateTime)
Try
'CType(da.SelectCommand.Parameters(0), IDataParameter).Value = i_CertID_PK
CType(da.SelectCommand.Parameters(0), IDataParameter).Value = dPaymentDate_PK
da.Fill(ds)
ds2object()
Catch e As Exception
handleException(e)
Return
End Try
End Sub 'load
'<summary>
'Updates the DataSet from the object
'properties and submit changes to the database.
'</summary>
Public Sub update()
Try
object2ds()
da.Update(ds)
Catch e As Exception
handleException(e)
Return
End Try
End Sub 'update
'<summary>
'Creates new record in the DataSet, populate it from object
'properties and submit changes to the database.
'</summary>
Public Sub insert()
Try
ds.Tables(0).Clear()
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow())
object2ds()
da.Update(ds)
Catch e As Exception
handleException(e)
Return
End Try
End Sub 'insert
'<summary>
'Populate Object properties from the DataSet.
'</summary>
Private Sub ds2object()
Dim row As DataRow = ds.Tables(0).Rows(0)
If row.IsNull("PaymentDate") Then
dPaymentDate = 0
Else
dPaymentDate = [DateTime].Parse(row("PaymentDate").ToString())
End If
If row.IsNull("InvoiceID") Then
nInvoiceID = 0
Else
nInvoiceID = [Double].Parse(row("InvoiceID").ToString())
End If
If row.IsNull("Amount") Then
nAmount = 0
Else
nAmount = [Double].Parse(row("Amount").ToString())
End If
sAmountCurrency = row("AmountCurrency").ToString()
If row.IsNull("PaymentType") Then
nPaymentType = 0
Else
nPaymentType = [Double].Parse(row("PaymentType").ToString())
End If
If row.IsNull("MonthID") Then
nMonthID = 0
Else
nMonthID = [Double].Parse(row("MonthID").ToString())
End If
If row.IsNull("Year") Then
nYear = 0
Else
nYear = [Double].Parse(row("Year").ToString())
End If
sNotes = row("Notes").ToString()
If row.IsNull("ContactID") Then
nContactID = 0
Else
nContactID = [Double].Parse(row("ContactID").ToString())
End If
If row.IsNull("PaymentID") Then
nPaymentID = 0
Else
nPaymentID = [Double].Parse(row("PaymentID").ToString())
End If
End Sub 'ds2object
'<summary>
'Populate DataSet from Object properties.
'</summary>
Private Sub object2ds()
Dim row As DataRow = ds.Tables(0).Rows(0)
row.BeginEdit()
row("PaymentDate") = dPaymentDate
row("InvoiceID") = nInvoiceID
row("Amount") = nAmount
row("AmountCurrency") = sAmountCurrency
row("PaymentType") = nPaymentType
row("MonthID") = nMonthID
row("Year") = nYear
row("Notes") = sNotes
row("ContactID") = nContactID
row("PaymentID") = nPaymentID
row.EndEdit()
End Sub 'object2ds
'<summary>
'Initialize commands.
'</summary>
Private Sub init()
ds = New DataSet("PAYMENTSDoc")
da.SelectCommand = dbconn.CreateCommand()
da.SelectCommand.CommandText = "SELECT * FROM Payments where PaymentDate = ? "
addParameter(da.SelectCommand, "@PaymentDate",DbType.Date, "PaymentDate")
End Sub 'init
'<summary>
'Add database parameter to the select command
'</summary>
Private Function addParameter(command As IDbCommand, _
parameterName As String, parameterType As DbType, _
sourceColumn As String) As IDbDataParameter
Dim param As IDbDataParameter = command.CreateParameter()
param.ParameterName = parameterName
param.DbType = parameterType
param.Direction = ParameterDirection.Input
param.SourceVersion = DataRowVersion.Current
param.SourceColumn = sourceColumn
command.Parameters.Add(param)
Return param
End Function 'addParameter
'<summary>
'Generate XML string from the current DataSet
'</summary>
Public Function getXml() As String
If Not (ds Is Nothing) Then
Return ds.GetXml()
Else
Return ""
End If
End Function 'getXml
'<summary>
'Set properties of the object from XML string
'</summary>
Public Sub setXml(sXml As String)
If Not (ds Is Nothing) Then
ds.ReadXml(New StringReader(sXml))
ds2object()
End If
End Sub 'setXml
'<summary>
'Get xml schema from current DataSet
'</summary>
Public Function getXmlSchema() As String
If Not (ds Is Nothing) Then
Return ds.GetXmlSchema()
Else
Return ""
End If
End Function 'getXmlSchema
'<summary>
'Get Database connection from OleDb connection string
'</summary>
Private Function getOleDbConnection() As IDbConnection
If dbconn Is Nothing Then
Try
dbconn = New OleDbConnection("PROVIDER=MSDASQL;UID=;PWD=;DSN=school;")
dbconn.Open()
Catch e As Exception
handleException(e)
Return Nothing
End Try
End If
Return dbconn
End Function 'getOleDbConnection
'<summary>
'Get Database connection from ODBC connection string
'</summary>
Private Function getODBCConnection() As IDbConnection
If dbconn Is Nothing Then
Try
dbconn = New OdbcConnection("PROVIDER=MSDASQL;UID=;PWD=;DSN=school;")
dbconn.Open()
Catch e As Exception
handleException(e)
Return Nothing
End Try
End If
Return dbconn
End Function 'getODBCConnection
'<summary>
'Overwrite this method with standard exception handling
'procedure for your application.
'</summary>
Private Sub handleException(e As Exception)
MessageBox.Show(e.ToString())
End Sub 'handleException
' <summary>
' Test Main method for the CERTIFICATION class.
' </summary>
'
' Shared<STAThread()> _
' Sub Main()
' Dim test As PAYMENTSClass;
' test = New CERTIFICATION( _
New ODBCConnection("PROVIDER=MSDASQL;UID=;PWD=;DSN=school;"))
' test.load(1)
' MessageBox.Show(test.getXml())
' End Sub 'Main
'
End Class 'PAYMENTSClass
End Namespace 'school
