ObjectDatasource and connectionstring problem

[Tomas]

Hi,

I've got a connectionstring problem, I know these kind of questions have been asked thousands of times before but still I cannot figure out what I'm doing wrong.

Here's a simplified structure of my solution:

DataModelClassLibrary
-includes my Entity Framework Model along with a app.config including my db connectionstring like this:

<connectionStrings>
   <add name="MyModelEntities" connectionString="metadata=res://*/Models.MyModel.csdl|res://*/Models.MyModel.ssdl|res://*/Models.MyModel.msl;provider=System.Data.SqlClient;provider connection string="Data Source=MYSERVER;Initial Catalog=MYDB;Persist Security Info=True;User ID=MyUser;Password=MyPassword;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />
 </connectionStrings>

TelerikReportClassLibrary
-here I've defined my reports i've also added a app.config with the same connectionstrings as the app.config
in my DataModelClassLibrary

MyWebProject
-here I've got my actual program and of course it has a web.config again with the same connectionstrings as the app.config
in my DataModelClassLibrary

Now to the problem, if I add a EntityDataSource or a SqlDataSource directly to a report and set it up using one of the
connectionstrings that i've copied to the app.config I'm able to connect to my data and design the report and preview it
within the designer. It also works as expected at runtime.

However recently I wanted to use the ObjectDataSouce so that I could separate out some logic from the report not to make
the report itself too complex and here's where I hit a problem.
I added a class let's say Cars to my TelerikReportClassLibrary which is responsible of retrieving data using my ef model defined in DataModelClassLibrary. I set the report to use a ObjectDataSource and points its select method to a method in the newly created class.
At runtime I can recieve data just fine but if i try to preview the report in the designer I get the following error:

An error has occurred while processing Report 'MyReport':
Exception has been thrown by the target of an invocation.
------------- InnerException -------------
The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid.

I was able to get around the problem using a technique similar to the one described here:
http://www.telerik.com/community/code-library/reporting/general/use-the-connectionstring-from-your-app-s-config-file.aspx
So that I first checked if it can find the configured connectionstring and if not I manually hardcode one using the EntityConnectionStringBuilder

Is this really my only option? Why can't the report designer find my connectionstring?

Tomas

[Steve]

Hello Tomas,

Check the following KB article:Using connectionStrings from configuration file. In short, you should copy the settings to the devenv.exe.config file in order to be found in Preview. That is the hard way.
The easy way is to use our native EntityDataSource Component instead, which would take care of finding the connectionString without any need for manual intervention.

Regards,
Steve
the Telerik team

[Tomas]

Hi Steve,

Specifying the connection string in devenv.exe.config sure fixes the problem but as you said it's a hard way of fixing it.
About your other suggestion to use EntityDataSource Component, I cannot see how this will solve my problem?
As I said I'm need to use the ObjectDataSource so that I can separate out the logic from the report. As I understand the EntityDataSource Component is used when you want to bind your report directly to your model.

If this is totally impossible to solve without using any of the above mentioned techniques then can you please explain to me the tecnique behind how the EntityDataSource Component correctly locates the connectionstring from the app.config maybe by a code example or by pointing me to the location in the sources so I can mimic this in my own implementation.

Tomas

[Ivan]

Hello Tomas,

The problem with the connection string occurs only when you preview the report at design-time, because the ConfigurationManager class cannot find the correct app.config / web.config file of your application. For this reason we simply ditch ConfigurationManager in the designer and use a complex logic instead, that involves a lot of COM interoperability with Visual Studio to resolve the correct configuration settings by hand. We simply do not recommend replicating all that logic in your application yourself. On the bright side this problem simply does not exist at runtime when everything should work as expected.

The EntityDataSource component is not so different from the ObjectDataSource component. You can think about EntityDataSource as a variant of ObjectDataSource dedicated to the Entity Framework. You can still separate your business logic from your reports by extending your ObjectContext with a partial class and place all the business logic there. Then EntityDataSource can call your business logic method while passing the required parameters the same way ObjectDataSource does it.

If you need more information regarding this, please check the help topic about retrieving data from an entity data model with the EntityDataSource component in our online documentation, which discusses more in-depth how to extend your ObjectContext with a method to execute a custom business logic against the entity model and how to invoke that method using the EntityDataSource component.

All the best,
Ivan
the Telerik team

[Tomas]

Hello Ivan,

Thank you for your reply, I knew there had to be some way to achieve what I wanted. However in my current project I have a report that needs data from two different DataModels because of some legacy databases I can't control. I guess I'm out of luck then as you suggested solution only extends a single model. I don't wont to use two EntityDataSources because then I'm back where I started as the data retrieved from these two models depend on each other.

Am I right or is there a solution for this situation as well?

Tomas

[Ivan]

Hi Tomas,

Generally speaking, it is possible to execute heterogeneous queries against multiple databases - at least SQL Server and Oracle databases support this feature. One recommended approach in this scenario is to create another database with views that select data from your other databases. This way you can treat your heterogeneous databases as a single monolithic database and base your entity data model entirely on that database.

If the above approach is not applicable in your specific case (e.g. you do not have permissions granted to create another database on the database server), you can continue using your own approach with the ObjectDataSource component. To resolve the connection string correctly both at design-time and at run-time, you can use a code snippet like the following one:

public static string ResolveConnectionString()
{
    // Retrieve the connection string settings from the configuration file.
    ConnectionStringSettings connectionSettings =
        ConfigurationManager.ConnectionStrings["MyConnectionString"];

    // If a valid connection string setting exist, then we are at run-time,
    // so we should use this connection string to connect to the database.
    if (null != connectionSettings)
    {
        return connectionSettings.ConnectionString;
    }

    // If no valid connection string setting exist, then we are at design-time,
    // so return a default connection string for preview and testing purposes.
    return "Data Source=...;Initial Catalog=...";
}

All the best,
Ivan
the Telerik team

[Dilan]

@Ivan , sir how do i config this , i mean ,, where do i put this method ,,,