Object-Oriented Report Development using Crystal Reports and ASP.NET 2.0 - The DBHelper class for interacting with the database
(Page 2 of 4 )
I developed this class for simplicity. You can extend it to make it more robust by including several other features such as caching, stored procedure parameters, and so forth. If you are familiar with the Microsoft Data Access Block (part of Microsoft Enterprise Library), you can even include that, too.
Create a new web site and add a new class named "DBHelper." The following is the code which contains a few methods to carry out most important tasks related to the database:
Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Public Class DBHelper
Private Shared Function getConnectionString() As String
Return System.Configuration.ConfigurationManager.ConnectionStrings
("SampleDB").ConnectionString
End Function
Public Shared Sub SQLExecute(ByVal strSQL As String)
Dim Conn As New SqlConnection(getConnectionString)
Dim cmd As New SqlCommand(strSQL, Conn)
With cmd
.Connection.Open()
.ExecuteNonQuery()
.Connection.Close()
.Dispose()
End With
End Sub
Public Shared Function getRow(ByVal strSELECT As String) As DataRow
Dim da As New SqlDataAdapter(strSELECT, getConnectionString)
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
If dt.Rows.Count = 0 Then Return Nothing Else Return dt.Rows(0) 'return only
first row
End Function
Public Shared Function getDataTable(ByVal strSELECT As String) As DataTable
Dim Conn As New SqlConnection(getConnectionString)
Dim da As New SqlDataAdapter(strSELECT, Conn)
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
Return dt
End Function
Public Shared Function getRowValue(ByVal strSELECT As String) As String
Dim Conn As New SqlConnection(getConnectionString)
Dim cmd As New SqlCommand(strSELECT, Conn)
Dim value As String = ""
With cmd
.Connection.Open()
value = .ExecuteScalar() & "" 'concatenating an empty string..to eliminate null
or nothing
.Connection.Close()
.Dispose()
End With
Return value
End Function
Public Shared Function SPgetDataTable(ByVal SPname As String) As DataTable
Dim cmd As New SqlCommand(SPname, New SqlConnection
(getConnectionString))
cmd.CommandType = CommandType.StoredProcedure
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()
Return dt
End Function
End Class
The web.config should have a ConnectionString entry as follows:
<connectionStrings>
<add name="SampleDB" connectionString="Data Source=.sqlexpress;initial
catalog=Northwind;user id=sa;password=eXpress2005"/>
</connectionStrings>
Next: Developing classes to hold information from the database >>
More ASP.NET Articles
More By Jagadish Chaterjee
