Discuss "Connections" in the .Net Development forum.

Shem · #1 May 23rd, 2008, 07:47 AM

Hi

I need to know how to connect to my db, and how to use it when needed
in my apps. I do know that my conn string goes in the web.config file,
though i'm not sure of how to set this up.

All help will be appreciated

Shem

jmurrayhead · #2 May 23rd, 2008, 07:56 AM

There are several ways to handle this...

One way would be to create a class to handle all of your configuration settings and return a string from the web.config:

Code:

Public Class myConfig
    Public Shared ReadOnly Property myConnection() As String
        Get
            Return ConfigurationManager.ConnectionStrings("yourConStringNameInWebConfig").ConnectionString
        End Get
    End Property
End Class

Then anywhere you need the string:

Code:

Dim conn As SqlConnection = New SqlConnection(myConfig.myConnection)

Of course, if you're using MySQL, it will be a little different, but you'll still use the string value returned from the property to connect to the database.

The preferred way, although a little more difficult, is to create a class that inherits from "ConfigurationSection". I won't get into that, but if you're interested, download the sample from here: Wrox::ASP.NET 2.0 Website Programming: Problem - Design - Solution:Book Information and Code Download

I also highly recommend the book associated with the code.

Shem · #3 May 23rd, 2008, 08:00 AM

Thanks for the help JMH

richyrich · #4 May 23rd, 2008, 08:02 AM

This is how I connect to MySQL db. This doesn't mean it's the best way!

web.config

Code:

  <connectionStrings>
    <add name="myconn" connectionString="Database=mydb;Data Source=mysql.domain.com;Port=3306;User Id=****;Password=****" />
  </connectionStrings>

Data Access Layer

Code:

Imports MySQL.Data.MySqlClient
Imports Company.App.MyBOL
 
    Public Class ConnDAL
        Public Shared ReadOnly Property connString() As String
            Get
                Return System.Configuration.ConfigurationManager.ConnectionStrings.Item("myconn").ConnectionString()
            End Get
        End Property
    End Class
 
    Public Class MyDAL
        Public Shared _error As String
        Public Shared Function Open(ByVal ref As Integer) As MyBOL
            Dim ret As New MyBOL
            Dim conn As MySqlConnection
            Dim mycommand As MySqlCommand
            conn = New MySqlConnection(ConnDAL.connString)
            mycommand = New MySqlCommand("SELECT field1,field2,field3,field4,field5 FROM tbl_name WHERE id=?id")
            mycommand.Connection = conn
            Dim rs As MySqlDataReader
            Try
                mycommand.Parameters.AddWithValue("id", ref)
                conn.Open()
                rsgr = mycommand.ExecuteReader()
                If rsgr.HasRows Then
                    rsgr.Read()
                    ret.property1 = rsgr("field1")
                    ret.property2 = rsgr("field2")
                    ret.property3 = rsgr("field3")
                    ret.property4 = rsgr("field4")
                    ret.property5 = rsgr("field5")
                Else
                    ret.page_error = "No record Found"
                End If
                rsgr.Close()
            Catch ex As Exception
                ret.page_error = ex.Message.ToString
            Finally
                mycommand.Dispose()
                conn.Close()
                conn.Dispose()
            End Try
            Return ret
        End Function
    End Class

You'll need to have the MySQL .NET connector in your bin folder.

Hope that helps.

Shem · #5 May 23rd, 2008, 08:09 AM

Hey RR, where do ya keep your DAL,BOL,BLL? are they folders and your classes
kept in their respective folder? and are these folders in the App_Code folder?

Shem

richyrich · #6 May 23rd, 2008, 08:12 AM

The DAL, BOL and BLL are seperate files that contain the Data Access classes, Business Object classes and Business Logic classes respectively.

The files are all in the App_Code folder.

Shem · #7 May 23rd, 2008, 08:14 AM

Quote:

Originally Posted by richyrich

The DAL, BOL and BLL are seperate files that contain the Data Access classes, Business Object classes and Business Logic classes respectively.

The files are all in the App_Code folder.

thanks again

ps. can't rep you or JMH yet

jmurrayhead · #8 May 23rd, 2008, 08:15 AM

This is a matter of preference, but I have the following folder structure:

App_Code
-DAL
-BLL
-BO

I also have a separate class file for each class for better organization.

richyrich · #9 May 23rd, 2008, 08:16 AM

Quote:

Originally Posted by jmurrayhead

This is a matter of preference, but I have the following folder structure:

App_Code
-DAL
-BLL
-BO

I also have a separate class file for each class for better organization.

Makes sense.

Does having seperate folders/files have any effect on performance J?