程序设计之三：OleDB数据库访问技术（VB.NET+Access）

 

近来正在做一个图书管理系统，采用VB.NET语言编写，所有程序运行都必须安装Microsoft .NET Framework 4.0并连接到Access2003数据库中。现在已近完工，我将本软件中用到的所有连接语句归纳于此处，一来方便大家，互相学习，二来，也方便自己日后的复习。

所有的数据库连接都要使用以下语句：Imports System.Data.OleDb，该语句声明了所使用到的OleDb类。下面分类介绍了VB.NET访问ACCESS数据库的增加，修改，删除等部分过程。

1、使用ADODB来访问数据库，对数据库进行查找。下面一段代码实现的是管理员登陆功能。用到的控件有以下：ADODB.Connection，ADODB.Recordset。并且需要在引用中添加：Microsoft ActiveX Date Objects Library 6.0来支持程序的运行。

Public Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.ClickTryDim conn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim sql As StringDim connstr As Stringconnstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"sql = "select * from admin where 管理员姓名='" & txtname.Text & "'"conn.Open(connstr)rs.Open(sql, conn, 1, 1)If rs.RecordCount = 0 ThenMsgBox("用户不存在")txtname.Text = ""txtPassword.Text = ""txtname.Select()rs.Close()conn.Close()Exit SubEnd IfIf txtPassword.Text <> rs("登陆密码").Value ThenMsgBox("用户口令错误")txtname.Text = ""txtPassword.Text = ""txtname.Select()rs.Close()conn.Close()ElseMsgBox("登陆成功")rs.Close()conn.Close()main.Show()Me.Close()End IfCatch ex As ExceptionMsgBox("登陆失败，请重试！")End TryEnd Sub

上面程序中，我通过rs("登陆密码").Value来实现对仅有一条结果的Select语句结果的某个字段的值进行输出。

2、使用OleDbConnection来访问数据库。需要用到的控件有：DataSet，OleDbConnection，OleDbDataAdapter。

首先要配置数据源，我们可以采用以下的方法：

connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

2.1、查找语句。下面这段代码实现的功能查找管理员帐户登陆密码。

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

Try

Dim password As String

Dim connstr As String

connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

Dim myConn As OleDbConnection = New OleDbConnection()

myConn.ConnectionString = connstr

myConn.Open()

Dim strSearch As String = "select * from admin where 登陆密码='" & txtPwdOld.Text & "'"

Dim myCommand As New OleDbCommand(strSearch, myConn)

Dim dr As OleDbDataReader

dr = myCommand.ExecuteReader

If dr.Read = False Then

MsgBox("原密码错误，请重新输入！", MsgBoxStyle.Information, "系统提示")

myConn.Close()

Exit Sub

Else

password = dr("登陆密码")

End If

Catch ex As Exception

MsgBox("查找失败，请重试！")

End Try

End Sub

上述代码能实现使用Select语句，从数据库中查找出指定的结果。我们可以通过以下程序段来实现和ADODB一样的数据字段绑定。

Try

Dim dt As New DataTable()

Dim connStr As String

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=bookmanage.mdb"

Dim sqlStr As String = "select * from book where 书名 like '%" & TextBox1.Text & "%'"

Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

dataAdapter.Fill(dt)

dataAdapter.Dispose()

If dt.Rows.Count = 0 Then

MessageBox.Show("找不到该书的记录！")

Else

Label1.Text = dt.Rows.Item(0).Item(2)

End If

Catch ex As Exception

MsgBox("查找失败，请重试！")

End Try

这段代码还使用到了DataTable控件，其中dt.Rows.Item(0).Item(2)的意思是，取出dt中的第0行，第2列的值。或者：也可以用以下的代码实现。

Try

Dim connstr As String

connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

Dim myConn As OleDbConnection = New OleDbConnection()

myConn.ConnectionString = connstr

myConn.Open()

Dim strSearch As String = "select * from book where 书号='" & Trim$(TextBox1.Text) & "'"

Dim myCommand As New OleDbCommand(strSearch, myConn)

Dim dr As OleDbDataReader

dr = myCommand.ExecuteReader

If (dr.Read = False) Then

MsgBox("错误！", MsgBoxStyle.Information, "系统提示")

Exit Sub

Else

Label1.Text = dr("书号")

End If

Catch ex As Exception

MsgBox("查找失败，请重试！")

End Try

这里是将dr("书号")字段的值取出来。要实现这个赋值，dr("书号")的内容最多最能为1条。

2.2、更新语句。这段代码实现的功能时修改登陆密码。

Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click

Try

If txtPwdOld.Text = password Then

Dim connstr As String

connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

Dim myConn As OleDbConnection = New OleDbConnection()

myConn.ConnectionString = connstr

myConn.Open()

Dim StrUpdate As String = "Update admin SET 登陆密码='" + txtPwd.Text + "'"

Dim cmdInsert As OleDbCommand = New OleDbCommand(StrUpdate, myConn)

cmdInsert.ExecuteNonQuery()

MsgBox("密码修改成功！")

myConn.Close()

End If

Catch ex As Exception

MsgBox("查找失败，请重试！")

End Try

End Sub

2.3、删除语句。

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Try

Dim ConnectionString As String

ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

Dim myConn As OleDbConnection = New OleDbConnection()

myConn.ConnectionString = ConnectionString

myConn.Open()

Dim strDelete As String

strDelete = "delete from borrower where 借书证号 = '" & txtNum.Text & "'"

Dim cmdInsert As OleDbCommand = New OleDbCommand(strDelete, myConn)

cmdInsert.ExecuteNonQuery()

MsgBox("注销成功！")

Catch ex As Exception

MsgBox("注销失败，请重试！")

End Try

End Sub

2.4、插入语句。

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

Try

Dim connstr As String

connstr = "provider=microsoft.jet.oledb.4.0;data source=bookmanage.mdb"

Dim myConn As OleDbConnection = New OleDbConnection()

myConn.ConnectionString = connstr

myConn.Open()

Dim strInsert As String = "insert into book  (书号,书名,出版社,数量,作者,出版日期,分类 ) values('" + txtNum.Text + "','" + txtBookName.Text + "','" + txtPublisher.Text + "','" + txtQuality.Text + "','" + txtAuthor.Text + "','" + txtDate.Text + "','" + txtDept.Text + "')"

Dim cmdInsert As OleDbCommand = New OleDbCommand(strInsert, myConn)

cmdInsert.ExecuteNonQuery()

MsgBox("添加成功！")

Catch ex As Exception

MsgBox("添加失败")

End Try

End Sub

3、使用DataGridView来显示数据。

Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

Try

Dim dt As New DataTable()

Dim connStr As String

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=bookmanage.mdb"

Dim sqlStr As String

sqlStr="select borrowbook.书号,book.书名,book.出版社,book.作者,book.分类,borrowbook.借书日期 from borrowbook, book where borrowbook.借书证号 = '" & lblNum.Text & "' and borrowbook.书号 = book.书号"

Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)

dataAdapter.Fill(dt)

dataAdapter.Dispose()

If dt.Rows.Count = 0 Then

MessageBox.Show("找不到借书记录！")

Else

dgvBorrowInformation.DataSource = dt

End If

Catch ex As Exception

MsgBox("填充失败")

End Try

End Sub

2011年4月10日星期日