locked
Dynamically change Entity Framework Connection String RRS feed

  • Question

  • Hi Everyone, 

    I'm struggling to really set the connection string dynamically. I'm new to EF so this sort of things are consuming a lot of time for me.

    I'm using VS2010, c#, WCF Ria and Silverlight 4. Depending on the connected user I want EF to access a different database. It is clear for me that I have two options now to achieve it:

    1)override the CreateObjectContext method in the Domain Service (my problem here is that I do not know how to pass to this class the connectionstring -or something - that will help this class to figure out the database which I want it to connect to)

    2) call the constructor of the context class that accepst the connection string (my problem here is that I do not see any call to this constructor so I do not know where should I make this call. While debugging, the trace jumps from the Domain Service CreateObjectMethod directly to the defualt Context constructor... but I could not even call it from that method)

    So... could someone please give any hint?

    Thanks in advance!

    Monday, February 27, 2012 5:21 PM

Answers

  • Hi nereo;

    Not being a Silverlight developer I had to do a little research. I believe the following addition to the SampleDomainService class by overriding CreateObjectContext will do what is needed.

    [EnableClientAccess()]
    public class SampleDomainService : LinqToEntitiesDomainService<SampleEntities>
    {
    
        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'articulos' query.
        public IQueryable<articulos> GetArticulos()
        {
            return this.ObjectContext.articulos;
        }
    
        protected override SampleEntities CreateObjectContext( )
        {
            // Start out by creating the SQL Server connection string
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder( );
    
            // Set the properties for the data source. The IP address network address
            sqlBuilder.DataSource = @"NEO-PC\SQLEXPRESS";
            // The name of the database on the server
            sqlBuilder.InitialCatalog = "erpblank";
            sqlBuilder.IntegratedSecurity = true;
            // Now create the Entity Framework connection string
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder( );
            //Set the provider name.
            entityBuilder.Provider = "System.Data.SqlClient";
            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = sqlBuilder.ToString( );
    
            // Set the Metadata location. 
            entityBuilder.Metadata = @"res://*/Model1.csdlres://*/Model1.ssdl|res://*/Model1.msl";
    
            // Create and entity connection
            EntityConnection conn = new EntityConnection( entityBuilder.ToString( ) );
    
            return new SampleEntities(conn);
    
        }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by nereo.lopez Thursday, March 1, 2012 5:43 AM
    Wednesday, February 29, 2012 8:39 PM

All replies

  • Hi nereo;

    The following are the steps you need to take to create an entity connection string. The only two things you will need to change are the DataSource, the address of the server on the network, and the model name in the section of code entityBuilder.Metadata.

    // Start out by creating the SQL Server connection string
    SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder( );
    
    // Set the properties for the data source.
    // Use the network address of the server here
    sqlBuilder.DataSource = @".\SQLEXPRESS";
    // The name of the database on the server
    sqlBuilder.InitialCatalog = "AdventureWorks";
    sqlBuilder.IntegratedSecurity = true;
    
    // Now create the Entity Framework connection string
    EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder( );
    
    //Set the provider name.
    entityBuilder.Provider = "System.Data.SqlClient";
    
    // Set the provider-specific connection string.
    entityBuilder.ProviderConnectionString = sqlBuilder.ToString( );
    
    // Set the Metadata location. The name used here, Model1, is the name given to the 
    // model when created through the wizzard which can be found by opening the EDMX file
    // and looking at the Model Browser window top most node, use that as the name without
    // the extension.
    entityBuilder.Metadata = @"res://*/Model1.csdl|
                    res://*/Model1.ssdl|
                    res://*/Model1.msl";
    
    // Create and entity connection
    using( EntityConnection conn = new EntityConnection( entityBuilder.ToString( ) ) )
    {
        // Now when you create the ObjectContext pass the EntityConnection as its parameter.
        using( AdventureWorksEntities ctx = new AdventureWorksEntities(conn))
        {
            var results = (from emp in ctx.Employees
                           select emp).ToList();
    
            // Code goes here
        }
    }

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Proposed as answer by Nikola Mihajlov Saturday, November 23, 2013 8:56 PM
    Monday, February 27, 2012 7:52 PM
  • Hi Fernando, thanks for your reply. I have a question. Where is the code you wrote placed? In the ViewModel? Because I tried to pass the connection string to the DomainContext instance's constructor but I couldn't... Thanks again
    Monday, February 27, 2012 8:20 PM
  • You can construct the EntiryConnection object anywhere but it is used in the layer that the query and ObjectContext is created in because it is needed to create the ObjectContext.

     You state, "Because I tried to pass the connection string to the DomainContext instance's constructor but I couldn't", can you show the code on how it is passed and how it is being used.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 27, 2012 8:40 PM
  • Thanks Fernando. 

    Here is the how it is right now.

    I have a Silverlight project and a Web project to host it.

    In the web project I have :

    1) the SampleDomainService.cs and the corresponding metadata file. Here is where I can override the CreateObjectContext.

    // [RequiresAuthentication]<o:p></o:p>
        [EnableClientAccess()]<o:p></o:p>
        public class SampleDomainService : LinqToEntitiesDomainService<SampleEntities><o:p></o:p>
        {<o:p></o:p>

    protected override SampleEntities CreateContextObject(){

    return base.CreateContextObject();

    }<o:p></o:p>


            public IQueryable<products> GetProducts()<o:p></o:p>
            {<o:p></o:p>
                return this.ObjectContext.products;<o:p></o:p>
            }<o:p></o:p>

    2)  the SampleModel.edmx. On its Designer.cs file is where the three constructors are found.

    	/// <summary><o:p></o:p>
            /// Initializes a new SampleEntities object using the connection string found in the 'SampleEntities' section of the application configuration file.<o:p></o:p>
            /// </summary><o:p></o:p>
            public SampleEntities() : base("name=SampleEntities""SampleEntities")<o:p></o:p>
            {<o:p></o:p>
                this.ContextOptions.LazyLoadingEnabled = true;<o:p></o:p>
                OnContextCreated();<o:p></o:p>
            }<o:p></o:p>
        <o:p></o:p>
            /// <summary><o:p></o:p>
            /// Initialize a new SampleEntities object.<o:p></o:p>
            /// </summary><o:p></o:p>
            public SampleEntities(string connectionString) : base(connectionString, "SampleEntities")<o:p></o:p>
            {<o:p></o:p>
                this.ContextOptions.LazyLoadingEnabled = true;<o:p></o:p>
                OnContextCreated();<o:p></o:p>
            }<o:p></o:p>
        <o:p></o:p>
            /// <summary><o:p></o:p>
            /// Initialize a new SampleEntities object.<o:p></o:p>
            /// </summary><o:p></o:p>
            public SampleEntities(EntityConnection connection) : base(connection, "SampleEntities")<o:p></o:p>
            {<o:p></o:p>
                this.ContextOptions.LazyLoadingEnabled = true;<o:p></o:p>
                OnContextCreated();<o:p></o:p>
            }<o:p></o:p>

    In the Silverlight project I have, among others, the ViewModels:

    3) SampleViewModel might have something like this:

    SampleDomainContext _Context = new SampleDomainContext();

    <o:p></o:p>

    _Context.Load(_Context.GetProductsQuery());

    This is just sample code but it is the structure it has. In the client (silverlight project) I know the database I want it to access... My question is, where should I put the code you shared before?

    Thanks in advance!


    • Edited by nereo.lopez Tuesday, February 28, 2012 11:46 AM Misspelling
    Tuesday, February 28, 2012 11:45 AM
  • Hi nereo;

    // This is the constructor that needs to be called when using your own EntityConntion Object
    // which is found in your SampleModel.edmx file, note it takes the EntityConnection object
    // as a parameter
    public SampleEntities(EntityConnection connection) : base(connection, "SampleEntities")
    {
        this.ContextOptions.LazyLoadingEnabled = true;
        OnContextCreated();
    }

    // This call seems to be returning ObjectContext. Where ever this function is
    // it is where you would create the EntityConnection and pass it in as the parameter to
    // var context = SampleEntities(EntityConnectionObject);
    SampleDomainContext _Context = new SampleDomainContext();

    I hope that helps.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 28, 2012 6:53 PM
  • Hi Fernando, thanks a lot for your help. But I still have a doubt. I do not have anywhere in the code the var context = SampleEntities(); expression.

    What is the difference then between the object I get with that expression and the line you wrote right after?(SampleDomainContext....) and what would I use the object I get for? Also I tried to create this in the client and I could not access the SampleEntities (Is it a matter of references or is just not possible?). The other concern is, if I only can use it on the server side, how do I send to it from the client the connection string so I can create the EntityConnection object and pass it to the constructor? I saw some examples to get this information from the client with cookies, but this does not fits in my scenario.

    Thanks a lot again... I see the solution closer now :-D

    Wednesday, February 29, 2012 5:37 AM
  • Hi nereo;

    It is hard to say without see all the code. If you can zip the project up and upload it to the web where I can download it or upload to your Windows Live Skydrive I will take a look at it when I get back into the office.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Wednesday, February 29, 2012 12:10 PM
  • Hi Fernando,

    here is the sample project. Thanks a lot for the time you are taking. In the project there is only code. You won't find any view as it is not needed for this purpose.

    Hope you can help me finally solving this issue.

    Thanks!


    Wednesday, February 29, 2012 7:08 PM
  • Hi nereo;

    Not being a Silverlight developer I had to do a little research. I believe the following addition to the SampleDomainService class by overriding CreateObjectContext will do what is needed.

    [EnableClientAccess()]
    public class SampleDomainService : LinqToEntitiesDomainService<SampleEntities>
    {
    
        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'articulos' query.
        public IQueryable<articulos> GetArticulos()
        {
            return this.ObjectContext.articulos;
        }
    
        protected override SampleEntities CreateObjectContext( )
        {
            // Start out by creating the SQL Server connection string
            SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder( );
    
            // Set the properties for the data source. The IP address network address
            sqlBuilder.DataSource = @"NEO-PC\SQLEXPRESS";
            // The name of the database on the server
            sqlBuilder.InitialCatalog = "erpblank";
            sqlBuilder.IntegratedSecurity = true;
            // Now create the Entity Framework connection string
            EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder( );
            //Set the provider name.
            entityBuilder.Provider = "System.Data.SqlClient";
            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = sqlBuilder.ToString( );
    
            // Set the Metadata location. 
            entityBuilder.Metadata = @"res://*/Model1.csdlres://*/Model1.ssdl|res://*/Model1.msl";
    
            // Create and entity connection
            EntityConnection conn = new EntityConnection( entityBuilder.ToString( ) );
    
            return new SampleEntities(conn);
    
        }
    }

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by nereo.lopez Thursday, March 1, 2012 5:43 AM
    Wednesday, February 29, 2012 8:39 PM
  • Hi Fernando, thanks for your help and ever your research. Now we are back to the first point of my first post in this thread:

    1)override the CreateObjectContext method in the Domain Service (my problem here is that I do not know how to pass to this class the connectionstring -or something - that will help this class to figure out the database which I want it to connect to)

    I'm just having some troubles getting the connection string information from the client to the service (the class you wrote right before). I can't seem to find a direct call to the constructor or overriden method... I saw some threads where they suggested to use cookies but it does not fit in my scenario... so, any suggestion on that?

    I'm marking it as an answer because it is helpful and fits with the thread title, but I would still appreciate help solving the problem to send the connectionstring information from the SL project to EF on the server side.

    Thanks a lot

    Thursday, March 1, 2012 5:43 AM
  • Hi nereo;

    Where do you instantiate the SampleDomainService?
    When and where do you know what server you will be using?

    The below SampleDomainService can take a EntityConnection store it in a field and use it in the function CreateObjectContext

    [EnableClientAccess()]
    public class SampleDomainService : LinqToEntitiesDomainService<SampleEntities>
    {
        // Add a field to hold the entity connection string passed in by constructor
        EntityConnection entityConnection = null;
    
        // Default constructor using connection string from web.config
        public SampleDomainService( ) : base()
        {    
    
        }
    
        // Constructor for this derived class and calls base constructor
        public SampleDomainService(EntityConnection connection) : base()
        {    
            entityConnection = entityConnection;    
        }
    
         public IQueryable<articulos> GetArticulos()
        {
            return this.ObjectContext.articulos;
        }
    
        protected override SampleEntities CreateObjectContext( )
        {
            if ( entityConnection == null )
                return base.CreateObjectContext();
            else
            	return new SampleEntities(conn);
        }
    }    

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Thursday, March 1, 2012 4:21 PM
  • Hi Fernando,

    as you could see in the code there is no explicit instantiation in the client. If you would run the code, once you create the SampleDomainContext _Context variable and make a call with the _Context.Load(...), if you put a break point in the service side in the protected override SampleEntities CreateObjectContext then you would get there automatically. So that's why I cant find the right place to send the information from the client to the service to have it available in that very point (the overriden method) 

    To your second question, when login the user I check in a global database where is his data found, so I retrieve the database he/she should connect to and send it to the client and store it in an Entity in memory.

    Thanks

    Friday, March 2, 2012 6:58 AM
  • Hi nereo;

    I am not knowledgeable enough with Sliverligh and the domain class you are using. You will get a quicker solution to your question if you post the question in the Silverlight forum.

    Good luck.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, March 2, 2012 2:55 PM
  • Fair enough Fernando, thanks a lot for al the time you took. 

    Regards.

    Saturday, March 3, 2012 7:41 AM
  • gr8 solution..

    helped me a lot

    but having difficulty with the following code

    using( AdventureWorksEntities ctx = new AdventureWorksEntities(conn))
       
    {
        }
    In my case, i'm unable to pass the 'conn' as parameters..

    there are no overloaded constructors for my entity..

    where do I create an overloaded method /constructor ?

    Wednesday, October 10, 2012 8:25 PM