Monthly Archives: September 2009

Passing Multiple Records to a Stored Procedure in SQL Server

There are so many different ways to work with data that it can make your head spin.  Regardless of the technology, one thing I always try to do when working with databases is minimize the number of calls that are made.  I use ORM frameworks a lot so I have to make sure that my code doesn’t end up calling the database a ton and being overly “chatty”. 

My company is currently working on a large Silverlight 3 application for a customer and have a requirement to allow CSV file uploads through the application.  That’s pretty straightforward to do with Silverlight but in looking through the CSV file there can be 1000s of records and there is a lot of custom functionality around each record.  Initially I was going to handle the business rules and insertion for each row individually.  However, once I saw how many rows were involved I backed off my initial approach since that would mean a single file upload could trigger thousands and thousands of calls to the database which certainly isn’t efficient.  Instead of going that route I decided it would be better to pass all of the rows into a stored procedure and let it handle everything.  That way I can make a single database connection yet handle inserting (and updating and deleting based upon the business rules) many records. 

So how can you pass multiple records into a stored procedure?  Back in “the day” I’d pass in delimited strings and then parse them.  Although that technique probably wasn’t the most efficient, it got the job done.  With SQL Server 2005 and higher we have access to a much more efficient technique due to the availability of XQuery.  The current project I’m working on relies on SQL Server 2005 behind the scenes so that’s what I’ll focus on here.  Note that SQL Server 2008 allows .NET DataTable objects to be passed when using table value parameters as well in a stored procedure.  See for an overview of the table value parameter functionality.

For the import I can serialize the list of records to XML and then pass them into a stored procedure.  It appears that you can pass an XmlDocument object from .NET if the stored procedure takes an XML input parameter type.  However, I never got it to work properly doing that so I pass a string containing serialized XML data and then load it into the XML data type.  The stored procedure can then use XQuery to convert the XML into actual rows.  Here’s some code that handles parsing a flat file, converting the data into a List<JobMaterialImport>, serializing the list into XML and then passing the serialized XML data to a stored procedure named ImportJobMaterials.

public List<ImportJobMaterialsResult> ImportJobMaterials(int jobID, Stream stream, string coNumber, string coDesc, string xmlMapFile) {     //Convert flat file to List<JobMaterialImport> using converter and XML mapping file     //Mapping file passed from FlatFileHandler.ashx in web project     FlatFileToObjectConverter<JobMaterialImport> converter = new FlatFileToObjectConverter<JobMaterialImport>(stream, xmlMapFile);     List<JobMaterialImport> imports = converter.ConvertToList();     imports = imports.Take(imports.Count - 2).ToList(); //last rows are bogus in flat-file      //Generate XML and pass to sproc     using (StringWriter sw = new StringWriter())     {         XmlSerializer xs = new XmlSerializer(typeof(List<JobMaterialImport>));         xs.Serialize(sw, imports);         try         {             //Pass materials into sproc             string xml = sw.ToString().Replace("utf-16", "utf-8");             return this.DataContext.ImportJobMaterials(xml, jobID, coNumber, coDesc).ToList();                     }         catch (Exception exp)         {             Logger.Log("Error in JobManagementRepository.ImportJobMaterials", exp);         }     }     return null; }


The XML that’s generated from the code above looks like the following.  Security Note: We’ll assume that appropriate measures have been taken to clean the data and ensure it doesn’t contain any bad stuff related to injection attacks. That’s especially important if you’ll be running any dynamic SQL in your stored procedure.  I’m not, but I thought I’d be a good citizen and point it out…always sanitize your data with some Clorox before using it. :-)

<ArrayOfJobMaterialImport xmlns:xsi="" xmlns:xsd="">   <JobMaterialImport>     <Area>BUILDING A</Area>     <Phase>LIGHTING</Phase>     <WorkCode>0</WorkCode>     <WorkCodeTitle>Manually Assigned</WorkCodeTitle>     <Description>4x1 1/2in. SQ BOX COMB KO</Description>     <Quantity>2</Quantity>     <TotalHours>10.46</TotalHours>   </JobMaterialImport>   <JobMaterialImport>     <Area>BUILDING A</Area>     <Phase>LIGHTING</Phase>     <WorkCode>0</WorkCode>     <WorkCodeTitle>Manually Assigned</WorkCodeTitle>     <Description>#8x   3/4 P/H SELF-TAP SCREW</Description>     <Quantity>4</Quantity>     <TotalHours>0.28</TotalHours>   </JobMaterialImport>   <JobMaterialImport>     <Area>BUILDING A</Area>     <Phase>LIGHTING</Phase>     <WorkCode>605</WorkCode>     <WorkCodeTitle>Wiring and System Devices</WorkCodeTitle>     <Description>1G TGL SWITCH PLATE - PLASTIC IVY</Description>     <Quantity>2</Quantity>     <TotalHours>0.89</TotalHours>   </JobMaterialImport> </ArrayOfJobMaterialImport>


