VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > VB.net教程 >

  • vb.net+oracle增删改查学习笔记

 
我的vb.net+oracle增删改查学习笔记
（本源码在VB.NET2008下测试通过）
学习VB.NET有一段时间了，之前一直学习VB6.0。过度到.NET后发现与之前所学习的有了相当大的变化。于是将编程经常用到的增删改查代码提炼出来，备后日使用及各位一起学习。各段代码均可以独立运行，读者可以活学活用，设置公共变量剔除冗余代码。可以将连接代码写成函数，随时调用，不用每次都加。
主要内容为：添加数据库部件引用、设置连接字符串、设置界面、查询并将结果放入表中、添加按钮代码、修改操作、删除操作、调用无参数的存储过程、调用有参数的存储过程、最终所有代码。
一、首先，要引用System.Data.OracleClient，我的电脑中已经安装Oracle9i，在COM选项卡下可以找到该项。如果不添加该引用，程序将无法运行。

二、连接字符串（可以添加一个OracleConnection对象，针对您的计算机进行设置，然后复制ConnectionString代码，记得复制完删除它；也可以复制下面的代码，然后更改用户名及密码）
        '对OracleConnection对象的ConnectionString属性赋值，指定连接位置
        objconn.ConnectionString = "Data Source=myzsyy;Persist Security Info=True;User ID=bzk22;Password=zxcvbn;Unicode=True"
三、界面如下

四、查询并将结果放入表中，代码如下：
我将该段代码放置在了Form_Load过程中，您也可以把该代码放置在按钮或其他过程中。
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strSql As String
        Dim strConn As String
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objds As New Data.DataSet
        strConn = "Data Source=myzsyy;Persist Security Info=True;User ID=bzk22;Password=zxcvbn;Unicode=True"
        strSql = "select * from TEST "
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(objds, "TEST")
        DataGridView1.DataSource = objds.Tables("TEST")
    End Sub
五、添加按钮代码如下：
添加后表格中数据未进行更新，加入Call Form1_Load(sender, e)代码实现即时更新。
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strSql As String
        Dim strConn As String
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objds As New Data.DataSet
        Dim objcb As OracleClient.OracleCommandBuilder
        Dim objrow As Data.DataRow
        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 OracleClient.OracleDataAdapter(strSql, strConn)
        objcb = New OracleClient.OracleCommandBuilder(objDA)
        objDA.Fill(objds, " TEST ")
 
        objrow = objds.Tables("TEST ").NewRow
        objrow("A") = TextBox1.Text
        objrow("B") = TextBox2.Text
        objrow("C") = TextBox3.Text
        objrow("D") = TextBox4.Text
        objds.Tables("TEST ").Rows.Add(objrow)
        objDA.Update(objds, " TEST ")
        Call Form1_Load(sender, e)
    End Sub
六、修改操作，分两步，首先将表格中选中行数据放在各个TextBox中，然后进行UpDate操作。
第一步：注意是DataGridView1_CellClick事件。其他事件单击后无此效果。
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        TextBox1.Text = DataGridView1.CurrentRow.Cells.Item(0).Value.ToString
        TextBox2.Text = DataGridView1.CurrentRow.Cells.Item(1).Value.ToString
        TextBox3.Text = DataGridView1.CurrentRow.Cells.Item(2).Value.ToString
        TextBox4.Text = DataGridView1.CurrentRow.Cells.Item(3).Value.ToString
    End Sub
第二部：修改按钮代码如下：
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim strSql As String
        Dim strConn As String
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objds As New Data.DataSet
        strConn = "Data Source=myzsyy;Persist Security Info=True;User ID=bzk22;Password=zxcvbn;Unicode=True"
        strSql = "update TEST set B='" + Trim(TextBox2.Text) + "',C='" + Trim(TextBox3.Text) + "',D='" + Trim(TextBox4.Text) + "'  where A='" + Trim(TextBox1.Text) + "'"
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(objds, " TEST ")
        Call Form1_Load(sender, e)
    End Sub
