Linq to SQL with Stored Procs

[Karen]

I have inherited a database where all data is retrieved using Select stored procedures. These have been mapped to functions in a linq to sql model, but when linking up to a Report I don't get any data back. 

I tried to follow the example given in this thread, using an objectdatasource but it still will not work: 
http://www.telerik.com/community/forums/reporting/telerik-reporting/linq-in-an-object-data-source.aspx

The code in my business logic class is:

 

public class BLL
    {
        public static IEnumerable<spGetPackages> GetPackages()
        {
            TardisDataContext context = new TardisDataContext();
            ISingleResult<spGetPackagesResult> result = context.spGetPackages();
            List<spGetPackagesResult> returnlist = result.ToList();
            return returnlist;
        }
    }

In my Report.Designer.cs class I have the following:

//
            // objectDataSource1
            //
            this.objectDataSource1.DataMember = "GetPackages";
            this.objectDataSource1.DataSource = typeof(BLL);
            this.objectDataSource1.Name = "objectDataSource1";

I know the function is working fine as I can retrieve data through a GridView. I think I read a comment somewhere that this method might not work with stored procedures? Where am I going wrong?

[Peter]

Hi Karen,

Have you tried to preview the report definition in report viewer? Generally the Visual Studio Report Designer operates inside the Visual Studio application. Thus when previewing a report the report constructor is executed in the context of the Visual Studio application. As described in MSDN,  the ConfigurationManager.ConnectionStrings property "gets the  ConnectionStringsSection data for the current application's default configuration". Generally in case of Visual Studio this is devenv.exe.config. However the SqlDataSource component manage to get the connection strings that are added in the currents project configuration file.

It seems that because you have used a custom data access layer (DAL) to access your database data and your DAL is reading the ConfigurationManager.ConnectionStrings from the report that is previewed you get all connection strings defined in the devenv.exe.config file. In order to avoid the error you have several options:

1. Hard code the ConnectionString
2. Use an actual application to view your reports in runtime instead of the Previews.
3. Use the SqlDataSource instead that will handle the ConnectionStrings in the Class Library's app.config no matter it's name.

Generally to debug your data access layer while you are using the VS designer you have to open another Visual Studio instance, select Debug->Attach to process to debug another instance of devenv.exe and select Debug->Exception... to enable  the First Chance Exceptions. 

Regards,
Peter
Telerik