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.

	/// <summary>
	/// Helper to create update statements in SQL. Adds 'key'='value' tokens
	/// </summary>
	/// <typeparam name="T"></typeparam>
	/// <param name="value"></param>
	/// <param name="key"></param>
	/// <param name="sb">the stringbuilder where the value gets appended to</param>
	/// <param name="commaNeeded">do i prefix a comma before adding my key-value</param>
	/// <param name="addQuoteToValue">override false for string values such as functions that shouldn't be quoted</param>
	/// <returns>true if we added something, false if not</returns>
	public static bool AppendValueParameter<T>(T value, string key, StringBuilder sb, bool commaNeeded, bool addQuoteToValue = true)
	{
	bool added = false;
	if (!EqualityComparer<T>.Default.Equals(value, default(T))) //null and default values excluded
	{
	string val;
	if (typeof(T) == typeof(DateTime))
	val = string.Format("'{0}'", ((DateTime)(object)value).ToString("yyyy-MM-dd HH:mm:ss"));
	//no need quote for numeric or bool
	else if (TypeHelper.IsNumeric(typeof(T)) || (Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T)) == typeof(bool))
	val = value.ToString();
	else if (addQuoteToValue)
	val = string.Format("'{0}'", value);
	else
	val = value.ToString();
	sb.Append(string.Format("{0}{1}={2}", commaNeeded ? "," : "", key, val));
	added = true;
	}
	return added;
	}

view raw SqlBuilder.cs hosted with ❤ by GitHub

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.

	public bool UpdatePerson(UpdatePersonEvent person)
	{
	var sb = new StringBuilder();
	bool commaNeeded = false;
	commaNeeded |= SqlBuilder.AppendValueParameter(person.personName, "Name", sb, commaNeeded);
	commaNeeded |= SqlBuilder.AppendValueParameter("Reverse(" + person.personName + ")", "NameInReverse", sb, commaNeeded, addQuoteToValue:false); //in case you're passing in a sql function as a value
	commaNeeded |= SqlBuilder.AppendValueParameter(DateTime.UtcNow, "UpdateUTC", sb, commaNeeded);
	string query = string.Format("update person set {0} where personUID={1}", sb, person.personUID.ToStringWithoutHyphens().WrapWithUnhex());
	using (var connection = new MySqlConnection(_connectionString))
	{
	int rowsAffected = connection.Execute(query, null, commandType: CommandType.Text);
	return (rowsAffected > 0);
	}
	}

view raw usage.cs hosted with ❤ by GitHub

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:

	import java.io.*;
	import java.util.*;
	class Node
	{
	int state;//current state of clocks
	byte[] moves;//past moves done
	public Node(int state, byte[] oldMoves, Byte lastMove)
	{
	this.state = state;
	moves = oldMoves == null ? new byte[0] :
	Arrays.copyOf(oldMoves, oldMoves.length+1);
	if(lastMove!=null)
	moves[oldMoves.length] = lastMove;
	}
	}
	public class clocks {
	static boolean[] vis = new boolean[1 << 18];
	static String[] chars = {"ABDE", "ABC", "BCEF", "ADG", "BDEFH", "CFI", "DEGH", "GHI", "EFHI"};
	static char[][] moves;
	//given clock state and key, get new clock state
	private static int doChange(int state, int m)
	{
	m--;
	int n = moves[m].length;
	for(int i = 0; i < n; ++i)
	{
	int maskPos = ((moves[m][i] - 'A')<<1);
	//get the two bits that are masked into least significant bits and increment
	int newstate = ((state >> maskPos) + 1) & 3;
	//reset the 2 bits in old state
	state &= ~(3 << maskPos);
	//add the two bits */
	state |= (newstate << maskPos);
	}
	return state;
	}
	private static byte[] go(int arr)
	{
	Queue<Node> q = new ArrayDeque<Node>();
	vis[arr] = true;
	q.add(new Node(arr, null, null));
	while(!q.isEmpty())
	{
	Node s = q.poll();
	if(s.state == 0)//finish
	return s.moves;
	byte[] cnt = new byte[10];
	for(byte m : s.moves)
	++cnt[m];
	for(byte i = 1; i <= 9; ++i)
	{
	if(cnt[i] >= 3)continue;
	int changed = doChange(s.state, i);
	if(vis[changed])
	continue;
	Node ns =new Node(changed, s.moves, i);
	vis[ns.state] = true;
	q.add(ns);
	}
	}
	return null;
	}
	public static void main(String[] args) throws Exception {
	long startTime = System.currentTimeMillis();
	String taskname = "clocks";
	Scanner scr = new Scanner(new BufferedReader(new FileReader(taskname + ".in")));
	/*
	* Use 2 bits to represent clocks. 12'0 by 0, 3'0clock by 01, 6'0 clock by 10 and 9'0 by 11
	*/
	moves = new char[chars.length][];
	for(int i = 0;i < chars.length; ++i)
	moves[i] = chars[i].toCharArray();
	int state = 0;
	int i = 0;
	while(i < 9)
	{
	//12'0 represented as 0, 3 o clock as 1 and so on
	int val = (scr.nextInt()/3)%4;
	//top left clock in last two bits...bottom right in left two bits
	int maskPos = (i<<1);
	state |= ((3&val) << maskPos);
	i++;
	}
	scr.close();
	//Call main
	byte[] res = go(state);
	//OUTPUT
	PrintWriter pout = new PrintWriter(new BufferedWriter(new FileWriter(taskname + ".out")));
	String line = "";
	if(res.length != 0)
	line += res[0];
	int nres = res.length;
	System.out.println(nres);
	for(i = 1;i < nres; ++i)
	{
	line += " " + res[i];
	}
	pout.println(line);
	System.out.println(line);
	System.out.println(System.currentTimeMillis()-startTime);
	pout.close();
	}
	}

