Out of Hours - Confessions of a Professional Developer

Manage multiple updates to Data using C#, TSQL and OPENXML in SQL Server

2012-01-09T11:25:00.002Z

I'd like to share this little technique I've been using for quite a while, especially to receive feedback and validation on whether or not this is a good idea!

I've used this technique with great success in the past in production systems which require a a "grid" of check-boxes and a single "update" button. This kind of thing usually occurs when you have any X can be related to any Y in a many to many kind of relationship. It comes in especially handy when mapping Users to Roles or that kind of thing. Though it can easily be adapted to manage multiple updates of anything in ONE database hit.

Traditionally this kind of code ends up hitting the database either once per cell in the grid or once per row in the grid because SQL Server doesn't allow arrays of things to be sent to a stored procedure. Database hits are expensive and generally less is better.

My technique leverage's SQL Servers ability to access XML data using the OPENXML keyword. When the grid of check-boxes is submitted we gather together the form collection elements (those check-boxes that have been ticked) and create an XML snippet of the results. This is then passed to SQL Server as a single input representing a set of information. OPENXML is then used inside the stored procedure to convert the XML into a temporary table which is then interrogated and used to update the relationship information that the check box represents.

Below is a link to a visual studio 2010 project that you can download , it contains all the source code for the project please feel free to download and try it for yourself - if you find it useful or have an idea of how it could be improved please leave a comment at the bottom of this page.

I have used MVC 3 and SQL Server as a simple container for the project but the technique is equally valid for any type of project Classic ASP, ASP.NET Web-forms included, so long as the RDBMS supports XML the technique will be valid.

I will refrain from a long explanation here as the code is simply one model, controller and view and the database contains just three tables and two stored procedures. If you'd like some more detail please leave a comment at the bottom of the page and Ill update as soon as possible. If I get a lot of feedback then I will flesh out the method some more in a series of posts.

I do hope you find this useful and would love to hear from you!

Project files can be downloaded here and unpacked using 7zip

disqus.com for @blogger @blogspot comments - really rather good!

2012-01-05T14:00:00.002Z

Having not made a new years resolution at the time I have decided that it should be "use my blog more often". I'm doing quite well this year so far but to be honest its quite tricky fitting it in "out of hours" what with the twins, learning guitar and Skyrim being released but we can but try!

This post centres around the ease with which I managed to sign up for and integrate Disqus comments into this very blog. It turns out that to replace the (fairly basic) standard Blogger comments with Disqus is REALLY straightforward. Simply a case of signing up to Disqus pointing it at your blog and clicking the appropriate options.

Disqus accounts are free (though there are paid for versions) and it adds a social edge and more integrated experience if you're a Blogger or a commenter. I original was looking to integrate LiveFyre but it didn't seem to offer the kind of seamless integration and ease for which I was looking (I may be wrong but Disqus just made it so easy!).

Anyway if you're looking for "comments on social steroids) I would definitely recommend it if you're serious about building communities around your content!

http://disqus.com

http://www.livefyre.com/
One last word on LiveFyre if you're looking for something a bit different it works just like other comments managers however it also include live chat as a first-class feature which, when you see it, really is quite impressive!

Kodak ESP 5250 All-in-One Colour Ink-jet - Printer / copier / scanner

2012-01-04T09:37:00.000Z

Having bought Kodak Camera's for both myself and my mother in law I decided to give the following printer a try when it came time to replace an older Epson model that got damaged in a house move a while ago. I have to say it is astounding for the price. B&W printing is both fast and sharp whilst the colour prints are beautiful even on normal paper! Let alone its excellent Wi-Fi facilities, combine that with its excellent cloud printing software (sadly this model does not support it natively but the software works very well indeed) its an all round winner!

Kodak also do iPhone, iPad and Andriod apps for printing directly from your smart devices for free which also "just work" - usually printers (at least for me) are a nightmare but setting all this up was a breeze!

Kodak ESP 5250 All-in-One Colour Ink-jet - Printer / copier / scanner:

'via Blog this'

UK Newspaper Front Pages @ Front Pages Today

2011-12-19T12:11:00.000Z

