Programmer's Journal - Chapter 2

Chapter 2 – Where’s my data?

Database access in dotNet

Connection String

DotNet framework comes with four data providers. They are:
1. Data provider for SQLServer.
2. Data provider for OleDB.
3. Data provider for ODBC
4. Data provider for Oracle.

The syntax and methods provided for each of the providers are similar enough to each other in terms of usage and parameters that it can be used interchangeably.

The examples below are for connecting to an Oracle instance (using the account “Scott” and password “Tiger” and the oracle sqlnet alias “orcl”). For more examples on the connection strings, you can search for ado connection strings in Internet. (The DSN less odbc allows you to skip the step of defining the DSN in the ODBC).

Data provider Connection String
Oledb provider "Provider=MSDAORA;Data Source=orcl;User Id=Scott;Password=Tiger;"
Oracle provider "Data Source=orcl;User Id=Scott;Password=Tiger;"
DSNless ODBC "Driver={Microsoft ODBC for Oracle};Server=orcl.world;Uid=Scott;Pwd=Tiger;"

Use the correct connection class with the correct connection string to open connection to the database.

To test the above, you can create all the connection string into your configuration file as follows:

<?xml version="1.0"?>
<configuration>
 <appSettings>
<add key="oleDBStr" value="Provider=MSDAORA;PASSWORD=tiger;User ID=scott;Data Source=orcl" />
<add key="odbcStr" value="Driver={Microsoft ODBC for Oracle};Server=orcl;Uid=Scott;Pwd=Tiger;" />
  <add key="orclStr" value="Data Source=orcl;User Id=Scott;Password=Tiger;" />  
 </appSettings>
</configuration>

Note that all the values are in one line. The database connection can be tested using the following code:

' Connection Examples
' User: Strovek
' Date: 8/22/2005
' Revised:
'
'
Imports System
Imports System.Configuration.ConfigurationSettings
Imports System.Data
Imports System.Data.Odbc
Imports System.Data.OleDb
imports System.Data.OracleClient

Module clsConn
Dim oleConn As New System.Data.OleDb.OleDbConnection(AppSettings("oleDBStr"))
Dim odbcConn As New OdbcConnection(AppSettings("odbcStr"))
Dim orclConn as new System.Data.OracleClient.OracleConnection(AppSettings("orclStr"))

'Test oledb Connection
Sub oleTest
 Try
  Console.WriteLine("Start OLEDB Test")
  oleConn.Open
  Console.WriteLine("Connection Successful")
 Catch ex As Exception
  Console.WriteLine(ex.Message)
  Console.WriteLine(ex.StackTrace)
 Finally
  If oleConn.State = ConnectionState.Open Then
   oleConn.Close   
  End If
 End Try
End Sub

'Test odbc Connection
Sub odbcTest
 Try
  Console.WriteLine("Start ODBC Test")
  odbcConn.Open
  Console.WriteLine("Connection Successful")
 Catch ex As Exception
  Console.WriteLine(ex.Message)
  Console.WriteLine(ex.StackTrace)
 Finally
  If odbcConn.State = ConnectionState.Open Then
   odbcConn.Close   
  End If
 End Try
End Sub

'Test oracleClient Connection
Sub orclTest
 Try
  Console.WriteLine("Start Oracle Client Test")
  oleConn.Open
  Console.WriteLine("Connection Successful")
 Catch ex As Exception
  Console.WriteLine(ex.Message)
  Console.WriteLine(ex.StackTrace)
 Finally
  If orclConn.State = ConnectionState.Open Then
   orclConn.Close   
  End If
 End Try
End Sub


Sub Main()
 Console.WriteLine("Program Start")
 'Dim CObj1 As New clsConn()
 oleTest
 odbcTest
 orclTest

End Sub
End Module

The output will look like this:



The above example illustrates how to connect using the different data providers – the syntax for connecting are identical no matter which provider you select. The difference lies in the class and connection string used. All the connection are placed within try-catch-finally blocks (and testing the connection state and closing it where necessary) to ensure that the connection is closed. This approach will not work if exception is thrown within the exception section (since this will stop the execution and go to the next catch statement - possibly outsite the current function or procedure).

It is critical that all database connection be closed after use otherwise the connection will not be returned to the connection pool. (IIS automatically creates and manages database connection pooling). If the connections are not returned to the connection pool, IIS will have to keep creating new connections to add to the connection pool and in time, it will degrade the performance of the server.

