New Things I Learned

Get return value from stored procedure

Muljadi Budiman — Wed, 17 Sep 2008 16:57:15 GMT

Recently we changed our DAC layer from using inline SQL to stored procedures in the database. On some of these SQL, we did record deletion (usually only 1 record), and we just execute it via IDBCommand.ExecuteNonQuery() and then check the return value to see how many records were affected (which should be 1) for verification that the query actually does something.

With the change to stored procedure, we just return 1 in the stored procedure if the delete is successful. However, the calling code then started to show these deletions as errors. Apparently ExecuteNonQuery only returns the number of affected rows on SELECT, INSERT and DELETE statements; for everything else it returns -1. So I tried to figure out how to get a return value from a stored procedure.

Let's assume a simplistic stored procedure as follows:

ALTER PROC ReturnOnly

BEGIN

RETURN 5

END

You can't use ExecuteScalar to get the returned value, and ExecuteNonQuery will always return -1. To get the value back, you need to add a return value parameter to the command. The name of the parameter is not important. The code to get the value returned by that procedure will be as follows:

private int ExecuteStoredProcedure(string storedProcedureName)

{

SqlConnection connection = new SqlConnection(connectionString);

// Command - specify as StoredProcedure

SqlCommand command = new SqlCommand(storedProcedureName, connection);

command.CommandType = CommandType.StoredProcedure;

// Return value as parameter

SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);

returnValue.Direction = ParameterDirection.ReturnValue;

command.Parameters.Add(returnValue);

// Execute the stored procedure

connection.Open();

command.ExecuteNonQuery();

connection.Close();

return Convert.ToInt32(returnValue.Value);

}

Returning Cursor from Stored Procedure Executing Dynamic Query

Muljadi Budiman — Tue, 26 Aug 2008 16:49:18 GMT

I usually stay on the C# development of things, and rarely venture to create SQL statements/code, other than the occasional SELECT / UPDATE / INSERT. However, we have a requirement from our Product Management group to DELETE records permanently from the database. We have proper enforced foreign keys in our DB schema, so one option we have is to create a stored procedure to help deletion, which will walk thru the referencing foreign keys to the record being deleted and then also delete those records (the children records essentially). Yes, I know records should not be deleted (it should just be marked as deleted or inactive or something), but it was a requirements insistence where my opinion was overridden.