End Class
七、删除按钮代码如下：
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim strSql As String
        Dim strConn As String
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objds As New Data.DataSet
        strConn = "Data Source=myzsyy;Persist Security Info=True;User ID=bzk22;Password=zxcvbn;Unicode=True"
        strSql = "delete from TEST  where A like '%" + Trim(TextBox1.Text) + "%'"
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(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 System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim strConn As New OracleConnection("Data Source=myzsyy;Persist Security Info=True;User ID=bzk22;Password=zxcvbn;Unicode=True")
        Dim objCmd As New OracleCommand()
        objCmd.Connection = strConn
         'bzk22.addtest为存储过程名
        objCmd.CommandText = "bzk22.addtest"
        objCmd.CommandType = CommandType.StoredProcedure
'Pro_A为存储过程中参数的名称
        objCmd.Parameters.Add("Pro_A", OracleType.VarChar).Value = TextBox1.Text
        objCmd.Parameters.Add("Pro_B", OracleType.VarChar).Value = TextBox2.Text
        objCmd.Parameters.Add("Pro_C", OracleType.VarChar).Value = TextBox3.Text
        objCmd.Parameters.Add("Pro_D", OracleType.VarChar).Value = TextBox4.Text
        Dim objDA As New OracleDataAdapter(objCmd)
        Dim objDs As New DataSet()
        objDA.Fill(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 OracleClient.OracleDataAdapter
        Dim objDs As New Data.DataSet
        strSql = "select * from test order by A"
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(objDs, "TEST")
        DataGridView1.DataSource = objds.Tables("TEST")
        'Label5.Text = DataGridView1.RowCount - 1        '显示记录条数，datagridview的方法
        Label5.Text = objDs.Tables("test").Rows.Count    '显示记录条数，记录集方法
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objDs As New Data.DataSet
        Dim objCb As OracleClient.OracleCommandBuilder
        Dim objRow As Data.DataRow
        strSql = "select * from test order by A DESC"
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objcb = New OracleClient.OracleCommandBuilder(objDA)
        objDA.Fill(objds, "test")
        objrow = objds.Tables("test").NewRow
        objrow("A") = TextBox1.Text
        objrow("B") = TextBox2.Text
        objrow("C") = TextBox3.Text
        objRow("D") = TextBox4.Text
        objRow("S") = DateTimePicker1.Value.Date
        objds.Tables("test").Rows.Add(objrow)
        objDA.Update(objds, "test")
        Call GridView()
    End Sub
 
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call GridView()
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objDs As New Data.DataSet
        strSql = "delete from test  where A ='" + Trim(TextBox1.Text) + "'"   '原来的删除里有通配符%，不好使，曾经一次删除多条数据
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(objds, "test")
        Call GridView()
    End Sub
    Private Sub DataGridView1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        TextBox1.Text = DataGridView1.CurrentRow.Cells.Item(0).Value.ToString
        TextBox2.Text = DataGridView1.CurrentRow.Cells.Item(1).Value.ToString
        TextBox3.Text = DataGridView1.CurrentRow.Cells.Item(2).Value.ToString
        TextBox4.Text = DataGridView1.CurrentRow.Cells.Item(3).Value.ToString
        DateTimePicker1.Value = DataGridView1.CurrentRow.Cells.Item(4).Value.ToString
    End Sub
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim objDA As OracleClient.OracleDataAdapter
        Dim objDs As New Data.DataSet
        strSql = "update test set B='" + Trim(TextBox2.Text) + "',C='" + Trim(TextBox3.Text) + "',D='" + Trim(TextBox4.Text) + "' ,S=to_date('" + DateTimePicker1.Value.Date + "','yyyy-mm-dd') where A='" + Trim(TextBox1.Text) + "'"
        objDA = New OracleClient.OracleDataAdapter(strSql, strConn)
        objDA.Fill(objDs, "test")
        Call GridView()
    End Sub
 
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim objCmd As New OracleClient.OracleCommand()
        objCmd.Connection = New OracleClient.OracleConnection(strConn)
        objCmd.CommandText = "bzk22.addtest"
        objCmd.CommandType = CommandType.StoredProcedure
        objCmd.Parameters.Add("Pro_A", OracleClient.OracleType.VarChar).Value = TextBox1.Text
        objCmd.Parameters.Add("Pro_B", OracleClient.OracleType.VarChar).Value = TextBox2.Text
        objCmd.Parameters.Add("Pro_C", OracleClient.OracleType.VarChar).Value = TextBox3.Text
        objCmd.Parameters.Add("Pro_D", OracleClient.OracleType.VarChar).Value = TextBox4.Text
        objCmd.Parameters.Add("Pro_S", OracleClient.OracleType.DateTime).Value = DateTimePicker1.Value.Date
        'cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
        Dim objDA As New OracleClient.OracleDataAdapter(objCmd)
        Dim objDs As New DataSet()
        objDA.Fill(objDs, "test")
        Call GridView()
    End Sub
End Class
    总之，虽然说VB.NET比VB6复杂，但是更加趋于面向对象，很多原来VB6不容易实现的内容现在很容易就可以实现了。相对于有VB6基础的人来说，上手VB.NET应该是很快的。同时，VB.NET也是未来的趋势，VB6最终还是会退出历史舞台的。大家一起好好学习吧。

相关教程