vb.net写的odbc连接dsn数据源和ole链接oracle的小例子

　　最近由于工作需要开始接触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 - 2).Value = DataGridView1.Rows.Count - 1
            DataGridView1.Item("bookCode", DataGridView1.Rows.Count - 2).Value = TextBox1.Text.Trim
            DataGridView1.Item("bookName", DataGridView1.Rows.Count - 2).Value = book.getBookName()
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(3).Value = book.getPrice()
            DataGridView1.Rows(DataGridView1.Rows.Count - 2).Cells(4).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 = 0
    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

View Code

今天就先写到这里，这个页面已经能跑起来了，页面使用了常用的DataGridview控件，输入顾客编号或者书目条码按回车自动检索，合计金额也是自动根据购书单算出的。程序待继续完善。。。