Help! Need quick answer...

[Chad A. Beckner]

Does anyone know how to make common database connections "common" in all
aspx files? In other words, instead of creating a SQLConnection, then a
DataAdapter in every ASPX file for my application, I would like to "include"
them, or "import", say an ascx file. In ASP, all you needed to to was
include another .asp file to do this.

Many thanks,

Chad

 

[Marina]

I would recommend you create a base class that inherits from Page that has
all that code. Then have all your pages inherit from the custom class
instead of from Page.

 

[Curt_C [MVP]]

you mean open the connection once and reuse it without re-opening? DONT...
BAD BAD practice.
Now if you simply mean the string for the connection and such, put it in a
parent class that you have all the others inherit from.

 

[Chad A. Beckner]

No, No, I don't want to keep the connection open. All I want is to create
some common code that will "allow" me to re-use a "standard" DataAdapter,
instead of re-creating the SQL connections on every page. Can you give me
some info (either code or a good website page) of where I could find an
example (a few mentioned inheriting from a custom class"???

Thanks,

Chad

 

[BluDog]

Does anyone know how to make common database connections "common" in all

aspx files? In other words, instead of creating a SQLConnection, then a
DataAdapter in every ASPX file for my application, I would like to "include"
them, or "import", say an ascx file. In ASP, all you needed to to was
include another .asp file to do this.

In Global.asax create a public shared variable something like this:

Public Shared DataSource As String = _
"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\db\data.mdb"

Then whenever you want to use it simpley use:

Global.DataSource

Hope this helps.

Blu

 

[Chad A. Beckner]

Ok, I have added it to my global.asa file, but when I try to view/edit the
sql statement, it says that I don't have an active connection. Ideas?

Chad

 

[BluDog]

Ok, I have added it to my global.asa file, but when I try to view/edit the
sql statement, it says that I don't have an active connection. Ideas?

Chad

You still need to create a connection when you need to use the
database, the above simply sets up a global connection string.

Here's what i do. I have created a class called data that i can used
anywhere in the project, this is for a oledb but it is easy enough to
change to SQL. I have a bunch of functions that i use regularly for my
database work:

<code>

Option Strict On

Imports System.Data.OleDb

Public Class Data

#Region "Retrevial"

Public Shared Function GetRow(ByVal Table As String, ByVal Key As
Guid) As DataRow

Dim DataSet As DataSet = GetDataSet("SELECT * FROM " & Table &
" WHERE ID = {" & Key.ToString & "}")
If DataSet.Tables(0).Rows.Count > 0 Then Return
DataSet.Tables(0).Rows(0)

End Function

Public Shared Function GetRow(ByVal Table As String, ByVal Key As
String, ByVal KeyField As String) As DataRow

Dim DataSet As DataSet = GetDataSet("SELECT * FROM " & Table &
" WHERE " & KeyField & " = " & Key)
If DataSet.Tables(0).Rows.Count > 0 Then Return
DataSet.Tables(0).Rows(0)

End Function

Public Shared Function GetValue(ByVal Table As String, ByVal Field
As String, ByVal Key As Guid) As String

Dim DataSet As DataSet = GetDataSet("SELECT " & Field & " FROM
" & Table & " WHERE ID = {" & Key.ToString & "}")
If DataSet.Tables(0).Rows.Count > 0 Then Return
DataSet.Tables(0).Rows(0).Item(0).ToString

End Function

Public Shared Function GetValue(ByVal Table As String, ByVal Field
As String, ByVal Key As String, ByVal KeyField As String) As String

Dim DataSet As DataSet = GetDataSet("SELECT " & Field & " FROM
" & Table & " WHERE " & KeyField & " = " & Key)
If DataSet.Tables(0).Rows.Count > 0 Then Return
DataSet.Tables(0).Rows(0).Item(0).ToString

End Function

Public Shared Function GetID(ByVal Table As String, ByVal Key As
String, ByVal KeyField As String) As Guid

Dim DataSet As DataSet = GetDataSet("SELECT ID FROM " & Table
& " WHERE " & KeyField & " = " & Key)
If DataSet.Tables(0).Rows.Count > 0 Then Return
CType(DataSet.Tables(0).Rows(0).Item(0), Guid)

End Function

Public Shared Function GetCount(ByVal Table As String, ByVal Key
As Guid) As Integer

Dim DataSet As DataSet = GetDataSet("SELECT COUNT(ID) FROM " &
Table & " WHERE ID = {" & Key.ToString & "}")
Try
Return CInt(DataSet.Tables(0).Rows(0).Item(0))
Catch ex As Exception
Return 0
End Try

End Function

Public Shared Function GetCount(ByVal Table As String, ByVal
Filter As String) As Integer

Dim DataSet As DataSet = GetDataSet("SELECT COUNT(ID) FROM " &
Table & " WHERE " & Filter)
Try
Return CInt(DataSet.Tables(0).Rows(0).Item(0))
Catch ex As Exception
Return 0
End Try

End Function

Public Shared Function GetCount(ByVal Query As String) As Integer

Dim DataSet As DataSet = GetDataSet(Query)
Try
Return DataSet.Tables(0).Rows.Count()
Catch ex As Exception
Return 0
End Try

End Function

Public Shared Function GetDataSet(ByVal Query As String) As
DataSet

Dim DataAdapter As New OleDbDataAdapter(Query,
Global.DataSource)
GetDataSet = New DataSet
DataAdapter.Fill(GetDataSet)
Return GetDataSet

End Function

#End Region

#Region "Append"

Public Shared Function Execute(ByVal Query As String) As String

Dim Connection As New OleDbConnection(Global.DataSource)
Connection.Open()
Dim Command As OleDbCommand = New OleDbCommand(Query,
Connection)
Try
Command.ExecuteNonQuery()
Catch ex As Exception
Execute = ex.Message & vbLf & vbLf & Query
End Try
Connection.Close()

End Function

#End Region

End Class

</code>

This is all very specific to my needs, but have a look at the
GetDataSet function, i think this is pretty much what you are after.
To create a dataset in the project all i have to do is the following
call:

Dim MyDataSet as DataSet = Data.GetDataSet("SELECT * FROM MyTable")


Hope this helps

Blu