none
How do I make a connection with "master"? RRS feed

  • General discussion

  • WHAT I HAVE:

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

    FIRST QUESTION:

    Try as I might, I can't create a connection string that will directly open the "master" database. I have only been able to make connections on my entity model's "default" database and any secondary databases I create from it--which I do by first opening the default database, executing "CREATE DATABASE" SQL, then opening the new database and executing ObjectContext.CreateDatabaseScript().

    (For example, there is an article, "How to create a SQL Server database programmatically by using ADO.NET and Visual Basic .NET" [http://support.microsoft.com/kb/305079], which opens an SQL connection using which a connection string refers to the server ["(local)\netsdk" in their case, ".\SQLEXPRESS" in mine] and the "master" catalog. When I try to use or adapt this code, I get a "login failed" exception. This also happens if I try to execute "CREATE DATABASE" SQL on an ObjectContext that opens a not-yet-existant database. It would be nice if my program could create databases using my model from scratch at run-time, rather than always needing to start with one "default" already using the model.)

    What do I need to know in order to open the master without first opening one of my databases?

    SECOND QUESTION:

    How do I "close" a database connection that's been made against my entity model? If I create a new database using the model (see "First Question", first paragraph), then try to delete it (using ObjectContext.DeleteDatabase), I get an "already in use" exception when attempting the second operation. (It's as if the connection, even when opened with a local variable, lasts for the life of the program. Does it?) It appears to be difficult to write an app that could create and remove at will databases using the model.


    Robert Gustafson








    Tuesday, September 24, 2013 12:39 AM

All replies

  • I still can't open a connection with, say, "Data Source=.\SQLEXPRESS; Initial Catalog=master;". I always get a "login failed for user '' " exception, as if there's something I don't know about accessing it (and I've tried using Integrated Security and even UserID settings--not that I remember creating any account for yours truly vis-a-vis the master database).

    Besides, what about my SECOND QUESTION? Why can't a delete a database (ObjectContext.DeleteDatabase) in the same application run that I used to create it (CREATE DATABASE SQL)? I always get a connection-in-use exception, even though I already disposed of the ObjectContext instance that created it before attempting delete.


    Robert Gustafson




    Tuesday, September 24, 2013 2:25 PM
  • UPDATE:

    (Remember: VB 2010, .NET 4.0, EF 4.0, SQL EXPRESS)

    I can open the master database using connection string, "Data Source=.\SQLEXPRESS; Initial Catalog=master; Integrated Security=True;" (I don't know why it works now), but I can't execute a "CREATE DATABASE" SqlCommand off it. I CAN do it off of an instance of the ObjectContext for my "original" "default" database.

    The following code, which creates a new instance of a database using my entity model, is shown below in 2 versions.

    This version fails at the sqlCom.ExecuteNonQuery() statement:

    Public Function CreateTheDatebase(Byval DBName As StringByval DBFileName As StringAs Boolean
    Dim CreateDatabaseSQL As StringDatabaseSchema As String
    '   create database using master
    Using SqlCon As SqlConnection = New SqlConnection
    sqlCon.ConnectionString = _
    	"Data Source=.\SQLEXPRESS; Initial Catalog=master; Integrated Security=True;"
    CreateDatabaseSQL = "CREATE DATABASE " & DBName & " ON PRIMARY " _
    	& "(NAME = " & DBName & "_Data, FILENAME = '" & DBFileName & ".mdf', " _
    	& "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) "
    Try
    	Dim sqlCom As SqlCommand = New SqlCommand(CreateDatabaseSQLsqlCon)
    	sqlCon.Open() : sqlCom.ExecuteNonQuery()
     Catch ex As Exception
    	MessageBox.Show(ex.GetType.ToString & vbCrLf & ex.ToString)
     Finally
    	sqlCon.Close()
    End Try
    End Using	
    '   create schema
    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 & """;" _
    		& "integrated security=True;connect timeout=30;user instance=True;" _
    		& "multipleactiveresultsets=True;App=EntityFramework'")
    Try
    	DatabaseSchema = objectContext.CreateDatabaseScript()
    	objectContext.ExecuteStoreCommand(DatabaseSchema)
    	objectContext.AcceptAllChanges()
     Catch ex As Exception
    	MessageBox.Show(ex.GetType.ToString & vbCrLf & ex.ToString) : Return False
    End Try
    End Using
    Return True
    End Function
    

    Whereas this version succeeds:

    Public Function CreateTheDatebase(Byval DBName As StringByval DBFileName As StringAs Boolean
    Dim CreateDatabaseSQL As StringDatabaseSchema As String
    '   create database using model
    Using objectContext As SocialContactsTracker = New SocialContactsTracker()
    CreateDatabaseSQL = "CREATE DATABASE " & DBName & " ON PRIMARY " _
    	& "(NAME = " & DBName & "_Data, FILENAME = '" & DBFileName & ".mdf', " _
    	& "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) "
    Try	
    	objectContext.ExecuteStoreCommand(CreateDatabaseSQL)
    	objectContext.AcceptAllChanges()
     Catch ex As Exception
    	MessageBox.Show(ex.GetType.ToString & vbCrLf & ex.ToString) : Return False
    End Try
    End Using
    '   create schema
    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 & """;" _
    		& "integrated security=True;connect timeout=30;user instance=True;" _
    		& "multipleactiveresultsets=True;App=EntityFramework'")
    Try
    	DatabaseSchema = objectContext.CreateDatabaseScript()
    	objectContext.ExecuteStoreCommand(DatabaseSchema)
    	objectContext.AcceptAllChanges()
     Catch ex As Exception
    	MessageBox.Show(ex.GetType.ToString & vbCrLf & ex.ToString) : Return False
    End Try
    End Using
    Return True
    End Function
    

    Why the difference? Is it simply, you-need-one-to-make-one?


    Robert Gustafson






    Tuesday, September 24, 2013 3:03 PM