I created the Fortunate.Data object to limit the amount of code that needed to be written to get any type of data object back and to make certain that data connections get closed when an error occurrs.
The Fortunate.Data object may be used to perform the following common database tasks:
- EscapeString
- ExecuteNonQuerySQL
- ExecuteScalarSQL
- GetDataSet
- AddTableToDataSet
- OpenDataReader
- CloseDataReader
The Fortunate.Data object will get the connection string and automatically determine if it's connecting to SQL Server or Access. It will then return any type of data object requested - DataSet, DataTable, DataReader, Scalar, etc.
When working with a web applicaton I like to place the Fortunate.Data object on the master page of the application and make it available to all my other pages from that one central location. I do so like this:
Master Page Code:
Private _Data As Fortunate.Data
Public ReadOnly Property DataObject() As Fortunate.Data
Get
Call InitializeDataObject()
Return _Data
End Get
End Property
Private Sub InitializeDataObject()
If _Data Is Nothing Then
Call InitializeApplicationObject()
Me._Data = New Fortunate.Data
'
'data1
'
Me._Data.DatabaseConnectionStringName = "MyDatabaseConnectionString"
End If
End Sub
Please note that the code above includes giving the Fortunate.Data object a ConnectionStringName which needs to be placed into the web.config file just like any other connection string:
<add name="MyDatabaseConnectionString" connectionString="Data Source=MyServer\SQL2005;Initial Catalog=MyDatabase;Integrated Security=True" providerName="System.Data.SqlClient"/>
To access the ReadOnly DataObject property from a web page the page needs to know what type of master page is being used. Note the second line of code in this page which tells the page's code-behind what master page is being used:
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" EnableSessionState="True" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" title="Default" %>
<%@ MasterType VirtualPath="~/masterPage.master" %>
Now in the code-behind accessing your database is as simple as creating your SqlCommand and calling the data object:
'---Create the stored procedure
Dim SQLCommand As New Data.SqlClient.SqlCommand()
SQLCommand.CommandType = Data.CommandType.StoredProcedure
SQLCommand.CommandText = "procCodeLibraryGetPagedEntries"
'---Add the parameters
With SQLCommand
.Parameters.Add("@TotalEntries", Data.SqlDbType.Int).Direction = Data.ParameterDirection.Output
.Parameters.Add("@PageIndex", Data.SqlDbType.Int).Value = _PageIndex
.Parameters.Add("@PageSize", Data.SqlDbType.Int).Value = CType(PageSizeDropDownList.SelectedValue, Int32)
End With
'---Get the DataTable
Dim DataTable As Data.DataTable = Master.DataObject.GetDataTable(SQLCommand, "tblPagedCodeEntries")
|