Microsoft has replaced ResultSet (in ASP and previous version of Microsoft languages - VB6, VC++ 6) with DataReader and DataAdapter. The preparedstatement in ADO is replaced with Command. These are linked to the specific provider type (e.g. OleDBDataAdapter, OleDBDataReader, OleDBCommand).

Data Query Options
Microsoft also introduced some new supporting classes such as DataSet, DataTable, DataRow, DataColumns and DataView (for in memory database manipulation), which is independent of the data providers.

The following users the emp sample table provided by Oracle (when you create a new database). The sql statement used is:

Select * from emp;

DataReader
A sample:

' Example of a database Reader
Public Sub doDBReader
 Console.WriteLine("Data adapter test")
 Dim dbCmd As OleDBCommand
 Dim dbReader As OleDBDataReader

 dbCmd = Conn.CreateCommand
 dbCmd.CommandText = sql
 dbReader = dbCmd.ExecuteReader
 
 While dbReader.Read
  Console.WriteLine("Rec is " & dbReader("ename"))
 End While
 dbReader.Close
 dbCmd.Dispose
 
End Sub

The dataReader is a read only and forward only stream. It uses minimal memory because by default only one row is in memory at a time. Also, the data can be used as soon as it is available (for DataTable all the rows have to be loaded into memory before it is). The database connection has to be maintained for as long as access to the datareader object is needed (once closed the items and objects with the datareader is no longer available).

Most database connection allows only for one open datareader (only exception is OracleConnection). As such, it is important to close the datareader immediately after use.

DataAdapter

A DataAdapter is normally used to populate the data from the database into either a datatable or a dataset.

A Dataset can contain multiple datatables. A DataTable can contain Multiple DataRow and a DataRow can contain multiple DataColumns. You can think of a DataSet as a database consisting of one or many tables. As in a database, you can also create views – i.e. DataView. The connection to the database don’t need to remain open after the dataset or datatable is populated.

A sample:

 Console.WriteLine("Data adapter test")
Dim dbCmd As OleDBCommand
Dim dbAdapt As New OleDBDataAdapter
Dim ds As New DataSet
 
 
dbCmd = Conn.CreateCommand
dbCmd.CommandText = sql
dbAdapt.SelectCommand = dbCmd
dbAdapt.Fill(ds, "empTab")
dbcmd.Dispose
CloseConn()
Console.WriteLine("Number of tables " & ds.Tables.Count)

Dim dbTab As DataTable = ds.Tables("empTab")
Console.WriteLine("Number of rows " & dbTab.Rows.Count)
 
Dim dRow As DataRow

For Each dRow In dbTab.Rows
 console.WriteLine("Rec is " & drow("ename"))
Next
 
Console.WriteLine("Number of columns are " & dbTab.Columns.Count)

The above example uses the DataCommand to query the database – the step is as follows:
1. Open the database connection.
2. Create a datacommand.
3. Assign an sql statement to the database command.
4. Assign the database command to an instantiated dataadapter (The database adapter can be instantiated during declaration as shown above).
5. Fill an instantiated Dataset. (when doing this, we need to provide the name for the table we are populating by providing a name – “empTab” in the above example).

After populating the datatable, the connection to the database may be closed and the datacommand disposed (to free up the memory used by the datacommand). The content of the data table is identical to the content obtained through a datareader.

DataView
DataView can be used to filter the records within the DataTable (similar to the way records are filtered using where clauses in sql select statements).

 Dim dView As DataView

dView = New DataView(dbTab, "eName Like 'A%'", "", DataViewRowState.CurrentRows)
Console.WriteLine("Filtered rows " & dView.Count)

Dim drView As DataRowView
 
For Each drView In dview
 console.WriteLine("Rec " & drview.Row("ename"))
Next

The dataview above example gets only records containing eName starting with “A”.

DataTable Select
Framework 2.0 introduced a select method in the datatable class. It can serve the same function as a DataView. As in the previous example, only name starting with A is selected. The select method will return an array of datarows:

 Dim dRowArray As DataRow()
 dRowArray = dbTab.Select("eName Like 'A%'")
 Console.WriteLine("Using Datatable select")
 Console.WriteLine("Filtered rows " & dRowArray.Length)
   
 For Each dRow In dRowArray
  Console.WriteLine("Rec " & dRow("ename"))
 Next

Complete Program Listing
The complete program is as follows:

' Chapter 2 - Data Query
' 
' User: Strovek
' Revised
' Date: 8/23/2005
'
' ========================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Reflection


<Assembly: AssemblyTitle("DB Query")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyCompany("Programmer's Journal")>
<Assembly: AssemblyProduct("")>
<Assembly: AssemblyCopyright("Programmer's Journal")>
<Assembly: AssemblyTrademark("")>
<Assembly: AssemblyVersion("1.0.0")>
<Assembly: AssemblyFileVersion("1.0.0")>

