schooldata.vb

An example to accompany PCW March 2004 Visual Programming Hands On. To use, open the solution in

Public Class SchoolData

	Dim mDataPath As String

	Public Shared DataModule As SchoolData

	Private Function GetConnection() As OleDb.OleDbConnection
		'return a new connection to the database

		Return New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
		 & "Data Source=" & mDataPath & "\school.mdb")
	End Function

	Public Overloads Function GetPupils() As DataSet
		Return Me.GetPupils("lastname")
	End Function

	Public Overloads Function GetPupils(ByVal sortfield As String) As DataSet
		Dim conn As OleDb.OleDbConnection = GetConnection()
		Try
			Dim ds As New DataSet
			Dim sql As String = "select firstname,lastname,id from pupils order by " + sortfield
			Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)

			Try
				da.Fill(ds, "pupils")
			Finally
				da.Dispose()
			End Try

			Return ds
		Finally
			conn.Close()
			conn.Dispose()
		End Try

	End Function

	Public Function GetPupil(ByVal id As Integer) As DataSet
		'Return a dataset representing a single pupil
		Dim conn As OleDb.OleDbConnection = GetConnection()

		Try
			Dim sql As String = "Select * from pupils where id = " & id.ToString
			Dim sa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
			Dim ds As New DataSet

			Try
				sa.Fill(ds, "pupils")
			Finally
				sa.Dispose()
			End Try
			Return ds

		Finally
			conn.Close()
			conn.Dispose()
		End Try

	End Function

	Public Function GetNewPupil() As DataSet

		'Return a dataset representing a single pupil
		Dim conn As OleDb.OleDbConnection = GetConnection()

		Try
			Dim sql As String = "Select * from pupils where id = -1"
			Dim sa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)
			Dim ds As New DataSet

			Try
				sa.Fill(ds, "pupils")		 ' returns an empty dataset but with the correct structure
				Dim dr As DataRow = ds.Tables(0).NewRow		 ' creates a new blank row
				ds.Tables(0).Rows.Add(dr)		 ' add the blank row to the dataset
			Finally
				sa.Dispose()
			End Try
			Return ds		' return the dataset containing one new, blank pupil

		Finally
			conn.Close()
			conn.Dispose()
		End Try

	End Function

	Public Sub SavePupils(ByVal ds As DataSet)

		Dim conn As OleDb.OleDbConnection = GetConnection()

		Try
			Dim sql As String = "select firstname,lastname,id from pupils"
			Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)

			Try
				Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
				If ds.HasChanges Then
					da.Update(ds, "pupils")
					ds.AcceptChanges()
				End If
			Finally
				da.Dispose()
			End Try

		Finally
			conn.Close()
			conn.Dispose()
		End Try

	End Sub

	Public Sub SavePupil(ByVal ds As DataSet)
		'Update a dataset representing pupils
		Dim conn As OleDb.OleDbConnection = GetConnection()

		Try
			Dim sql As String = "Select * from pupils"
			Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, conn)

			Try
				Dim cb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
				If ds.HasChanges Then
					da.Update(ds, "pupils")
					ds.AcceptChanges()
				End If
			Finally
				da.Dispose()
			End Try

		Finally
			conn.Close()
			conn.Dispose()
		End Try

	End Sub


	Public Sub New(ByVal sDatapath As String)
		MyBase.new()
		Me.mDataPath = sDatapath
		SchoolData.DataModule = Me
	End Sub
End Class