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

最近由于工作需要开始接触vb2010,也叫vb.net。相比vb6.0有面向对象编程的优势。同时接触一门新语言，要更快的实际应用起来，链接数据库是必不可少的。之前用vba写过一个售书工具，正好可以拿来改造成vb.net程序。同时考虑到面向对象编程，尽力使用MVC模式编程。其中链接数据库的部分被写在一个模块中，可以切换选择用ole直连oracle，或者用odbc连接dsn数据源。具体如下：

1.dao层新建一个模块

Option Explicit Off

ImportsMicrosoft.Data.OdbcModuledaoPublic conndsn AsOdbcConnectionPublic connole AsOleDb.OleDbConnectionPublic connectionString1 As String

Public connectionString2 As String = "Provider=MSDAORA;Data Source=xx;User ID=xx;Password=xx;"

SubOdbcConnection()

connectionString1= "DSN=sht1;UID=sheet;Pwd=sheet;"

Tryconndsn= NewOdbcConnection(connectionString1)

conndsn.Open()MsgBox("数据库连接成功!", MsgBoxStyle.OkOnly, "连接状态")Catch ex AsExceptionMsgBox("数据库连接发生错误!", MsgBoxStyle.OkOnly, "系统错误")End

End Try

End Sub

SubOleConnection()Tryconnole= NewSystem.Data.OleDb.OleDbConnection(connectionString2)

connole.Open()MsgBox("采用ole数据库连接成功!", MsgBoxStyle.OkOnly, "连接状态")Catch ex AsExceptionMsgBox("数据库连接发生错误!", MsgBoxStyle.OkOnly, "系统错误")End

End Try

End Sub

SubCloseConndsn()

conndsn.Close()

conndsn.Dispose()End Sub

SubCloseConole()

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 ClassbookClassPrivate 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 =bookCodeMe.bookName =bookNameMe.price =priceMe.discount =discountMe.classification =classificationMe.classificationCode =classificationCodeMe.inventoryNum =inventoryNumEnd Sub

Function getBook(ByVal bookCode As String) AsbookClassReturnselectBook(bookCode)End Function

Function getBookCode() As String

Return Me.bookCodeEnd Function

Function getBookName() As String

Return Me.bookNameEnd Function

Function getPrice() As Single

Return Me.priceEnd Function

Function getDiscount() As Single

Return Me.discountEnd Function

Function getClassification() As String

Return Me.classificationEnd Function

Function getClassificationCode() As String

Return Me.classificationCodeEnd Function

Function getInventoryNum() As Integer

Return Me.inventoryNumEnd Function

Sub setBookCode(ByVal bookCode As String)Me.bookCode =bookCodeEnd Sub

Sub setBookName(ByVal bookName As String)Me.bookName =bookNameEnd Sub

Sub setPrice(ByVal price As Single)Me.price =priceEnd Sub

Sub setDiscount(ByVal discount As Single)Me.discount =discountEnd Sub

Sub setClassification(ByVal classification As String)Me.classification =classificationEnd Sub

Sub setClassificationCode(ByVal classificationCode As String)Me.classificationCode =classificationCodeEnd Sub

Sub setInventoryNum(ByVal inventoryNum As Integer)Me.inventoryNum =inventoryNumEnd Sub

End Class

新建customerClass实体类：

Public ClasscustomerClassPrivate customerCode As String

Private name As String

Private jobNum As String

Sub New()Me.customerCode =customerCodeMe.name =nameMe.jobNum =jobNumEnd Sub

Function getCustomer(ByVal customerCode As String) AscustomerClassReturnselectCustomer(customerCode)End Function

Function getCustomerCode() As String

Return Me.customerCodeEnd Function

Function getName() As String

Return Me.nameEnd Function

Function getJobNum() As String

Return Me.jobNumEnd Function

Sub setCustomerCode(ByVal customerCode As String)Me.customerCode =customerCodeEnd Sub

Sub setName(ByVal name As String)Me.name =nameEnd Sub

Sub setJobNum(ByVal jobNum As String)Me.jobNum =jobNumEnd Sub

End Class

4.表现层上用vb控件画出窗体很方便快速：

其页面代码如下：

Public ClasssellBookPrivate Sub TextBox1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) HandlesTextBox1.KeyDownIf e.KeyCode = Keys.Enter Then

Dim book AsbookClass

book= NewbookClass

book=book.getBook(TextBox1.Text)

DataGridView1.Rows.Add()

DataGridView1.Item("num", DataGridView1.Rows.Count - 2).Value = DataGridView1.Rows.Count - 1DataGridView1.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.TextEnd If

End Sub

Private Sub sellBook_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosingCallCloseConole()End Sub

Private Sub sellBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadCallOleConnection()

Label2.Text= 0

End Sub

Private Sub TextBox2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) HandlesTextBox2.KeyDownIf e.KeyCode = Keys.Enter Then

Dim customer As NewcustomerClass

customer=customer.getCustomer(TextBox2.Text.Trim)

Label11.Text=customer.getName

Label12.Text=customer.getJobNumEnd If

End Sub

End ClassView Code

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

原文：http://www.cnblogs.com/wangxiaoyang/p/3573359.html