Website of the day on Pocket-lint today - what a fantastic idea, keep up to date with the big stories in the press day by day with direct links to all the papers. Also spans multiple countries too!
UK Newspaper Front Pages @ Front Pages Today:

'via Blog this'

One-click Blogging with BlogThis! Chrome Extension | Blogger Buzz

2011-12-19T11:05:00.000Z

An extremely useful extension that should improve the frequency with which I update my blog. I usually post links through my twitter account ( @bUKaneer ) but will endeavour to post to my blog using this tool too!
One-click Blogging with BlogThis! Chrome Extension | Blogger Buzz:

'via Blog this'

NuGet, SQL CE 4 and CDN - oh my!

2011-06-29T09:33:00.002Z

NuGet: http://nuget.org/

This is a system for “installing” components into your web application quickly and easily. So lets say your doing a data driven website using SQL CE 4, you’re going to be using jQuery and CSS control adapters. Simply create your new website as per normal fire up NuGet, do a search for jQuery and hit install, do a search for CSSFriendly double click and install. At this point it will have put copies of jQuery and CSS Friendly control adapters straight into your project! Brilliant! Theres LOADS of stuff available ousing NuGet these are only two VERY small example – check it out!

SQL CE 4: http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx

This is an “in site” file based database and needs no server components to run just some webspace supporting .NET 4 – it replaces using MS Access SQLite or PostgresSQL. In order to use it you simply need Visual Studio 2010 sp1 and the SQL CE 4 tooling support installed which can be downloaded from here http://www.microsoft.com/download/en/details.aspx?id=23691 and here http://go.microsoft.com/fwlink/?LinkId=212219 . Then when you right click App_Data select New Item a new option will appear call SQL Server CE 4 – double click it and it will create for you a new database which you can use as per you would access.

SQL CE 4 Membership Provider: http://www.hanselman.com/blog/IntroducingSystemWebProvidersASPNETUniversalProvidersForSessionMembershipRolesAndUserProfileOnSQLCompactAndSQLAzure.aspx

Righty this little gem (provided via NuGet) is a way to use the Membership provider system built into .NET with SQL CE 4 – this is actually really easy to implement in about three step and takes about 5 minutes (I know because I did it yesterday). Once the steps are completed you will be able to use all the Membership functions directly from your new SQL CE 4 database saving you the time and effort of implementing this on small websites each time.

Google and Microsoft CDN

