none
How do I "close" the connection to a database .mdf file? RRS feed

  • Question

  • WHAT I HAVE:

    Visual Basic 2010, .NET Framework 4.0, Entity Framework 4.0, SQL Express

    WHAT I'VE DONE (actual problem follows later) SO FAR:

    I think I finally figured out how to create in instance of a database using my entity model completely from scratch. The following code creates a database with catalog name DBName and physical .mdf file name DBFileName (neither the catalog or file exist beforehand, but the folder of the file must already exist):

    	Public Function CreateTheDatebase(Byval DBName As StringByval DBFileName As StringAs Boolean
    	'   create database using model
    	Using objectContext As SocialContactsTracker = New SocialContactsTracker( _
    			"metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;" _
    			& "provider=System.Data.SqlClient;" _
    			& "provider connection string=" _
    				& "'data source=.\SQLEXPRESS;" _
    				& "initial catalog =""" & DBName & """;" _
    				& "attachdbfilename=""" & DBFileName & ".mdf"";" _
    				& "integrated security=True;connect timeout=30;user instance=True;" _
    				& "multipleactiveresultsets=True;App=EntityFramework'")
    		Try
    			objectContext.CreateDatabase()
    			objectContext.AcceptAllChanges()
    		 Catch ex As Exception
    			MessageBox.Show(ex.GetType.ToString & vbCrLf & ex.ToString) : Return False
    		End Try
    	End Using
    	Return True
    	End Function
    

    True or False: This code doesn't require a pre-existing instance of a database which uses my model (i.e., SocialContactsTracker.mdf), provided that the model files defined in the ObjectContext opening statement (the 3 Model1 files) exist. (??)

    MY CURRENT PROBLEM:

    It seems that once I've created the .mdf database file, I can't reference the file again in a connection string--say, opening the connection again with "attachdbfilename=" & DBFileName & ";",--or, for that matter, delete either the .mdf file or its .ldf log file--without triggering an "already in use" exception. It seems as if the connection to a specific file lasts for the life of the program run. Is there a way to disconnect the file after the database has been created? If I want to be able to create and drop a given database at will, I've got a slight problem here.


    Robert Gustafson













    Saturday, October 5, 2013 12:37 AM

Answers

  • Maybe, you would want to do a  Try/Catch/Finally.

    In the finally, objectContext.Dispose

    Yeah yeah, I know the Using is suppose to do it, but I don't trust the Using statement to close/dispose (got burnt one time thinking the Using was going to do it but it didn't),  and I always explicitly do a close or dispose on the Finally, when it comes to database stuff. 

    Saturday, October 5, 2013 9:22 AM