Passing XML to Sql Server

One may want to pass an array of data to SQL Server. The most recent reason for me was so I could look for a specific record based on multiple criteria.

There are several ways to pass XML to SQL Server. One of the simplest ways to pass the data is as a string. However other options are available such as creating an SqlXML object from a list using the following C# function from

* Get an SqlXml representation of the list
* @param IEnumerable list: The list to convert
* @return SqlXml
public static SqlXml GetSqlXml(IEnumerable list)
    //We don't use 'using' or dispose or close the stream,
    //since it lives in the return variable
    MemoryStream stream = new MemoryStream();
    using (XmlWriter writer = XmlWriter.Create(stream))
        foreach (object obj in list)
            writer.WriteElementString("item", obj.ToString());
        stream.Position = 0;
        return new SqlXml(stream);


Another way to create the XML representation, in, is to use the following:

Dim xmlDoc As New System.Xml.XmlDocument
Dim table As System.Xml.XmlElement = xmlDoc.CreateElement("table")
For Each child As String In New String() {"s1", "s2", "s3"}
    Dim id As System.Xml.XmlElement = xmlDoc.CreateElement("id")
    id.innerText = child

Then pass the xmlDoc as a string. Once you have the parameter set up, you can define a stored procedure to accept an XML parameter such as the following:

CREATE PROCEDURE [dbo].[StoredProcedure1] ( @Criteria XML = NULL )

The last important piece is to parse the XML in SQL Server. If you have the following XML:


You can access the “id” nodes by using the following:

SELECT'.', 'varchar(50)') AS ID
FROM @Criteria.nodes('/table/id') AS r(id)

Easy Pop-Ups with JQuery

