' BankDb.vb

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module BankDb
	Private connStr As String = "server=localhost;uid=sa;pwd=;database=SimpleBank"
	Private conn As IDbConnection
	Private sqlConn As New SqlConnection()
	Private adapter As IDbDataAdapter
	Private ds As New DataSet()

	Sub Main()
		conn = sqlConn
		conn.ConnectionString = connStr
		Console.WriteLine("Using SQL Server to access SimpleBank")
		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 SqlCommand(query, sqlConn)
	End Function

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

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