generic database class in vb.net

D

dave

I'm classic ASP developer and trying to switch to .net ...I'm newbie to ..net...
In classic ASP for ADO connectivity i used to put below code in one separate file and used to include that file whereever I need database opreation...

Dim objDatabase
Set objDatabase = Nothing
'-- Singleton for the CDatabase class
Function Database
If objDatabase Is Nothing Then
Set objDatabase = New CDatabase
End If
Set Database = objDatabase
End Function

'-- Begin CDatabase Class
Class CDatabase

Private m_objConn

Public Sub Class_Initialize()
Set m_objConn = Nothing
End Sub


Public Property Get Connection

'-- Reuse connection if one exist, create a new one otherwise
If m_objConn Is Nothing Then
Set m_objConn = Server.CreateObject("ADODB.Connection")

'On Error Resume Next

'm_objConn.Open Application.Contents.Item("ConnString")
m_objConn.Open SQL_CONNECTION

If Err.number <> 0 Then
Set m_objConn = Nothing
Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorDatabaseConnect")
Response.End
End If
On Error GoTo 0
End If

Set Connection = m_objConn
End Property


Public Function ExecuteQuery (SqlQuery)
Dim objRs
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.Open SqlQuery, Me.Connection
If Err.number <> 0 Then
Set objRs = Nothing
Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If
On Error GoTo 0
Set ExecuteQuery = objRs
End Function

Public Function ExecuteGetID (SqlQuery,tblName)
Dim intRecords
Dim objCmd
intRecords = -1
Set objCmd = Server.CreateObject("ADODB.Command")
'On Error Resume Next
objCmd.ActiveConnection = Me.Connection
objCmd.CommandText = SqlQuery
Call objCmd.Execute(intRecords)
If Err.number <> 0 Then
Set objCmd = Nothing
Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If
Set objRs = Server.CreateObject("ADODB.Recordset")
Set objRs = Me.Connection.Execute("SELECT @@IDENTITY FROM " & tblName)
ExecuteGetID = objRs.fields(0).value
On Error GoTo 0
End Function

Public Function ExecuteCommand (SqlQuery)

Dim intRecords
Dim objCmd

intRecords = -1

'-- Create new command object
Set objCmd = Server.CreateObject("ADODB.Command")

On Error Resume Next

objCmd.ActiveConnection = Me.Connection
objCmd.CommandText = SqlQuery

Call objCmd.Execute(intRecords)

If Err.number <> 0 Then
Set objCmd = Nothing

Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0

ExecuteCommand = intRecords
End Function


Public Function CreateRecordset (SqlQuery, CursorType, LockType)

Dim objRs

'-- Create new recordset object
Set objRs = Server.CreateObject("ADODB.Recordset")

On Error Resume Next

objRs.Open SqlQuery, Me.Connection, CursorType, LockType


If Err.number <> 0 Then
Set objRs = Nothing

Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0
Set CreateRecordset = objRs
End Function

Public Function CreateRecordsetPage (SqlQuery, CursorType, LockType, iPageSize)

Dim objRs

'-- Create new recordset object
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.PageSize = iPageSize
objRs.CacheSize = iPageSize


On Error Resume Next

objRs.Open SqlQuery, Me.Connection, CursorType, LockType


If Err.number <> 0 Then
Set objRs = Nothing

Response.Write "Error-->" & Err.description 'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0
Set CreateRecordsetPage = objRs
End Function


Public Sub Class_Terminate()
Set m_objConn = Nothing
End Sub
End Class

I need similar kind of vb.net version...so whenever i need data, i just need to call class object...
I'm using sql server database and my connection string is stored in web.config file...
Can anyone help me to write class so I have same functionality...
Any help would be appreciated...
Thanx
dave
 
K

Kevin Spencer

Hi Dave,

Well, .Net is a totally different kind of animal from VBScript and ASP.Net,
so you can basically trash that code. For one thing, the .Net platform has
built-in Connection Pooling, which means that not only do you not need to
reuse a Connection object, it is in fact better to open and close them as
quickly as possible.

I had some very similar tools when I was doing ASP, and got into ASP.Net
very early on, so I didn't have some of the conveniences available to you
today, like the freely-downloadable .Net database application access block:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/daab.asp

