最近由于工作需要开始接触vb2010,也叫vb.net。相比vb6.0有面向对象编程的优势。同时接触一门新语言，要更快的实际应用起来，链接数据库是必不可少的。之前用vba写过一个售书工具，正好可以拿来改造成vb.net程序。同时考虑到面向对象编程，尽力使用MVC模式编程。其中链接数据库的部分被写在一个模块中，可以切换选择用ole直连oracle，或者用odbc连接dsn数据源。具体如下：
1.dao层新建一个模块
Option Explicit Off
Imports Microsoft.Data.Odbc
Module dao
Public conndsn As OdbcConnection
Public connole As OleDb.OleDbConnection
Public connectionString1 As String
Public connectionString2 As String = "Provider=MSDAORA;Data Source=xx;User ID=xx;Password=xx;"
Sub OdbcConnection()
connectionString1 = "DSN=sht1;UID=sheet;Pwd=sheet;"
Try
conndsn = New OdbcConnection(connectionString1)
conndsn.Open()
MsgBox("数据库连接成功!", MsgBoxStyle.OkOnly, "连接状态")
Catch ex As Exception
MsgBox("数据库连接发生错误!", MsgBoxStyle.OkOnly, "系统错误")
End
End Try
End Sub
Sub OleConnection()
Try
connole = New System.Data.OleDb.OleDbConnection(connectionString2)
connole.Open()
MsgBox("采用ole数据库连接成功!", MsgBoxStyle.OkOnly, "连接状态")
Catch ex As Exception
MsgBox("数据库连接发生错误!", MsgBoxStyle.OkOnly, "系统错误")
End
End Try
End Sub
Sub CloseConndsn()
conndsn.Close()
conndsn.Dispose()
End Sub
Sub CloseConole()
connole.Close()
connole.Dispose()
End Sub
End Module
2.数据库设计，字典表如下：
| TableName | TableId | FieldName | TableId | Type(Oracle) | Primary key |
| 书目表 | bookTab | 书目号 | bookCode | varchar2(20) | Y |
| 书名 | bookName | varchar2(50) | |||
| 定价 | price | number(5,2) | |||
| 折扣 | discount | number(3,2) | |||
| 分类 | classification | varchar2(50) | |||
| 分类号 | classificationCode | varchar2(50) | |||
| 库存数 | inventoryNum | INTEGER |
| TableName | TableId | FieldName | TableId | Type(Oracle) | Primary key |
| 顾客表 | customerTab | 顾客号 | customerCode | varchar2(50) | Y |
| 姓名 | name | varchar2(50) | |||
| 工号 | jobNum | varchar2(50) |
| TableName | TableId | FieldName | TableId | Type(Oracle) | Primary key |
| 购书经历表 | purchaseExperienceTab | 顾客号 | customerCode | varchar2(50) | Y |
| 购书日期 | purchaseDate | DATE | Y | ||
| 书单 | bookList | varchar2(3000) | |||
| 金额 | moneyAmount | number(5,2) |
建表语句：
| DB Create | ||||
| create table bookTab ( | ||||
| bookCode | varchar2(20) | not null, | ||
| bookName | varchar2(50), | |||
| price | number(5,2), | |||
| discount | number(3,2), | |||
| classification | varchar2(50), | |||
| classificationCode | varchar2(50), | |||
| inventoryNum | INTEGER | |||
| ) | ||||
| ; | ||||
| alter table bookTab add(constraint pk_bookCode primary key(bookCode)); | ||||
| create table customerTab ( | |||||
| customerCode | varchar2(50) | not null, | |||
| name | varchar2(50), | ||||
| jobNum | varchar2(50) | ||||
| ) | |||||
| ; | |||||
| alter table customerTab add(constraint pk_customerCode primary key(customerCode)); | |||||
| create table purchaseExperienceTab ( | ||||||
| customerCode | varchar2(50) | not null, | ||||
| purchaseDate | DATE | not null, | ||||
| bookList | varchar2(3000), | |||||
| moneyAmount | number(5,2) | |||||
| ) | ||||||
| ; | ||||||
| alter table purchaseExperienceTab add(constraint pk_CodeDate primary key(customerCode,purchaseDate)); | ||||||
插入数据：
insert into bookTab (bookCode,bookName,price,discount,classification,classificationCode,inventoryNum)values('9787532489510','老象恩仇记',12.8,0.75,'童话系列','1111',100)
insert into bookTab (bookCode,bookName,price,discount,classification,classificationCode,inventoryNum)values('9787532489527','神奇的警犬-沈石溪激情动物小说',13.85,0.8,'童话系列','1111',200)
insert into bookTab (bookCode,bookName,price,discount,classification,classificationCode,inventoryNum)values('9787533266066','小男生杜歌飞',133.85,0.9,'男生系列','2222',50)
insert into bookTab (bookCode,bookName,price,discount,classification,classificationCode,inventoryNum)values('9787533266067','Public変数/定数の宣',133.85,0.9,'男生系列','2222',50)
insert into customerTab (customerCode,name,jobNum)values('1','汪晓阳','wm139a0')
insert into customerTab (customerCode,name,jobNum)values('02','汪雨','wm110')
3.新建一个bookClass实体类：
Public Class bookClass
Private bookCode As String
Private bookName As String
Private price As Single
Private discount As Single
Private classification As String
Private classificationCode As String
Private inventoryNum As Integer
Sub New()
Me.bookCode = bookCode
Me.bookName = bookName
Me.price = price
Me.discount = discount
Me.classification = classification
Me.classificationCode = classificationCode
Me.inventoryNum = inventoryNum
End Sub Function getBook(ByVal bookCode As String) As bookClass
Return selectBook(bookCode)
End Function
Function getBookCode() As String
Return Me.bookCode
End Function
Function getBookName() As String
Return Me.bookName
End Function
Function getPrice() As Single
Return Me.price
End Function
Function getDiscount() As Single
Return Me.discount
End Function
Function getClassification() As String
Return Me.classification
End Function
Function getClassificationCode() As String
Return Me.classificationCode
End Function
Function getInventoryNum() As Integer
Return Me.inventoryNum
End Function Sub setBookCode(ByVal bookCode As String)
Me.bookCode = bookCode
End Sub
Sub setBookName(ByVal bookName As String)
Me.bookName = bookName
End Sub
Sub setPrice(ByVal price As Single)
Me.price = price
End Sub
Sub setDiscount(ByVal discount As Single)
Me.discount = discount
End Sub
Sub setClassification(ByVal classification As String)
Me.classification = classification
End Sub
Sub setClassificationCode(ByVal classificationCode As String)
Me.classificationCode = classificationCode
End Sub
Sub setInventoryNum(ByVal inventoryNum As Integer)
Me.inventoryNum = inventoryNum
End Sub
End Class
新建customerClass实体类：
Public Class customerClass
Private customerCode As String
Private name As String
Private jobNum As String
Sub New()
Me.customerCode = customerCode
Me.name = name
Me.jobNum = jobNum
End Sub
Function getCustomer(ByVal customerCode As String) As customerClass
Return selectCustomer(customerCode)
End Function
Function getCustomerCode() As String
Return Me.customerCode
End Function
Function getName() As String
Return Me.name
End Function
Function getJobNum() As String
Return Me.jobNum
End Function
Sub setCustomerCode(ByVal customerCode As String)
Me.customerCode = customerCode
End Sub
Sub setName(ByVal name As String)
Me.name = name
End Sub
Sub setJobNum(ByVal jobNum As String)
Me.jobNum = jobNum
End Sub End Class
4.表现层上用vb控件画出窗体很方便快速：
其页面代码如下：
Public Class sellBook
Private Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
If e.KeyCode = Keys.Enter Then
Dim book As bookClass
book = New bookClass
book = book.getBook(TextBox1.Text)
DataGridView1.Rows.Add()
DataGridView1.Item("num", DataGridView1.Rows.Count - ).Value = DataGridView1.Rows.Count -
DataGridView1.Item("bookCode", DataGridView1.Rows.Count - ).Value = TextBox1.Text.Trim
DataGridView1.Item("bookName", DataGridView1.Rows.Count - ).Value = book.getBookName()
DataGridView1.Rows(DataGridView1.Rows.Count - ).Cells().Value = book.getPrice()
DataGridView1.Rows(DataGridView1.Rows.Count - ).Cells().Value = book.getDiscount()
Label2.Text = book.getPrice() * book.getDiscount() + Label2.Text
End If
End Sub
Private Sub sellBook_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Call CloseConole()
End Sub
Private Sub sellBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call OleConnection()
Label2.Text =
End Sub
Private Sub TextBox2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox2.KeyDown
If e.KeyCode = Keys.Enter Then
Dim customer As New customerClass
customer = customer.getCustomer(TextBox2.Text.Trim)
Label11.Text = customer.getName
Label12.Text = customer.getJobNum
End If
End Sub
End Class
今天就先写到这里，这个页面已经能跑起来了，页面使用了常用的DataGridview控件，输入顾客编号或者书目条码按回车自动检索，合计金额也是自动根据购书单算出的。程序待继续完善。。。