467,146 Members | 1,299 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,146 developers. It's quick & easy.

generic database class in vb.net

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

Nov 19 '05 #1
  • viewed: 4492
Share:
1 Reply
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/de.../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.

"dave" <fo****@foo.netREMOVE> wrote in message
news:eD******************@TK2MSFTNGP14.phx.gbl...
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
Nov 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Edward Diener | last post: by
3 posts views Thread by SimonH | last post: by
3 posts views Thread by .Net Newbie | last post: by
3 posts views Thread by markww | last post: by
13 posts views Thread by rkausch@gmail.com | last post: by
4 posts views Thread by tadmill@yahoo.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.