Need help with dynamic table creation

Need help with dynamic table creation

Postby INDI03 » Mon 23 Feb 2015 15:13

Hallo dear Devart Team! :)

I'm new to entity developer and I'm trying to create databases and tables etc from code.
I managed to do this with sqlite like this after I created my edml model:

my appconfig file:

Code: Select all
<?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=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="EntitiesConnectionStringSQLite" connectionString="metadata=res://*/DataModel1.csdl|res://*/DataModel1.ssdl|res://*/DataModel1.msl;provider=Devart.Data.SQLite;provider connection string=&quot;Data Source=&quot;&quot;C:\Users\oliver.perner\Documents\Visual Studio 2013\Projects\WindowsFormsAppEntDevArt\db\TestDbSQLite.sqlite&quot;&quot;;FailIfMissing=False&quot;" providerName="System.Data.EntityClient" />
    <add name="EntitiesConnectionStringPostgre" connectionString="metadata=res://*/DataModel1.csdl|res://*/DataModel1.ssdl|res://*/DataModel1.msl;provider=Devart.Data.PostgreSql;provider connection string='user id=postgres;password=postgres;host=DEVELOP03;database=TestDbPostgreSQL;initial schema=public'" providerName="System.Data.EntityClient" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
 
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="Devart.Data.SQLite" type="Devart.Data.SQLite.Entity.SQLiteEntityProviderServices, Devart.Data.SQLite.Entity, Version=5.2.352.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
      <provider invariantName="Devart.Data.PostgreSql" type="Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices, Devart.Data.PostgreSql.Entity, Version=7.3.352.0, Culture=neutral, PublicKeyToken=09af7300eec23701" />
    </providers>
  </entityFramework>
</configuration>


my function:
Code: Select all
private void btnCreateSQLite_Click(object sender, EventArgs e) {
            var connectionString = ConfigurationManager.ConnectionStrings["EntitiesConnectionStringSQLite"].ConnectionString;
Entities lData = new Entities(connectionString);
lData.CreateDatabase();
}


This worked like a charme!

Now I'm trying to do the same with PostgreSQL - which is not working
The difference is in Postgre I don't want to create the database but only the schema

I get an exception in this peace of code
Code: Select all
public Entities(string connectionString) :
base(connectionString, "Entities")
:

{"The 'Instance' member of the Entity Framework provider type 'Devart.Data.PostgreSql.Entity.PgSqlEntityProviderServices, Devart.Data.PostgreSql.Entity, Version=7.3.352.0, Culture=neutral, PublicKeyToken=09af7300eec23701' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must inherit from this class and the 'Instance' member must return the singleton instance of the provider. This may be because the provider does not support Entity Framework 6 or later; see http://go.microsoft.com/fwlink/?LinkId=260882 for more information."}

my function
Code: Select all
private void btnCreatePostgre_Click(object sender, EventArgs e) {
            var connectionString = ConfigurationManager.ConnectionStrings["EntitiesConnectionStringPostgre"].ConnectionString;Entities lData = new Entities(connectionString);
lData.CreateDatabaseScript();
}


Im not sure if my connectionstring for postgres in the appconfig is quite ok or if I'm missing something.
Edit: The Creation of the SqLite db only works with the original connectionstring name that was created when I set up the model ("EntitiesConnectionString") - all my manually added connection strings do not work.
Just to get the picture clear: my target is to create a winforms gui with 3 buttons - each button should automatically create a database for sqlite or a schema for postgres and mssql server.
Any help or approach appreciated :)

thx in advance, Oliver
INDI03
 
Posts: 5
Joined: Mon 23 Feb 2015 14:57

Re: Need help with dynamic table creation

Postby Shalex » Tue 24 Feb 2015 09:23

Please replace 7.3.352.0 with 7.3.352.6 in your app.config.

JIC: the revision number of provider in the entityFramework section is *.6 but it should be *.0 in DbProviderFactories. For more information, refer to http://blog.devart.com/entity-framework-6-support-for-oracle-mysql-postgresql-sqlite-and-salesforce.html.
Shalex
Devart Team
 
Posts: 7773
Joined: Thu 14 Aug 2008 12:44

Re: Need help with dynamic table creation

Postby INDI03 » Tue 24 Feb 2015 11:04

Thx for Answer!

I got one step forward.
But I ran into the next problem.
Did you realize that I created the model with your wizard?
When I do so and I - for example - choose the SQLite schema when creating the model
I get an entry in the .edps file

Code: Select all
<EntityDeveloper Version="5.7.549.0">
  <ModelSettings xmlns:ed="http://devart.com/schemas/EntityDeveloper/1.0" AppConnectionStringName="EntitiesConnectionString" ConnectionStringInAppConfig="True" RegenareteConnectionStringInAppConfig="True" TargetFramework="Net45" DetectTPTInheritance="False" EntityFrameworkVersion="Version6" IncludeForeignKeysInModel="true" DetectFunctionBasedColumnDefault="false">
    <Connection ConnectionString="Data Source=&quot;C:\Users\oliver.perner\Documents\Visual Studio 2013\Projects\WindowsFormsAppEntDevArt\db\dbblog.sqlite&quot;;FailIfMissing=False" Provider="Devart.Data.SQLite" />
    <Generation />


and also in the .edml file

Code: Select all
<edmx:StorageModels>
      <Schema Namespace="Model.Store" Alias="Self" Provider="Devart.Data.SQLite" ProviderManifestToken="SQLite, 3.8.8.2" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns:devart="http://devart.com/schemas/edml/StorageSchemaExtensions/1.0" xmlns="http://schemas.microsoft.com/ado/2009/11/edm/ssdl">


There all the connection strings and connections are fixed and when I try to connect to my postgreSQL database like this:

Code: Select all
var connectionString = ConfigurationManager.ConnectionStrings["EntitiesConnectionStringPostgre"].ConnectionString;      Entities lData = new Entities(connectionString);
      lData.CreateDatabaseScript();
      lData.CreateDatabase();


I get this error message: "Additional information:
Code: Select all
Mapping_Provider_WrongConnectionType(typeof(SQLiteConnection))



Please tell me if it is possible to create 3 different databases or schemas from 1 model with your Entity Developer Designer with just 1 program as I mentioned in my first post?!
Strange thing is, that all your example-code-models are not created with your designer, but with the built-in entity framework designer from microsoft.

I really need answeres for all these questions and I need to know if this can be done.
INDI03
 
Posts: 5
Joined: Mon 23 Feb 2015 14:57

Re: Need help with dynamic table creation

Postby MariiaI » Thu 26 Feb 2015 13:10

Please tell me if it is possible to create 3 different databases or schemas from 1 model with your Entity Developer Designer with just 1 program as I mentioned in my first post?!

Yes, it is possible. We recommend you to take a look at these articles:
http://forums.devart.com/viewtopic.php?f=30&t=28002
http://blog.devart.com/dynamic-database-creation-in-entity-framework.html
http://www.devart.com/dotconnect/postgresql/docs/?DBScripGeneration.html
http://www.devart.com/dotconnect/sqlite/docs/?DBScripGeneration.html

If this information doesn't help, please specify the full stack traces of all exceptions you get and send us a small test project, which demonstrates your scenario. We will investigate it more clearly and try to find the most suitable solution for you.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL