|
Subject:
|
Global SQL connection
|
|
Posted By:
|
MichaelTJ
|
Post Date:
|
10/9/2003 6:08:57 PM
|
Hi all,
I was coding in ASP before. And resently started with ASP.NET. When I coded in ASP I used to make a SQL connection function in a file I called config.inc. I called this file with
<!-- #Include File="config.inc" -->
And used the SLQ connection info by calling the function Connect()
I'm now want to make something simmilar in an ASPX.VB, but after studying some web pages and some books (from Wrox) I just can't figure out how to do this...
So if anyone have done this in some way all help will be accepted with delight..
In advance thanks!
Michael
|
|
Reply By:
|
planoie
|
Reply Date:
|
10/9/2003 6:34:03 PM
|
Try some searches on the forums, there are numerous threads regarding this topic. Here are a couple I found:
http://p2p.wrox.com/topic.asp?TOPIC_ID=5019&SearchTerms=global,variable
http://p2p.wrox.com/topic.asp?TOPIC_ID=2064&SearchTerms=global,variable
Also, you can search for "web.config" or "ConfigurationSettings". Most of the solutions involve the use of that configuration file and call.
Peter
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
10/9/2003 7:09:28 PM
|
Thank Peter... I looked at a reply you made to this other guy..
**********************Copied text from other reply******************************
Public Class myCommonFunctions
Public Shared Sub myFunction()
End Sub
End Class
Now, elsewhere in your code you can just call the functions like this:
myCommonFunctions.myFunction()
***********************************************************
This looks like what I need.
I now my next question may be a bit newbee, but in what file do I put this? config.web?
*****************************
I used to use:
Const CONSTRING = "provider=sqloledb;Server=sql.myweb.com;UID=user;Database=BASE;PWD=Password"
Function Connect()
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.CursorLocation = 3
Conn.Open(CONSTRING)
Conn.Execute("set dateformat dmy")
End Function
******************************
And whenever I needed a connection I just called connect(). This was SOOOOO simple and nice to use.
Any "spoon fed" pointers??
|
|
Reply By:
|
planoie
|
Reply Date:
|
10/10/2003 11:03:58 AM
|
In you web application create a new class file, just a plain class (maybe "DBhelpers.vb").
Then you have something that ends up looking like this...
Imports System.Data.SqlClient
Public Class DBHelpers
Public Const CONSTRING As String = "provider=sqloledb;Server=sql.myweb.com;UID=user;Database=BASE;PWD=Password"
Public Shared Function GetDBConn() As SqlConnection
Dim objConn As SqlConnection
objConn.ConnectionString = CONSTRING
objConn.Open()
Dim objCommand As New SqlCommand("set dateformat dmy", objConn)
objCommand.ExecuteNonQuery()
objCommand.Dispose()
Return objConn
End Function
End Class
Peter
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
10/10/2003 11:32:10 AM
|
Thanks Peter... this is exactly what I needed...
All help is Good help!
Regards
Michael
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
10/10/2003 5:14:57 PM
|
Hi again all...
I've tied out this new code, but I get one error no mather what I do with it.
*********************Error in Browser*******************************
Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 15: Public Shared Function Conn() As SqlConnection
Line 16: Dim objConn As SqlConnection
******************Error Line**************************
Line 17: objConn.ConnectionString = CONSTRING
******************Error Line**************************
Line 18: objConn.Open()
******************CODE********************************
Imports System.Data.SqlClient
Public Class GBV
Public Shared Function Connect() As SqlConnection
Dim Conn = New SqlConnection
Dim Command As New SqlCommand("set dateformat dmy", Conn)
Conn.ConnectionString = "provider=sqloledb;Server=sql.myweb.com;UID=User;Database=BASE;PWD=Password"
Conn.Open()
End Function
End Class
************************From File .ASPX.VB***********************
GBV.Connect()
Response.Write(GBV.Connect())
Dim SQ As Data.SqlClient.SqlDataReader
Dim strTable As String
Try
GBV.Connect.CreateCommand.CommandText = ("select meTable from BASE where meTable = '" & MobileNr.Text & "'")
SQ = GBV.Connect.CreateCommand.ExecuteReader()
Response.Write(SQ)
Do While SQ.Read = True
strTable = SQ("meTable")
Loop
Catch ex As Exception
Response.Write(ex.Message)
End Try
SQ.Close()
*********************Tried*********************
I've tried encasulating everything in ()
Setting the ConnectionString as an variable and hardcoded and constant
raw copy of the code above
++++
After 5 hours I thought I'd try here again!
All help is Good help!
Regards
Michael
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
10/10/2003 6:50:20 PM
|
IT WORKS... :d
After another 2-3 hours of testing I finally got it to work:
**************************GlobalVar.vb**************************
Public Class DB2
Dim myCon As SqlConnection
Public Shared Function SqlCon()
Dim myCon As New SqlConnection
myCon.ConnectionString = "workstation id=COMPUTERNAME;packet size=4096;user id=User;PWD=Password;data source=""sql.myweb.com"";persist security info=False;initial catalog=Table"
myCon.Open()
Return myCon
End Function 'CreateSqlConnection
******************************TEST.ASPX.VB**************************
DB2.SqlCon()
Dim SQ As Data.SqlClient.SqlDataReader
Dim strmeTable As String
Dim myCommand As New System.Data.SqlClient.SqlCommand("set dateformat dmy", DB2.SqlCon)
Try
myCommand.CommandText = ("select * from MEMBERS where meTable = 'test'")
SQ = myCommand.ExecuteReader()
Do While SQ.Read = True
strmeTable = SQ("meTable")
Loop
Catch ex As Exception
Response.Write(ex.Message)
End Try
SQ.Close()
******************************END CODE**********************
All help is Good help!
Regards
Michael
|
|
Reply By:
|
jhouse
|
Reply Date:
|
10/10/2003 6:55:52 PM
|
when you are using System.Data.SqlClient you must leave the provider=sqloledb; out of the connection string. Although the error I get is "Keyword not supported: 'provider'. "
|
|
Reply By:
|
jhouse
|
Reply Date:
|
10/10/2003 7:19:36 PM
|
Whoops looks like i was late with my reply.
You may want to take a different approach; your connection is never being closed as far as I can tell.
you may want to take a look at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp see the section "Pooling with the SQL Server .NET Data Provider" in addition I recommend downloading the Data Access Application Block even if you don't use it take a look at the code http://msdn.microsoft.com/library/en-us/dnbda/html/daab-rm.asp?frame=true
SqlClient automatically creates pooled connections for you but "Developers should be careful not to rely on the garbage collector to free connections because a connection is not necessarily closed when the reference goes out of scope. This a common source of connection leaks, resulting in connection exceptions when new connections are requested."
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
10/10/2003 7:48:39 PM
|
Your just in time jhouse *smile*
That was my next question here, I've just been trying to close down my SQL connections using ANY type of command I could find in Visual.Studio. But as u guessed, to No avail. So I'm going to print out your links and have myself a bit of bed time reading (time fly when your having fun.. he he he, it's 02:43 here!)
Just as a thought, I've been trying to get some variables from one function in a class to another.. Is this possible??
*****************************GlobalVar.vb****************''
Public Class DB2
***Function1**
Public Shared Function SqlCon() As SqlConnection
Dim myCon As New SqlConnection
myCon.ConnectionString = "packet size=4096;user id=User;PWD=Password;data source=192.168.0.10;persist security info=False;Database=BASE"
myCon.Open()
Return myCon
End Function 'CreateSqlConnection
******Function2**
Public Shared Function SqlDisCon()
Dim myCon As New SqlConnection
Dim isNull
If myCon Is Nothing Then ' Conn is nothing not(Conn = Nothing)
myCon.Close()
myCon = Nothing
End If
myCon.Close()
End Function
End Class
****************************************
A big thanks to Peter and jhouse for quick and helpful replys to a newbee! (bear with me huh?)
All help is Good help!
Regards
Michael
|
|
Reply By:
|
planoie
|
Reply Date:
|
10/14/2003 8:32:03 AM
|
Public Class DB2
Private Shared m_objConn As SqlConnection
Public Shared Function SqlCon() As SqlConnection
m_objConn = New SqlConnection
m_objConn.ConnectionString = "packet size=4096;user id=User;PWD=Password;data source=192.168.0.10;persist security info=False;Database=BASE"
m_objConn.Open()
Return m_objConn
End Function 'CreateSqlConnection
Public Shared Sub SqlDisCon()
If Not m_objConn Is Nothing Then
If m_objConn.State = ConnectionState.Open Then
m_objConn.Close()
m_objConn = Nothing
End If
End If
End Sub
End Class
Peter
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:03:54 PM
|
Just a little update from last time:
My page globalvar.vb now contains a number of functions I call when needed in my pages. I'll post them here (Thay may be of use to somebody somewhere (I hope.. he hehe))
My page (Globalvar.vb) starts like this:
'*********************PAGE START*********************
Imports System.Data.SqlClient
Friend Class SQL
Private Shared m_objConn As SqlConnection
Private Shared m_objConn2 As SqlConnection
' CONSTANTS
Public Const DbString As String = "packet size=4096;user id=username;PWD=password;data source=sql.mydomain.com;persist security info=False;Database=MYBASE"
Public Const DbString2 As String = "packet size=4096;user id=username;PWD=password;data source=sql.mydomain.com;persist security info=False;Database=MYBASE"
Public Const AuthCODE As String = "hRE74mF4Loogh38s64jF9Llots5noF6Sj83NsD59sM" 'DO NOT MODIFY
Public Const UploadCatalog As String = "D:\inetpub\wwwroot\myweb\upload\"
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:13:09 PM
|
This next bit I use to call up an SQL connection on any page on my site.
'**************GETTING AN SQL CONNECTION*****************
Public Shared Function Connect() As SqlConnection
m_objConn = New SqlConnection
m_objConn.ConnectionString = DbString '**Using the constant above
m_objConn.Open()
Return m_objConn
End Function '**CreateSqlConnection
Public Shared Function Connect2() As SqlConnection
m_objConn2 = New SqlConnection
m_objConn2.ConnectionString = DbString'**Using the constant above
m_objConn2.Open()
Return m_objConn2
End Function '**CreateSqlConnection
'***************END SQL CONNECTION IN globalvar.vb*************
'*****************TO USE THE CODE ABOVE IN MYPAGE.ASPX*********************
Dim SQ As Data.SqlClient.SqlDataReader
Dim SQLConnect As Data.SqlClient.SqlConnection
SQLConnect = SQL.Connect()
Dim SQLCmd As New System.Data.SqlClient.SqlCommand("set dateformat dmy", SQLConnect)
'********************************************************************
'Get info from table TABLE
'********************************************************************
Dim strVar1, strVar2 As String
Try
SQLCmd.CommandText = ("select SQLVar1, SQLVar2 from TABLE where Var1 = '" & Session.Item("AnyVar") & "'")
SQ = SQLCmd.ExecuteReader()
Do While SQ.Read = True
strVar1 = SQ("SQLVar1")
strVar2 = SQ("SQLVar2")
strmeNick = SQ("meNick")
Loop
Catch ex As Exception
Response.Write("Error in mypage.aspx: " & ex.Message)
Finally
SQ.Close()
SQLCmd.Connection.Close()
SQLConnect.Close()
End Try
'********************END USING CODE*******************************
------------------------
All help is Good help!
Regards
Michael
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:18:57 PM
|
This next part is to just execte an SQL command like INSERT, DELETE, UPDATE....
'****************CODE TO EXECUTE SQL COMMANDS IN Globalvar.vb*************
Public Shared Function Execute(ByVal SQLStatement As String) As Boolean
Dim SQLConnection As New System.Data.SqlClient.SqlConnection
Dim SQLCommand1 As New System.Data.SqlClient.SqlCommand
Try
SQLConnection.ConnectionString = DbString
SQLConnection.Open()
Catch ex As Exception
Try
SQLConnection.ConnectionString = DbString2
SQLConnection.Open()
Catch ex1 As Exception
End Try
End Try
SQLCommand1.Connection = SQLConnection
SQLCommand1.CommandText = SQLStatement
SQLCommand1.ExecuteNonQuery()
SQLConnection.Close()
End Function
'************END EXECUTE SQL COMMAND IN Globalvar.vb*************
'*************TO USE THE CODE ABOVE IN MYPAGE.ASPX**************
SQL.Execute("Insert into TABLE (SQLVar1, SQLVar2)" values ('" & Request("strVar1") & "', '" & SQL.URLEncode(Textbox1.Text) & "')")
'*************END USE THE CODE ABOVE IN MYPAGE.ASPX**************
------------------------
All help is Good help!
Regards
Michael
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:24:19 PM
|
This here is a nice way to URL encode a string for use with sending SMS ++
'********URL ENCODE A STRING in Globalvar.vb******************
Public Shared Function URLEncode(ByVal strData As String) As String
Dim i As Integer
Dim strTemp As String
Dim strChar As String
Dim strOut As String
Dim intAsc As Integer
strTemp = Trim(strData)
For i = 1 To Len(strTemp)
strChar = Mid(strTemp, i, 1)
intAsc = Asc(strChar)
Select Case intAsc
Case 48 To 57, 65 To 90, 97 To 122
strOut = strOut & strChar
Case Else
strOut = strOut & "%" & Hex(intAsc)
End Select
Next i
strOut = Replace(strOut, "%D%A", "%0d%0a")
strOut = Replace(strOut, "linBR", "%0d%0a")
URLEncode = strOut
End Function
'******************END globalvar.vb CODE***************
'*************TO USE THE CODE ABOVE IN MYPAGE.ASPX**************
Dim strEncodedText as String = SQL.URLEncode(TextBox1.Text)
Response.Write("Encoded text from textbox: " & strEncodedText)
'*************END USE THE CODE ABOVE IN MYPAGE.ASPX**************
------------------------
All help is Good help!
Regards
Michael
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:27:39 PM
|
This here is to decode the string
'*******TO DECODE THE STRING in Globalvar.vb*************
Public Shared Function URLDecode(ByVal strConvert)
Dim arySplit
Dim strHex
Dim strOutput
Dim i As Integer
Dim letter
If strConvert = "" Then
URLDecode = ""
Exit Function
End If
'**First convert the + to a space
strOutput = Replace(strConvert, "+", " ")
'**Convert %0d%0a to linebreake for ASPX
strOutput = Replace(strConvert, "%0d%0a", "%3Cbr%3E")
'**Then convert the %number to normal code
arySplit = Split(strOutput, "%")
If IsArray(arySplit) Then
strOutput = arySplit(i)
For i = LBound(arySplit) To UBound(arySplit) - 1
strHex = "&H" & Mid(arySplit(i + 1), 1, 2)
letter = Chr(strHex)
strOutput = strOutput & letter & Right(arySplit(i + 1), Len(arySplit(i + 1)) - 2)
Next
End If
URLDecode = strOutput
End Function
'******************END globalvar.vb CODE***************
'*************TO USE THE CODE ABOVE IN MYPAGE.ASPX**************
Dim strDecodedText as String = SQL.URLDecode(strEncodedText)
Response.Write("Decoded text: " & strDecodedText)
'*************END USE THE CODE ABOVE IN MYPAGE.ASPX**************
|
|
Reply By:
|
MichaelTJ
|
Reply Date:
|
12/16/2003 8:34:08 PM
|
This last bit is a unique ID creator and a random 4 digit password generator
'*******UNIQUE ID AND PWD GENERATOR in Globalvar.vb*************
Public Shared Function SMSID()
SMSID = Right(Now.Year, 2) & Format(Now.Month, "00") & Format(Now.Day, "00") & Format(Now.Hour, "00") & Format(Now.Minute, "00") & Format(Now.Second, "00") & Format(Now.Millisecond, "0000")
End Function
Public Shared Function rndPassword()
Dim pwd, i, ch
pwd = Second(Now())
pwd = pwd & Int(437657 * Rnd())
For i = 1 To 6
Randomize()
ch = Int(26 * Rnd()) + 97 & Second(Now())
pwd = pwd & ch
If i = 3 Then pwd = pwd & Second(Now())
Next
pwd = Right(pwd, 4)
rndPassword = pwd
End Function
End Class
'******************END globalvar.vb CODE***************
'*************TO USE THE CODE ABOVE IN MYPAGE.ASPX**************
'**Unique ID
Response.Write("Unique ID: " & SQL.SMSID)
'Random Password
Response.Write("Random Password: " & SQL.rndPassword())
'*************END USE THE CODE ABOVE IN MYPAGE.ASPX**************
After all the help I have received here in Wrox, I hope this will be useful to someone out there.
------------------------
All help is Good help!
Regards
Michael
|
