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控件画出窗体很方便快速:
其页面代码如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
今天就先写到这里,这个页面已经能跑起来了,页面使用了常用的DataGridview控件,输入顾客编号或者书目条码按回车自动检索,合计金额也是自动根据购书单算出的。程序待继续完善。。。