VBNET连接Oracle增删改查学习笔记(经典CRUD_含存储过程)

我的+oracle增删改查学习笔记

（本源码在2008下测试通过）

学习有一段时间了，之前一直学习VB6.0。过度到.NET后发现与之前所学习的有了相当大的变化。于是将编程经常用到的增删改查代码提炼出来，备后日使用及各位一起学习。各段代码均可以独立运行，读者可以活学活用，设置公共变量剔除冗余代码。可以将连接代码写成函数，随时调用，不用每次都加。

主要内容为：添加数据库部件引用、设置连接字符串、设置界面、查询并将结果放入表中、添加按钮代码、修改操作、删除操作、调用无参数的存储过程、调用有参数的存储过程、最终所有代码。

一、首先，要引用Client，我的电脑中已经安装Oracle9i，在COM选项卡下可以找到该项。如果不添加该引用，程序将无法运行。

二、连接字符串（可以添加一个OracleConnection对象，针对您的计

算机进行设置，然后复制ConnectionString代码，记得复制完删除它；也可以复制下面的代码，然后更改用户名及密码）

'对OracleConnection对象的ConnectionString属性赋值，指定连接位置

tionString = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

三、界面如下

四、查询并将结果放入表中，代码如下：

我将该段代码放置在了Form_Load过程中，您也可以把该代码放置在按钮或其他过程中。

Private Sub Form1_Load(ByVal sender As , ByVal e As rgs) Handles

Dim strSql As String

Dim strConn As String

Dim objDA As DataAdapter

Dim objds As New t

strConn = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

strSql = "select * from TEST "

objDA = New DataAdapter(strSql, strConn)

(objds, "TEST")

urce = ("TEST")

End Sub

五、添加按钮代码如下：

添加后表格中数据未进行更新，加入时更新。

Private Sub Button1_Click(ByVal sender As , ByVal e As rgs) Handles

Dim strSql As String

Dim strConn As String

Dim objDA As DataAdapter

Dim objds As New t

Dim objcb As CommandBuilder

Dim objrow As w

strConn = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

strSql = "select * from TEST order by A DESC"

objDA = New DataAdapter(strSql, strConn)

objcb = New CommandBuilder(objDA)

(objds, " TEST ")

objrow = ("TEST ").NewRow

objrow("A") =

objrow("B") =

objrow("C") =

objrow("D") =

("TEST ").(objrow)

(objds, " TEST ")

Call Form1_Load(sender, e)

End Sub

Call Form1_Load(sender, e)代码实现即六、修改操作，分两步，首先将表格中选中行数据放在各个TextBox中，然后进行UpDate操作。

第一步：注意是DataGridView1_CellClick事件。其他事件单击后无此效果。

Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As

idViewCellEventArgs) Handles ick

= (0).ng

= (1).ng

= (2).ng

= (3).ng

End Sub

第二部：修改按钮代码如下：

Private Sub Button3_Click(ByVal sender As , ByVal e As rgs) Handles

Dim strSql As String

Dim strConn As String

Dim objDA As DataAdapter

Dim objds As New t

strConn = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

strSql = "update TEST set B='" + Trim() + "',C='" + Trim() +

"',D='" + Trim() + "' where A='" + Trim() + "'"

objDA = New DataAdapter(strSql, strConn)

(objds, " TEST ")

Call Form1_Load(sender, e)

End Sub

End Class

七、删除按钮代码如下：

Private Sub Button2_Click(ByVal sender As , ByVal e As rgs) Handles

Dim strSql As String

Dim strConn As String

Dim objDA As DataAdapter

Dim objds As New t

strConn = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

strSql = "delete from TEST where A like '%" + Trim() + "%'"

objDA = New DataAdapter(strSql, strConn)

(objds, " TEST ")

End Sub

八、调用带参数的存储过程

第一步：创建存储过程

create or replace procedure addTest(

Pro_A TEST.A%type,

Pro_B TEST.B%type,

Pro_C TEST.C%type,

Pro_D TEST.D%type)

as

begin

