locked
How do I close the database in this situation? RRS feed

  • Question

  • User-1416423428 posted

    Here's my working code. Using a separate db, I can do a db.Close() when I'm done. 

      public static string DisplayStoryPhotos(int storyid)
      // display all photos that are assigned to this story
      // using slideshow helper
      {
        // read database
        var db = Database.Open("News2014");
        var selectCommand = @" SELECT * FROM StoryPhoto INNER JOIN 
                               Photo ON StoryPhoto.PhotoID = Photo.PhotoID 
                               WHERE (StoryPhoto.StoryID = @0) 
                               ORDER BY Photo.PhotoSortOrder ";
    
        var photos = db.Query(selectCommand, storyid);
        db.Close();
    
        // create a List<string> of photoids
        List<string> photoids = new List<string>();
        
        // get photoids from database, add to List
        foreach (var photo in photos)
        {
          photoids.Add(photo.PhotoID.ToString());
        }
    
        // call slideshow helper
        return Utilities.DisplaySlideShow(photoids).ToString();
      }

    if I combine the Open and Query() commands, how do I close the database?
    i.e. I won't have a "db" variable anymore.

      public static string DisplayStoryPhotos(int storyid)
      // display all photos that are assigned to this story
      // using slideshow helper
      {
        // read database
        var selectCommand = @" SELECT * FROM StoryPhoto INNER JOIN 
                               Photo ON StoryPhoto.PhotoID = Photo.PhotoID 
                               WHERE (StoryPhoto.StoryID = @0) 
                               ORDER BY Photo.PhotoSortOrder ";
    
        var photos = Database.Open("News2014").Query(selectCommand, storyid);
        // db.Close();
    
        // create a List<string> of photoids
        List<string> photoids = new List<string>();
        
        // get photoids from database, add to List
        foreach (var photo in photos)
        {
          photoids.Add(photo.PhotoID.ToString());
        }
    
        // call slideshow helper
        return Utilities.DisplaySlideShow(photoids).ToString();
      }

    ... or will ASP.NET close the database automatically? 

    Wednesday, June 4, 2014 7:59 PM

Answers

  • User379720387 posted

    I have understood from previous conversations on this subject that one opens the db once per page and that ASP.net will close it for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 4, 2014 9:40 PM
  • User-821857111 posted

    As Wavemaster says, the Web Pages runtime closes connections opened by the Database helper for you, so you don't actually need to worry about it. Having said that, I have crawled through the source code for Web Pages and WebMatrix.Data and haven't found where it does that. Perhaps I haven't looked hard enough...

    When opening a connection using ADO.NET, I use using statements to ensure that the object is closed and Dispose is called on it properly:

    using(var conn = new SqlConnection(connString))
    using(var cmd = new SqlCommand(qry, conn)){
        //....
    }

    Same with contexts in Entity Framework:

    using(var context = new MyModelContext()){
        //.....
    }

    You can also use then for the Database helper:

    using(var db = Database.Open("MyDatabase")){
        //....
    }
    

    The only thing you need to worry about is that anything declared within the using block is scoped just to that block and is not accessible outside it. Also, if using EF, you must call ToList() on a query to ensure it executes, otherwise when you try to reference a collection outside of the using block, you will get an exception saying that the context has already been disposed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 5, 2014 4:36 AM

All replies

  • User379720387 posted

    I have understood from previous conversations on this subject that one opens the db once per page and that ASP.net will close it for you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 4, 2014 9:40 PM
  • User-1416423428 posted

    That got me thinking... in linq sql, one doesn't close a database connection, right?  
    But maybe because linq is taking care of it for the user?

    So is this a better/preferred way? using using{} code blocks? 
    This compiles fine, and runs fine just like the above (2) styles I posted above.

    So now there's 3 ways of doing this. Which one is the "correct" way? 

      public static string DisplayStoryPhotos(int storyid)
      // display all photos that are assigned to this story
      // using slideshow helper
      {
        // create a List<string> of photoids
        List<string> photoids = new List<string>();
        
        // read database
        var selectCommand = @" SELECT * FROM StoryPhoto INNER JOIN 
                               Photo ON StoryPhoto.PhotoID = Photo.PhotoID 
                               WHERE (StoryPhoto.StoryID = @0) 
                               ORDER BY Photo.PhotoSortOrder ";
        
        using (var db = Database.Open("News2014"))
        {
          var photos = db.Query(selectCommand, storyid);
        
          // get photoids from database, add to List
          foreach (var photo in photos)
          {
            photoids.Add(photo.PhotoID.ToString());
          }  
        }
        
        // call slideshow helper
        return DisplaySlideShow(photoids).ToString();
      }

    How do you guys do it?  

    Wednesday, June 4, 2014 10:31 PM
  • User-821857111 posted

    As Wavemaster says, the Web Pages runtime closes connections opened by the Database helper for you, so you don't actually need to worry about it. Having said that, I have crawled through the source code for Web Pages and WebMatrix.Data and haven't found where it does that. Perhaps I haven't looked hard enough...

    When opening a connection using ADO.NET, I use using statements to ensure that the object is closed and Dispose is called on it properly:

    using(var conn = new SqlConnection(connString))
    using(var cmd = new SqlCommand(qry, conn)){
        //....
    }

    Same with contexts in Entity Framework:

    using(var context = new MyModelContext()){
        //.....
    }

    You can also use then for the Database helper:

    using(var db = Database.Open("MyDatabase")){
        //....
    }
    

    The only thing you need to worry about is that anything declared within the using block is scoped just to that block and is not accessible outside it. Also, if using EF, you must call ToList() on a query to ensure it executes, otherwise when you try to reference a collection outside of the using block, you will get an exception saying that the context has already been disposed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 5, 2014 4:36 AM
  • User938738290 posted

    I've been doing it the same way Mike does, with a using block, since he suggested it when I was having DB issues.  Haven't had any problems since then.

    Thursday, June 5, 2014 9:06 AM