Calling SQL Server Stored Procedure Synonym fails with parameter error

I’ve just tried to call a stored procedure in another database via a synonym but received an error ‘Procedure or function ‘LM_Employees_HasNewStoreTempsInStore’ expect parameter ‘@OrganisationLevelValueID’, which was not supplied.

Here’s how I defined the synonym and called it :

CREATE SYNONYM [dbo].[LM_Employees_HasNewStoreTempsInStore] 
FOR [PHENDRY].[LabourManager_DEV].[dbo].[LM_Employees_HasNewStoreTempsInStore]
GO

exec [dbo].[LM_Employees_HasNewStoreTempsInStore] 1119
GO

As you can see, I’m definitely passing the parameter!!

The problem appears to be how the synonym is defined when the two database exist on the same server. The machine name, here ‘[PHENDRY].’ can be removed completely and everything works fine!

Generic Sorting Routine for ASP.NET GridView

I noticed a lot of code in the current project I’m working on which looked something like this :

switch (sortExpression)
{
    case "Name":
        items.OrderBy(i => i.Name);
        break;
    case "Date":
        items.OrderBy(i => i.Date);
        break;
    case "Cost":
        items.OrderBy(i => i.Cost);
        break;
}

This was then repeated all over again for descending orders!! I’ve now replaced it with something like this :

private void Populate(string sortExpression)
{
   var items = GetData();
   gvList.DataSource = CreateValueList(items).OrderByExpression(new OrderByExpression(sortExpression));
   gvList.DataBind();  
}

The sort expression is handled by the GridView code such that AllowSorting is turned on and the OnSorting event is wired up to a method :

<asp:GridView ID="gvList" runat="server" AllowSorting="True" OnSorting="gvList_Sorting" DataKeyNames="ID">
   <Columns>
       <asp:TemplateField HeaderText="Name" SortExpression="Name">
           <ItemTemplate>
               <asp:Label Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>' runat="server" ID="lblName" />
           </ItemTemplate>
       </asp:TemplateField>
   </Columns>
</asp:GridView>

The code-behind deals with the sorting events and calls populate :

protected void gvList_Sorting(object sender, GridViewSortEventArgs e)
{
   Populate(e.SortExpression + " " + GetSortDirection(e.SortExpression));
}

private string GetSortDirection(string column)
{

   // By default, set the sort direction to ascending.
   string sortDirection = "ASC";

   // Retrieve the last column that was sorted.
   string sortExpression = ViewState["SortExpression"] as string;

   if (sortExpression != null)
   {
       // Check if the same column is being sorted.
       // Otherwise, the default value can be returned.
       if (sortExpression == column)
       {
           string lastDirection = ViewState["SortDirection"] as string;
           if ((lastDirection != null) && (lastDirection == "ASC"))
           {
               sortDirection = "DESC";
           }
       }
   }

   // Save new values in ViewState.
   ViewState["SortDirection"] = sortDirection;
   ViewState["SortExpression"] = column;

   return sortDirection;
}

What this all boils down to is the OrderByExpression() extension method called in the Populate() method above:

   public static IEnumerable<TSource> OrderByExpression<TSource>(this IEnumerable<TSource> data, OrderByExpression expression)
   {
       string sortOrderMethod = (expression.SortOrder == SortOrderEnum.Ascending) ? "OrderBy" : "OrderByDescending";

       // data.OrderBy(o => o.propertyname);   
       var dataAsQueryable = data.AsQueryable<TSource>();
       ParameterExpression lambdaParameter = Expression.Parameter(typeof(TSource), "o");
       MemberExpression member = Expression.PropertyOrField(lambdaParameter, expression.SortProperties[0]);
       LambdaExpression lambda = Expression.Lambda(member, lambdaParameter);
       Type[] argumentTypes = { dataAsQueryable.ElementType, lambda.Body.Type };
       MethodCallExpression orderBy = Expression.Call(typeof(Queryable), sortOrderMethod, argumentTypes, dataAsQueryable.Expression, lambda);
       return dataAsQueryable.Provider.CreateQuery<TSource>(orderBy);
   }

I have to say thanks to Joseph Albahari and Ben Albahari of LinqPad fame since I found this solution to my problems (after getting within so close through my own efforts whilst our internet connection was down in the office!!) in the samples that came with LinqPad.

The code above creates a dynamic Linq expression then executes it through the queryables provider – although this only occurs once the whole linq expression is enumerated.

The last few modifications I need to make require the order by to cope with multiple properties (The OrderByExpression.SortProperties class already supports multiple properties but I’m only taking the first at the moment) and I need to ensure that the query always deferred since composable against a database – this is important since I’ve yet to consider paging in the GridView and I want to make sure that specifiying .Skip(n).Take(m) can be applied after the ordering but the Linq expression be composed as SQL and executed against the database in one go otherwise I’ll be returning all the rows to the application layer before extracting just the required rows for the current page.

