' BankDb.vb

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Xml
'Imports System.Data.SqlTypes

Module BankDb
	Private connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
	  & "Data Source=C:\OI\Databases\SimpleBank.mdb"
	Private conn As IDbConnection
	Private jetConn As New OleDbConnection()
	Private adapter As IDbDataAdapter
	Private ds As New DataSet()

	Sub Main()
		conn = jetConn
		conn.ConnectionString = connStr
		Console.WriteLine("Using Access to access SimpleBank.mdb")
		Console.WriteLine("Database state: " & conn.State.ToString())
		CommandLoop()
	End Sub

	Private Sub OpenDb()
		conn.Open()
		Console.WriteLine("Database state: " & conn.State.ToString())
	End Sub

	Private Sub CloseDb()
		conn.Close()
		Console.WriteLine("Database state: " & conn.State.ToString())
	End Sub

	Private Sub CommandLoop()
		Dim iw As New InputWrapper()
		Dim cmd As String
		Dim buf As String
		Dim index As Integer
		Console.WriteLine("Enter command, quit to exit")
		cmd = iw.getString("> ")
		While Not cmd.Equals("quit")
			Try
				If cmd.Equals("fill") Then
					FillDataSet()
				ElseIf cmd.Equals("show") Then
					ShowAccountsDs()
				ElseIf cmd.Equals("add") Then
					Dim id As Integer = iw.getInt("id: ")
					Dim owner As String = iw.getString("owner: ")
					Dim bal As Decimal = iw.getDecimal("balance: ")
					AddAccountDs(bal, owner, id)
				ElseIf cmd.Equals("remove") Then
					Dim id As Integer = iw.getInt("id: ")
					DeleteAccountDs(id)
				ElseIf cmd.Equals("change") Then
					Dim id As Integer = iw.getInt("id: ")
					Dim owner As String = iw.getString("new owner: ")
					ChangeOwnerDs(owner, id)
				ElseIf cmd.Equals("update") Then
					UpdateAccount()
				ElseIf cmd.Equals("showdb") Then
					ShowAccountsDb()
				ElseIf cmd.Equals("adddb") Then
					Dim id As Integer = iw.getInt("id: ")
					Dim owner As String = iw.getString("owner: ")
					Dim bal As Decimal = iw.getDecimal("balance: ")
					AddAccountDb(bal, owner, id)
				ElseIf cmd.Equals("close") Then
					CloseDb()
				Else
					Help()
				End If
			Catch e As Exception
				Console.WriteLine(e.Message)
				If Not e.InnerException Is Nothing Then
					Console.WriteLine(e.InnerException.Message)
				End If
			End Try
			cmd = iw.getString("> ")
		End While
	End Sub

	Private Sub Help()
		Console.WriteLine("The following commands are available:")
		Console.WriteLine("  fill     -- fill dataset")
		Console.WriteLine("  show     -- show accounts in dataset")
		Console.WriteLine("  add      -- add an account to dataset")
		Console.WriteLine("  remove   -- remove an account from dataset")
		Console.WriteLine("  change   -- change owner in dataset")
		Console.WriteLine("  update   -- update database from dataset")
		Console.WriteLine("  showdb   -- show accounts in database")
		Console.WriteLine("  adddb    -- add an account to database")
		Console.WriteLine("  close    -- close the database")
		Console.WriteLine("  quit     -- exit the program")
	End Sub

	Private Sub FillDataSet()
		OpenDb()
		Dim query As String = "select * from Account"
		adapter = CreateAdapter(query)
		adapter.Fill(ds)
		CloseDb()
	End Sub

	Private Sub ShowAccountsDs()
		Dim dt As DataTable = ds.Tables("Table")
		Dim row As DataRow
		For Each row In dt.Rows
			Console.WriteLine("{0}  {1,-10}  {2:C}", _
			   row("AccountId"), row("Owner"), row("Balance"))
		Next
	End Sub

	Private Sub AddAccountDs(ByVal bal As Decimal, _
	 ByVal owner As String, ByVal id As Integer)
		Dim dt As DataTable = ds.Tables("Table")
		Dim row As DataRow = dt.NewRow()
		row("AccountId") = id
		row("Owner") = owner
		row("Balance") = bal
		dt.Rows.Add(row)
	End Sub

	Private Sub DeleteAccountDs(ByVal id As Integer)
		Dim dt As DataTable = ds.Tables("Table")
		Dim rows() As DataRow = dt.Select("AccountId = " & id)
		rows(0).Delete()
	End Sub

	Private Sub ChangeOwnerDs(ByVal owner As String, ByVal id As Integer)
		Dim dt As DataTable = ds.Tables("Table")
		Dim rows() As DataRow = dt.Select("AccountId = " & id)
		rows(0)("Owner") = owner
	End Sub

	Private Sub UpdateAccount()
		OpenDb()
		Dim numrow As Integer
		numrow = adapter.Update(ds)
		Console.WriteLine("{0} rows updated", numrow)
		CloseDb()
	End Sub

	Private Sub ShowAccountsDb()
		OpenDb()
		Dim query As String = "select * from Account"
		Dim command As IDbCommand = CreateCommand(query)
		Dim reader As IDataReader = command.ExecuteReader()
		While reader.Read()
			Console.WriteLine("{0}  {1,-10}  {2:C}", _
			   reader("AccountId"), reader("Owner"), reader("Balance"))
		End While
		reader.Close()
		CloseDb()
	End Sub

	Private Sub AddAccountDb(ByVal bal As Decimal, _
	 ByVal owner As String, ByVal id As Integer)
		OpenDb()
		Dim query As String = "insert into Account values(" _
		 & id & ", '" & owner & "', ''," & bal & ")"
		Dim command As IDbCommand = CreateCommand(query)
		Dim numrow As Integer = command.ExecuteNonQuery()
		Console.WriteLine("{0} rows updated", numrow)
		CloseDb()
	End Sub

	Private Function CreateCommand(ByVal query As String) As IDbCommand
		Return New OleDbCommand(query, jetConn)
	End Function

	Private Function CreateAdapter(ByVal query As String) As IDbDataAdapter
		Dim adapter As New OleDbDataAdapter(query, connStr)
		Dim builder As New OleDbCommandBuilder(adapter)
		ShowCommand(builder.GetDeleteCommand)
		ShowCommand(builder.GetInsertCommand)
		ShowCommand(builder.GetUpdateCommand)
		Return adapter
	End Function

	Private Sub ShowCommand(ByVal command As OleDbCommand)
		Console.WriteLine(command.CommandText)
	End Sub
End Module