VB.NET 对 Oracle 数据库的访问策略 -- 宝蓝战神(built by sgpro 2005-4-3)-- 编程爱好者博客

关键字：VB.NET, 数据库编程, Oracle, DataSet, DataGrid, DataAdapter, OracleConnection 简介： VB.NET 同样使用 .NET数据库组件，这和.NET FrameWork支持的其他语言上没有区别。只是语法上的差异。对Oracle数据库的访问， .NET FrameWork提供了两种方法，一种是使用Ado.Net，另一种是OracleConnection 套件。类似于微软专门为Sql Server提供的 SqlDataConnection。两种方法非常类似。下面只介绍 OracleConnection. 1、先创建 OracleConnection对象，使用连接字符串连接到指定数据库。 2、建立OracleDataAdpter 或者 OracleDataCommand. 构造参数为连接对象,sql语句 3、建立DataSet或者 OracleDataReader 读取OracleDataAdpter和OracleDataCommand采集的数据。 DataSet可以集合多张表。一下是一个具体的实例程序： ================================= ' Copyright (C) 2006-11 sgpro Imports System.Data.OracleClient.OracleDataAdapterImports System.Data.DataSet Public Class OracleSample Inherits System.Windows.Forms.Form #Region " Windows フォームデザイナで生成されたコード " Public Sub New() MyBase.New() ' この呼び出しは Windows フォームデザイナで必要です。 InitializeComponent() ' InitializeComponent() 呼び出しの後に初期化を追加します。 End Sub ' Form は、コンポーネント一覧に後処理を実行するために dispose をオーバーライドします。 Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub ' Windows フォームデザイナで必要です。 Private components As System.ComponentModel.IContainer ' メモ : 以下のプロシージャは、Windows フォームデザイナで必要です。 'Windows フォームデザイナを使って変更してください。 ' コードエディタを使って変更しないでください。 Friend WithEvents OracleConnection1 As System.Data.OracleClient.OracleConnection Friend WithEvents DataView1 As System.Data.DataView Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid Friend WithEvents Button1 As System.Windows.Forms.Button Friend WithEvents PageSetupDialog1 As System.Windows.Forms.PageSetupDialog Friend WithEvents DataSet1 As System.Data.DataSet Friend WithEvents k1 As System.Windows.Forms.TextBox Friend WithEvents k2 As System.Windows.Forms.TextBox Friend WithEvents k3 As System.Windows.Forms.TextBox Friend WithEvents Button2 As System.Windows.Forms.Button Friend WithEvents Button3 As System.Windows.Forms.Button Friend WithEvents Label1 As System.Windows.Forms.Label Friend WithEvents OracleDataAdapter1 As System.Data.OracleClient.OracleDataAdapter Friend WithEvents OracleCommand1 As System.Data.OracleClient.OracleCommand Friend WithEvents OracleSelectCommand1 As System.Data.OracleClient.OracleCommand Friend WithEvents OracleInsertCommand1 As System.Data.OracleClient.OracleCommand Friend WithEvents OracleUpdateCommand1 As System.Data.OracleClient.OracleCommand Friend WithEvents OracleDeleteCommand1 As System.Data.OracleClient.OracleCommand <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.OracleConnection1 = New System.Data.OracleClient.OracleConnection Me.DataView1 = New System.Data.DataView Me.DataGrid1 = New System.Windows.Forms.DataGrid Me.Button1 = New System.Windows.Forms.Button Me.PageSetupDialog1 = New System.Windows.Forms.PageSetupDialog Me.DataSet1 = New System.Data.DataSet Me.k1 = New System.Windows.Forms.TextBox Me.k2 = New System.Windows.Forms.TextBox Me.k3 = New System.Windows.Forms.TextBox Me.Button2 = New System.Windows.Forms.Button Me.Button3 = New System.Windows.Forms.Button Me.Label1 = New System.Windows.Forms.Label Me.OracleDataAdapter1 = New System.Data.OracleClient.OracleDataAdapter Me.OracleCommand1 = New System.Data.OracleClient.OracleCommand Me.OracleSelectCommand1 = New System.Data.OracleClient.OracleCommand Me.OracleInsertCommand1 = New System.Data.OracleClient.OracleCommand Me.OracleUpdateCommand1 = New System.Data.OracleClient.OracleCommand Me.OracleDeleteCommand1 = New System.Data.OracleClient.OracleCommand CType(Me.DataView1, System.ComponentModel.ISupportInitialize).BeginInit() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'OracleConnection1 ' Me.OracleConnection1.ConnectionString = "user id=heli;password=heli;data source=jib1;persist security info=False" ' 'DataGrid1 ' Me.DataGrid1.AllowDrop = True Me.DataGrid1.DataMember = "" Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location = New System.Drawing.Point(8, 48) Me.DataGrid1.Name = "DataGrid1" Me.DataGrid1.Size = New System.Drawing.Size(384, 208) Me.DataGrid1.TabIndex = 0 ' 'Button1 ' Me.Button1.Location = New System.Drawing.Point(416, 264) Me.Button1.Name = "Button1" Me.Button1.TabIndex = 1 Me.Button1.Text = "save" ' 'DataSet1 ' Me.DataSet1.DataSetName = "NewDataSet" Me.DataSet1.Locale = New System.Globalization.CultureInfo("ja-JP") ' 'k1 ' Me.k1.Location = New System.Drawing.Point(16, 8) Me.k1.Name = "k1" Me.k1.Size = New System.Drawing.Size(80, 19) Me.k1.TabIndex = 2 Me.k1.Text = "" ' 'k2 ' Me.k2.Location = New System.Drawing.Point(144, 8) Me.k2.Name = "k2" Me.k2.Size = New System.Drawing.Size(96, 19) Me.k2.TabIndex = 3 Me.k2.Text = "" ' 'k3 ' Me.k3.Location = New System.Drawing.Point(280, 8) Me.k3.Name = "k3" Me.k3.Size = New System.Drawing.Size(104, 19) Me.k3.TabIndex = 4 Me.k3.Text = "" ' 'Button2 ' Me.Button2.Location = New System.Drawing.Point(416, 8) Me.Button2.Name = "Button2" Me.Button2.Size = New System.Drawing.Size(80, 24) Me.Button2.TabIndex = 5 Me.Button2.Text = "search" ' 'Button3 ' Me.Button3.Location = New System.Drawing.Point(416, 48) Me.Button3.Name = "Button3" Me.Button3.Size = New System.Drawing.Size(80, 23) Me.Button3.TabIndex = 6 Me.Button3.Text = "clear" ' 'Label1 ' Me.Label1.Font = New System.Drawing.Font("MS UI Gothic", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(128, Byte)) Me.Label1.Location = New System.Drawing.Point(8, 272) Me.Label1.Name = "Label1" Me.Label1.Size = New System.Drawing.Size(376, 24) Me.Label1.TabIndex = 7 ' 'OracleDataAdapter1 ' Me.OracleDataAdapter1.DeleteCommand = Me.OracleDeleteCommand1 Me.OracleDataAdapter1.InsertCommand = Me.OracleInsertCommand1 Me.OracleDataAdapter1.SelectCommand = Me.OracleSelectCommand1 Me.OracleDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "STU", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("STU_NO", "STU_NO"), New System.Data.Common.DataColumnMapping("STU_NAME", "STU_NAME"), New System.Data.Common.DataColumnMapping("STU_SEX", "STU_SEX")})}) Me.OracleDataAdapter1.UpdateCommand = Me.OracleUpdateCommand1 ' 'OracleCommand1 ' Me.OracleCommand1.Connection = Me.OracleConnection1 ' 'OracleSelectCommand1 ' Me.OracleSelectCommand1.CommandText = "SELECT STU_NO, STU_NAME, STU_SEX FROM STU" Me.OracleSelectCommand1.Connection = Me.OracleConnection1 ' 'OracleInsertCommand1 ' Me.OracleInsertCommand1.CommandText = "INSERT INTO STU(STU_NO, STU_NAME, STU_SEX) VALUES (:STU_NO, :STU_NAME, :STU_SEX)" Me.OracleInsertCommand1.Connection = Me.OracleConnection1 Me.OracleInsertCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_NO", System.Data.OracleClient.OracleType.VarChar, 7, "STU_NO")) Me.OracleInsertCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_NAME", System.Data.OracleClient.OracleType.VarChar, 20, "STU_NAME")) Me.OracleInsertCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_SEX", System.Data.OracleClient.OracleType.VarChar, 2, "STU_SEX")) ' 'OracleUpdateCommand1 ' Me.OracleUpdateCommand1.CommandText = "UPDATE STU SET STU_NO = :STU_NO, STU_NAME = :STU_NAME, STU_SEX = :STU_SEX WHERE (" & _ "STU_NO = :Original_STU_NO) AND (STU_NAME = :Original_STU_NAME) AND (STU_SEX = :O" & _ "riginal_STU_SEX)" Me.OracleUpdateCommand1.Connection = Me.OracleConnection1 Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_NO", System.Data.OracleClient.OracleType.VarChar, 7, "STU_NO")) Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_NAME", System.Data.OracleClient.OracleType.VarChar, 20, "STU_NAME")) Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":STU_SEX", System.Data.OracleClient.OracleType.VarChar, 2, "STU_SEX")) Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_NO", System.Data.OracleClient.OracleType.VarChar, 7, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_NO", System.Data.DataRowVersion.Original, Nothing)) Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_NAME", System.Data.OracleClient.OracleType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_NAME", System.Data.DataRowVersion.Original, Nothing)) Me.OracleUpdateCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_SEX", System.Data.OracleClient.OracleType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_SEX", System.Data.DataRowVersion.Original, Nothing)) ' 'OracleDeleteCommand1 ' Me.OracleDeleteCommand1.CommandText = "DELETE FROM STU WHERE (STU_NO = :Original_STU_NO) AND (STU_NAME = :Original_STU_N" & _ "AME) AND (STU_SEX = :Original_STU_SEX)" Me.OracleDeleteCommand1.Connection = Me.OracleConnection1 Me.OracleDeleteCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_NO", System.Data.OracleClient.OracleType.VarChar, 7, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_NO", System.Data.DataRowVersion.Original, Nothing)) Me.OracleDeleteCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_NAME", System.Data.OracleClient.OracleType.VarChar, 20, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_NAME", System.Data.DataRowVersion.Original, Nothing)) Me.OracleDeleteCommand1.Parameters.Add(New System.Data.OracleClient.OracleParameter(":Original_STU_SEX", System.Data.OracleClient.OracleType.VarChar, 2, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "STU_SEX", System.Data.DataRowVersion.Original, Nothing)) ' 'OracleSample ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 12) Me.ClientSize = New System.Drawing.Size(512, 302) Me.Controls.Add(Me.Label1) Me.Controls.Add(Me.Button3) Me.Controls.Add(Me.Button2) Me.Controls.Add(Me.k3) Me.Controls.Add(Me.k2) Me.Controls.Add(Me.k1) Me.Controls.Add(Me.Button1) Me.Controls.Add(Me.DataGrid1) Me.Name = "OracleSample" Me.Text = "OracleSample" CType(Me.DataView1, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region Private Sub OracleSample_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.Button2_Click(sender, e) Me.k1.TabIndex = 0 End Sub Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try Me.OracleDataAdapter1.Update(Me.DataSet1, "STU") Me.Label1.Text = "Save successfully" Catch ex As Exception MsgBox(ex.ToString()) Me.Label1.Text = "Save failed!" Me.DataSet1.RejectChanges() End Try End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim sql As String Me.DataSet1.Clear() sql = "SELECT * FROM STU " Dim andStr As Boolean andStr = False If Me.k1.Text.Trim() & Me.k2.Text.Trim() & Me.k3.Text.Trim() <> "" Then sql = sql & " where " End If If Me.k1.Text.Trim() <> "" Then sql = sql & " STU_NO = " & "'" & Me.k1.Text.Trim() & "'" andStr = True End If If Me.k2.Text.Trim() <> "" Then If andStr Then sql = sql & " and " End If sql = sql & " STU_NAME = " & "'" & Me.k2.Text.Trim() & "'" andStr = True End If If Me.k3.Text.Trim() <> "" Then If andStr Then sql = sql & " and " End If sql = sql & " STU_SEX = " & "'" & Me.k3.Text.Trim() & "'" andStr = True End If Try Me.OracleDataAdapter1.SelectCommand.CommandText = sql Me.OracleDataAdapter1.Fill(Me.DataSet1, "STU") Me.DataGrid1.DataSource = Me.DataSet1.Tables("STU") If Me.DataSet1.Tables("STU").Rows.Count = 0 Then MsgBox("Search Count as 0") End If Me.Label1.Text = "Search Count: " & Me.DataSet1.Tables("STU").Rows.Count Catch ex As Exception MsgBox(ex.ToString() & Chr(13) & sql) Me.Label1.Text = "Search failed!" End Try End Sub Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Me.DataSet1.Clear() Me.k1.Text = "" Me.k2.Text = "" Me.k3.Text = "" Me.k1.Focus() Me.Label1.Text = "Ready..." End SubEnd Class 运行界面：

博客介绍

正文

VB.NET 对 Oracle 数据库的访问策略2006-12-01 12:57:00

评论