web 2.0

ASP.NET(vb.net) & Oracle Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0)

ASP.NET(vb.net) & Oracle Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0) - This is  example scripts how to use ASP.NET and Oracle database on Visual Studio 2005,2008,2010 (.NET 2.0,3.5,4.0)

ShotDev Focus:
- ASP.NET(vb.net) & Oracle Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0)

Example

clsDatabase.vb


Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OracleClient
Imports System.Configuration
Public Class clsDatabase
Private objConn As OracleConnection
Private objCmd As OracleCommand
Private Trans As OracleTransaction
Private strConnString As String

Public Sub New()
strConnString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")
End Sub

Public Function QueryDataReader(ByVal strSQL As String) As OracleDataReader
Dim dtReader As OracleDataReader
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New OracleCommand(strSQL, objConn)
dtReader = objCmd.ExecuteReader()
Return dtReader '*** Return DataReader ***'
End Function

Public Function QueryDataSet(ByVal strSQL As String) As DataSet
Dim ds As New DataSet
Dim dtAdapter As New OracleDataAdapter
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
objCmd = New OracleCommand
With objCmd
.Connection = objConn
.CommandText = strSQL
.CommandType = CommandType.Text
End With
dtAdapter.SelectCommand = objCmd
dtAdapter.Fill(ds)
Return ds   '*** Return DataSet ***'
End Function

Public Function QueryDataTable(ByVal strSQL As String) As DataTable
Dim dtAdapter As OracleDataAdapter
Dim dt As New DataTable
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
dtAdapter = New OracleDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
Return dt '*** Return DataTable ***'
End Function

Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New OracleCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Return True '*** Return True ***'
Catch ex As Exception
Return False '*** Return False ***'
End Try
End Function

Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
Dim obj As Object
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Try
objCmd = New OracleCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
obj = objCmd.ExecuteScalar()  '*** Return Scalar ***'
Return obj
Catch ex As Exception
Return Nothing '*** Return Nothing ***'
End Try
End Function

Public Function TransStart()
objConn = New OracleConnection
With objConn
.ConnectionString = strConnString
.Open()
End With
Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
End Function

Public Function TransExecute(ByVal strSQL As String) As Boolean
objCmd = New OracleCommand()
With objCmd
.Connection = objConn
.Transaction = Trans
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
End Function

Public Function TransRollBack()
Trans.Rollback()
End Function

Public Function TransCommit()
Trans.Commit()
End Function

Public Sub Close()
objConn.Close()
objConn = Nothing
End Sub

End Class

Default.aspx.vb


Imports System.Data
Imports System.Data.OracleClient
Partial Class _Default
Inherits System.Web.UI.Page
Dim clsDB As New clsDatabase

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Call myDataReader()
Call myDataSet()
Call myDataTable()
Call myQueryExecuteScalar()
Call myExecuteNonQuery()
Call myExecuteTransaction()
End Sub

'*** DataReader ***'
Sub myDataReader()
Dim strSQL As String
Dim dtReader As OracleDataReader
strSQL = "SELECT * FROM customer "
dtReader = clsDB.QueryDataReader(strSQL)
Me.myGridView1.DataSource = dtReader
Me.myGridView1.DataBind()

'*** Bind Rows ***'
'If dtReader.HasRows = True Then
'    Me.lblCustomerID.Text = dtReader.Item("CustomerID")
'    Me.lblName.Text = dtReader.Item("Name")
'    Me.lblEmail.Text = dtReader.Item("Email")
'    Me.lblCountryCode.Text = dtReader.Item("CountryCode")
'    Me.lblBudget.Text = dtReader.Item("Budget")
'    Me.lblUsed.Text = dtReader.Item("Used")
'End If
clsDB.Close()
End Sub

'*** DataSet ***'
Sub myDataSet()
Dim strSQL As String
Dim ds As DataSet
strSQL = "SELECT * FROM customer "
ds = clsDB.QueryDataSet(strSQL)
Me.myGridView2.DataSource = ds.Tables(0).DefaultView
Me.myGridView2.DataBind()