Here’s what the stored procedure looks like:


CREATE PROCEDURE [ImportJobMaterials]     @JobMaterialsXml AS VARCHAR(MAX),     @JobID AS INT,     @ChangeOrderNumber AS VARCHAR(10) = NULL,     @ChangeOrderDescription AS VARCHAR(100) = NULL AS     BEGIN         DECLARE @XML AS XML                  DECLARE @MaterialsTable TABLE         (             ID INT IDENTITY(1,1),             Area VARCHAR(250),             Phase VARCHAR(250),             WorkCodeID INT,             WorkCodeTitle VARCHAR(250),             MaterialTitle VARCHAR(250),             Quantity DECIMAL(18,2),             TotalHours DECIMAL(18,2)         )                  SELECT @XML = @JobMaterialsXml                  INSERT INTO @MaterialsTable (Area, Phase, WorkCodeID, WorkCodeTitle, MaterialTitle, Quantity, TotalHours)         SELECT M.Item.query('./Area').value('.','VARCHAR(250)') Area,                M.Item.query('./Phase').value('.','VARCHAR(250)') WorkCode,                M.Item.query('./WorkCodeID').value('.','INT') WorkCodeID,                M.Item.query('./WorkCodeTitle').value('.','VARCHAR(250)') WorkCodeTitle,                M.Item.query('./MaterialTitle').value('.','VARCHAR(250)') MaterialTitle,                    M.Item.query('./Quantity').value('.','DECIMAL(18,2)') Quantity,                    M.Item.query('./TotalHours').value('.','DECIMAL(18,2)') TotalHours         FROM @XML.nodes('/ArrayOfJobMaterialImport/JobMaterialImport') AS M(Item)                  --Process the data                         END 

Once the XML data comes in it’s converted into an XML data type using SELECT @XML = @JobMaterialsXml syntax.  The key part of the T-SQL code is the SELECT statement that grabs each value from the XML data type and looks for specific child nodes.  If the child nodes were attributes instead then you would do something like M.Item.value(‘@attributeName’,’DBType’).  Each JobMaterialImport node in the XML is located by the @XML.nodes(‘/ArrayOfJobMaterialImport/JobMaterialImport’) AS M(Item) code.  What’s nice about this approach is that a single call can be made to the database yet 1000s of records can be processed.  Not optimal for every situation, but exactly what I needed and fairly straightforward to use.

Mark Jaquith: Using PHP5 object constructors in WP Widget API