Namespace PJ

 Public Class clsDBQry
  
  Dim connStr as string = System.Configuration.ConfigurationSettings.AppSettings("connStr")
  Dim Conn As New OleDBConnection(System.Configuration.ConfigurationSettings.AppSettings("connStr"))
  Dim sql As String = "select * from emp"

  
  ' For opening the database connection
  Public Sub OpenConn()
   Conn.Open   
  End Sub
  
  ' Will Close the database connection if it is open
  Public Sub CloseConn()
   If Conn.State = ConnectionState.Open Then
    Conn.Close
   End If
  End Sub
  
  
  ' Example of a database Reader
  Public Sub doDBReader
   Console.WriteLine("Data adapter test")
   Dim dbCmd As OleDBCommand
   Dim dbReader As OleDBDataReader
   
   dbCmd = Conn.CreateCommand
   dbCmd.CommandText = sql
   dbReader = dbCmd.ExecuteReader
   
   While dbReader.Read
    Console.WriteLine("Rec is " & dbReader("ename"))
   End While
   dbReader.Close
   dbCmd.Dispose
   
  End Sub
  
  ' Example of a DataBase Adapter/DataSet/DateTable/DataColumn/DataView
  Public Sub doDataSet
   
   Console.WriteLine("Data adapter test")
   Dim dbCmd As OleDBCommand
   Dim dbAdapt As New OleDBDataAdapter
   Dim ds As New DataSet
   
   
   dbCmd = Conn.CreateCommand
   dbCmd.CommandText = sql
   dbAdapt.SelectCommand = dbCmd
   dbAdapt.Fill(ds, "empTab")
   dbcmd.Dispose
   CloseConn()
   Console.WriteLine("Number of tables " & ds.Tables.Count)

   Dim dbTab As DataTable = ds.Tables("empTab")
   Console.WriteLine("Number of rows " & dbTab.Rows.Count)
   
   Dim dRow As DataRow
   
   For Each dRow In dbTab.Rows
    console.WriteLine("Rec is " & drow("ename"))
   Next
   
   Console.WriteLine("Number of columns are " & dbTab.Columns.Count)
   
   Dim dView As DataView
   
   dView = New DataView(dbTab, _
"eName Like 'A%'", "", DataViewRowState.CurrentRows)
   Console.WriteLine("Filtered rows " & dView.Count)
   
   Dim drView As DataRowView
   
   For Each drView In dview
    console.WriteLine("Rec " & drview.Row("ename"))
   Next
   
   
   
  End Sub
  
  ' Routine to return the version of this program
     Public Shared Function Ver() As String
          Return System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).ProductVersion
     End Function   
 
  Public Shared Sub Main()
   Console.WriteLine("Program Start - Version " & Ver())
   
   Dim qryObj As New clsDBQry
   
   Try
    qryObj.OpenConn
    qryObj.doDBReader
    qryObj.doDataSet
   Catch ex As Exception
   Console.WriteLine("Error has occurred " & vbNewLine & ex.Message())
    Console.WriteLine(ex.StackTrace())
   Finally
    qryObj.CloseConn    
   End Try
   Console.WriteLine("End Program")
   
  End Sub
 End Class
End Namespace

The output is as follows:

Program Start - Version 1.0.0
Data adapter test
Rec is SMITH
Rec is ALLEN
Rec is WARD
Rec is JONES
Rec is MARTIN
Rec is BLAKE
Rec is CLARK
Rec is SCOTT
Rec is KING
Rec is TURNER
Rec is ADAMS
Rec is JAMES
Rec is FORD
Rec is MILLER
Data adapter test
Number of tables 1
Number of rows 14
Rec is SMITH
Rec is ALLEN
Rec is WARD
Rec is JONES
Rec is MARTIN
Rec is BLAKE
Rec is CLARK
Rec is SCOTT
Rec is KING
Rec is TURNER
Rec is ADAMS
Rec is JAMES
Rec is FORD
Rec is MILLER
Number of columns are 8
Filtered rows 2
Rec ALLEN
Rec ADAMS
End Program

It is important to keep in mind that the DataAdapter may be using DataReader internally. This is important to keep in mind when writing a multi-threading application that could cause more than one DataAdapter to be executed at the same time (against the same database connection) which may affect you program (due to single open DataReader per database connection limitation).

This chapter has shown you how you can connect to the database and retrieve the data. This will serve as a foundation for the rest of the book.