Silverlight: Update service reference for a WCF service generating empty class

My Visual Studio sometimes goes angry. I had a Silverlight 2 – WCF – LINQToSQL application that I recently converted to Silverlight 3. I noticed that sometimes the Update Service Reference does not function properly and instead blanks out the generated reference.cs. It broke once again today and I decided to blog it. Here are the contents of Error window when such abnormal activity happens:

Custom tool error: Failed to generate code for the service reference '..'.  Please check other error and warning messages for details.


The warning tab may have many warnings, some of which are:

Custom tool warning: Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Custom tool warning: The type 'System.Collections.ObjectModel.ObservableCollection`1' could not be found.  Ensure that the assembly containing the type is referenced.  If the assembly is part of the current development project, ensure that the project has been built.

Custom tool warning: Cannot import wsdl:portType
Detail: An exception was thrown while running a WSDL import extension: System.ServiceModel.Description.DataContractSerializerMessageContractImporter
Error: Exception has been thrown by the target of an invocation.
XPath to Error Source: //wsdl:definitions[@targetNamespace='']/wsdl:portType[@name=..]

Custom tool warning: Cannot import wsdl:port
Detail: There was an error importing a wsdl:binding that the wsdl:port is dependent on.
XPath to wsdl:binding: //wsdl:definitions[@targetNamespace='..']/wsdl:binding[@name='..']

Custom tool warning: No endpoints compatible with Silverlight 3 were found. The generated client class will not be usable unless endpoint information is provided via the constructor.

Ok. Looks like something is wrong with the Observable Collection reference. This was further confirmed when I brought up the service configuration window (by right clicking the WCF service and selecting Configure Service Reference), and noticed that the ObservableCollection collection type has been replaced by { Custom } :

For comparison, here’s the normal screenshot for this window:

To get rid of this and regenerate service proxy properly, I had to remove the Reuse types in all referenced assemblies (or at least System assembly).

So, if such behavior ever happen to you, try to update the service reference after unchecking the reuse types checkbox. Once done, you can recheck the option and update your service reference again.

T-SQL: Using result of a dynamic SQL query in a variable or table

Although, not a recommended practice, but sometimes we have to write our queries using dynamic SQL. In such situations, it is generally needed to fetch the result (scalar or tabular) of dynamic SQL into the main (non-dynamic) query. This is not straight forward because dynamic SQL runs in its own scope and we cannot access the variables defined in main query. This post presents a few approaches to consume the result of a dynamic SQL query:

sp_ExecuteSql stored procedure

This is the most generic and powerful method of invoking dynamic SQL since it allows us to write a parameterized dynamic query with input/output parameters. Here’s a simple example of using sp_executesql to consume the result of a dynamic SQL query:

declare @today datetime
exec sp_executesql
    N'Select @internalVariable = GetDate()', --dynamic query
    N'@internalVariable DateTime output', --query parameters
    @internalVariable = @today output --parameter mapping
select @today

Table variables and Temporary tables

This method is used when we want to get a tabular result set from our dynamic query. Here’s an example to get the result of a dynamically created SQL query by using table variables:

declare @myTable table
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into @myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from @myTable

Here’s the same example that uses a temporary table to fetch the result set of a dynamic SQL query:

create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

insert into #myTable
    exec (N'select name, database_id, create_date from sys.databases') --dynamic query

select * from #myTable
drop table #myTable

Since temporary tables have physical existence so we can refer to the temporary table inside our dynamic SQL query as well. Here’s an example illustrating this technique:

create table #myTable
(
    DatabaseName nvarchar(256),
    DatabaseID int,
    CreateDate datetime
)

--dynamic query
exec sp_executesql
    N'insert into #myTable
        select name, database_id, create_date from sys.databases'

select * from #myTable
drop table #myTable

Temporary tables or Table variables can also be used to fetch the result of a stored procedure. Notice that for saving this result, the columns of table variable/temporary table must match with the result of stored procedure. That is, we need to take “ALL” the columns. Here’s an example that grabs the result set from a stored procedure into a table variable.

declare @myTable table
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into @myTable
    exec sp_helpserver

select * from @myTable

Also, here’s an example to get result of a stored procedure using temporary table:

create table #myTable
(
    ServerName nvarchar(256),
    NetworkName nvarchar(256),
    Status nvarchar(4000),
    ID int,
    Collation nvarchar(256),
    ConnectTimeOut int,
    QueryTimeOut int
)

insert into #myTable
    exec sp_helpserver

select * from #myTable
drop table #myTable

Thats all from me. Let me know if you have any more solutions.

LINQ to SQL: Visual Studio designer failed to autogenerate .designer.cs data classes

I had a really strange observation today. I opened one of my LINQ to SQL dbml files, made some changes, and then saved it back to have my designer generated data classes updated. But instead of reflecting my changes in the .designer.cs class, Visual studio deleted that designer generated file. I tried several times but every time LINQ designer was deleting my autogenerated data classes. I googled and found an amazing answer by Marc Gravell at this stackoverflow question. The position of using statements was the source of problem!!! I had extended the LINQ generated partial classes and the first statement in that file was “using System”. I removed that and the VS designer was happy again.

So for anyone else that experiences the same problem, try moving your using statements after the namespace declaration. For example, if you have extended your data context or any other data class like this:

using System;
namespace MyNamespace
{
    partial class MyDataContext
    {
        ...
    }
    ...
}

Try rearranging the declarations like this: 

namespace MyNamespace
{
    using System;

    partial class MyDataContext
    {
        ...
    }
    ...
}

Hope this post helps someone else as well.