Having the stored procedure (hence I'll refer to this as sproc) is nice since consumer just need to call the same sproc passing in the table name & record id to delete, and the sproc will do the heavy lifting. However, the sproc has some tasks to solve; since children records can contain further children records, the sproc needs to call itself recursively. Since the sproc needs to query different tables (depending on which record is deleted), the sproc has to build dynamic SQL statements and execute that. Since you need the values of the record Ids that references the parent (so it can see if those records have further children), you also need a CURSOR to loop thru those Ids).

Separately, each of the problem is fairly simple enough, sprocs can call itself recursively, you can create sprocs that execute dynamic SQL, and you can have sprocs that has a CURSOR as OUTPUT. However when you combine them together, I consider them to be a fairly neat challenge to try to tackle. The hard part is actually with getting a CURSOR back from a dynamic SQL, so I'm going to focus on that part.

To execute dynamic SQL, you need to use the built-in sp_executesql sproc in SQL Server. The sp_executesql sproc can accept the following parameters:

The SQL statement that will be executed
The parameters & types that's needed by the SQL statement (#1) in text format
The actual parameters to use in that SQL statement

So, essentially sp_executesql will create a stored procedure, containing the SQL statement passed (#1), with the parameter list of that sproc as defined in #2 and then it will invoke that newly created stored procedure, passing in the parameters as defined in the latter parameters.

To solve the problem, then we just need to create a dynamic SQL that has a CURSOR parameter which is an OUTPUT parameter. The following test sproc demonstrates this:

ALTER PROC TestProc

DECLARE @dynamicSQL nvarchar(200)

-- Have code that will construct the dynamic SQL

SET @dynamicSQL = 'SELECT FirstName FROM Contacts'

-- The cursor that will be filled by the dynamic SQL

DECLARE @outputCursor CURSOR

-- Create the dynamic SQL to fill a CURSOR instead

SET @dynamicSQL = 'SET @outputCursor = CURSOR FORWARD_ONLY STATIC FOR ' +

@dynamicSQL + ' ; OPEN @outputCursor'

-- Execute dynamic sql

exec sp_executesql -- sp_executesql will essentially create a sproc

@dynamicSQL, -- The SQL statement to execute (body of sproc)

N'@outputCursor CURSOR OUTPUT', -- The parameter list for the sproc: OUTPUT CURSOR

@outputCursor OUTPUT -- The parameter to pass to the sproc: the CURSOR

-- Code that will just output the values from the cursor

DECLARE @firstName nvarchar(200)

FETCH NEXT FROM @outputCursor INTO @firstName

-- Loop while there're more things in the cursor

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @firstName

FETCH NEXT FROM @outputCursor INTO @firstName

END

-- Be nice, close & deallocate cursor

CLOSE @outputCursor

DEALLOCATE @outputCursor

I just choose to use a simple SELECT to a table as the dynamic SQL statement; you can make it to be whatever SELECT is needed based on parameter being passed. Then the sproc declares a CURSOR, which will then be used to contain the result of the SQL statement. The whole text, including the CURSOR is the dynamic SQL we want to execute. sp_executesql is then called with the SQL statement, we then define the statement to have 1 parameter (which is the CURSOR as an OUTPUT parameter), and then we pass in the cursor as the object to pass into the dynamic SQL to be executed. The latter half of the code just loops thru the cursor to proof that the results are proper.

Not having dealt with much SQL code, this was an interesting journey for me. There are other ways to solve the problem (DELETE records), we can use cascade delete, but it doesn't support self-referencing tables. In the end we didn't use this approach, instead we just create a sproc for each table that needs to be deleted; it seems like a heck of a lot of maintenance job, but I'll let the DBA handle that.

Refresh / Update WPF controls

Muljadi Budiman — Mon, 25 Aug 2008 15:22:11 GMT

Sometime in the past, a friend asked me how to update a control to show status while his code is doing a loop of stuff. Essentially changing the text of a label (or sophisticatedly we can say a text-based progress bar). In my past coding with MFC and WinForms, it's fairly easy enough, you just invalidate and do an update (Invalidate / UpdateWindow in MFC or Invalidate / Update in WinForms). This approach also coincides with how Windows UI operate, where you specify the region that needs to be redrawn and then you send a message to the message pump for that control to paint itself.

So, I expected something similar (if not exactly the same) to also be present in WPF; much to my surprise, there is no equivalent. All my internet searches actually shows how to do this using background thread - it is the approach that needs to be taken in a proper programming context, however there are times when you just want to do something quick & dirty or you want to augment an existing app / port where you don't want to introduce new elements. There are also considerations to be made when both UI and worker thread access the same data, especially with regard to data binding (see my post about collection change not supporting multi-threading out of the box).

So, I've decided to add a helper method to refresh a WPF control. I really appreciated the Refresh method in WinForms (which executes both Invalidate & Update), so I'm renaming my method to be Refresh as well. The code snippet below also show some C# specific techniques, namely: anonymous delegates and extension methods.

public static class ExtensionMethods

{

private static Action EmptyDelegate = delegate() { };

public static void Refresh(this UIElement uiElement)

{

uiElement.Dispatcher.Invoke(DispatcherPriority.Render, EmptyDelegate);

}

private void LoopingMethod()

{

for (int i = 0; i < 10; i++)

{

label1.Content = i.ToString();

label1.Refresh();

Thread.Sleep(500);

}

The LoopingMethod is just the method I use in my Window class to update the label (updating the progress) and then the code does some heavy lifting (Sleep ). The Refresh method is the extension method that takes any UI element and then calls that UIElement's Dispatcher's Invoke method. The trick is to call the Invoke method with DispatcherPriority of Render or lower. Since we don't want to do anything, I created an empty delegate. So how come this achieves refresh functionality?

When the DispatcherPriority is set to Render (or lower), the code will then execute all operations that are of that priority or higher. In the example, the code already sets label1.Content to something else, which will result in a render operation. So by calling Dispatcher.Invoke, the code essentially asks the system to execute all operations that are Render or higher priority, thus the control will then render itself (drawing the new content). Afterwards, it will then execute the provided delegate (which is our empty method).

Pretty weird; there was a post somewhere in my google search that led me this route, and I was surprised as to how it worked. I couldn't find it anymore, but credit where credit is due, someone else figured out that Invoke-ing a Render or lower priority task will result in the UI being redrawn.

Calling Generic Method when given a type

Muljadi Budiman — Fri, 22 Aug 2008 14:41:54 GMT

Every now and then I have a need to call a generic method, but I can't quite call it generically. Usually that particular situation involves needing to call a generic method, and I have the type for that generic method. There are various reasons/examples of why anyone would get to this point, in my case we have an infrastructure code that can deal with objects generically, but because it is infrastructure code, we can't have it refer to the actual entity classes that are in the higher layer/tier.

However, if we only have the type, we can't just call the method as is, we have to call it via reflection. And as much as I like reflection, it has some drawbacks. Consider the following snippet:

public void InvokeGenericMethod(object o)

{

MethodInfo mi = this.GetType().GetMethod("MyGenericTestMethod", BindingFlags.DeclaredOnly | BindingFlags.NonPublic | BindingFlags.Instance);

mi = mi.MakeGenericMethod(new Type[] { o.GetType() });

mi.Invoke(this, new object[] { o });

}

private string MyGenericTestMethod<T>(T value)

{

return value.GetType().ToString();

}

To call the method (it is private), I have to call Type.GetMethod with some not very nice looking parameters. The name supplied is in quotes; as such if the actual method name is changed, this code will still compile, it'll just crash when actually ran. Mind you, the code works; it's just not very maintenance-friendly.

A trick I use for this then is to use delegates as follows:

public void InvokeGenericMethod(object o)

{

Func<string, string> temp = MyGenericTestMethod<string>;

MethodInfo mi = temp.Method.GetGenericMethodDefinition();

mi = mi.MakeGenericMethod(new Type[] { o.GetType() });

mi.Invoke(this, new object[] { o });

}

private string MyGenericTestMethod<T>(T value)

{

return value.GetType().ToString();

}

By using delegates, I no longer need to use reflections to get the method to call; I just point to the method as a delegate, and then create a generic method definition from that delegate. Everything after that is still the same. One nice benefit to this is if the method name is changed, the refactoring engine will also change the delegate reference, and if it doesn't the code will fail at compile-time. Nicer, cleaner looking code as well, IMHO.

Pack URI Authority - Local Assembly = Executable

Muljadi Budiman — Wed, 30 Apr 2008 16:02:50 GMT

I had a post about accessing XAML resources using Pack URI, and actually had the chance to try it out. The format of the pack URI is "pack://authority/path", and there are only 2 valid authority: application:/// and siteoforigin:///, as described in Microsoft's Pack URI documentation.

The path will just point to the resource you want to get; you can include the folder / subfolder, other assemblies (containing the resource), even versions of the assembly. The simplest example they had was:

pack://application:,,,/ResourceFile.xaml

that points to ResourceFile.xaml in the local assembly. What the documentation forget to define however, is what is local assembly; the local assembly is essentially the WPF executable. If you have other XAML in a different assembly, and you have code in that same assembly to load the XAML using Pack URI, you have to treat it as if you're loading from a different assembly.

As an example, if you have a WPFResource.DLL which has a XAML resource (in this case test.xaml) you'd like to load, the code to load it will be as follows:

private static void InitializeWPFApplicationResources()

{

Uri uri = new Uri("pack://application:,,,/WPFResource;component/test.xaml");

Application.Current.Resources.Source = uri;

}

The Pack URI has to refer to an external assembly - even though test.xaml resides in the same assembly with the executing code. Anytime the pack URI doesn't refer to a different assembly, it will try to load the XAML resource from the executable file - I'm actually surprised with this, since it forces the Uri to always contain the assembly name which can be erroneous (say the assembly gets renamed). Granted you can get around this problem by getting the assembly's name in code.

Files downloaded by IE are marked Untrusted (or why the CHM file I downloaded didn't work)

Muljadi Budiman — Mon, 28 Apr 2008 21:53:54 GMT

I downloaded a CHM (Windows help) file for SharpZipLib software package to be used in my test app (pretty cool - allows me to do most stuff relating to zip files), and when I open the help file, none of the information shows.

Of course when I tried to open it up, Windows always asks me to verify that I wanted to open the file, with the dialog below:

However, even if I do allow this, the CHM file doesn't show its contents; the IE control that should display the help contents only show that the Program cannot display the webpage. It turns out that this is a side effect of an IE security update - downloaded files are marked as untrusted and thus will require user's verification before it can be opened. However, even if you verify for opening the files, the file itself is still marked untrusted, and as such IE will not allow rendering the information on the screen.

To fix this, you'd have to mark the uncheck the 'Always ask before opening this file' checkbox, and you're good to go. You can also go to the file's properties and click the Unblock button near the bottom. This situation puzzles me for a few minutes before the light bulbs start switching on. I'm pretty sure I will get this again in the future and be puzzled the same way .

Refer to resources in WPF with Pack URI

Muljadi Budiman — Mon, 28 Apr 2008 18:41:44 GMT

It's been over 2 months since I last updated my blog. I have been busy; we got a new VP of IT and he made some changes and there are a bunch of stuff that's getting rejigged and people moved, projects retargeted, etc. etc. In the end, it's not enough reason for me to not blog at all.

I started this blog so I can record my experiences with learning new stuff. I have to say that I came back to this blog more than enough times to relook at the stuff I had learnt, to the point where my lapse in adding new stuff actually prevents me from being very productive - I learned some stuff that I should've put in here but I didn't, and in the end have to relearn it again since I didn't record it. D'uh.

Anyway, I need to be more disciplined and will try to be more active. Relearnt something today about how to access file resources in WPF, instead of using the typical StaticResource or DynamicResource - use Pack URIs, as further detailed here. The 2 important ones for me to remember are referencing items in the same assembly and in a different assembly (use the following format with the Source attribute in xaml):

pack://application:,,,/ResourceFile.xaml

pack://application:,,,/ReferencedAssembly;component/ResourceFile.xaml

This is actually very relevant to me; sometime last week, we had decided on trying to consolidate all our resources into a single DLL for each team project we have - it helps with internationalization (minimizing the number of DLLs), easier checking for entries that may already exist, can be used by other applications / modules if necessary, and can be swapped with another resource DLL or a newer one.

With our application, we won't actually use the pack URI to point to different XAML (we may, but I see the use to be fairly limited in that regard), but we will use the pack URI to point to proper image / icon / bitmap files - so it's nice if we can consolidate all those files into a single DLL.

Lambda Expression type inference doesn't quite work on actual methods

Muljadi Budiman — Thu, 14 Feb 2008 00:47:18 GMT

One of the nice things with lambda expression is type inference - it is actually pretty neat. A neat example that is as follows:

private void Test()

{

string original = "Muljadi";

string upper = Demo(original, s => s.ToUpper());

int length = Demo(original, s => s.Length);

}

private static TResult Demo<T, TResult>(T value, Func<T, TResult> func)

{

return func(value);

}

In the example above, the first call to the Demo method has first parameter being a string - which then is immediately inferred to type T. The compiler will also then infer that the second parameter is a lambda expression, which expects a single parameter, also of type T (which it has recognized as a string), walks thru the lambda expression and recognizes that it returns also a string. As such, it determines that the first Demo method call will return a string and infer TResult as string.

The second call to the Demo method follows the same path, and again when parsing the lambda expression, it will determine that it returns string.Length, which is of type int - it will then infer TResult as int.

However, I found out that the type inference doesn't work as nicely with actual methods. The snippet below wouldn't work - I tried with as many combinations as possible, and I could not get it to work without actually creating an instance of the delegate.

private void Test()

{

string original = "Muljadi";

int length = Demo(original, GetLength); // Type can't be inferred error

length = Demo<string, int>(original, GetLength);

length = Demo(original, new Func<string, int>(GetLength));

length = Demo(original, s => GetLength(s));

}

private int GetLength(string s)

{

return s.Length;

}

In my mind it just doesn't seem to make sense - the GetLength method is non-generic, has the return types and parameter type in the method and the compiler can't make sense of it. The other 3 calls are valid because:

You specify the type in the Demo method call itself
Your instantiate the method as a Func delegate, where you also specify the types
You actually make it as a lambda expression call

In the end, lambda expressions actually has better inference support than actual methods. Hopefully Microsoft can add this in .NET 4.0.

Expensive Home theatre...

Muljadi Budiman — Mon, 11 Feb 2008 15:45:03 GMT

One of my hobby / passion is home theaters, and even though $6 million is overkill, I thought I would brought it up as a good read . Kipnis Studio Standard, 4096x2160 video, 18 ft screen, 8 speakers with 8 subs...

The Sony Quad HD projector is one of interest to me - it costs $100K (if this post is to be believed) with NO lens (another $19K option give or take a couple of grand given what kind of lens you want), but I would like to see how an 8 megapixel image would look like on a big screen.

Of course, the article doesn't talk about black level, contrast level, etc., but the equipment list is enough to make my mouth water. My current ideal HT setup would be a Sony G90 with DynAudio Contour speakers (Velodyne subs), driven with some nice amps (Mark Levinson, Krell, Plinius, etc.). Most probably by the time I can afford these, my wife would insist on a small (read: digital) PJ, so current choices for me would be either JVC RS-1/RS-2 (with a scaler that can do color control) or a Sony Diamond.

Perchance to dream...

My foray into Lambda Expressions

Muljadi Budiman — Mon, 11 Feb 2008 15:16:50 GMT

I was aware of lambda expressions because I was reading about them in the C# 3.0 white paper specification, along with other new language features like extension methods, anonymous types, basis for LINQ, etc. I thought it was cool enough, it makes passing anonymous delegates much better in terms of code writing/reading. However, actually using them takes a bit of getting used to. As such, I'd like to give a short example as to how I visualize writing lambda expressions.

Consider the following class structure representing a sports organization:

public class League

{

private List<Conference> _conferences;

public IList<Conference> Conferences

{

get { return _conferences; }

}

public class Conference

{

private List<Division> _divisions;

public IList<Division> Divisions

{

get { return _divisions; }

}

public class Division

{

private List<Team> _teams;

public IList<Team> Teams

{

get { return _teams; }

}

public class Team

{

private string _name;

public string Name

{

get { return _name; }

}

Basically, we have a League, which has a collection Conferences, which in turn have a collection of Divisions and each division having the Teams belonging to that division. Let's say the task is to go collecting the names of all teams in the league - the code to do that is simple, you just loop through the conferences, loop through the division and then loop through the teams. The code to do so will look like the following:

List<string> teamNames = new List<string>();

League league = new League();

foreach (Conference conference in league.Conferences)

{

if (conference == null)

continue;

foreach (Division division in conference.Divisions)

{

if (division == null)

continue;

foreach (Team team in division.Teams)

{

if (team == null)

continue;

teamNames.Add(team.Name);

}

The code above, it works, but it just seems mundane - you have multiple foreach loops, multiple null checks - it would be nice if we can refactor this somewhat. So, we can create a method to refactor this - basically the method needs to be able to loop through a collection, check for null, and also do something else to the item being iterated. In the code above, it needs to basically iterate through the inner collection twice (one for division and one for team), and when iterating through teams, it needs to add the team name to the collection (not iterate through). With that pattern, a refactored method can be written as follows:

private void ForEach<T>(IEnumerable<T> enumerable, Action<T> action )

{

if (enumerable == null)

return;

foreach (T item in enumerable)

{

action(item);

}

The method above will basically take an enumerable, checks to make sure it can enumerate it, and then loop through that enumerable and do something to the item being iterated - these 3 things satisfy the condition of the refactored method as mentioned above. Now, that something that needs to be done is defined by the Action<T> delegate that the consumer of this function needs to specify. So the above foreach loops can be written (using anonymouse delegates) as follows:

ForEach(league.Conferences, delegate(Conference conf)

{

ForEach(conf.Divisions, delegate(Division div)

{

ForEach(div.Teams, delegate(Team team)

{ teamNames.Add(team.Name); });

});

Well... it looks OK, but it's fairly unreadable - not to mention the developer needs to make sure everything ends properly, especially with the combination of the curly braces and the parentheses. Enters Lamda expression, and the above code becomes like this instead:

ForEach(league.Conferences,

conf => ForEach(conf.Divisions,

div => ForEach(div.Teams,

team => { if (team != null) teamNames.Add(team.Name); })));

Now, it is more compact, no question about it. However, is it more readable? Given the above example, assuming the developer knows what needs to be expected, it may be more readable. 6 months from now if I were walking through the code, I would imagine it would take me awhile to read through that line - it requires knowing what the ForEach method does, it requires the developer to understood what the lambda expression is doing at each level, and it will be very hard to customize if the need arises.

That said, I have to say I learned quite a bit from trying to refactor the above code into lambda expressions - it gives me a better understanding as to how to use them, how to refactor methods as lambda expressions, and also shows me a glimpse as to how they can be dangerous. Most probably, I would make the last piece of logic (collecting the team name) to be its own function and pass that as a delegate, instead of writing the delegate in-line. Two reasons for that:

It's the portion where most probably will require logic changes (say not to add the team name if team name is empty, or has spaces, etc.)
It limits the consumer code to read like calling functions, so as I read through the code, I can understand it calls another piece of code (method) and I don't have to read the function definition in-line.

The above example is also a bit advanced since we're refactoring multiple foreach loops into a recursive delegate calls - take some time to make it sink through - it took me a good 2 hours to go through this the first time around.