Generate SQL update queries in C# for models with empty data

Here's the scenario:
You get a payload using a model that contains only a subset of the fields populated. The remaining fields contain null\default values, however, while writing an update query, you cannot set the default values in your statement since you'd like to ignore them.

So, I wrote a small helper method that allows you to handle this case.
You can then build your update statement using the following method. I'm using the Dapper library as ORM.

There are risks using this method such as SQL Injection, etc. so you're better of using Prepared Statements 99% of the time. However, if you'd like to write ad-hoc SQL queries, this should suffice.

What my job is like

Imaging you're entering a building. Within the lobby you have a door on the side. Opening the door leads you to a space where there's another building. Often it looks like the same building as the one you entered the first time, sometimes it's different. Do this 5 times. Now you have to get a smoke signal in the 5th building out to the watchman of the first building, all the time while traps keep opening and monsters check your progress. Welcome to crazy land. This is what I do for a living. I'm a coder.

USACO - Clocks

This was a rather tricky problem listed here that would not run quickly for a standard BFS. So the name of the game was "OPTIMIZE', especially given the time constraint of 1 sec and for some solutions having no. of moves greater than 20.

The crucial thing to realize is that iterating 9 decisions(1 for each key) will not work for large depths, so we need to prove keys that are repeated more than 3 times, since using a key for 4 will bring the clock back to the same position.

After this, to represent a state of 9 clocks, we can use just 2 bits for each clock, for a total of 18 bits(which can be fit in a java int). But how can I fit in 12 0 clock in 2 bits (won't it take 4 bits)? Actually you can't but that's not a problem since the clock can only be in 4 states so representing 00 as 12'0clock, 01 as 3'0clock and so on will work. This will make your final state be 0 since all clocks are then at 12 o'clock.  After this you need some bit mask magic to change the state for a clock. (This is a bit non-trivial since we're changing 2 bits, but is still pretty very doable). If you're unfamiliar with bitmasks, check out bmerry's article at topcoder here.

We also need to add memoization to our BFS to make sure repeated states are not processed. Again, we can use an int to to represent a state of clocks similar to what we just saw.
I also discovered that using an ArrayDeque instead of a LinkedList for my Queue implementation speeded up things by about 20%, even though both are supposed to work in O(1). I believe this is because ArrayDeque does not resize it's elements as often as LinkedList, but I haven't confirmed this. Everything else is just straightforward BFS.  Here's the code:

Dynamically executing stored procedures with output parameters

A very interesting case crops up when you are trying to execute dynamically created SQL using sp_executesql when you have to pass in output parameters.

Let's assume that you have a stored procedure called 'OnScreenChemistry' which is an ingenious algorithm that you created to calculate the chemistry between movie stars.
It takes in 2 input parameters - Actor1, Actor2 of type varchar(255) and returns an output parameter of MatchScore which is a float that reflects on screen chemistry through a normalized range of 0 - 1.

Assuming you're trying to compare on-screen chemistry between all actors in your fictitious Actor table.
You can use a cross join to compare the actors and put each Actor1,Actor2 parameter inputs into 2 variables via a cursor.

Having these 2 variables you could create create SQL dynamically using


Yes, I know it's ugly, but it's a simple way to create dynamic SQL, when all you need to do is to provide input parameters.
However, the catch is that you you can't add an output parameter in the same manner since you need to provide a placeholder for the output variable, instead of a value like you can for input parameters.

So here's how you can call the stored proc dynamically using output parameters:


At this point, your @Result variable should contain the value from the stored proc. You could perform an insert of this variable along with your @Actor1 and @Actor2 variables into a result table for instance. It's upto you!

Paging with the ASP.NET repeater control

Since ASP.NET does not provide paging for repeaters, and I could not find any material out there that would work in a user control, I decided to create my own custom paging. Most examples I saw used dynamically created controls for paging. As dynamically created controls go, they tend to cause more problems than the problem they solve, especially when you're planning to modify them. Don't blame the coders, wiring up dynamically created controls correctly requires an intricate knowledge of the ASP.NET page life cycle. I however felt that in most cases a simpler version would do, so I opted for statically created paging controls.

Let's say that I have a simple repeater in my user control:


I'm going to add a placeholder for my paging controls.


Notice how i'm using only 4 link buttons, which might not be adequate for most situations. So the idea is to dynamically change the properties of these link buttons depending on the current page.
Once we have a way to keep track of the current page we are on as well as the total number of data items(or pages), we are well on our way to finishing our control.
To deal with keeping track of the current page and total number of data items, i'm going to use control state here. I could have used Viewstate instead for simplicity but there's a possibility of viewstates being turned off by another developer using my control, and in a critical feature such as paging, it's better to store state in a control state.
Here's the code to make that work:


I'm using a structure to store my state specific properties here. The SaveControlState and LoadControlState methods are used by ASP.NET to serialize and deserialize my structure into control state.
The things remaining to do are to load data and also to format our control dynamically based on the page we are at. Let's look at loading data first:
I initialize page load to fire our data loading method the first time the page is called.



The actual loading is done like this:

The important thing to note here is that we use a PagedDataSource as our datasource for the repeater, not the data items directly. This is because the repeater control does not support paging out of the box. To enable paging, we need to use the PagedDataSource as an adapter, whose properties such as the size of the page can now be set.

For formatting the output of the page links, I use the following method:
Here n has been set to 4, since we are using 4 linkbuttons in our ascx file. If we wish to add more buttons and spacers (which are to add seperation between the buttons), it would be as simple as adding those controls following the same naming convention as the other buttons, and changing the value of n.

For eg. let's say that we have 7 pages, and are currently in the second page.

The paging would look like " Prev 2 3 4 5 Next"
2 would be disabled. If you look at the ascx file again, you'll notice that the linkbuttons were hardcoded with values 1 to 4. The values have now been dynamically changed.



The following code performs event handling for the page buttons. If you look at the LoadItemsBasedOnCurrentPage() method, you will find that the method the call to LoadData fetches only items we will require on the current page. This results in a smaller payload of information being sent at each access to a page.


That's about it. All you need to do is to add you own method to get all the data items, change items per page (which i have deliberately set to the ridiculous value of 2) and bind them however you see fit.

This example, can with a bit of care, be easily extended to support "jump to first, last" or having .. paging feature, where you can jump to the middle of the page list. However, I have not included that for the sake of simplicity.

Happy plagiarizing. Just kidding, you have my blessings.

Strip paragraph tags in umbraco properties using C#

Today I was pulling some properties from an umbraco document for use in a user control, and I found to my horror that some of the content still had starting and ending paragraph tags around it. This was of course thanks to the rich text editor,TinyMCE.
Normally, while using xslt one would write the following to remove them quickly.

<umbraco:item field='property' stripParagraph='true'
runat='server'>

Not finding an equivalent function in the umbraco API, i wrote this quick-and-dirty method that works quite well for the task(at least for me). Hope it saves some time for you.