Failed to map the path ‘/’.

I was trying to run a simple MVC# example to see how it all hung together and whilst I found the WinForms example up and running within a couple of minutes the WebForms was a little harder and was failing with ‘Failed to map the path ‘/’.’

A few minutes consultation with support (aka Google) didn’t seem to suggested anything. But a moment later (and a slap of the forehead) I’d realised the problem – I was running Visual Studio as myself instead of Administrator and the default web server was struggling.

If someone out there knows which folder needs the appropriate permissions then let me know but for the moment I’ll just run as Administrator

Redirecting Assembly Versions in .Net

We were having a problem with deserialization on one of our projects and although the cause seemed obvious the solution wasn’t!

The error we were getting is at the bottom of this post. It says that one type cannot be converted to another type but both types are the same! However, what it isn’t highlighting is that the version number of the assembly that these two types come from is different. When an object is serialized the assembly versions of the objects being serialized are being stored too and when the deserializing these versions are compared to what can be created and if they don’t match the error is thrown.

One solution (I thought) would be to changed the AssemblyFormat property on the binary formatter as shown below. However, this simply does not work. There’s been a bug raised with Microsoft for this issue a year ago but it doesn’t look like it’s been fixed!

BinaryFormatter formatter = new BinaryFormatter();
formatter.AssemblyFormat = FormatterAssemblyStyle.Simple;

So the next option that was suggest by several other blogs was to implement a SerializationBinder that can intercept the requests for the assemblies to create and strip off the assembly version. This, however, is just plain awful since we’re now writing even more code to a simple problem. Here’s some template code:

class Program
    {
        static void Main(string[] args)
        {
            BinaryFormatter formatter = new BinaryFormatter();
            formatter.Binder = new SimpleBinder();
        }
    }

    class SimpleBinder : SerializationBinder
    {
        public override Type BindToType(string assemblyName, string typeName)
        {
            /* Implement code to modify the typeName */
            return Type.GetType(typeName);
        }
    }

The third option, and certainly the simplest, is to modify the assembly bindings to redirect one version of an assembly to another. In the example below the bindingRedirect allows requests for version 1.0.0.0 of this particular assembly to be serviced by version 2.0.0.0. So long as we’re sure the deserializing functionality is compatible, this solves the problem without having to change any code.

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
        <assemblyIdentity name="Configuration" publicKeyToken="........." culture=""/>
        <bindingRedirect oldVersion="1.0.0.0" newVersion="2.0.0.0" />
        <codeBase href="C:\bin\Configuration.dll" version="2.0.0.0" />
    </dependentAssembly>
</assemblyBinding>

Here’s the error :

Object of type ‘com.iMeta.metaCore.Utilities.Base.Pair`2[System.Type,System.Collections.Generic.List`1[M.Box.Core.Command]][]‘ cannot be converted to type ‘com.iMeta.metaCore.Utilities.Base.Pair`2[System.Type,System.Collections.Generic.List`1[M.Box.Core.Command]][]‘. (Exception Type: ArgumentException, Stack:    at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr)
   at System.Reflection.RtFieldInfo.InternalSetValue(Object obj, Object value, BindingFlags invokeAttr, Binder binder, CultureInfo culture, Boolean doVisibilityCheck, Boolean doCheckConsistency)
   at System.Runtime.Serialization.SerializationFieldInfo.InternalSetValue(Object obj, Object value, BindingFlags invokeAttr, Binder binder, CultureInfo culture, Boolean requiresAccessCheck, Boolean isBinderDefault)
   at System.Runtime.Serialization.FormatterServices.SerializationSetValue(MemberInfo fi, Object target, Object value)
   at System.Runtime.Serialization.ObjectManager.CompleteObject(ObjectHolder holder, Boolean bObjectFullyComplete)
   at System.Runtime.Serialization.ObjectManager.DoNewlyRegisteredObjectFixups(ObjectHolder holder)
   at System.Runtime.Serialization.ObjectManager.RegisterObject(Object obj, Int64 objectID, SerializationInfo info, Int64 idOfContainingObj, MemberInfo member, Int32[] arrayIndex)
   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.RegisterObject(Object obj, ParseRecord pr, ParseRecord objectPr, Boolean bIsString)
   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.ParseObjectEnd(ParseRecord pr)
   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Parse(ParseRecord pr)
   at System.Runtime.Serialization.Formatters.Binary.__BinaryParser.Run()
   at System.Runtime.Serialization.Formatters.Binary.ObjectReader.Deserialize(HeaderHandler handler, __BinaryParser serParser, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
   at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(Stream serializationStream, HeaderHandler handler, Boolean fCheck, Boolean isCrossAppDomain, IMethodCallMessage methodCallMessage)
   at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Deserialize(Stream serializationStream)