JQuery is a powerful Javascript framework and I recently wanted to use it to make a popup submenu for on non-touch devices. I also wanted to position the popup dynamically on pageload since I wasn’t sure where the link that would trigger the popup would be positioned since the site is responsive.
You start with a DIV that contains the content to be displayed that you can trigger using the OnMouseOver event on a link or other HTML element. You can hide the popup using the OnMouseOut event event. In this example, the popup is triggered when the user positions their mouse over the Showcase link as shown here:
Showcase Popup
The trickiest part comes when you want to trigger the popup but you want to allow the user to move their mouse over the popup without causing the popup to disappear since their mouse is leaving the link.
To achieve this, you need to set up a timeout mechanism that will allow you hide the popup only when the user is NOT hovering the link or the popup. After that it is a matter of positioning the popup so it is under the link (and the user can move between the link and the popup. Here is a bit of code that I used for Maus Jewelers.
<!-- The Nav menu, I am using Bootstrap so this will be hidden on extra small devices -->
<div class="header-bottom header-nav hidden-xs">
    <ul class="nav navbar-nav">
          <li class="<?php checkActive("Location", $section); ?>"><a href="/location">Location</a></li>
          <li class="<?php checkActive("Services", $section); ?>"><a href="/services">Services</a></li>
          <li class="<?php checkActive("About", $section); ?>"><a href="/about">About Us</a></li>
          <li class="<?php checkActive("Showcase", $section); ?>" onmouseover="openPopup();" onmouseout="closePopup(true);" id="link"><a href="/showcase">Our Showcase</a></li>
         <li class="<?php checkActive("Home", $section); ?>"><a href="/">Home</a></li>

<!-- The popup -->
<div id="popup" style="display:none; z-index:10; position:absolute; top: 135px;" onmouseover="openPopup();" onmouseout="closePopup(true);" class="popup hidden-xs">
     <!-- I manually position the div so it will be far enough down the on the page. In this case, the nav link will not change it's top position. I am also using Bootstrap so this will be hidden on extra small devices. -->
     Hello Popup.

<script type="text/javascript">
var enablePopup = true; // if set to false, we won't open a popup
var pixelOffsetFromLink = 75; // The number of pixels to move to the left, from the link, this helps to align the popup

// Global variables
var popupOpen = false; // Indicates if the popup is currently open
var popupTimer = null; // Is a timer that triggers the closePopup function

 * Calculate where the popup should be positioned based on where the link is currently at
 * @return null
function calculatePopupPosition() {
     var $popup = $("#popup");
     var $link = $("#link");
     $popup.css("left", ($link.offset().left - pixelOffsetFromLink) + "px");
     return null;

 * Close the popup if the popup is open and we don't want to set a timer
 * @param boolean setTimer: Indicates if we should set the timer and then close the popup after the timer's function is triggered
 * @return null
function closePopup(setTimer) {
     // Return if the popup isn't open
     if (!popupOpen) {
          return null;
     // If we have a popup timer already, clear it so we can make a new one
     if (popupTimer != null) {
          popupTimer = null;
     // If we have set the timer, we will return early but set a timer to trigger this function and actually close the popup
     if (setTimer) {
          popupTimer = window.setTimeout("closePopup(false);", 250);
          return null;
     // Check if the mouse is over the popup, if it is, keep the popup open

     // If we aren't hovering anymore, close the popup and remove the hover class from the link
     if ($("#link:hover").length == 0 && $("#popup:hover").length == 0) {
          popupOpen = false;

     return null;

 * Open the popup
 * @return null
function openPopup() {
     // If the popup is open or if we don't want to show a popup at all, return
     if (popupOpen || !enablePopup) {
          return null;
     popupOpen = true;
     // Give the link a hover class

     return null;

When Notepad++ Crashes

If you are using Notepad++ on Windows, you may have encountered the run into a problem with the Application crashing with the following message:

Notepad++ Crash

This seems to be a death sentence for the file you are working on and after you restart Notepad++, the file will be blank.

You can try a few different methods to retrieve the file you were working on:
1) Check if Notepad++ cached your file by going to the AppData folder under C:\Users\\Roaming\Notepad++\backup.
2) Check the Previous Versions on the file. Right-click on the file and go to properties. Under Previous Versions, Windows may have stored a previous version that you can revert to.
3) You can also try a using this solution:

If all else fails, try to use a backup solution that automatically syncs changes and allows you to revert to previous versions such as Dropbox.

Wrapping HTML Elements with a Parent DIV using jQuery

One of our projects required us to add a target element to a parent DIV dynamically. In this example, we are targeting the “child” CSS class and adding any elements that have that CSS class to a DIV with the “parent” CSS class.

// Add a selector to a parent DIV with the wrapping class
var targetSelector = ".child";
$(targetSelector).each(function() {
// Check the parent doesn't already have the wrapperClass on it
var wrapperClass = "parent";
if ($(this).parent().hasClass(wrapperClass)) {

// Wrap the target element with a parent div with class = wrapperClass
var parent = document.createElement(“div”);
var $currNode = $(this).clone();

// Replace the current node with the parent and append the current node content

YouTube Video Event Tracking

Easily Track YouTube Videos with Google Analytics has Javascript code available to perform tracking on YouTube video events in Google Analytics. I encountered a problem where the code didn’t work with YouTube videos already embedded with iframes. The YouTube JavaScript Player API Reference says that it should be able to handle YouTube videos embedded in iframes tags. I found a work-around by replacing iframes tags with div tags and creating a player for each div created. The following code requires jQuery and was tested with version 1.7.2.

function trackYouTube() {
var i = 1;
$('iframe').each(function() {
var vidSrc = "";
var newDivID = "";
var vidID = "";

if ($(this).attr('src')===undefined) {
} else {
vidSrc = $(this).attr('src');

var regex = /h?t?t?p?s?\:?\/\/www\.youtube\.com\/embed\/([\w-]{11})(?:\?.*)?/;
var matches = vidSrc.match(regex);
if (matches && matches.length > 1) {
vidID = matches[1]; // Get the id of the youtube video

// Create a new element for each video
newDivID = vidID + '-num' + i;
var div = $('<div/>', { id: newDivID });
divArray[i] = newDivID;

// Replace the iFrame with the div we created
videoArray[i] = vidID;

function updateYouTubePlayers() {
for (var key in videoArray) {
var newDivID = divArray[key];
var vidID = videoArray[key];
playerArray[key] = new YT.Player(document.getElementById(newDivID), { videoId: vidID, events: { 'onStateChange': onPlayerStateChange }});

var tag = document.createElement('script');
tag.src = "";
var firstScriptTag = document.getElementsByTagName('script')[0];
firstScriptTag.parentNode.insertBefore(tag, firstScriptTag);

var divArray = {};
var videoArray = {};
var playerArray = {};

(function($) {
$(document).ready(function() {

function onYouTubeIframeAPIReady() {

var _pauseFlag = false;
function onPlayerStateChange(event) {
var videoID = "";
try {
videoID =;
} catch (e) { }

if ( ==YT.PlayerState.PLAYING){
_gaq.push(['_trackEvent', 'Videos', 'Play', videoID ]);
_pauseFlag = false;
if ( ==YT.PlayerState.ENDED){
_gaq.push(['_trackEvent', 'Videos', 'Watched to End', videoID ]);
if ( ==YT.PlayerState.PAUSED && _pauseFlag == false){
_gaq.push(['_trackEvent', 'Videos', 'Pause', videoID ]);
_pauseFlag = true;
if ( ==YT.PlayerState.BUFFERING){
_gaq.push(['_trackEvent', 'Videos', 'Buffering', videoID ]);
if ( ==YT.PlayerState.CUED){
_gaq.push(['_trackEvent', 'Videos', 'Cueing', videoID ]);

Export SQL Server Stored Procedures using Python

The following short Python 2.7 script creates an export of all the stored procedures and functions within a SQL Server database. This will export each procedure as a CREATE PROCEDURE or CREATE FUNCTION statement. The code uses PyODBC to connect to the installation of SQL Server.

import pyodbc, time

# Database configuration
dbServer = "" # The database server to connect to
db = "" # The database to connect to
uid = "" # The user
pwd = "" # The password
connString = """
driver={SQL Server};
server=""" + dbServer + """;
database=""" + db + """;
user id=""" + uid + """;
password=""" + pwd + """;
dateStr = time.strftime("%Y-%m-%d")
fileName = dbServer + "-" + db + "-" + dateStr + "-procedures.sql"
# i.e. TESTSERVER-TESTDB-2014-08-19-procedures.sql

# Connect to the database
conn = pyodbc.connect(connString)
cursor = conn.cursor()

# Export a stored procedure using its name and connected cursor
# @param string name: The name of the procedure to export
# @return string
def exportProcedure(name):
sql = "EXEC sp_helptext N'" + name + "';"
rows1 = cursor.fetchall()
content = ""
for r in rows1:
content += r.Text.strip() + "\n"
return content

sysobjects contains different types of objects
-- P is a procedure
-- If the type contains F then it is a function
-- V is a view
-- U is a table
category = 0 indicates it is user-created

# Get a list of system objects sorted by views, tables, procedures, and functions created by the user
content = ""
sql = "SELECT * FROM sysobjects WHERE (type LIKE '%F%' OR type = 'V' OR type = 'U' OR type='P') AND category = 0 ORDER BY type DESC, name"
rows = cursor.fetchall()

# Export each of the stored procedures and functions
for r in rows:
if r.type.strip() == "P" or "F" in r.type.strip():
content += exportProcedure( + "\n\n"

# Write out the content
f = open(fileName, "w")


Overwriting the Browser Confirm pop up using jQuery

The browser’s default confirm function is a synchronous blocking function that usually pops up a dialog box causing a web page to stop executing code and wait for user input. In most browsers, the user is allowed to select “OK” or “Cancel” and developers usually have very little control over the confirm box other than the message displayed.

This can lead to some problems when the confirm box blends into the website such as in the case with Chrome 35.0 or when you simply want more control over the button options.

You can overwrite the default confirm function such as in the following snippet of code which combines jQuery UI and Bootstrap to create a nice looking confirm box that is easier to read.

It is important to note that you will still run into a problem if you attempt to write code that blocks until the user presses a button. For that reason, the code will fall back to the old confirm function if you don’t give it a function to call when you press the OK or the Cancel button.

<link rel="stylesheet" href="" />
<script src=""></script>
<script src=""></script>
<link rel="stylesheet" href="" />
<script src=""></script>
<div id="wrap">
<div class="container">
<div class="row">
<div class="col-sm-12">

<h1>Overwrite the Browser's Default Confirm Box</h1>

<div id="status"></div>

	<a href="#" class="btn btn-default" onclick="confirm('Are you sure that you want to continue?', confirm_function1, cancel_function1); return false;">Click to confirm</a>

	<a href="#" class="btn btn-default" onclick="if (confirm('Are you sure that you want to continue?')) { confirm_function1(); } else { cancel_function1() }; return false;">Click to confirm using default confirm box</a>

<script type="text/javascript">
function confirm_function1() {
	$('#status').html('Pressed OK');

function cancel_function1() {
	$('#status').html('Pressed Cancel');

 * Replace the jQuery UI buttons styles in the confirm box with Bootstrap styles
 * @param string id: The jQuery object to update
 * @param string buttonClass: The button class to the give the button
 * @return void
function updateConfirmBoxButton(id, buttonClass) {
	var disableJqueryUICss = function() { 

		.prop("class", buttonClass)

// Adjust how the confirm box is rendered
window.default_confirm = window.confirm; // Save the old confirm box function in case we need to fallback to it

 * Create a new confirm box function
 * @param string message: The message to display
 * @param function confirm_function: The function to execute when the user presses the 'confirm' button
 * @param function cancel_function: The function to execute when the user presses the 'cancel' button
 * @return object: If confirm_function or cancel_function are null then return the value returned by the old confirm box function Else return the new confirm box object
window.confirm = function(message, confirm_function, cancel_function){
	// Fall back to the old default confirm box if we don't have both a confirm and cancel function
	if (confirm_function == null || cancel_function == null) {
		return window.default_confirm(message);

	// Create the new confirm box
	var confirmBox = document.createElement("div");
			dialogClass: "confirmBox",
			buttons: {
				"OK": {
					id: "OK",
					text: "OK",
					click: function() {
				"Cancel": {
					id: "Cancel",
					text: "Cancel",
					click: function() {
			close: function() {
			draggable: false,
			modal: true,
			resizable: false,
			width: 'auto'
	// Adjust the dialog box we just created
	// Update the background so it is higher contrast
	$(".confirmBox.ui-widget-content").css("background", "#fff");
	// Update the background so it is higher contrast and the buttons are centered
	$(".confirmBox .ui-dialog-buttonpane").css("background", "#fff").css("border-width", "0").css("padding", "0");
	$(".confirmBox .ui-dialog-buttonset").css("text-align", "center").css("float", "none");
	// Hide the Titlebar
	$(".confirmBox .ui-dialog-titlebar").hide();
	// Update the confirm box buttons
	updateConfirmBoxButton(".confirmBox .ui-dialog-buttonset #OK", "btn btn-success");
	updateConfirmBoxButton(".confirmBox .ui-dialog-buttonset #Cancel", "btn btn-danger");

	return confirmBox;


Simple Cache Class in C#

A simple class that caches Hashtables within a Session object in C#.

using System;
using System.Web;
using System.Web.SessionState;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;

public class Cache
	 * Delete the cache item from the cache by key
	 * @param string CacheKey: The data to delete
	 * @return bool: Indicates the cache key was deleted
	public static bool Delete(string CacheKey)
		HttpContext.Current.Session[CacheKey] = null;
		return true;

	 * Get the cache item by key
	 * @param string CacheKey: The data to retrieve
	 * @return Hashtable: The data retrieved
	public static Hashtable Get(string CacheKey)
		if (HttpContext.Current.Session[CacheKey] != null)
			Cache.Item item = (Cache.Item)HttpContext.Current.Session[CacheKey];
			if (item.Expiration >= DateTime.Now)
				return item.Data;

		return null;

	 * Set a cache item
	 * @param string CacheKey: The key to set for the item
	 * @param Hashtable Data: The data to set
	 * @param int NumSecondsAlive: The number of seconds to set the cache for (defaults to 5 seconds)
	 * @return Hashtable: The data retrieved
	public static bool Set(string CacheKey, Hashtable Data = null, int NumSecondsAlive = 5)
		HttpContext.Current.Session[CacheKey] = new Cache.Item(Data, NumSecondsAlive);

		return true;

	 * Start Item Class
	private class Item
		public Hashtable Data = null;
		public DateTime Expiration = new DateTime();

		public Item(Hashtable Data, int NumSecondsAlive)
			this.Data = Data;
			this.Expiration = DateTime.Now.AddSeconds(NumSecondsAlive);
	} // End Item Class
} // End Cache Class

You may want to consider performance and efficiency when implementing this on an actual site.

Caching the results of a PDO Query in Memcache

This is a quick example of code that will prepare a PDO statement and cache the results in Memcache.

It is easiest to use the two following sets of instructions to set up a LAMP environment that will handle the PHP, PDO, and Memcache packages that the code uses:

  1. How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu
  2. How To Install and Use Memcache on Ubuntu 12.04


function db_connect() {
	global $memcache, $db;

	// Connect to the Memcache server
	$memcache = new Memcache();
	$memcache->pconnect('localhost', 11211);

	// Connect to the Database using PDO
	$host = "localhost";
	$dbname = "test";
	$user = "test";
	$pass = "test";

	try {
		$db = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
	} catch (PDOException $e) {

function db_fetch_all($sql, $params = array(), $use_cache = true) {
	global $memcache, $db, $cache_expiration;

	$result = false;
	if ($use_cache) {
		// Generate a key for the cache
		$cache_key = md5($sql . serialize($params));
		$result = $memcache->get($cache_key);

	if (!$result) {
		// Cache Miss: Prepare the sql and recache it
		$sth = $db->prepare($sql);
		// This handles ? within a sql query
		// i.e. "SELECT id FROM example WHERE name = ?";
		$i = 0;
		foreach ($params as $param) {
		    $sth->bindParam(++$i, $param);

		// This handles :params within queries
		// "SELECT id FROM example WHERE name = :name";
		foreach ($params as $key => $value) {
		    // keys must be in the form :key within the query
		    $sth->bindParam($key, $value);

		$result = $sth->fetch(); // Fetch the entire result into an array
		// Cache expires in 10 seconds
                $cache_key = md5($sql . serialize($params));
		$cache_expiration = 10;
		$memcache->set($cache_key, serialize($result), MEMCACHE_COMPRESSED, $cache_expiration);

		echo "used mysql";
	} else {
		// Cache Hit
		echo "used memcache";

	return $result;

echo "Time: " . time() . "<br />";
if (class_exists("Memcache")) {

	// The query only works with ? as variables in the prepared statement
	$query = "SELECT id FROM example WHERE name = ?";
	$result = db_fetch_all($query, array("new_data"));
} else {
	echo "Memcache not installed :(";

Fix MySQL corrupted configuration on Ubuntu

Today, I ran into a problem involving a corrupted MySQL server 5.5 configuration in Ubuntu 13.04. I am not sure how it got to be corrupted in the first place but whenever I ran “mysql” to open a connection to the server, I would get a very generic MySQL error code:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

As far as I can tell, the only way to fix that particular problem is to remove and purge configuration so I attempted to remove the server using “sudo apt-get –purge remove mysql-server-5.5” and clean up the other packages that depend on mysql-server-5.5. I also tried to reinstall the packages but I still ran into the same problem. After reinstalling the package, I noticed a problem where a root user had already been created on the server and my password wasn’t working with the account nor did I know what the original password was. I am not sure if this particular issue is related or not. However, after Googling the problem and trying many different things and combining solutions I finally found something that seems to have worked:

sudo apt-get --purge remove mysql-server mysql-common mysql-client
sudo mv /var/lib/mysql /var/lib/mysql.backup
sudo apt-get install mysql-server mysql-common mysql-client

I believe that the problem involves a corrupted table in the directory where MySQL stores the database files so you have to do additional file cleanup when trying to remove MySQL.

I wish you luck if you ever find yourself in this situation and hopefully you can easily remove the server. If not, the commands will create a backup of the files which you may be able to restore after you have reinstalled MySQL.