vb.net and oracle

• • Reply

  18 years ago

  by gundput

  gundput

  , United States

  Joined 18 years ago

  Does anyone know how i would go about writing a sql statement
  in vb .net to go to oracle.
  with vb 6 i could just use recordset but since that is no more how would i do it in .net
  

  Report abuse
• • Reply

  18 years ago

  by musician

  Brian O'Connell

  Dublin, Ireland, Ireland

  Joined 19 years ago

  You can still use the old ado by adding a reference to it. .net creates a wrapper for it and you can then use it as you did before. Otherwise you would need to read up on ado.net and decide if you wanted to use a datareader for read-only data or a dataset with a dataadapter for fully updateable disconnected data. Either way with ado.net you would use the oledbclient namespace to connect to oracle via the ole provider. The DataReader is pretty straightforward stuff whereas the DataSet is almost like having a local database in your application. For readonly data heres an example of how to use ado.net with the DataReader:-
  

  
  You have to import the following or add a reference in the vs.net designer
  Imports System.Data
  Imports System.Data.OleDBClient
  

  
  Now assuming I have a form with a listview control called lvEmployees and a button called cbConnect I can fill the listview with a list of employees and their hiredate from nwind.mdb with the following code (this is MsAccess a sI do not use Oracle but you should find it straightforward to change the connection string as your needs require as well as the sql statement you want to pass to the database):-
  

  
  

  Code:

                 
         Dim strPath As String = System.AppDomain.CurrentDomain.BaseDirectory
         Dim dbLoc As String = strPath.Replace("bin", "db") & "nwind.mdb"
         Dim strSQL As String = "select firstname, lastname, hiredate"
         strSQL &= " from employees order by employeeid"
         Dim Conn As New OleDb.OleDbConnection()
         Dim Cmd As OleDb.OleDbCommand
         Dim Dr As OleDb.OleDbDataReader
         Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbLoc
         Cmd = Conn.CreateCommand
         Cmd.CommandText = strSQL
         Cmd.CommandType = CommandType.Text
  

  
         Try
             Conn.Open()
             Dr = Cmd.ExecuteReader
             lvEmployees.View = View.Details
             Dim x As Integer
             For x = 0 To Dr.FieldCount - 1
                 lvEmployees.Columns.Add(Dr.GetName(x).ToString, 100, HorizontalAlignment.Left)
             Next
             Do While Dr.Read
                 Dim lvItem As New ListViewItem()
                 Dim lvSubItem1 As New ListViewItem.ListViewSubItem()
                 Dim lvSubItem2 As New ListViewItem.ListViewSubItem()
                 lvItem.Text = Dr.GetString(0)
                 lvSubItem1.Text = Dr.GetString(1)
                 lvSubItem2.Text = CType(Dr.GetDateTime(2), Date).ToShortDateString
                 lvItem.SubItems.Add(lvSubItem1)
                 lvItem.SubItems.Add(lvSubItem2)
                 lvEmployees.Items.Add(lvItem)
             Loop
         Catch ex As OleDb.OleDbException
             MsgBox(ex.Message)
         Catch ex2 As Exception
             MsgBox(ex2.Message & ex2.ToString)
         Finally
             Dr.Close()
             Conn.Close()
         End Try
  

  
  

  
  Don't worry too much about the creation of the dbLoc variable. I just use this because when developing in vs.net the bin directory is where the project executes from so I put the nwind.mdb in a db folder within the project folder so it was simple enough to replace the bin part of the basedirectory with db to create a location string. This code might have to be changed before deployment as the bin directory might not be the location of the exe.
  I might be going overboard using the listview as an example but the code early in this procedure show how to connect to the database, use a command object to return the data to a datareader object and then iterating through the data. With the datareader the GetString or GetDateTime etc. method is used as it is much faster when you know what type of data is being returned. Also you use the Do While DataReader.Read loop without needing to move next as it were. This is more efficient if we are to believe MS and the Read method returns false when there are no more rows. Until then it moves through each row whenever it is called.
  I program in vs.net with Option Strict On so I have to convert the date type to a string as you can see in this case to a ShortDate format. Hope this helps as it isn't actually Oracle.

  Report abuse