www.pudn.com > 用ADO.Net实现通用数据库编程源码.rar > BankDb.vb

' 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