CODEWITHSUNDEEP
asp.netasp.net-mvcentity-frameworkentity-framework-4.1
eaglestorm
eaglestorm

Reputation: 1202

EF 4.1 Code First Initialization - Alternative

I am building a web application using the entity framework and the code first approach and I really like it so far except one thing. The initialization process and seeding data is crap.

I have set it up as recommended with ASP.NET MVC with the setinitialiser being called in app start and a custom initialization class to add data but it always seems to fail silently and never work. (The database creation works just the data init fails)

Can anyone provide recommended paractice for this or a way to run an sql script from a file.

The given method for adding data, especially for a demo site seems cumbersome and I would prefer the ability to just run a database script directly from a file that is run once as part of an install process rather than depending on a process that fails without any indication that something has gone wrong.

EDIT

I have noticed it throwing exceptions ( idiotic datetime -> datetime2 conversion errors that should be handled by the entity framework.)

But part of the problem may be that my version of express 2010 is not breaking on errors it seems to be very buggy when debugging.

But the issue still stands. I find it a cumbersome and buggy way of essentially running sql scripts on the database. And don't want to end up with a huge set of methods and classes just to setup a demo site when someone installs my web application in IIS.

Upvotes: 3

Views: 834

Answers (2)

Simon Hazelton
Simon Hazelton

Reputation: 1255

If you want to run SQL scripts from your initializer I would recommend adding

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;


        string scriptDirectory = HttpContext.Current.Server.MapPath("~/SqlScripts");
        string sqlConnectionString = context.Database.Connection.ConnectionString;
        DirectoryInfo di = new DirectoryInfo(scriptDirectory);
        FileInfo[] rgFiles = di.GetFiles("*.sql");
        foreach (FileInfo fi in rgFiles)
        {
            FileInfo fileInfo = new FileInfo(fi.FullName);
            using (TextReader reader = new StreamReader(fi.FullName))
            {
                using (SqlConnection connection = new SqlConnection(context.Database.Connection.ConnectionString))
                {
                    Server server = new Server(new ServerConnection(connection));
                    server.ConnectionContext.ExecuteNonQuery(reader.ReadToEnd());
                }
                reader.Close();
                reader.Dispose();
            }
        }

The reason for using the SqlServer Management Objects is that you can use "GO" in your scripts. it then becomes incredibly easy to script from SSMS and paste the scripts into your SqlScripts directory.

You can find the SMO Libraries at:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

and if you need help scripting your data

SP_Generate_Inserts

Upvotes: 2

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364389

Until you will show reproducible code snippet where initialization fails without throwing an exception I hardly believe that this happens.

You can always execute any SQL script by falling back to classic ADO.NET with SqlConnection and SqlCommand. Just open the file, load commands into string and execute them with SqlCommand or Database.ExecuteSqlCommand.

Upvotes: 2

Related Questions