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

' ConnectedSql.vb 
 
Imports System 
Imports System.Data 
Imports System.Data.SqlClient 
 
Module ConnectedSql 
	Private connStr As String = "server=localhost;uid=sa;pwd=;database=SimpleBank" 
	Private conn As IDbConnection 
	Dim sqlConn As New SqlConnection() 
	Sub Main() 
		OpenSql() 
		CommandLoop() 
	End Sub 
 
	Private Sub OpenSql() 
		conn = sqlConn 
		conn.ConnectionString = connStr 
		Console.WriteLine("Using SQL Server to access SimpleBank") 
		Console.WriteLine("Database state: " & conn.State.ToString()) 
		conn.Open() 
		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("show") Then 
					ShowList() 
				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: ") 
					AddAccount(bal, owner, id) 
				ElseIf cmd.Equals("remove") Then 
					Dim id As Integer = iw.getInt("id: ") 
					RemoveAccount(id) 
				ElseIf cmd.Equals("change") Then 
					Dim id As Integer = iw.getInt("id: ") 
					Dim owner As String = iw.getString("new owner: ") 
					ChangeAccount(id, owner) 
				ElseIf cmd.Equals("clear") Then 
					ClearAccounts() 
				ElseIf cmd.Equals("init") Then 
					AddAccount(100, "Bob", 1) 
					AddAccount(200, "Mary", 2) 
					AddAccount(300, "Charles", 3) 
				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("  show     -- show accounts in database") 
		Console.WriteLine("  add      -- add an account to database") 
		Console.WriteLine("  remove   -- remove an account from database") 
		Console.WriteLine("  change   -- change owner name") 
		Console.WriteLine("  clear    -- clear accounts from database") 
		Console.WriteLine("  init     -- initialize with starting accounts") 
		Console.WriteLine("  quit     -- exit the program") 
	End Sub 
 
	Private Sub ShowList() 
		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() 
	End Sub 
 
	Private Sub AddAccount(ByVal bal As Decimal, _ 
	 ByVal owner As String, ByVal id As Integer) 
		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) 
	End Sub 
 
	Private Sub RemoveAccount(ByVal id As Integer) 
		Dim query As String = "delete from Account where AccountId = " & id 
		Dim command As IDbCommand = CreateCommand(query) 
		Dim numrow As Integer = command.ExecuteNonQuery() 
		Console.WriteLine("{0} rows updated", numrow) 
	End Sub 
 
	Private Sub ChangeAccount(ByVal id As Integer, ByVal owner As String) 
		Dim query As String = "update Account set Owner = '" _ 
		 & owner & "' where AccountId = " & id 
		Dim command As IDbCommand = CreateCommand(query) 
		Dim numrow As Integer = command.ExecuteNonQuery() 
		Console.WriteLine("{0} rows updated", numrow) 
	End Sub 
 
	Private Sub ClearAccounts() 
		Dim query As String = "delete from Account" 
		Dim command As IDbCommand = CreateCommand(query) 
		Dim numrow As Integer = command.ExecuteNonQuery() 
		Console.WriteLine("{0} rows updated", numrow) 
	End Sub 
 
	Private Function CreateCommand(ByVal query As String) As IDbCommand 
		Return New SqlCommand(query, sqlConn) 
	End Function 
End Module