Is it possible to pass an Entity Framework SQL script model to work with a database

Question

Is it possible to pass an SQL script to some method that the Entity Framework should use for my model? for example equivalent:

context.ExecuteStoreCommand(<tsql script path>); 

Reference Information. I need a way to reset the database during unit tests, and making a call to run the created EF TSQL script (from the Generate Database from Model) seems to be one way to achieve this.

Answer 1

I have a simple code that calls sql as follows:

 if (!_context.CableSweepDebug.Any(rec => /* CHECK TO SEE IF SQL ALREADY RUN */ )) { var sql = System.IO.File.ReadAllText("SqlScript.sql"); _context.Database.ExecuteSqlCommand(sql); } 

Answer 2

I found a simple way:

• Get the SQL script in a string variable:

   string result = ""; using (Stream stream = assembly.GetManifestResourceStream(resourceName)) { using (StreamReader reader = new StreamReader(stream)) { result = reader.ReadToEnd(); } } 

• Then split the line using GO as a delimiter:

   string[] commands = result.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries); 

• Finally, execute each command using the same order, using the database connection from your context (contains the code from https://stackoverflow.com/a/128/16 ):

   YourContext context = new YourContext(); //Instance new Context DbConnection conn = context.Database.Connection; // Get Database connection ConnectionState initialState = conn.State; // Get Initial connection state try { if (initialState != ConnectionState.Open) conn.Open(); // open connection if not already open using (DbCommand cmd = conn.CreateCommand()) { // Iterate the string array and execute each one. foreach (string thisCommand in commands) { cmd.CommandText = thisCommand; cmd.ExecuteNonQuery(); } } } finally { if (initialState != ConnectionState.Open) conn.Close(); // only close connection if not initially open } 

So I did it. Hope this helps!

Answer 3

I can’t believe that no one answered this. I'm trying to figure it out now. I assume one way to do this is to read the script in a line and then execute executecorecommand. I am trying to figure out what are the limits of this. Are all TSQL statements except GO allowed? Or is it that you better use sqlcmd.exe to run. Here is the best solution I found - use SMO to run the script:

http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/44835d6f-6bca-4374-93e2-3a0d81280781

Answer 4

Why don't you just make SqlConnection as a simple ADO.NET connection to execute SQL against your db in a test environment. Since there will be very few simple SQL statements, and you are not going to deploy or export your test anywhere outside your development premises. I do not think that there is a need to do this through the essence of the framework.

Answer 5

Keeping easy.

 using (var context = new MyDBEntities()) { var m = context.ExecuteStoreQuery<MyDataObject>("Select * from Person", string.Empty); //Do anything you want to do with MessageBox.Show(m.Count().ToString()); }