connectedsql.vb

用ADO.Net实现通用数据库编程源码.rar

' 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