If you’re wanting to include framework such as jQuery jQuery UI and jQuery Validate then consider using a CDN (Content Delivery Network) version. This is basically an include to a file or files stored in the cloud. The advantages are tht you do not have to keep local copies of the files in your project and also that when a request is made when the page loads the file will be delivered from the nearest server to your user which should in theory speed up download times. The google CDN and Microsoft CDNS can be used in tandem so you could include jQuery and jQuery UI files from Google and Validate from Microsoft (I've done this and it works fine) – it’s a good way to keep your file footprint down on small sites:

http://code.google.com/apis/libraries/devguide.html#jquery (Shows links to .js files) for the CSS to be included from CDN also please read the following http://blog.jqueryui.com/2009/03/jquery-ui-17/ (Both CSS and Javascript files are hosted on the Google CDN but only the js files are listed – bit silly however the previous link shows how to include the CSS files as well!)

Microsofts version can be foud here: http://www.asp.net/ajaxlibrary/cdn.ashx (Webpage currently not working!)

Tips & Tricks for ASP.NET, IIS, and Visual Web Developer : Tip#102: Did you know… How to specify tag specific formatting

2010-03-30T08:28:00.000Z

Tips & Tricks for ASP.NET, IIS, and Visual Web Developer : Tip#102: Did you know… How to specify tag specific formatting: "Tip#102: Did you know… How to specify tag specific formatting"

Microsoft/Amazon Deal: Nothing to See Here | The Linux Foundation

2010-02-23T12:03:00.000Z

Microsoft/Amazon Deal: Nothing to See Here | The Linux Foundation

Down and dirty with Trace Listerners for SOAP Messages

2010-02-15T10:36:00.004Z

Recently I was trying to implement a web service call for a client who had another firm implementing a very secure web service using client certificates (Ill blog about how to get that working sometime soon). However once Id got the security sorted I was getting a Method not allowed 403 error in order to diagnose the problem I needed ot get at the raw data travelling across the wire. as it happens dot net has a brilliant diagnostics suite which is really simple to implement. Simply add the following into your web.config file underneath the system.web node and presto youll get a trace.log file will all that lovely low level info in!

Original Article here on StackOverflow; http://stackoverflow.com/questions/300674/getting-raw-soap-data-from-a-web-reference-client-running-in-aspnet

<system.diagnostics>
<trace autoflush="true">
<sources>
<source name="System.Net" maxdatasize="1024">
<listeners>
<add name="TraceFile">
</add>

<source name="System.Net.Sockets" maxdatasize="1024">
<listeners>
<add name="TraceFile">
</add>

</listeners>
<sharedlisteners>
<add name="TraceFile" type="System.Diagnostics.TextWriterTraceListener" initializedata="trace.log">
</add>
<switches>
<add name="System.Net" value="Verbose">
<add name="System.Net.Sockets" value="Verbose">
</add>
</add>
</switches></sharedlisteners></listeners></sources></trace></system.diagnostics>

A simple way to add OnUnload to the body of a page

2009-11-06T09:39:00.000Z

Page.ClientScript.RegisterStartupScript(typeof(string),"AutoCloseWindow","window.onunload = function () { window.opener.location.href = window.opener.location.href; window.close(); };", true);

Old Skool - Classic ASP ServerVariables List

2009-09-14T11:31:00.005Z

This is an oldie but a goodie – I don’t know how many times in my career I’ve rewritten these few lines of code but I’ve preserved it here to save me writing it yet again! A simple method for outputting all the server variables, query string parameters and form variables in Classic ASP!

response.Write("<hr/>")

response.Write("SERVER VARIABLES COLLECTION ")

Dim variableName

for each variableName in Request.ServerVariables

response.write(variableName & ": " & Request.ServerVariables(variableName) & " ")

response.Write("<hr/>")

response.Write("QUERY STRING COLLECTION ")

for each variableName in Request.QueryString

response.write(variableName & ": " & Request.QueryString(variableName) & " ")

response.Write("<hr/>")

response.Write("FORM COLLECTION ")

for each variableName in Request.Form

response.write(variableName & ": " & Request.Form(variableName) & " ")

response.Write("<hr/>")

response.Write("SESSION COLLECTION ")

for each variableName in Session.Contents

response.write(variableName & ": " & Session(variableName) & " ")

response.Write("<hr/>")

Useful code to properly record and redirect an unhandled exception from Global.asax Application_Error Handler

2009-09-01T15:34:00.001Z

void Application_Error(object sender, EventArgs e)

{

// Code that runs when an unhandled error occurs

string httpCode = ((HttpException) Server.GetLastError()).GetHttpCode().ToString();

if (HttpContext.Current.Session != null)

{

HttpContext.Current.Session["LastError"] = Server.GetLastError();

Server.ClearError();

}

switch (httpCode)

{

case "404":

Response.Redirect("/Error/Error404.aspx");

break;

default:

Response.Redirect("/Error/Error.aspx");

break;

}

GridView (non-object/sql DataSource) Paging and Sorting

2009-08-12T08:30:00.001Z

This is just a quick reminder post about how to do things and in what order to get Paging and Sorting working together on a GridView Control when you’re datasource is NOT a SqlDataSource or ObjectDataSource reference by DataSourceId in the ASPX file but rather a (collection of) POCO objects or other datasource!

.ASPX file

<asp:GridView CssClass="tableStyle1" HeaderStyle-CssClass="bgA" RowStyle-CssClass="bgB"

DataKeyNames="Id" AlternatingRowStyle-CssClass="bgB" AllowPaging="true"

AllowSorting="true" PageSize="50" OnPageIndexChanging="ResultsGrid_PageIndexChanging"

AutoGenerateColumns="false" runat="server" ID="ResultsGrid" OnRowDataBound="ResultsGrid_RowDataBound"

OnSorting="ResultsGrid_Sorting" PagerStyle-ForeColor="White">

<asp:BoundField DataFi

.CS file

protected void Page_Load(object sender, EventArgs e)

{

if (!this.IsPostBack)

{

ViewState["SortExpression"] = "Title";

ViewState["SortDirection"] = "ASC";

}

public void ResultsGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

ResultsGrid.PageIndex = e.NewPageIndex;

BindResultGrid();

}

private void BindResultGrid()

{

SqlDataAdapter da = new SqlDataAdapter("ReportingSelect", new SqlConnection(connectionString));

GetSqlParametersForFilter(PrepareFilterTerms(), da.SelectCommand);

DataSet results = new DataSet();

da.Fill(results);

ResultsGrid.DataSource = ApplySorting(results.Tables[0].DefaultView);

ResultsGrid.DataBind();

}

private DataView ApplySorting(DataView dataViewForSorting)

{

dataViewForSorting.Sort = ViewState["SortExpression"].ToString() + " " + ViewState["SortDirection"].ToString();

return dataViewForSorting;

}

public void ResultsGrid_Sorting(object sender, GridViewSortEventArgs e)

{

if (e.SortExpression.ToString() == ViewState["SortExpression"].ToString())

{

if (ViewState["SortDirection"].ToString().StartsWith("ASC"))

{

ViewState["SortDirection"] = "DESC";

}

else

{

ViewState["SortDirection"] = "ASC";

}

else

{

ViewState["SortExpression"] = e.SortExpression.ToString();

if (e.SortDirection == SortDirection.Ascending)

{

ViewState["SortDirection"] = "ASC";

}

else

{

ViewState["SortDirection"] = "DESC";

}

BindResultGrid();

}

ASCII Encoded/Binary String Automated SQL Injection Attack

2009-08-03T10:48:00.001Z

Useful code for preventing SQL injections in .NET Querystrings, lifted from the following page:
http://www.bloombit.com/Articles/2008/05/ASCII-Encoded-Binary-String-Automated-SQL-Injection.aspx

///

/// global.asax///

public class Global : System.Web.HttpApplication{
...
private static string[] SQLKeywords = new string[]
{
"EXEC", "SELECT", "INSERT", "UPDATE", "DELETE",
"CAST", "DECLARE", "NVARCHAR", "VARCHAR"
};
...
protected void Application_BeginRequest(object sender, EventArgs e)
{
HttpContext context = HttpContext.Current;
if (context != null)
{
string queryString =
context.Request.ServerVariables["QUERY_STRING"];
if (string.IsNullOrEmpty(queryString) == false)
{
if (queryString.Length > 500)
throw new SQLInjectionException(string.Format("Unexpected 'QUERY_STRING' length ({0}).", queryString));
queryString = Server.UrlDecode(queryString);
queryString =
queryString.Replace(" ", string.Empty).ToUpper();
foreach (string keyword in SQLKeywords)
{
if (queryString.IndexOf(keyword) != (-1))
throw new SQLInjectionException(string.Format("Unexpected T-SQL keyword ('{0}') has been detected ({1})", keyword, queryString));
}
}
}
}
...}

My 30th birthday is looming....

2009-08-03T08:33:00.004Z

...so I've written a list of things I'd like!

Stuff I’d Like for my Birthday:

Framework Design Guidelines: Conventions, Idioms, and Patterns for re-useable .NET Libraries 2nd Edition, Book/DVD Package
http://www.compman.co.uk/scripts/browse.asp?ref=895703
£22.34

Hitman DVD – Unrated
http://www.movietyme.com/catalog/product_info.php?products_id=39063&osCsid=dd991dc5d4a98e3f30952bf39550ad0c
£15.99

HMV Vouchers!

Iomega 1TB desktop hard drive - Catalogue number: 204-6510
http://direct.tesco.com/q/R.204-6510.aspx
£69.94

Philips SPC1330NC Webcam pro
http://shop.philips.co.uk/servlet/ControllerServlet?Action=DisplayProductDetailsPage&Locale=en_GB&SiteID=rpeeub2c&productID=124455100&s_kwcid=TC9368philips%20spc1330ncS3385570448
£69.99

Subscription to WebUser Magazine
http://www.magazinesubscriptionsipc.com/ipc/subs/subsorder.asp?title=XWU&promcode=i272&ctryID=NONE
About £25 I think

CLR via C#, 3rd Edition by Jeffrey Richter (I’ve already got the 2nd Edition)
Not out yet!
£TBC

Hanns-G 22in HG221AP Wide LCD TFT Black/Silver MonitorMonitor
http://www.svp.co.uk/displays/monitor-22-hanns-g-22-hannsg001_monitor.html
£109.62

Search Engine Optimization Toolkit : The Official Microsoft IIS Site

2009-06-04T10:01:00.000Z

Search Engine Optimization Toolkit : The Official Microsoft IIS Site: "The IIS Search Engine Optimization (SEO) Toolkit helps Web developers, hosting providers, and Web server administrators to improve their Web site’s relevance in search results by recommending how to make the site content more search engine-friendly. The IIS SEO Toolkit includes the Site Analysis module, the Robots Exclusion module, and the Sitemaps and Site Indexes module, which let you perform detailed analysis and offer recommendations and editing tools for managing your Robots and Sitemaps"

Download details: Visual Studio Team System 2010 Team Suite Beta 1

2009-06-04T09:40:00.000Z

Download details: Visual Studio Team System 2010 Team Suite Beta 1: "Visual Studio Team System 2010 Team Suite Beta 1 - ISO" free download from microsoft now available (Im very late in posting this but still....)

July 2006 - Posts - Tony Rogerson's ramblings on SQL Server

2009-06-03T14:44:00.000Z

July 2006 - Posts - Tony Rogerson's ramblings on SQL Server: "Collations in SQL Server, examples and restrictions" Very handy notes on solving Database Collation Problems!

Neighbours' shock as cows invade housing estate - Rugby Today - Back to Home Page

2009-05-28T19:16:00.000Z

Its ol news but only just came across it - wish Id have moved here sooner!
Neighbours' shock as cows invade housing estate - Rugby Today - Back to Home Page: "Neighbours' shock as cows invade housing estate"

How to use SQL Server T-SQL cursors | Microsoft SQL Server Advisor

2009-05-15T13:31:00.000Z

This is a brilliant little example script what absoluted HAVE TO use a cursor - my take generally on this is "If you have to use a cursor youre doing it wrong", however during some work for a client I needed to "duplicate" a hierarchy of items from different nested tables with foreign key references for a questionnaire something along the lines of
Questionnaire table links to Questions table, Questions table links to QuestionLinking table (Where a dependancy between one question and another is held) and so forth. I spent a while looking for a "clean" solution but the only option I found was to iterate through each Questionnaire, Question and Question link at a time as I needed to update the QuestionnaireId, QuestionId and QuestionLinkId from the Source Questionnaire to the Destination Questionnnaire row equivalents... if you have any patterns or approaches tot his type of thing not involving cursors I would love to hear from you!

How to use SQL Server T-SQL cursors Microsoft SQL Server Advisor

Internet Explorer 8: Videos

2009-05-06T21:19:00.000Z

Internet Explorer 8: Videos: "Internet Explorer 8: Videos" these, whilst very"microsoft", "vision of the future", "overly gloosy with an irritatingly up-beat narrator", if you know what I mean; much like the Windows Tour video (which i always make a bee line for when a new O/S is released), is actually very informative, concise and easy to understand. It shows key new features and how they work giving a very good insight into the lovely new "community" features. Well worth 5 minutes of your time!! Note: Especially useful is the Compatibility feature which, a developer, I might just find ver handy if it does indeed invoke the previous versions' of IE' rendering engines!

Test post via email

2009-05-01T09:52:00.001Z

A simple test post via email!

HITMAN THE MOVIE

2007-12-11T11:38:00.000Z

Well, Im not one for blogging about general everyday stuff but after reading some of the reviews of Hitman I felt I had to my two penneth in. Firstly thes think about those other not-so-great films of computer games, Street Fighter, Tomb Raider, Mario Bros, Mortal Kombat (to a
lesser extent) were all, well, not to be unkind but quite poor. So by that benchmark given the genre anything better than quite poor is a big plus. OK, so nmow we have a general benchmark lets consider some other mitigating factors - whilst Hitman has some superb stunts, special
effects and action sequences you should not go to this film with "This is another Borne Identity/James Bond type of film", it not, firstly the main character is more reminiscent of Leon than either of the two previously mentioned, think World Cinema, Darkened Eastern
European/Russian location, think World Cinema with a bigger budget. There are also some nice "in jokes" - at one point Agent 47 crashes thru a window into a hotel room with two kids playing Hitman the game on the telly - quite amusing if you catch the reference. Now the leading lady
isn't your typical "Bond Girl" shes dark, damaged, sassy and gothic AND she appears partially naked now and again - in my book all these things make her much more intriguing and interesting and indeed more sexy than anything the Bond franchise has produced. The main character IS NOT A "GOODIE" as with Borne and Bond, this guy has been trained from birth to be an assassin and it is his sole purpose, no sleeping with anything that moves, no witty repartee just a case full of weaponry and some bad-ass hand to hand combat get this guy thru life. The gadgets whilst fanciful in places have at least the potential to exist and are generally believable and used judiciously. Personally I thought that this film was one of the best Ive seen so far this year and, even if you don't go and see it at the cinema its certainly worth a rental - especially if you have surround sound and a hi-def telly to enjoy all the gun fights and explosions! Its efinitely a lads movie but not so much your "I like guns and stuff blowing up me" type of lad film more a "Im interested in see some alternative characters play out an intercontinental game of cat and mouse with high violence and some reasonable character development". The leading man, though Ive forgotten his name, must have been able to memorise his lines in an afternoon - he remains mostly silent throughout the film, punctuated occaisionally by some direct instruction which generally either ends or saves someones life, this guy however is not devoid of character, you are left with the distinct impression that theres a world of torment and angst behind those eyes and that whilst he never expresses it in words his facial reactions say it all whilst giving away nothing!

All in all a FIVE STAR film - SEE IT NOW!

Javascript Goodies

2007-11-28T13:04:00.000Z

I’ve recently had to deal with some very run of the mill client side validation, namely validation a comma separated list of email addresses – I was so pleased with my cobble together of bits of code I found on the net that I thought Id post it here for the next time! Also a handy bit of script to “trim” a string in javascript and adding the trim method to the String object… NICE!

String.prototype.trim = function() { return this.replace(/^\s+\s+$/g, ''); }

function CheckEmailAddresses()
{
var recipients = document.getElementById('recipients').getAttribute('innerText').split(',');
var emailRegEx = /^(("[\w-\s]+")([\w-]+(?:\.[\w-]+)*)("[\w-\s]+")([\w-]+(?:\.[\w-]+)*))(@((?:[\w-]+\.)*\w[\w-]{0,66})\.([a-z]{2,6}(?:\.[a-z]{2})?)$)(@\[?((25[0-5]\.2[0-4][0-9]\.1[0-9]{2}\.[0-9]{1,2}\.))((25[0-5]2[0-4][0-9]1[0-9]{2}[0-9]{1,2})\.){2}(25[0-5]2[0-4][0-9]1[0-9]{2}[0-9]{1,2})\]?$)/i
for (var email in recipients)
{
if(emailRegEx.test(recipients[email].trim()))
{
alert('good');
} else {
alert('bad');
}
}
}

Thanx go to the following locations:
Trim function: http://www.nicknettleton.com/zine/javascript/trim-a-string-in-javascript
Email Regex: http://xyfer.blogspot.com/2005/01/javascript-regexp-email-validator.html

The Brooba Revolution!

2007-04-30T11:05:00.001Z

I just thought Id drop in a quick note about my new project, Brooba.com , the concept behind this site is quite simple - Why shoudl you have to pay extra fees when buying/selling/exchanging/searching for a house - this site puts users together giving them the tools necessary to end their Housing woes - do check it out and pre-register - more info to follow!

http://www.brooba.com