I would suggest not only using it, but studying it, for your own edification
and education. True Object-Oriented programming is a big meal to eat at one
sitting. Studying the code that Microsoft provides should help you get a
good handle on it.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

I'm classic ASP developer and trying to switch to .net ...I'm newbie to
..net...
In classic ASP for ADO connectivity i used to put below code in one separate
file and used to include that file whereever I need database opreation...

Dim objDatabase
Set objDatabase = Nothing
'-- Singleton for the CDatabase class
Function Database
If objDatabase Is Nothing Then
Set objDatabase = New CDatabase
End If
Set Database = objDatabase
End Function

'-- Begin CDatabase Class
Class CDatabase

Private m_objConn

Public Sub Class_Initialize()
Set m_objConn = Nothing
End Sub


Public Property Get Connection

'-- Reuse connection if one exist, create a new one otherwise
If m_objConn Is Nothing Then
Set m_objConn = Server.CreateObject("ADODB.Connection")

'On Error Resume Next

'm_objConn.Open Application.Contents.Item("ConnString")
m_objConn.Open SQL_CONNECTION

If Err.number <> 0 Then
Set m_objConn = Nothing
Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorDatabaseConnect")
Response.End
End If
On Error GoTo 0
End If

Set Connection = m_objConn
End Property


Public Function ExecuteQuery (SqlQuery)
Dim objRs
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.Open SqlQuery, Me.Connection
If Err.number <> 0 Then
Set objRs = Nothing
Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If
On Error GoTo 0
Set ExecuteQuery = objRs
End Function

Public Function ExecuteGetID (SqlQuery,tblName)
Dim intRecords
Dim objCmd
intRecords = -1
Set objCmd = Server.CreateObject("ADODB.Command")
'On Error Resume Next
objCmd.ActiveConnection = Me.Connection
objCmd.CommandText = SqlQuery
Call objCmd.Execute(intRecords)
If Err.number <> 0 Then
Set objCmd = Nothing
Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If
Set objRs = Server.CreateObject("ADODB.Recordset")
Set objRs = Me.Connection.Execute("SELECT @@IDENTITY FROM " & tblName)
ExecuteGetID = objRs.fields(0).value
On Error GoTo 0
End Function

Public Function ExecuteCommand (SqlQuery)

Dim intRecords
Dim objCmd

intRecords = -1

'-- Create new command object
Set objCmd = Server.CreateObject("ADODB.Command")

On Error Resume Next

objCmd.ActiveConnection = Me.Connection
objCmd.CommandText = SqlQuery

Call objCmd.Execute(intRecords)

If Err.number <> 0 Then
Set objCmd = Nothing

Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0

ExecuteCommand = intRecords
End Function


Public Function CreateRecordset (SqlQuery, CursorType, LockType)

Dim objRs

'-- Create new recordset object
Set objRs = Server.CreateObject("ADODB.Recordset")

On Error Resume Next

objRs.Open SqlQuery, Me.Connection, CursorType, LockType


If Err.number <> 0 Then
Set objRs = Nothing

Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0
Set CreateRecordset = objRs
End Function

Public Function CreateRecordsetPage (SqlQuery, CursorType, LockType,
iPageSize)

Dim objRs

'-- Create new recordset object
Set objRs = Server.CreateObject("ADODB.Recordset")
objRs.PageSize = iPageSize
objRs.CacheSize = iPageSize


On Error Resume Next

objRs.Open SqlQuery, Me.Connection, CursorType, LockType


If Err.number <> 0 Then
Set objRs = Nothing

Response.Write "Error-->" & Err.description
'Application.Contents.Item("ErrorQueryExecute")
Response.End
End If

On Error GoTo 0
Set CreateRecordsetPage = objRs
End Function


Public Sub Class_Terminate()
Set m_objConn = Nothing
End Sub
End Class

I need similar kind of vb.net version...so whenever i need data, i just need
to call class object...
I'm using sql server database and my connection string is stored in
web.config file...
Can anyone help me to write class so I have same functionality...
Any help would be appreciated...
Thanx
dave
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top