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