'**************************************************************************
'@author Abhijeet Kulkarni
'@version 1.0
'Development
Environment : MS Visual Studio .NET
'Name of the
File : DBAccess.vb
'Creation/Modification
History :
'
15-Apr-2003 Created
'Overview: This
file is used by the FetchSize application. This file defines
'a class called
as DBAccess. This class has methods that allow the application
'to connect to
the database and create necessary table required to run the
'application.
Along with some support methods, it also has the methods that
'populate the
DataGrid and set/get RowSize.
'**************************************************************************
Imports Oracle.DataAccess.Client
Imports
System.Windows.Forms.TextBox
Public Class
DBAccess
Protected conn As OracleConnection
Public isConnected As Boolean
Public sbox As TextBox
Public dataAdapter As OracleDataAdapter
Public ocmd As OracleCommand
Public oreader As OracleDataReader
'************************************************************************
'The constructor.
'************************************************************************
Public Sub
New(ByRef box As TextBox)
MyBase.new()
sbox = box
'isConnected is set to False (Not
connected state)
isConnected = False
End Sub
'************************************************************************
'This method tries to connect to the
database using given connection
'parameters.
'************************************************************************
Public Function
connect(ByVal username As
String, ByVal
password As String,
ByVal datasource As
String) As String
'Create the connect string
Dim Str As String = New String("User
Id=" + username + ";Password=" + password + ";Data
Source=" + datasource)
Dim errStr As String = "Not
Connected"
Try
'Retrieve connection object
sbox.AppendText("Connecting to the database.." +
Environment.NewLine)
sbox.Update()
'Create new OracleConnection
instance
conn = New
OracleConnection(Str)
'Open the connection
conn.Open()
'Set the isConnected to
true(Connected State)
isConnected = True
'Display message
errStr = "Connected to the database as " +
username
sbox.AppendText(errStr + Environment.NewLine)
sbox.Update()
Catch ex As Exception
'If there is an error
isConnected is set to false
isConnected = False
'Display message
sbox.AppendText("Error ocurred while connecting to
the Database" + Environment.NewLine)
sbox.AppendText("Error Message" +
ex.ToString() + Environment.NewLine)
End Try
Return errStr
End Function
'************************************************************************
'This method creates producttab table
required by this application and
'inserts some data in the table.
'************************************************************************
Public Function
CreateTable()
Try
sbox.AppendText("Dropping the table
countrytab" + Environment.NewLine)
sbox.Update()
'Create an OracleCommand
object using the connection object
Dim cmd As OracleCommand = New
OracleCommand("DROP TABLE producttab", conn)
cmd.CommandType = CommandType.Text
Try
'Drop the table if it
exists
cmd.ExecuteNonQuery()
Catch ex As Exception
sbox.AppendText("Table does not exist" +
Environment.NewLine)
sbox.Update()
End Try
sbox.AppendText("Creating database table " +
Environment.NewLine)
sbox.Update()
'Create table countrytab
cmd = New
OracleCommand("CREATE TABLE producttab(productname VARCHAR2(30) PRIMARY
KEY, description VARCHAR2(100),
category VARCHAR2(30),price NUMBER(10,2))", conn)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
sbox.AppendText("Table Created" +
Environment.NewLine)
sbox.Update()
sbox.AppendText("Inserting data in created
table" + Environment.NewLine)
sbox.AppendText("Please wait this may take some
time..." + Environment.NewLine)
sbox.Update()
'Insert rows in the table
Dim i As Integer
For i = 0 To 999
cmd = New
OracleCommand("INSERT INTO producttab VALUES(:productname,:description,:category,:price)",
conn)
Dim prm1 As OracleParameter = New
OracleParameter("productname", OracleDbType.Varchar2)
prm1.Direction = ParameterDirection.Input
prm1.Value = "Product" + i.ToString()
Dim prm2 As
OracleParameter = New
OracleParameter("description", OracleDbType.Varchar2)
prm2.Direction = ParameterDirection.Input
prm2.Value = "Description" + i.ToString()
Dim prm3 As OracleParameter = New
OracleParameter("category", OracleDbType.Varchar2)
prm3.Direction = ParameterDirection.Input
prm3.Value = "Category" + i.ToString()
Dim prm4 As OracleParameter = New
OracleParameter("price", OracleDbType.Double)
prm4.Direction = ParameterDirection.Input
Dim doub As Double
doub = Double.Parse("100.30")
doub = doub + i
prm4.Value = doub
cmd.Parameters.Add(prm1)
cmd.Parameters.Add(prm2)
cmd.Parameters.Add(prm3)
cmd.Parameters.Add(prm4)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cmd.Dispose()
Next
sbox.AppendText(i.ToString() + " rows
Inserted" + Environment.NewLine)
sbox.Update()
Catch ex As Exception
'If an exception is caught
set isConnected to false
isConnected = False
sbox.AppendText("An error oucurred while creating
table" + Environment.NewLine)
sbox.AppendText(ex.toString() + Environment.NewLine)
sbox.Update()
End Try
End Function
'************************************************************************
'This method closes and disposes the
connection.
'************************************************************************
Public Function
closeConnection()
Try
'Check if connection is open
or not
If Not conn.State.Closed Then
conn.Close()
conn.Dispose()
End If
Catch ex As Exception
Finally
sbox.AppendText("Connection closed" +
Environment.NewLine)
sbox.Update()
End Try
End Function
'************************************************************************
'This method populates the datagrid.
'************************************************************************
Public Function
PopulateDataGrid(ByRef dg As DataGrid)
'Create a DataTable
Dim dt As DataTable = New
DataTable("producttab")
Dim dcolumn As DataColumn
Dim i As Integer
Dim count As Integer
Dim colname As String
Dim coltype As System.Type
sbox.AppendText("Current FetchSize value is " +
oreader.FetchSize.ToString() + " bytes " + Environment.NewLine)
sbox.Update()
'Get the number of columns in
this OracleDataReader
count = oreader.FieldCount
sbox.AppendText("Fetching data with given
FetchSize.." + Environment.NewLine)
sbox.Update()
'Add the columns in the DataTable
For i = 0 To count - 1
colname = oreader.GetName(i)
coltype = oreader.GetFieldType(i)
dcolumn = New
DataColumn(colname, coltype)
dt.Columns.Add(dcolumn)
Next
'Make the first column the
primary key column.
Dim PrimaryKeyColumns(0) As DataColumn
PrimaryKeyColumns(0) = dt.Columns("productname")
dt.PrimaryKey = PrimaryKeyColumns
'Populate the table
While oreader.Read()
Dim drow As DataRow = dt.NewRow()
drow(oreader.GetName(0)) = oreader.GetString(0)
drow(oreader.GetName(1)) = oreader.GetString(1)
drow(oreader.GetName(2)) = oreader.GetString(2)
drow(oreader.GetName(3)) = oreader.GetDecimal(3)
dt.Rows.Add(drow)
End While
'Assign the DataSource
dg.DataSource = dt
'Dispose and close the
OracleDataReader
oreader.Dispose()
oreader.Close()
End Function
'************************************************************************
'This method creates the
OracleCommand object. Once this object is created
'RowSize becomes available.
'************************************************************************
Public Function
CreateFetchCommand()
'Dispose and close
Try
ocmd.Dispose()
Catch ex As Exception
End Try
Try
oreader.Dispose()
oreader.Close()
Catch ex1 As Exception
End Try
ocmd = New
OracleCommand("SELECT productname AS ""Product Name"",
description AS ""Description""" & _
", category AS ""Category"", price
AS ""Price"" FROM producttab", conn)
ocmd.CommandType = CommandType.Text
oreader = ocmd.ExecuteReader()
End
Function
'************************************************************************
'This method returns RowSize
associated with ocmd object.
'************************************************************************
Public Function
GetRowSize() As Integer
GetRowSize = ocmd.RowSize
End Function
'************************************************************************
'This method sets the FetchSize
before populateDataGrid method is called.
'FetchSize(i) = RowSize *
NumberOfRows per Database trip.
'************************************************************************
Public Function
SetFetchSize(ByVal i As
Integer)
oreader.FetchSize = i
End Function
End Class
Bookmark
