Help! Need quick answer...

  • Thread starter Thread starter Chad A. Beckner
  • Start date Start date
C

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
 
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.
 
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.
 
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
 
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.
Click to expand...

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
 
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
 
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?
Click to expand...

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
 
Back
Top