How to use the result of a stored procedure in a MSSQL query

This problem is one I faced several years ago, but today I remembered it and thought someone might need the solution. The problem was simple, I needed to use the result of a stored procedure in a query. The client had used a backend using MS SQL as data store, but couldn’t adjust the database schema.

When I needed to retrieve data from that database, some of the data was calculated in a stored procedure, which I needed to join on data in tables. Of course it was an option to do that in the software I was writing, but I didn’t like that idea, since it could much data and at that time passing tables as a parameter wasn’t possible.

The solution I found isn’t nice and I wouldn’t recommend to use it, unless there is absolutely no other way of achieving the desired result.

Suppose we have a stored procedure that returns a rows with 2 fields, a varchar and an int, and we need to join it on the int field, you could do this:

/*Create a table typed variable,*/
DECLARE @procresults TABLE ( field1 VARCHAR(1000), field2 INT ); 
/*execute the stored procedure, store the result in the variable*/
INSERT @procresults EXEC storedprocedure
/*Use the result in a query*/
SELECT * FROM @procresults pr
INNER JOIN otherTable ot ON pr.field2 = ot.id;

 

I hope you don’t need it, but if you do, I hope it helps you.

Comments Off on How to use the result of a stored procedure in a MSSQL query  

C# base64 encoding and decoding

Today a simple thing, which I always have to lookup because I rarely use it. While communicating with some systems you have to encode the data using base 64 encoding. Base64 encoding is an encoding where every character is encoded using only a..z A..Z 0..9 + and /. This solves problems with special control characters. 

The functionality is built in the .net framework. To Encode a string to base64 you can use:

public static string EncodeTo64(string toEncode)
{
    byte[] arrayToEncode = System.Text.Encoding.Unicode.GetBytes(toEncode);
    return System.Convert.ToBase64String(arrayToEncode);
}

And to decode it, you can use:

public static string DecodeFrom64(string toDecode)
{
    byte[] arrayToDecode = System.Convert.FromBase64String(toDecode);
    return  System.Text.Encoding.Unicode.GetString(arrayToDecode);
}

As you see, these methods are quite similar.

I hope you can use these methods.

Comments Off on C# base64 encoding and decoding  

Use the WP8 appbar in ANY MVVM framework

While some frameworks, like Caliburn.Micro,have the possibility the use the appbar button with no code at all in the cod behind file, most frameworks don’t. This blogpost explains how you can use the appbar buttons in any mvvm framework the doesn’t support this feature, like MVVM Light or NRoute.

The solution I found is very simple, some people will say that is not fully MVVM complainant. But in my opinion it is, I will also explain why I believe it is MVVM.

If you used Google to solve this problem, you have probably seen several solution that require a bunch XAML and 1 or more external libraries. I’ve tried they too, they work, but they also make the XAML less readable and increase the size and decrease the speed of your app. And that are real disadvantages.

My simple solutions it to use the code-behind file. In the XAML you put this code:

    <phone:PhoneApplicationPage.ApplicationBar>
        <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True" Opacity="1">
            <shell:ApplicationBarIconButton x:Name="Cancel" IconUri="/Toolkit.Content/ApplicationBar.Cancel.png" Text="Cancel" Click="Cancel_Click"></shell:ApplicationBarIconButton>
        </shell:ApplicationBar>
    </phone:PhoneApplicationPage.ApplicationBar>

And in the code behind this (when using a framework that uses a viewmodellocator):

private void Cancel_Click(object sender, System.EventArgs e)
{   
    ViewModelLocator.RapportageStatic.Cancel();
}

Or this, in any other case:

private void Cancel_Click(object sender, System.EventArgs e)
{   
    ((RapportageViewModel)this.DataContext).Cancel();
}

 

I hear you think:”When using MVVM the code behind should be empty” but that is absolutely not true. MVVM is about separation of concerns, and this solution respects that fully. Why? Well the code behind is part of the VIEW, and the only thing it does, is call the appropriate method in the VIEWMODEL. Just like  EventToCommand does in the XAML and the code is still fully automatic testable.

If you think that this breaks MVVM, I disagree, but I still advise you to use this. Any framework should make your work easier, not harder, breaking the rules a little can do no harm.

Comments Off on Use the WP8 appbar in ANY MVVM framework  

Jquery.selectbox inside updatepanel fails postback

For an ASP.NET website I worked on a while ago I’ve use the jquery selectbox to make the dropdownlist look a little nicer (this is just an ugly example, I’m not a designer):

JQuery selectbox

This looks great and worked great until the dropdownlist was placed inside an updatepanel. The first time the paged was loaded it looked just like it should, but after the first postback the dropdownlist turned back to the default style:

JQuery selectbox

I found rather quickly what the problem was, but it took me some time before I found the solution. That solution is very simle, just put add this code to the Page_Load event

if (this.IsPostBack)
{
    ScriptManager.RegisterStartupScript(this.Page, this.GetType(), "temp", "<script type='text/javascript'>$('select').selectbox();</script>", false);
}

Hope it helps you…

Comments Off on Jquery.selectbox inside updatepanel fails postback  

MsSQL having max(id) = id problem –> row_number partition

One problem I haven’t been able to solve in a satisfying way is to create a query that returns a full row based on an aggregate function. Suppose we have the following database schema:

If you need to write a query that returns the most expensive book for each customer you probably would first write a query like:

SELECT C.customerId, C.Name, B.Title FROM customers C 
INNER JOIN orders O ON C.CustomerId = O.CustomerId 
 
INNER JOIN Books B ON B.BookId = O.BookId 
GROUP BY C.customerId, C.Name, B.Title 
 
HAVING B.price = MAX(B.price)

Which results in this error:

Column ‘Books.Price’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

The only solution I found was to create a sub-query that returns price of the most expensive book per customer and join that sub query on the price. Since Sql server 2008 there is a very nice solution to this problem. The ROW_NUMBER PARTITION function, that function returns a row number for each record, which in nice. But how is this going to solve your problem? That will happen when you use the “OVER (PARTITION BY …. ORDER BY…) “. This tells Sql server when the row number needs to be reset and how to number. For our database schema this this query looks like this:

SELECT *, ROW_NUMBER() OVER(PARTITION BY C.CustomerId ORDER BY PRICE DESC) rownumber FROM customers C 
INNER JOIN orders O ON C.CustomerId = O.CustomerId 
INNER JOIN Books B ON B.BookId = O.BookId

 

This query returns all books every customer has ordered, with an extra column “rownumber” that is reset per customer, and is ordered from highest to lowest price:

Now, all we need to do is to get the rows where rownumber equals 1. For that we need to create a sub query, but this sub queru doesn’t cause any extra table scans and is very light:

SELECT * FROM ( 
    SELECT C.customerId, C.name, B.Title,B.Author, B.Price, ROW_NUMBER() OVER(PARTITION BY C.CustomerId ORDER BY PRICE DESC) rownumber FROM customers C 
    INNER JOIN orders O ON C.CustomerId = O.CustomerId 
    INNER JOIN Books B ON B.BookId = O.BookId 
) tmp 
WHERE tmp.rownumber = 1

That gives use this result:

And that is what e needed in the first place.

Comments Off on MsSQL having max(id) = id problem –> row_number partition