'*** Bind Rows ***'
'If ds.Tables(0).Rows.Count > 0 Then
'    Me.lblCustomerID.Text = ds.Tables(0).Rows(0)("CustomerID")
'    Me.lblName.Text = ds.Tables(0).Rows(0)("Name")
'    Me.lblEmail.Text = ds.Tables(0).Rows(0)("Email")
'    Me.lblCountryCode.Text = ds.Tables(0).Rows(0)("CountryCode")
'    Me.lblBudget.Text = ds.Tables(0).Rows(0)("Budget")
'    Me.lblUsed.Text = ds.Tables(0).Rows(0)("Used")
'End If
clsDB.Close()
End Sub

'*** DataTable ***'
Sub myDataTable()
Dim strSQL As String
Dim dt As DataTable
strSQL = "SELECT * FROM customer "
dt = clsDB.QueryDataTable(strSQL)
Me.myGridView3.DataSource = dt
Me.myGridView3.DataBind()

'*** Bind Rows ***'
If dt.Rows.Count > 0 Then
Me.lblCustomerID.Text = dt.Rows(0)("CustomerID")
Me.lblName.Text = dt.Rows(0)("Name")
Me.lblEmail.Text = dt.Rows(0)("Email")
Me.lblCountryCode.Text = dt.Rows(0)("CountryCode")
Me.lblBudget.Text = dt.Rows(0)("Budget")
Me.lblUsed.Text = dt.Rows(0)("Used")
End If
clsDB.Close()
End Sub

'*** Execute Scalar ***'
Sub myQueryExecuteScalar()
Dim strSQL As String
strSQL = "SELECT MAX(Budget) FROM customer "
Me.lblText.Text = clsDB.QueryExecuteScalar(strSQL)
clsDB.Close()
End Sub

'*** ExecuteNonQuery ***'
Sub myExecuteNonQuery()
Dim strSQL1, strSQL2, strSQL3 As String

'*** Insert ***'
strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
" VALUES('C005','Weerachai Nukitram','webmaster@thaicreate.com','TH','200000','100000')"
If clsDB.QueryExecuteNonQuery(strSQL1) = True Then
'*** Condition Success ***'
Else
'*** Condition Error ***'
End If
clsDB.Close()

'*** Update ***'
strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' "
If clsDB.QueryExecuteNonQuery(strSQL2) = True Then
'*** Condition Success ***'
Else
'*** Condition Error ***'
End If
clsDB.Close()

'*** Delete ***'
strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' "
If clsDB.QueryExecuteNonQuery(strSQL3) = True Then
'*** Condition Success ***'
Else
'*** Condition Error ***'
End If
clsDB.Close()
End Sub

'*** Execute Transaction ***'
Sub myExecuteTransaction()
Dim strSQL1, strSQL2, strSQL3 As String

'*** Start Transaction ***'
clsDB.TransStart()

Try
'*** Insert ***'
strSQL1 = "INSERT INTO customer (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
" VALUES('C005','Weerachai Nukitram','webmaster@thaicreate.com','TH','200000','100000')"
clsDB.TransExecute(strSQL1) '*** Execute Query 1 ***'

'*** Update ***'
strSQL2 = "UPDATE customer SET Budget = '3000000' WHERE CustomerID = 'C005' "
clsDB.TransExecute(strSQL2) '*** Execute Query 2 ***

'*** Delete ***'
strSQL3 = "DELETE FROM customer WHERE CustomerID = 'C005' "
clsDB.TransExecute(strSQL3) '*** Execute Query 3 ***

'*** Commit Transaction ***'
clsDB.TransCommit()

Catch ex As Exception
'*** RollBack Transaction ***'
clsDB.TransRollBack()
End Try
clsDB.Close()
End Sub

End Class

Screenshot

ASP.NET(vb.net) & Oracle Database Class (Visual Studio 2005,2008,2010 - .NET 2.0,3.5,4.0)
.
.
.
Download this script.
Download

1 Star2 Stars3 Stars4 Stars5 Stars6 Stars7 Stars8 Stars9 Stars10 Stars (1 votes, average: 10.00 out of 10)
Loading ... Loading ...

Leave a Reply

You must be logged in to post a comment.