none
ExecuteNonQuery RRS feed

  • Question

  • Hello,

    I am using the following to create a database, tables, relationships, etc from a T-SQL script:

    Server server = new Server(new ServerConnection(new SqlConnection(myConnection))); 
    server.ConnectionContext.ExecuteNonQuery(script);
    

    This is working. I am trying to do the same but inside IDatabaseInitializer:

      public class ContextInitializer : IDatabaseInitializer<Context> {

        public void InitializeDatabase(Context context) {

           // Run code

        }

      }

    I am trying to do this without using Server ... Just using context elements.

    But I can't find the ExecuteNonQuery in context.Database.

    Could someone tell me the best way to do this inside IDatabaseInitializer?

    Thank You,

    Miguel

     

     

    Wednesday, July 6, 2011 12:47 AM

Answers

All replies

  • Hi,

    Are you talking about ExecutingCommand method?

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx

    Best regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Wednesday, July 6, 2011 7:17 AM
  • Are you talking about ExecutingCommand method?

    http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx

    Not really ... I think.

    Execute command is for insert, update and so on ... But can it be used to run something as follows:

    if exists (select name from sys.databases where name = 'FOP')
    drop database FOP
    go
    
    create database FOP on primary ( 
     name = 'FOP',
     filename = '\DATA\FOP_Data.mdf',
     size = 40MB,
     maxsize = 2GB,
     filegrowth = 20MB 
    ),
    filegroup [STORAGE] contains filestream ( 
     name = 'FOP_Storage',
     filename = '\DATA\FOP_Storage.ndf'
    )
    log on ( 
     name = 'FOP_Log',
     filename = '\DATA\FlyOnPages_Log.ldf',
     size = 4MB,
     maxsize = 200MB,
     filegrowth = 2MB 
    )
    go
    
    use FOP
    go
    
    create table dbo.Errors
    (
     Id int identity not null,
       constraint Errors_Id_PK primary key clustered (Id),
     [Key] uniqueidentifier rowguidcol not null
       constraint Errors_Key_U unique,
     Content varbinary (max) filestream not null
    		constraint Errors_Content_DF default (0x), 	
    ) filestream_on [STORAGE]
    
    


    The problem is that Code First can't create such database or even FileStream.

    I am not sure if I should do this through IDatabaseInitializer.

    Basically, what I am doing is to define this through IDatabaseInitialize.

    Then, every time I want to initialize the database I just run a test method which does Database.Initialize ... Does this make sense?

    But to run this code I think ExecuteNonQuery is necessary ... I think

    Thank You,

    Miguel

     

    Wednesday, July 6, 2011 10:41 AM
  • For example, if I use:

    public void InitializeDatabase(Context context) {
    
       using (DbConnection connection = context.Database.Connection) {
    
        connection.Open();
    
        using (DbCommand command = connection.CreateCommand()) {
    
          command.CommandText = _SQL_Script;
    
         command.ExecuteNonQuery();
    
        }
       }
    }
    

    I get the error:

    Incorrect syntax: Go Go Go

    Thank You,

    Miguel

    Wednesday, July 6, 2011 10:51 AM
  • Hi,

    What happens if you try ; rather than GO ? If it still doesn't work you could also try to send each statement separately (it could be also easier to handle/diagnose if one of those statement fails).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    • Edited by Patrice ScribeMVP Wednesday, July 6, 2011 1:12 PM If still doesn't work...
    • Marked as answer by MDMoura Thursday, July 7, 2011 10:25 AM
    Wednesday, July 6, 2011 12:56 PM
  • Hi,
    Could you create your database in this way?
    For creating a table, this works for me with my typed DataContext:
    public void CreateTable(Type linqTableClass)
    {
    	using (var dc = new DataContext())
    	{
    		var metaTable = dc.Mapping.GetTable(linqTableClass);
    		var typeName = "System.Data.Linq.SqlClient.SqlBuilder";
    		var type = typeof(DataContext).Assembly.GetType(typeName);
    		var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
    		var sql = type.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable });
    		var sqlAsString = sql.ToString();
    		dc.ExecuteCommand(sqlAsString);
    	}
    }
    
    

    Or also in this other way:
     public void CreateTable(string tableName)
     {
     Type type = GetTableType(tableName);
     var metaTable = Mapping.GetTable(type);
     var bf = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.InvokeMethod;
     var builderTypeName = "System.Data.Linq.SqlClient.SqlBuilder";
     var builderType = typeof(DataContext).Assembly.GetType(builderTypeName);
     string command = builderType.InvokeMember("GetCreateTableCommand", bf, null, null, new[] { metaTable }) as string;
    
     using (var dc = new DataContext())
     {
      dc.ExecuteCommand(command);
     }
     }
    
    
     
    Best regards,
    JA Reyes.

    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Wednesday, July 6, 2011 8:40 PM
  • There is another thread I am having with Miguel that contains a working code for this. Pasting the link here since I think it may be useful to other people:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/23aad482-c304-4645-8d8b-42c90d784d6a

    Thanks,

    Pawel

    • Marked as answer by MDMoura Thursday, July 7, 2011 10:25 AM
    Wednesday, July 6, 2011 10:49 PM
  • Removing the GO and using ; solved the problem ...

    Thank You,

    Miguel

    Thursday, July 7, 2011 10:25 AM
  • Thanks to all the participators!

    David Peng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Friday, July 8, 2011 1:14 AM
    Moderator