SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Open Close db Connection: Best Practice

    Hi,

    I am a Classic ASP developer and now learning ASP.net.
    In Classic ASP, I developed a few functions that Connects, Executes Queries & Disconnect databases like for example:

    PHP Code:
    //To Connection to db
      
    set objConn connectDB() 
    PHP Code:
    //Exceute Select Query
      
    set rRecordSet executeQuery("sql query here"objConn
    PHP Code:
    //Disconnect database
      
    disConnect(objConn
    Is there any Expert .Net developer who can guide me on how to create similar functions in .Net so that I do not have to write same db connection code in each page of application.


    Thanks!

  2. #2
    SitePoint Zealot davejuk's Avatar
    Join Date
    Apr 2006
    Location
    UK
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do not access your database at all from within the page.

    And why would you want to create your own methods to open and close your database. You can't get much simpler than objConn.Close.

  3. #3
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually I am just looking for best way to connect to database and execute sql statement and then close the connection. Right now I have to write following code to each page of website I am working on.

    Dim strConnection As String = ConfigurationManager.ConnectionStrings("objConn").ConnectionString
    Dim objConn As New SqlConnection(strConnection)

    objConn.Open()

    Dim dAdapter As New SqlDataAdapter
    dAdapter.SelectCommand = New SqlCommand("SELECT * FROM tblCMS WHERE lngContentId=1", objConn)

    Dim dSet As New DataSet
    dAdapter.Fill(dSet)

    objConn.Close()

  4. #4
    SitePoint Addict itHighway's Avatar
    Join Date
    Jan 2008
    Location
    GUJ
    Posts
    270
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please help.

  5. #5
    SitePoint Zealot behati's Avatar
    Join Date
    Feb 2012
    Location
    Copenhagen, Denmark
    Posts
    152
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    I find that the "using" function keyword is the way to go. Basically it lets you write any method and set it to be "using" your SQLconnection, meaning that the connection is automatically closed upon leaving the method scope. What you do is you create connection Pools depending on a connection string, if the connection string is the same for a connection then the same pool is used, if not a new one is created as described in this MSDN article.

    Here's an example from the article, working with Connection pools with the "using" keyword:

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
    {
    connection.Open();
    // Pool A is created.
    }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=pubs"))
    {
    connection.Open();
    // Pool B is created because the connection strings differ.
    }

    using (SqlConnection connection = new SqlConnection(
    "Integrated Security=SSPI;Initial Catalog=Northwind"))
    {
    connection.Open();
    // The connection string matches pool A.
    }
    Hope that helps

    In all honesty, .NET takes care of most of it for you, it opens/closes the connections as described above and even manages your resources while doing so. Such is the power of .NET

  6. #6
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Microsoft ADO. NET is a bad design.
    Although easy to learn but programmers spend a lot of work with it just to do simple things.

    If you want to simplify programming, you should use the class SQLhelper from Data Access Application Block in Microsoft Enterprise Library .
    It is easy to understand and save the energy to type the code.

  7. #7
    SitePoint Enthusiast
    Join Date
    Jun 2007
    Location
    Marysville, CA
    Posts
    59
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Store your connection string in your Web.Config and access it from a DALC class:

    static string connectionString = ConfigurationManager.ConnectionStrings["cccConn"].ConnectionString;


    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    ...
    }


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •