none
How do I create a new database file at run-time with a predefined schema in Entity Framework? RRS feed

  • Question

  • I'm creating a Visual Basic 2010 application that relies on one or more instances of a database with a particular schema defined by Entity Framework (probably using Model-First). I would like the program to be able to create new instances in code at run-time--same database schema, different database file. It appears that one uses the ObjectContext's CreateDatabaseScript and CreateDatabase methods to create a new database instance--with the EDMX metadata files specified in the connection string--but how do I specify the NAME of the database FILE to create? It seems that the InitialCatalog and AttachDbFileName compete in the documentation for this status.
    Thursday, September 5, 2013 3:14 AM

Answers

  • Hi RobertGustafson,

    Sorry for my late reply.

    For the first question, we need to install entity framework 5. However, you have already used the ObjectContext object, so you neednt to do the translation like below:

    Dim objectContext As ObjectContext = CType(db, IObjectContextAdapter).ObjectContext
            

    Because I use Entity Framework 5 and VS2012, it will create the DBContext object by default.

    For the second question, I think it is need if you want get the schema dynamicly.

    When we create a new instance for the Model1Container using:

    Using objectContext As Model1Container = New Model1Container()

    It checks the connectionString to ensure it is correctly.

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, September 9, 2013 3:34 AM
    Moderator

All replies

  • I'm creating a Visual Basic 2010 application that relies on one or more instances of a database with a particular schema defined by Entity Framework (probably using Model-First). I would like the program to be able to create new instances in code at run-time--same database schema, different database file. It appears that one uses the ObjectContext's CreateDatabaseScript and CreateDatabase methods to create a new database instance--with the EDMX metadata files specified in the connection string--but how do I specify the NAME of the database FILE to create? It seems that the InitialCatalog and AttachDbFileName compete in the documentation for this status.


    • Moved by Carl Cai Thursday, September 5, 2013 1:22 AM get better support
    • Merged by Fred BaoModerator Thursday, September 5, 2013 3:53 AM same
    Wednesday, September 4, 2013 6:08 AM
  • I'm creating a Visual Basic 2010 application that relies on one or more instances of a database with a particular schema defined by Entity Framework (probably using Model-First). I would like the program to be able to create new instances in code at run-time--same database schema, different database file. It appears that one uses the ObjectContext's CreateDatabaseScript and CreateDatabase methods to create a new database instance--with the EDMX metadata files specified in the connection string--but how do I specify the NAME of the database FILE to create? It seems that the InitialCatalog and AttachDbFileName compete in the documentation for this status.


    • Moved by Bob Beauchemin Wednesday, September 4, 2013 3:38 PM Moved to the entity framework forum
    • Merged by Fred BaoModerator Thursday, September 5, 2013 3:56 AM same
    Wednesday, September 4, 2013 7:30 AM
  • I'm creating a Visual Basic 2010 application that relies on one or more instances of a database with a particular schema defined by Entity Framework (probably using Model-First). I would like the program to be able to create new instances in code at run-time--same database schema, different database file. It appears that one uses the ObjectContext's CreateDatabaseScript and CreateDatabase methods to create a new database instance--with the EDMX metadata files specified in the connection string--but how do I specify the NAME of the database FILE to create? It seems that the InitialCatalog and AttachDbFileName compete in the documentation for this status.


    Wednesday, September 4, 2013 7:33 AM
  • You should post to the MSDN EF forum for help.
    Wednesday, September 4, 2013 8:14 AM
  • Hello,

    I have moved this thread to Data Platform forum for better response.

    Best regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 5, 2013 1:23 AM
  • Hi Robert,

    Thank you for visiting the MSDN forum.

    I’m afraid that it is not the correct forum about this issue, since this forum is to discuss Visual Basic. I moved this thread to ADO.NET Entity Framework and LINQ to Entities Forums for a better response.

    Thanks for your understanding.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 5, 2013 1:43 AM
  • Hello,

    Thanks for posting your question to this forum.

    As far as I know, the method CreateDatabase() is used to be create the database by using the current data source connection and the metadata in the StoreItemCollection. So it cannot create a new database at runtime if the database instance exists.

    For creating the database which has the same schema at runtime, in my opinion, we can do as following:

    1: Creates a database using sqlcommand statement.

    2: Creates table structure with script generated by method CreateDatabaseScript().

    3: Uses the new database instance.

    Please have a try with codes below:

    ObjectContext objectContext = ((IObjectContextAdapter)db).ObjectContext;
                    string DBName = "MyDatabase";
                    string str = string.Format("CREATE DATABASE {0} ON PRIMARY " +
            "(NAME = MyDatabase_Data, " +
            "FILENAME = 'E:\\MyDatabaseData.mdf', " +
            "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
            "LOG ON (NAME = MyDatabase_Log, " +
            "FILENAME = 'E:\\MyDatabaseLog.ldf', " +
            "SIZE = 3MB, " +
            "MAXSIZE = 5MB, " +
            "FILEGROWTH = 10%)", DBName);
                    string schema = objectContext.CreateDatabaseScript();
                    objectContext.ExecuteStoreCommand(str);
                    objectContext.SaveChanges();
                    objectContext = new ObjectContext("metadata=res://*/S09.csdl|res://*/S09.ssdl|res://*/S09.msl;provider=System.Data.SqlClient;provider connection string=\"data source=(localdb)\\v11.0;initial catalog=" + DBName + ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework\"");
                    objectContext.ExecuteStoreCommand(schema);
                    objectContext.SaveChanges();

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, September 5, 2013 8:19 AM
    Moderator
  • Thank you--almost. I converted your code to VB as follows, and I have addition questions regarding 3 lines of code.

    Dim objectContext As ObjectContext = _
       CType(db, IObjectContextAdapter).ObjectContext ' WHAT'S THIS LINE?
    '   initialize strings
    Dim DBName As String = "MyDatabase", DBFile As String = "E:\MyPath\MyDatabase"
    Dim DBModel = "S09"
    Dim str As String = "CREATE DATABASE " & DBName & " ON PRIMARY " _
       & "(NAME = " & DBName & "_Data, FILENAME = '" & DBFile & ".mdf', " _
       & "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " _
       & "LOG ON (NAME = " & DBName & "_Log, " & "FILENAME = '" & DBFile & ".ldf', " _
       & "SIZE = 3MB, MAXSIZE = 5MB, FILEGROWTH = 10%)"
    Dim schema As String = _
       objectContext.CreateDatabaseScript() ' IS THIS LINE WHERE IT SHOULD BE?
    '   create database
    objectContext.ExecuteStoreCommand(str)
    objectContext.SaveChanges()
    '   set up schema
    objectContext = New ObjectContext( _
       "metadata=res://*/" & DBModel & ".csdl|res://*/" & DBModel & ".ssdl|" _
       & "res://*/" & DBModel & ".msl;" _
       & "provider=System.Data.SqlClient;" _
       & "provider connection string=""data source=(localdb)\v11.0;" _
          & "initial catalog=" & DBName & ";integrated security=True;"
          & "MultipleActiveResultSets=True;App=EntityFramework""")
    objectContext.ExecuteStoreCommand(schema)
    objectContext.SaveChanges()

    QUESTIONS:

    1. What is "db" supposed to be? And I can't find the "IObjectContextAdapter" interface in my VB 2010 help! Are you sure your code isn't designed to run on a more recent version of Visual Studio?

    2. Also, "S09" is merely a PLACEHOLDER, like "MyDatabase", for whatever metadata files are created by my model, right? I need to know what should or shouldn't be changed as per the specific data model I'm using.

    3. How does "CreateDatabaseScript" know what the table structure is? If it's relying--as I THINK it is--on the metadata files, then shouldn't it be execute AFTER the database is created using ExecuteStoreCommand and the ObjectContext is re-instantiated with the connection string that SPECIFIES the metadata files? That is, shouldn't "schema = ObjectContext.CreateDatabaseScript" occur JUST before "ObjectContext.ExecuteStoreCommand(schema)"? Or does CreateDatabaseScript look for the metadata files without being told by ObjectContext? (THE DOCUMENTATION ON THE "CreateDatabaseScript" vs. "CreateDatabase" METHODS IS VERY CONFUSING. I'M NOT SURE THEY'RE THAT DIFFERENT REGARDING THE StoreItemCollection EXCEPT THAT ONE GENERATES SCRIPT, THE OTHER EXECUTES IT.)

    All in all, are you sure the code will execute as written?



    • Edited by RobertGustafson Friday, September 6, 2013 3:50 AM clarification
    Thursday, September 5, 2013 9:45 PM
  • Hi,

    >>1. What is "db" supposed to be? And I can't find the "IObjectContextAdapter" interface in my VB 2010 help! Are you sure your code isn't designed to run on a more recent version of Visual Studio?

    The "db" is DBContext object, because I use VS2012, and I notice that you have used ObjectContext.

    So I have to  translate it from DBontext object to ObjectContext so that it will be same with your program.

    You neednt to do this translation.

    For using IObjectContextAdapter, we need to involve namesapce sa below:

    Imports System.Data.Objects
    Imports System.Data.Entity
    Imports System.Data.Entity.Infrastructure

    >>2. Also, "S09" is merely a PLACEHOLDER, like "MyDatabase", for whatever metadata files are created by my model, right? I need to know what should or shouldn't be changed as per the specific data model I'm using.

    The "S09" is the name of the edmx file.

    >>3. ...

    You can dim a string value to show the context of the return value with CreateDatabaseScript()

    And I made a sample with VB:

    Imports System.Data.Objects
    Imports System.Data.Entity
    Imports System.Data.Entity.Infrastructure
    Module Module1
        Sub Main()
            Console.WriteLine("Begin To Create A DataBase")
            Console.WriteLine("Please Input The DBName:")
            Dim DBName As String = Console.ReadLine()
            Console.WriteLine("Begin...")
            Dim db As ModelFirstDBEntities = New ModelFirstDBEntities()
            Dim objectContext As ObjectContext = CType(db, IObjectContextAdapter).ObjectContext
            Dim str As String = String.Format("CREATE DATABASE {0} ON PRIMARY " +
    "(NAME = " + DBName + "_Data, " +
    "FILENAME = 'E:\\" + DBName + ".mdf', " +
    "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
    "LOG ON (NAME = " + DBName + "_Log, " +
    "FILENAME = 'E:\\" + DBName + "Log.ldf', " +
    "SIZE = 3MB, " +
    "MAXSIZE = 5MB, " +
    "FILEGROWTH = 10%)", DBName)
            Dim schema As String = objectContext.CreateDatabaseScript()
            objectContext.ExecuteStoreCommand(str)
            objectContext.SaveChanges()
            objectContext.Dispose()
            objectContext = New ObjectContext("metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=(localdb)\v11.0;initial catalog=" + DBName + ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'")
            objectContext.ExecuteStoreCommand(schema)
            objectContext.SaveChanges()
            Console.WriteLine("Create Success")
            Console.ReadLine()
        End Sub
    End Module

    You need to change codes below to fit yours.

    objectContext = New ObjectContext("metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=(localdb)\v11.0;initial catalog=" + DBName + ";integrated security=True;MultipleActiveResultSets=True;App=EntityFramework'")

    My app config file:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <configSections>
        <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
      </configSections>
      <connectionStrings>
        <add name="ModelFirstDBEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(localdb)\v11.0;initial catalog=ModelFirstDB;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
      </connectionStrings>
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
      </startup>
      <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
          <parameters>
            <parameter value="v11.0" />
          </parameters>
        </defaultConnectionFactory>
      </entityFramework>
    </configuration>
    Regards

    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.


    Friday, September 6, 2013 8:46 AM
    Moderator
  • Before I mark the above response "As Answer", I have the following code, with the following questions:

    1. The namespace System.Data.Entity.Infrastructure and interface IObjectContextAdapter DON'T SEEM TO EXIST ON VB 2010. Is there a work-around? (cf. VB 2010 code below)

    2. Does the original SocialContactsTracker.mdf database (cf. App.config file below) have to exist, or can I use the following code to create a new instance COMPLETELY FROM SCRATCH (using just the pre-defined model Model1Container)?

    VB 2010 code:

    Imports System.Data.Objects
    Imports System.Data.Entity
     
     
     
    Public Class Form1
    '   code yet to be determined
     
     
     
    End Class
     
    Module CreateADatabase
     
    Sub CreateTheDatebase(Byval DBName As StringByval DBFileName As String)
      '   create database using model, get script for schema
      Dim CreateDatabaseSQL As StringDatabaseSchema As String
      Using objectContext As Model1Container = New Model1Container()
         CreateDatabaseSQL = "CREATE DATABASE " & DBName & " ON PRIMARY " _
            & "(NAME = " & DBName & "_Data,  " _
            & "FILENAME = '" & DBFileName & ".mdf', " _
            & "SIZE = 3MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " _
            & "LOG ON (NAME = " & DBFileName & "_Log, " _
            & "FILENAME = '" & DBFileName & "_Log.ldf', " _
            & "SIZE = 3MB, MAXSIZE = 5MB, FILEGROWTH = 10%)"
         DatabaseSchema  = objectContext.CreateDatabaseScript()
         objectContext.ExecuteStoreCommand(CreateDatabaseSQL)
         objectContext.SaveChanges()
      End Using
      '   create schema
      Using objectContext As Model1Container = New Model1Container( _
            "metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;" _
            & "provider=System.Data.SqlClient;" _
            & "provider connection string=" _
               & "'data source=.\SQLEXPRESS;attachdbfilename=""" & DBFileName & """;" _
               & "integrated security=True;connect timeout=30;user instance=True;" _
               & "multipleactiveresultsets=True;App=EntityFramework'")
         objectContext.ExecuteStoreCommand(DatabaseSchema)
         objectContext.SaveChanges()
      End Using
      End Sub
     
    End Module
    

    App.config file:

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
      <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
      </startup>
      <connectionStrings>
        <add name="Model1Container" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string='data source=.\SQLEXPRESS;attachdbfilename=&quot;C:\Users\Robert G\Documents\Visual Studio 2010\Projects\SocialContactsTracker\SocialContactsDatabase\SocialContactsTracker.mdf&quot;;integrated security=True;connect timeout=30;user instance=True;multipleactiveresultsets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />
      </connectionStrings>
    </configuration>


    Robert Gustafson




    Sunday, September 8, 2013 10:06 AM
  • Hi RobertGustafson,

    Sorry for my late reply.

    For the first question, we need to install entity framework 5. However, you have already used the ObjectContext object, so you neednt to do the translation like below:

    Dim objectContext As ObjectContext = CType(db, IObjectContextAdapter).ObjectContext
            

    Because I use Entity Framework 5 and VS2012, it will create the DBContext object by default.

    For the second question, I think it is need if you want get the schema dynamicly.

    When we create a new instance for the Model1Container using:

    Using objectContext As Model1Container = New Model1Container()

    It checks the connectionString to ensure it is correctly.

    Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, September 9, 2013 3:34 AM
    Moderator