.NET question

[Winged Spider]

I'm calling my database connection way to many times during the course of the App. I've experimented with a global.aspx file but .NET wants me to declare my Connection in every single sub program I have. Anybody have any tips on making my conn string more centralized?

[dhtmlgod]

There is a number of ways you could create a connection to the database.

One of which would be to use the global.asax file. But your could also use a custom control, or encasulate it into a business object.

I would suggest creating a business object, with all the sql/database stuff within it to easlily and efficently use the database.

And with the advanced caching features of ASP.NET, you could cache the business object so it will work even faster.

If you would like me to create the business object for you, just supply me with all your database code and SQL statements and I'll knock it uo for you.

[Winged Spider]

Thanks for the offer but I would rather learn to fish before I ate. Could you instead provide sample code of how a basic buisness database object would work or direct me to a good link? Any help is very appreciated.

I just read the chapter on page caching, what a great feature. Thanks for the idea.

[dhtmlgod]

Open your favourite text/html editor and type this

[vbs]
Imports System
Imports System.Sata
Imports System.Sata.OleDB

Namespace DatabaseStuff

Public Class theDBStuff
Public strCon as String
Private objCon as OleDBConnection
Private objCmd as OleDbCommand

Public Function doSelect(selectSQL as string) as OleDBDataReader
Try
objCon = new OleDBConnection(strConn)
objCmd = new OleDBCommand(selectSQL, objCon)
objCmd.Connection.Open
return objCmd.ExecuteReader
objCmd.Connection.close()
catch exp as OleDBException
return nothing
end try
end function

end class
end namespace
[/vbs]

Should be easy to follow, tho if you have questions, just ask.



Now save it as testDB.vb

Now in the root of the ASP.NET application, create a new folder caled bin. Now click start > run... and type command

When this comes up, navigate to the directory where the vb file is, and then type:

[vbs]
vbc /t:library /out:\bin\testDB.dll /r:system.dll /r:system.data.dll testDB.VB
[/vbs]

The VBC is the VB.NET compiler, which has tons of options, but I don't know anymore yet, lol . I'll go over what I do know

/t = type of file to output

/out = the directory and filename to output, must go into th /bin folder

/r = reference, and we referenced the system and system.data namespaces beacuse we used them and the vb file



Hope you understood that, if not, just let me know



Now to use it...

[vbs]
<%@ Page Language="VB" %>
<%@ System.Data %>
<%@ System.Data.OleDB %>

<script runat="server">
Sub page_load(obj as object, e as eventargs)
Dim objDB as new Database.TheDBStuff
objDB.strCon = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\inetpub\aspdotnet\data.mdb"

Dim objReader = OleDBDataReader
objReader = objDB.doSelect("SELECT * FROM tblsBlah")

If not objReader is nothing then
' Do stuff with Data
end if
end sub
</script>
[/vbs]



Hope that helps!

[Winged Spider]

Thanks a lot, I created the buisness component just fine, but decided on another method to speed up the code. I used another method instead just because the buisness object didn't decrease the lines of code I had to use. Here's my source code so far. Could this be improved any?

Sub Page_Load(s as Object, e As EventArgs)
	Dim conMain As OleDbConnection
	Dim cmdMain As OleDbCommand
	Dim dtrMain As OleDbDataReader

	conMain = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=d:/whatever" )
	conMain.Open()
	
	cmdMain = New OleDbCommand (  "SELECT Top 10 * FROM headlines ORDER BY headlinedate DESC", conMain )
	dtrMain = cmdMain.ExecuteReader()
	While dtrMain.Read()
	headlines.innerHTML = headlines.innerHTML & "<b>" & dtrMain( "headlinedate" )  & "</b> - " & dtrMain( "headline" ) & "<br />" 
	End While
	dtrMain.Close

	cmdMain = New OleDbCommand (  "SELECT * FROM ccnetbooks ORDER BY NetDate DESC", conMain )
	dtrMain = cmdMain.ExecuteReader()
	While dtrMain.Read()
	update.innerHTML = update.innerHTML & "<b>" & dtrMain( "NetDate" ) & "</b> - <a href=" & dtrMain( "link" ) & ">" & dtrMain( "netbook" ) & "</a> - " & dtrMain( "Netupdate" ) & "<br />" 
	End While
	dtrMain.Close
	
	cmdMain = New OleDbCommand (  "SELECT * FROM ccnetbooks ORDER BY netbook", conMain )
	dtrMain = cmdMain.ExecuteReader()
	While dtrMain.Read()
	netbook.innerHTMl = netbook.innerHTML & "of <a href=" & dtrMain( "link" )  & ">" & dtrMain( "netbook" ) & "</a><br />" 
	End While
	dtrMain.Close

	conMain.Close

	End Sub

[westmich]

Forgive my .NET ignorance, but couldn't something like what I used in ASP work; seems simpler.

'placed in a global include
Dim oConn, sConn

Public Sub openDB()
 'set your connection string here
 sConn = "DSN=myDSN"
 Set oConn = CreateObject("ADODB.Connection")
 oConn.open sConn
End Sub

Public Sub closeDB()
 oConn.close
 Set oConn = Nothing
End Sub

'On any given asp page
openDB()
'one or more recordsets
closeDB()

[Winged Spider]

I could of used that method also. The way I'm doing it now only opens the connection once. The Page_Load function reads all the SQL statements and populates the data into the HTML page directly. My problem with opening a database multiple times, or even using it on the page multiple times doesn't apply anymore.

A little explanation of my .NET code:

This:

headlines.innerHTML

Is used to populate the the inside of a <p> or any tag you feel like using.

<p id="headlines" runat="server" />

Is what you would have on your page before the server side executes. And when the database calls go thru it looks like this:

<p id="headlines"><b>1/22/2002</b> - sdfgsdfg<br /><b>12/20/2001</b> - FANcc Officially online<br /></p>

How nice is that? And it gets better, you don't even have to use a paragraph tag. In the last instance code is spit our between a <netbook></netbook> tag.