I am trying to create a VB.NET web application that uses MySQL, I am currently using classic asp and need some guidance.
Just now I include db_inc.asp file for each asp page and I can simply use the following subroutines to pull data without needing to recreate ado/connection string for each page.
Basically I am looking for some help to implement a very similar approach in VB.NET using the mySQL
Call GetFromDataBase("SELECT * FROM Users WHERE User_Id = 1", oRsTemp1)
Call GetFromDatabasePerPage("SELECT * FROM Users WHERE User_Id = 1", oRsTemp1) # incudes paging
Call UpdateDataBase("UPDATE users SET name=’test’ WHERE User_Id = 1")
Example
mySQL = " SELECT name FROM users;"
Call GetFromDataBase(mySQL, oRsTemp1)
If Not oRsTemp1.EOF Then
Do While Not oRsTemp1.EOF
response.Write oRsTemp1("name")
<%
'***********************************************
'FUNCTIONS TO EXECUTE AGAINST THE DATABASE
'***********************************************
'-----------------------------------------------
' Store Connection String
'-----------------------------------------------
Dim oConnectionString
blnShowDebugStatsQueries = True
blnShowDebugStats = True
blnShowDebugStatsQueries = True
oConnectionString = "DSN=test;DATABASE=testdb;Persist Security Info=True;Pooling=False"
'-----------------------------------------------
' CREATE DATABASE CONNECTION
'-----------------------------------------------
If Not blnInstall then
Set oDataConn = Server.CreateObject("ADODB.Connection")
oDataConn.Open oConnectionString
Set oCommand = Server.CreateObject("ADODB.Command")
Set oRsTemp1 = Server.CreateObject("ADODB.Recordset")
Set oRsTemp2 = Server.CreateObject("ADODB.Recordset")
Set oRsTemp3 = Server.CreateObject("ADODB.Recordset")
End If
strDateDelimiter = "'"
numCursorType = 3
'-----------------------------------------------
' SET EXECUTION STATS
' Counters for execution. This is useful for debug stats
'-----------------------------------------------
Dim numTotalExecuteRS, numTotalExecuteScalar, numTotalExecuteNonQuery, numQueryExecutionTime, strQueryBuildup
numTotalExecuteRS = 0: numTotalExecuteScalar = 0: numTotalExecuteNonQuery = 0
numQueryExecutionTime = 0
QueryBuildup = ""
'-----------------------------------------------
'Functions called for error reporting
'-----------------------------------------------
Sub ReportQueryError(Query, strErrorDescription)
If blnUpgrade = true then
'Do nothing
Else
'Close any tags we may be in
response.Write "<div id=""dbtrappederror""></textarea></select>"
response.Write "<div style=""width: 80%; font-family:verdana; font-size:12px; font-weight:normal; background-color: #eeeeee; margin: 10pt 10pt 10pt 10pt; padding: 20pt 20pt 20pt 20pt; border: 1pt solid black"">"
response.Write "<p style=""font-weight: bold"">An error occurred executing the following query, and page execution has been terminated.</p>"
response.Write "<p style=""font-family:courier new; font-size:14px; color:#800000"">" & Query & "</p>"
response.write "<p>Error Description : " & strErrorDescription & "</p>"
response.write "<p>Page of error : " & Request.ServerVariables("URL") & "</p>"
response.write "<p>Query String : <br /><br />" & Request.QueryString & "</p>"
response.Write "</div></div>"
Call EndPage()
response.end
End if
End Sub
'-----------------------------------------------
' Executes the query, and records the recordset
' filled with the results
'-----------------------------------------------
Sub GetFromDataBase(Query, rsTemp)
'Start error trapping
If Not (blnDoingTransaction Or blnDoingCallback) Then On Error Resume Next
'Start the timer for query execution
numStartTime = Timer
'Execute query
oCommand.CommandText = Query
oCommand.CommandType = 1
Set oCommand.ActiveConnection = oDataConn
rsTemp.CursorLocation = 3
rsTemp.Open oCommand, , 1, numCursorType
'Increment count and query time
numQueryExecutionTime = numQueryExecutionTime + (Timer - numStartTime)
numTotalExecuteRS = numTotalExecuteRS + 1
QueryBuildup = QueryBuildup & "<li>" & WriteSafe(Query) & "<br />ExecuteRS - Time: " & Round(Timer - numStartTime, 2) * 100 & "ms<br /><br /></li>"
'Report error, end error trapping
if err.Description <> "" and not blnDoingTransaction then Call ReportQueryError(Query, err.Description)
if not (blnDoingTransaction or blnDoingCallback) then on error goto 0
End Sub
'-----------------------------------------------
'Executes a non-query - i.e. one that returns
'no results (INSERT, UPDATE)
'-----------------------------------------------
Sub UpdateDataBase(Query)
'Start error trapping
if not (blnDoingTransaction or blnDoingCallback) then On Error Resume Next
'Start the timer for query execution
numStartTime = Timer
'Execute query
oDataConn.Execute(Query)
'Increment count and query time
numQueryExecutionTime = numQueryExecutionTime + (Timer - numStartTime)
numTotalExecuteNonQuery = numTotalExecuteNonQuery + 1
QueryBuildup = QueryBuildup & "<li>" & WriteSafe(Query) & "<br />ExecuteNonQuery - Time: " & Round(Timer - numStartTime, 2) * 100 & "ms<br /><br /></li>"
'Report error, end error trapping
if err.Description <> "" and not blnDoingTransaction then Call ReportQueryError(Query, err.Description)
if not (blnDoingTransaction or blnDoingCallback) then on error goto 0
End Sub
'-----------------------------------------------
' Executes the query, and records the recordset
' filled with the results
'-----------------------------------------------
Sub GetFromDatabasePerPage(Query, rsTemp)
'Start error trapping
if not (blnDoingTransaction or blnDoingCallback) then On Error Resume Next
'Start the timer for query execution
numStartTime = Timer
'Execute query
oCommand.CommandText = Query
oCommand.CommandType = 1
Set oCommand.ActiveConnection = oDataConn
rsTemp.CursorLocation = 3
rsTemp.Open oCommand, , 1, numCursorType
'Increment count and query time
numQueryExecutionTime = numQueryExecutionTime + (Timer - numStartTime)
numTotalExecuteRS = numTotalExecuteRS + 1
QueryBuildup = QueryBuildup & "<li>" & WriteSafe(Query) & "<br />ExecuteRS - Time: " & Round(Timer - numStartTime, 2) * 100 & "ms<br /><br /></li>"
'Report error, end error trapping
if err.Description <> "" and not blnDoingTransaction then Call ReportQueryError(Query, err.Description)
if not (blnDoingTransaction or blnDoingCallback) then on error goto 0
End Sub
'-----------------------------------------------
' This sub closes all the connections. Should be called at
' the bottom of everypage, and before and page redirect
'-----------------------------------------------
Sub EndPage()
'Close all database stuff
oDataConn.Close
set oRsTemp1 = nothing
set oRsTemp2 = nothing
set oRsTemp3 = nothing
set oCommand = nothing
set oDataConn = nothing
'Show debug stats at bottom of page
'If blnShowDebugStats then
'End script execution timer
numScriptExecutionTime = Timer - numScriptExecutionStartTime
response.Write "<div style=""text-align: left; width: 600px; font-family:verdana; font-size:12px; font-weight:normal; background-color: #eeeeee; margin: 10pt 10pt 10pt 10pt; padding: 20pt 20pt 20pt 20pt; border: 1pt solid black"">"
response.write "Total Querys Executed : " & numTotalExecuteRS + numTotalExecuteScalar + numTotalExecuteNonQuery & "<br />"
response.write "Recordsets : " & numTotalExecuteRS & "<br />"
response.write "Scalars : " & numTotalExecuteScalar & "<br />"
response.write "Nonquerys : " & numTotalExecuteNonQuery & "<br />"
response.Write "<br />"
response.Write "Total Page Execution Time : " & round(numScriptExecutionTime, 2) * 100 & "ms<br />"
response.Write "queries : " & round(numQueryExecutionTime, 2) * 100 & "ms<br />"
response.Write "scripts : " & round(numScriptExecutionTime - numQueryExecutionTime, 2) * 100 & "ms<br />"
response.Write "<br />"
'Show all the queries that have executed
'If blnShowDebugStatsQueries then
response.Write "Queries Executed:<br />"
response.Write "<span style=""font-size: 11px; font-family: courier new;""><ul>" & QueryBuildup & "</ul></span>"
'End If
response.Write "</div>"
'end if
End Sub
mySQL = " SELECT name FROM users;"
Call GetFromDataBase(mySQL, oRsTemp1)
If Not oRsTemp1.EOF Then
Do While Not oRsTemp1.EOF
response.Write oRsTemp1("name")
oRsTemp1.MoveNext
Loop
End If
oRsTemp1.Close
Call EndPage()
%>
Submit an article or tip