Using Linq to SQL and Sql Reporting Services

Question

I have a question regarding the integration of business objects developed using Linq To Sql for data query and integrating with Sql Server Reporting Services.

We have a set of business objects that query a couple of back end databases that is developed with Linq to SQL. The SQL that gets generated is relatively dynamic (based on the conditions the user selects) and involves multiple joins, some inner, some outer, etc. Linq to SQL worked great for this. However, we ran into issues when we tried to implement reports with SQL Reporting Services after the initial roll out. We didn't have the ability to bind the SSRS reports to our business layer. What we essentially ended up doing is getting the SQL that is executed from SQL Profiler and creating stored procedures, and using the stored procedures in the reports. As one can imagine, this became a problem as we maintained the code, needing to update both our business layer and the stored procedure.

I have done some looking and I see that Custom Data Extensions appear to be an approach to do this. Is this the solution to the problem? Does anyone have a better approach? Are there any example of implementing a solution like this using LINQ?

http://www.devx.com/dbzone/Article/31336

Thanks

Answer 1

BTW, you don't need to use Profiler to see the generated SQL.

var query = ( from c in db.Customers where c.LastName = "Someone" select c );

// output the query SQL Debug.WriteLine(query);

Return query.ToList();

Alternatively, what we did was to hook into DataContext's Log property. This writes out our SQL and parameters automatically everytime we hit the database. We have found this very useful to identify unnecessary database calls.

public class DataBase : DataBaseModelDataContext
{
    internal DataBase()
    {
    }

    public DataBase(CommonObjects.BaseParameters param) {
        #If (DEBUG) 
        Log = new DataBaseLoger();
        #endIf //(DEBUG) 
    }

    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {   
        System.Data.Linq.ChangeSet cs = GetChangeSet();

        // update audit fields for each insert
        foreach (object entity in cs.Inserts)
        {
            UpdateAuditFields(entity);                               
        }

        // update audit fields for each update
        foreach (object entity in cs.Updates)
        {
            UpdateAuditFields(entity);
        }

        base.SubmitChanges(failureMode);
    }
}

public class DataBaseLoger : System.IO.TextWriter { public override Encoding Encoding { get { return new System.Text.UTF8Encoding(); } }

    public override void WriteLine(string value)
    {
        System.Diagnostics.Trace.WriteLine(System.DateTime.Now.ToString("hh:mm:ss") + " " + value, "Information");
    }

    public override void WriteLine()
    {
        System.Diagnostics.Trace.WriteLine("", "Information");            
    }

    public override void WriteLine(string format, params object[] arg)
    {
        WriteLine(string.Format(format, arg));
    }
}