view raw clocks.java hosted with ❤ by GitHub

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

	SET @dynamicSql = 'exec ' + @proc + ' ''' + @Actor1 + ''', '''+@Actor + ''''
	EXECUTE sp_ExecuteSQL @dynamicSql

view raw gistfile1.txt hosted with ❤ by GitHub

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:

	DECLARE @proc varchar(255), @dynamicSql varchar(255), @ParamDefinition varchar(255), @Actor1 nvarchar(255),@Actor2 nvarchar(255)
	SET @proc = 'OnScreenChemistry'
	--*cursor declare/fetch stuff*
	--set @Actor1, @Actor2 values here from your cursor
	--create your query with placeholders for the parameters to your stored proc
	SET @dynamicSql = 'exec ' + @proc + '@Actor1Param,@Actor2Param,@ResultParam output'
	--create definitions for your parameter placeholders
	Set @ParamDefinition = '@Actor1Param nvarchar(255) ,@Actor2Param nvarchar(255), @ResultParam float(53) output'
	-- call your stored proc using your dynamic query and pass in values to your parameters.
	execute sp_executesql
	@dynamicsql
	,@ParamDefinition
	,@Actor1Param = @Actor1
	,@Actor2Param = @Actor2
	,@ResultParam = @Result output

view raw gistfile1.sql hosted with ❤ by GitHub

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:

	<asp:Repeater ID="Repeater1" runat="server">
	<HeaderTemplate>
	<ul id="navi">
	</HeaderTemplate>
	<ItemTemplate>
	<li>
	<a href='<%#DataBinder.Eval(Container.DataItem, "ArticleURL")%>'>
	<%#DataBinder.Eval(Container.DataItem, "Name")%>
	</a>
	</li>
	</ItemTemplate>
	<FooterTemplate>
	</ul>
	</FooterTemplate>
	</asp:Repeater>

view raw gistfile1.html hosted with ❤ by GitHub

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

<table>

<tr><td>

<asp:PlaceHolder ID="plcPaging" runat="server" >

<asp:LinkButton ID="prevLnk" Text = "Prev" runat="server" OnClick="prev_Click"/>

<asp:Label ID="prevspacer" Text = " " runat="server" />

<asp:LinkButton ID="lnkPage1" Text = "1" runat="server" OnClick="lnk_Click"/>

<asp:Label ID="spacer1" Text = " | " runat="server" />

<asp:LinkButton ID="lnkPage2" Text = "2" runat="server" OnClick="lnk_Click"/>

<asp:Label ID="spacer2" Text = " | " runat="server" />

<asp:LinkButton ID="lnkPage3" Text = "3" runat="server" OnClick="lnk_Click"/>

<asp:Label ID="spacer3" Text = " | " runat="server" />

<asp:LinkButton ID="lnkPage4" Text = "4" runat="server" OnClick="lnk_Click"/>

<asp:Label ID="spacer4" Text = " | " runat="server" />

<asp:LinkButton ID="nextLnk" Text = "Next" runat="server" OnClick="next_Click"/>

</asp:PlaceHolder>

</td></tr>

</table>

view raw gistfile1.html hosted with ❤ by GitHub

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.

	protected void Page_Load(object sender, EventArgs e)
	{
	if (!Page.IsPostBack)
	LoadData(itemsPerPage, 0);
	}

view raw gistfile1.txt hosted with ❤ by GitHub

The actual loading is done like this:

	private void LoadData(int take, int pageSize)
	{
	//somehow return all the objects you need. could be a datatable instead of a list of objects
	List articles = GetAllItems();
	//set total item count
	RowCount = articles.Count;
	var topDocs = (from c in articles select c).Skip(pageSize).Take(take);
	PagedDataSource pagedSrc = new PagedDataSource();
	pagedSrc.DataSource = topDocs;
	pagedSrc.PageSize = itemsPerPage;
	pagedSrc.AllowCustomPaging = true;
	pagedSrc.AllowPaging = true;
	this.Repeater1.DataSource = topDocs;
	this.Repeater1.DataBind();
	}

view raw gistfile1.cs hosted with ❤ by GitHub

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.

	/// <summary>
	/// sets the paging control's properties
	/// </summary>
	protected void FormatPagingControl()
	{
	//number of page links
	int n = 4;
	int lastpage = ((RowCount -1) / itemsPerPage) + 1;
	int startPage = CurrentPage;
	int lastPageToDisplay = Math.Min(lastpage, startPage + (n-1));
	//if we have only page, do not show paging controls
	if (RowCount <= itemsPerPage)
	{
	plcPaging.Visible = false;
	return;
	}
	else plcPaging.Visible = true;
	//in the case that current page is not in the first n pages and belongs to last (n-1) pages
	//current page will not be the first page link.
	int numberOfPagesDisplayed = lastPageToDisplay - startPage + 1;
	//make sure we display maximum number of page links as possible.
	if(numberOfPagesDisplayed < n)
	{
	//push start page back
	startPage -= (n - numberOfPagesDisplayed);
	}
	if (startPage < 1)
	startPage = 1;
	/*
	* Now, using startpage and lastPageToDisplay,perform render
	* */
	//previous and next buttons
	prevLnk.Visible = (startPage > 1);
	nextLnk.Visible = (lastPageToDisplay < lastpage);
	//page links and spacers
	for (int i = 1; i <= n; ++i)
	{
	LinkButton lnk = ((plcPaging.FindControl("lnkPage" + i)) as LinkButton);
	Label spacer = ((plcPaging.FindControl("spacer" + i)) as Label);
	int displayNum = startPage + i - 1;
	//visible if page's number is less than or equal to last page to display
	spacer.Visible = lnk.Visible = displayNum <= lastPageToDisplay;
	//make final spacer invisible
	if (i != 1 && lnk.Visible == false)
	((plcPaging.FindControl("spacer" + (i - 1).ToString())) as Label).Visible = false;
	//set text
	lnk.Text = displayNum.ToString();
	//grey out if page represents current page
	lnk.Enabled = (CurrentPage != displayNum);
	}
	}

view raw gistfile1.cs hosted with ❤ by GitHub

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.

	protected void LoadItemsBasedOnCurrentPage()
	{
	int take = CurrentPage * itemsPerPage;
	int skip = CurrentPage == 1 ? 0 : take - itemsPerPage;
	LoadData(take, skip);
	}
	protected void prev_Click(object sender, EventArgs e)
	{
	LinkButton lnk = sender as LinkButton;
	CurrentPage--;
	LoadItemsBasedOnCurrentPage();
	}
	protected void lnk_Click(object sender, EventArgs e)
	{
	LinkButton lnk = sender as LinkButton;
	CurrentPage = int.Parse(lnk.Text);
	LoadItemsBasedOnCurrentPage();
	}
	protected void next_Click(object sender, EventArgs e)
	{
	LinkButton lnk = sender as LinkButton;
	CurrentPage++;
	LoadItemsBasedOnCurrentPage();
	}

view raw gistfile1.cs hosted with ❤ by GitHub

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.

	private static string StripParagraphTag(string input)
	{
	int startPIndex = input.IndexOf("<p>");
	//if found a <p> tag
	if (startPIndex != -1)
	{
	//if <p> tag lies in between content, do not strip it.
	string preP = input.Substring(0, startPIndex);
	for (int i = 0; i < preP.Length; ++i)
	{
	if (preP[i] != '\r' || preP[i] != '\n')
	return input;
	}
	//if ending of p tag at end, then strip contents before
	if (input.LastIndexOf("</p>") == input.Length - 4)
	return input.Substring(startPIndex + 3, input.Length - (startPIndex + 3) - 4);
	}
	return input;
	}

view raw gistfile1.cs hosted with ❤ by GitHub