insert into TEST (A,B,C,D)

values(Pro_A,Pro_B,Pro_C,Pro_D);

commit;

end;

第二步：利用存储过程添加记录按钮的代码

Private Sub Button4_Click(ByVal sender As , ByVal e As rgs) Handles

Dim strConn As New OracleConnection("Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True")

Dim objCmd As New OracleCommand()

tion = strConn

't为存储过程名

dText = "t"

dType = Procedure

'Pro_A为存储过程中参数的名称

("Pro_A", r).Value =

("Pro_B", r).Value =

("Pro_C", r).Value =

("Pro_D", r).Value =

Dim objDA As New OracleDataAdapter(objCmd)

Dim objDs As New DataSet()

(objDs, " TEST ")

Call Form1_Load(sender, e)

End Sub

九、调用带参数的存储过程

各段代码均可以独立运行，读者可以活学活用，设置公共变量剔除冗余代码。可以将连接代码写成函数，随时调用，不用每次都加。

Public Function strConn() As String

strConn = "Data Source=myzsyy;Persist Security Info=True;User

ID=bzk22;Password=zxcvbn;Unicode=True"

End Function

十、最终代码

Public Class Form2

Public Sub GridView()

Dim objDA As DataAdapter

Dim objDs As New t

strSql = "select * from test order by A"

objDA = New DataAdapter(strSql, strConn)

(objDs, "TEST")

urce = ("TEST")

' = nt - 1 '显示记录条数，datagridview的方法

= ("test"). '显示记录条数，记录集方法

End Sub

Private Sub Button1_Click(ByVal sender As , ByVal e As rgs) Handles

Dim objDA As DataAdapter

Dim objDs As New t

Dim objCb As CommandBuilder

Dim objRow As w

strSql = "select * from test order by A DESC"

objDA = New DataAdapter(strSql, strConn)

objcb = New CommandBuilder(objDA)

(objds, "test")

objrow = ("test").NewRow

objrow("A") =

objrow("B") =

objrow("C") =

objRow("D") =

objRow("S") =

("test").(objrow)

(objds, "test")

Call GridView()

End Sub

Private Sub Form2_Load(ByVal sender As , ByVal e As rgs) Handles

Call GridView()

End Sub

Private Sub Button2_Click(ByVal sender As , ByVal e As rgs) Handles

Dim objDA As DataAdapter

Dim objDs As New t

strSql = "delete from test where A ='" + Trim() + "'" '原来的删除里有通配符%，不好使，曾经一次删除多条数据

objDA = New DataAdapter(strSql, strConn)

(objds, "test")

Call GridView()

End Sub

Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As

idViewCellEventArgs) Handles ick

= (0).ng

= (1).ng

= (2).ng

= (3).ng

= (4).ng

End Sub

Private Sub Button3_Click(ByVal sender As , ByVal e As rgs) Handles

Dim objDA As DataAdapter

Dim objDs As New t

strSql = "update test set B='" + Trim() + "',C='" + Trim() +

"',D='" + Trim() + "' ,S=to_date('" + + "','yyyy-mm-dd')

where A='" + Trim() + "'"

objDA = New DataAdapter(strSql, strConn)

(objDs, "test")

Call GridView()

End Sub

Private Sub Button4_Click(ByVal sender As , ByVal e As rgs) Handles

Dim objCmd As New Command()

tion = New Connection(strConn)

dText = "t"

dType = Procedure

("Pro_A", r).Value =

("Pro_B", r).Value =

("Pro_C", r).Value =

("Pro_D", r).Value =

("Pro_S", me).Value =

'("io_cursor", ).Direction =

Dim objDA As New DataAdapter(objCmd)

Dim objDs As New DataSet()

(objDs, "test")

Call GridView()

End Sub

End Class

总之，虽然说比VB6复杂，但是更加趋于面向对象，很多原来VB6不容易实现的内容现在很容易就可以实现了。相对于有VB6基础的人来说，上手应该是很快的。同时，也是未来的趋势，VB6最终还是会退出历史舞台的。大家一起好好学习吧。