Someone mentioned to me that they couldn’t use PHP5-style object constructors when using the WP Widget API. I looked into it, and it turns out it does work. Example:

 class My_Widget extends WP_Widget { 	function __construct() { 		$widget_ops = array( 'classname' => 'css-class', 'description' => 'Description' ); 		parent::__construct( 'css-class', 'Title', $widget_ops ); 	} 	// Rest of your widget subclass goes here } 

The key is using parent::__construct() instead of $this->WP_Widget().

Use SUSE Studio to Build a Linux OS From Scratch [Operating Systems]

Think you can make a better fast-booting, Chrome-focused OS than Google? Want to craft a custom Linux system that boots from a USB stick? SUSE Studio gives you 15 GB to do exactly that, and you do it all online.

SUSE Studio is what powered the fan-made “Chrome OS” we posted yesterday, which, in that case, was a semi-stripped-down system loaded with the developers’ version of Chrome, Google webapp links, and OpenOffice. If speed and cloud computing aren’t your bag, you can create a fully functional system with Firefox, 3D graphics, and whatever apps you can find installed. Want your system to start up with an AWN dock and Launchy keystroke launcher running? Not a problem.

Even if you don't know all that much about Linux, it's pretty easy to build a system you can boot from a USB stick or live CD/DVD, run inside a virtual machine program, or actually install it—or, heck, even test it out in your web browser.

Here’s a basic walkthrough of building a system with SUSE Studio. In this case, we’re looking to build a GNOME-based system that would boot fairly quick and use Chrome for most of its functions, and use GNOME-Do as the primary application launcher.

Get an account, choose your desktop

First things first, you’ll need to grab an invitation and account from SUSE Studio. While it’s invite-only at the moment, I received my invite only 10 minutes after registering and filling out a quick survey that suggested it would boost my invite reply time. Once your invite arrives, you can sign into SUSE Studio with your Google or Yahoo account, or any OpenID provider. Not sure how to nab an OpenID? Here’s a quick video tutorial.

Once you’re signed in, head to your “Home” screen and click the “Create new appliance” link in the upper-right. SUSE Studio calls each bootable system you create an “appliance” throughout the process. You’ll be asked to choose your “base template,” which includes the GNOME and KDE desktops, a Just Enough OS (jeOS) option, and server or command-line-only choices. Most folks will want to lean toward GNOME or KDE setups, as they're the most familiar graphical environments. If you're familiar with Linux enough to know how to build a login manager and desktop from a command line system, though, go ahead and play around—you can't really hurt anything.

Choose your software

This is the real meat and potatoes of creating a system. Click the “Software” tab and check out the packages already going into your system.

Based on your selection of a GNOME desktop, and SUSE Studio assuming you want the Linux basics needed to boot, a few packages and repositories are already installed for you. They’re based on a basic installation of OpenSUSE, but you could wipe the slate clean and start over with another RPM-based repository, if you so chose.

If you wanted to add Firefox to your system, simply search for it in the search bar farther down the page. Results from the repositories you've chosen appear, and you can click "Add+" to load them into your system, with dependencies and other needed packages automatically included. What if you don't see something you know runs on Linux—like, say, Google Chrome? Find an RPM-formatted package, like those I found at Ben Kevan’s blog, or add in a repository URL that carries regular updates. Generally, a good Google search for the name of your program and “OpenSUSE” should yield fruit. Hit the “Upload and Manage RPMs” link near the top of the Software page, and you’ll be able to upload from your computer, or point to a file on the web. What’s really neat is, once you upload your RPM files, you’ll have a special repository created for you that can be loaded into any system you build with SUSE Studio.

Change the look and feel

Once you’re done tinkering with your apps, head over to the Configuration tab to mess with your eye candy and determine how your system will boot up. Start at the “General” sub-section, making sure to change the user name at bottom to something other than “Tux” and change the password away from the standard “linux.” You can set how you want your system to find a network connection (anything other than the manual or no-network options should be fine), and whether to enable a firewall.

The Personalize section only has two parameters, but who doesn’t like to see their own logos and backgrounds stamped on a system? Next over, make sure the “Startup” section has you set to boot into a graphical login. Under “Desktop,” you can set the OS to automatically boot to a desktop for faster start-up times, and the “Configuration” field lets those planning to install to a disk or USB drive, or run in a virtual machine, fine-tune their memory and disk use settings. “Overlay files” and “Scripts” can mostly be skipped, unless you’ve got documents you need to have in your test system or already work at a high level of Linux knowledge.

Grab and boot your OS

The “Build” section is where you get the good stuff. Pick the format you’d like to download, whether an ISO for creating a CD/DVD, a disk image for hard disk or USB transfer, or a ready-made virtual machine file for VirtualBox or VMWare. Choose your format, set a version number, and that build will always be available for downloading or “cloning.” Not quite sure what to do with the files you received? Here’s SUSE Studio’s guide to using SUSE Studio appliances—though we'd certainly welcome more tips, especially on imaging USB drives with .RAW image files, in the comments.

Don’t have the time or patience to burn a CD or install a new virtual machine? SUSE Studio actually lets you run your custom-built appliances on their own virtualization servers, for up to one hour, for free. Hit the “testdrive” link on one of your builds, and wait for it to boot up.

I was fairly impressed with the performance of a virtual machine I created entirely online, running on servers likely a world away and controlled entirely through a browser.

That's just a quick glance at what you can do with SUSE Studio—we're planning on building out a very slim, but helpfully app-loaded OS and sharing our configuration in the future. Tell us about your own "appliance" builds, and other custom OS tools, in the comments.

100- Low Entertainment Center Pt. 4

Download Low Resolution .mp4
Download High Resolution .mp4
Download High Resolution .wmv

An important aspect of building furniture that many new woodworkers overlook, is the importance of finishing BEFORE the project is completely glued together. That’s the primary focus of this part of the series.

A few of the topics covered in this part:

  • How to deal with color differences between plywood and solid wood trim.
  • Raising the grain and applying water-based dyes.
  • Theory and application of wiping varnish.
  • Creating shelf pin holes for adjustable shelving.
  • Applying angled trim to the shelves.
  • Final glueup and clamping strategy.

I mentioned my finishing DVD in the video, so here’s a quick link for anyone who might be interested. A Simple Varnish Finish

JIG IT® Shelving Jig JIG IT® Shelving Jig
Our original JIG IT® Shelving Jig is now even better!
Get perfectly, consistently spaced and centered shelf-pin holes!

JIG IT® Shelving Jig

**New Project Plan!!**
sketchupfileimageIf you are interested, we now have a digital plan available for this project. The set includes a PDF plan/cutlist and a full-featured Sketchup file. The download is available in the Wood Whisperer Store.

Quick Links:
Low Entertainment Center Pt. 1
Low Entertainment Center Pt. 2
Low Entertainment Center Pt. 3

Use Google Docs to Convert Images to Text [Documents]

It’s not an official feature (yet), but Google Docs can perform OCR image-to-text conversions on high-resolution files you upload to it. They have to be pretty darned clear and crisp, but it’s a nice freebie.

Those with webapps or services that upload to Google Docs can use this URL parameter to accept PNG, JPG, and GIF files for conversion, listed as an “experimental” feature at the moment.

Users, in the meantime, can try out a conversion using their own accounts at the Google Code Samples link. I tried uploading PNG screen grabs of Lifehacker and Wikipedia to Google Docs for conversion. Docs returned nothing with Lifehacker’s text, and a somewhat muddled take on the Wikipedia entry for “life hack” with both PNG and JPG uploads (the full-quality JPG conversion, not pictured, fared just a bit better). Those were taken using nothing more than Ubuntu’s screen capture tool and GIMP, however, so if you’ve got a better screen capture tool, or an actual camera shot of some relatively clear text, you’ll probably do better.

Tell us how automatic Google Doc OCR might help you out in the comments.

Turn Your Windows 7 PC Into a Wireless Hotspot [Windows 7]

Everybody’s got a wireless network at home, but if you’ve ever wanted to get your iPod touch, iPhone, or other wireless device connected, but all you’ve got is a wired network at work, school, or elsewhere, Windows 7 makes this process trivial.

Not using Windows 7 yet? You can accomplish the same thing in Windows Vista, XP, and even OS X—the Windows Vista method is almost identical to Windows 7, but XP requires a few more steps.

Before we begin, you should make sure that you've got a laptop or desktop with a wireless card that isn't currently connected—if your laptop is connected to the wired network, your wireless card should be free, and we can use it to allow access to the internet. Note that you have to be plugged into a wired connection in order to share the connection wirelessly with others, or have a second wireless card. Readers should also note that this won't work on (some) work networks that use group policies to enforce TPS report cover sheet boredom and prevent you from having any fun at all.

You’ll want to start out by heading into the Network and Sharing Center through the Control Panel, or you can quickly get to it by right-clicking on the network icon in the system tray. Once you are there, find the link for “Set up a new connection or network”.
You’ll be prompted with a wizard that allows you to connect to VPNs, dial-up, or create a new ad hoc wireless network, which is what we want to do. You can easily use an ad hoc network to share files back and forth between two computers, but today we’ll be using it for sharing the internet connection.

You'll need to give your network a name and choose some security options—remember that WEP is extremely easy to crack—and you'll want to make sure to use at least a decent sized key even for WPA2. The really important option on this page is to remember to check the box for "Save this network".

At this point your ad hoc network should be running and ready to start connecting your devices, but you’ll want to hold off just a minute.

You'll notice that the ad hoc networks that you create get added to the quick-select wireless network list—when you disconnect from your ad hoc network, it's the same as stopping it. Connecting to the network is the same as starting it back up; this way you can quickly switch back and forth between connections with just a few clicks.

The last step is enabling connection sharing through your regular network card, which will allow anybody connected to your ad hoc wireless to use your internet connection. To do so, you'll want to head into the Network and Sharing Center, click the "Change adapter settings" link on the left, and then find your network connection in the list—it's very important that you only enable internet connection sharing on the adapter that is actually connected to the internet. In this case, my internet access at work goes through my Local Area Connection, so I've enabled it there.

At this point, you should be able to connect any wireless device to your new ad hoc network and access the internet, or even share files directly with your laptop.

Have you been able to successfully get your wireless device connected to your PC? Tell us about your experience in the comments.

The How-To Geek is having fun downloading apps to his iPod Touch while sitting at his desk at work. His geeky articles can be found daily here on Lifehacker, How-To Geek, and Twitter.

Todoist Anywhere Turns Gmail Messages, Web Sites Into Tasks [To-do Lists]

Web-based to-do manager Todoist already integrates with Gmail forwards and opens from Launchy, but a “Todoist Anywhere” bookmarklet makes the service easy to integrate with Gmail labels or individual messages, as detailed in this video.

As noted in the video, you can sign up directly from the pop-open bookmarklet, and clicking the bookmarklet while an email message or search/tag results page is open gives Todoist a little special context for your tasks and sub-tasks. Got another tool to integrate Todoist, or another task manager, with your Gmail? We’d love to hear about it in the comments.

Install a “Garage Open” Indicator to Increase Security [Security]

We’ve all been there. You climb into bed, you turn off the nightstand lamp, and you think “Did I close the garage door?” Save yourself the midnight trip to check on the garage with this ingenious hack.

Instructables user kcj2010 hated peeling himself out of bed to double check on the garage door and hated waking up in the morning realizing he’d left it open even more. His solution was to wire a sensor to his garage door and place an indicator light hidden in the light fixture of the master bedroom.

When the garage is open, a small red LED shines on the light fixture and gives an immediate and highly visible indicator that the garage door is open. No light? The garage is closed. Seems like an idea that could be applied to any swinging or sliding door that causes you concern. Check out the Instructable for the parts list and wiring diagram. Sound off in the comments if you’ve undertaken a novel hack of your own to solve a problem around the house.

DDD7…the videos! #DDD #DDD7

At last year’s DDD7 event, we were lucky enough to have a very professional bunch of guys record the sessions for us.

Finding somewhere to host the videos has been a worry for me / us. Then, the very generous Ben Nunney offered us space and a huge amount of bandwidth, which should solve our problem. The videos are about 300MB each. Huge thanks to Ben for sorting that out for us!

Wire in…go nuts:

A Developers Guide to Network Admin – Part 1_low.wmv
and A Developers Guide to Network Admin – Part 2_low.wmvDave McMahon
ASP.NET 4.0 – TOP SECRET_low.wmvDave Sussman & Phil Winstanley
ASP.NET MVC – Show Me the Code_low.wmvSteven Sanderson
Concurrent Software in .NET_low.wmv – Nick Butler
How to Develop .NET on Linux using Ubuntu Distro_low.wmvToby Henderson
Implementing LINQ to Objects in 60 Minutes_low.wmvJon Skeet
Make the Most of Your Cores – Parallel Extensions for .NET_low.wmv – Ben Lamb
Microsoft Pex – The Future of Unit Testing_low.wmvBen Hall
Oslo, Microsofts Vision for the Future of Modelling_low.wmvRobert Hogg
TDD and Hard-To-Test Code_low.wmvIan Cooper
The Bleeding Edge of Web_low.wmvHelen Emerson
This One Goes Up to 11, or How to Write Scalable ASP.NET_low.wmvPhil Pursglove
Top 10 WCF Tips_low.wmvBarry Dorrans
Trust me, I Know What You Want!_low.wmvBeverley Hatchard
Using an Inversion of Control Container in a Real Word Application_low.wmvMike Hadlow
Virtualisation for Developers – What, Why, Where_low.wmvLiam Westley
Welcome to the CloudChris Hay
WPF Tips ‘n’ Tricks_low.wmv Sebastien Lambla

Make a Chess Set Out of Nuts and Bolts [Games]

If you’re pondering over a gift to give a chess-loving, mechanically minded friend this holiday season, you could do worse than a chess set made of nuts, bolts, and washers.

Julia Suits, a tinkerer with a Flickr account, had this to say to Make Online about her endeavors in chess set building:

[As I] headed toward the light-bulb aisle in my local hardware store a few years ago, I stopped to admire the bins of nuts,bolts and the like … I love metal,and have cast and welded all types as a sculpture major in graduate school. When I saw the little bin containing two different types of castle nuts, I immediately thought of rooks.

At the time my three sons and I hosted a weekly chess club, so chess was on my mind a lot. With my boys in tow, I returned with graph paper and we computed what sorts of bits we might want (we didn’t know for sure) for each type of piece and how many in total. An hour later, after poring over numerous bins and waiting for the clerk to saw the threaded rod into measured lengths (for kings, rooks,and bishops), we went home with about fifteen pounds of loot, including spray paint for the black pieces. We created a set not far different from what is pictured here. Since then we’ve added washers to some and added a flanged hex nut to each of the bases to make the set uniform and even more stable. The hardware chessmen were a huge hit and the other boys built their own sets.

If the set caught your eye, you’ll definitely want to check out her Flickr account for a deep look. There you’ll find close ups and “exploded parts” style photos of various pieces. If you’ve tried your hand at building a custom chess set of your own, we’d love to see pictures of it in the comments.

WP Like Button Plugin by Free WordPress Templates