Last Wednesday we went away for a break (nothing to do with Thanksgiving, which we don't celebrate in the UK). Our plan was to spend a couple of days in Sleights, near Whitby, before heading further north to visit friends in Sunderland for the weekend.

The plans changed when we woke up Thursday morning and drew back the curtains to this scene of un-seasonally bad weather:

We decided to cut our losses and head to the North East a day earlier - not realising the weather up there was about to get a whole lot worse.

We had fun in all the snow. Well, the kids lasted about 5 minutes per outing. The photo below is down by the sea at Sunderland, where it was bitterly cold *.

No children were harmed in the making of that photo and we were soon back in the warmth of the car.

All the while I was keeping one eye on the local travel news to see how likely it was we'd get home - as planned - on Sunday.

While checking the travel news I noticed some funny reports of the A1 being "wide open both ways".

Wide open? Both ways?

"Come on Karen", I thought, "pack the bags! Lets get going while the roads are empty!!"

After a while I worked out that, in fact, Wide Open must be the name of a place up there. Sure enough, after asking a local and checking the map I found a place on the A1 called Wideopen (which I guess is pronounced "wide open").

Luckily we didn't hit the road at that point and waited until last night, by which time the roads were almost clear and we got home safely.

Anyway, I just thought the above anecdote might be amusing. Probably not, but it made me laugh.

What bothered me was the way they were characterizing Lotus Notes was wrong, based on everything I knew from my years of experience as DominoPower's editor.

So I wrote that article. Then another. All told, over the next few months I wrote 12 full articles, which eventually wound up in a book, Where Have All The Emails Gone? How Something as Seemingly Benign as White House Email Can Have Freaky National Security Consequences.

During the months I researched those articles and wrote the book, I was grateful to have had the support of both our advertisers and you, our readers. I got exceptional feedback after each article was published by some of the top technical experts dealing with email and messaging management. And I got encouragement and suggestions for more questions to ask and avenues to research.

Since then, over the years, I've written a steady flow of articles and special reports about White House communications for CNN, CBS Interactive's ZDNet Government blog, for Counterterrorism Magazine, for Harvard's Nieman Foundation for Journalism, and more. I've done hundreds of interviews on Presidential communication and advise homeland security professionals on countering cyberterrorism and conducting and defending against cyberwarfare.

And in July, as a direct result of the small articles begun because of my gig here as editor of both DominoPower and OutlookPower magazines, I had the opportunity to participate in the filming of a History Channel special, The President's Book of Secrets, which premieres Wednesday night at 9pm.

The President's Book of Secrets takes viewers on a journey inside White House history to unveil staggering information about secrets known only to the President, from top-secret intelligence and classified events to covert codes and future technologies.

The President's Book of Secrets features exclusive interviews with Washington insiders, including former CIA Director Michael Hayden, former Director of Homeland Security Michael Chertoff, former Vice President Dan Quayle, former White House Press Secretary Dana Perino and presidential daughter Susan Ford who reveal what it is like to live and work in the White House.

Additionally, Former Speaker of the House Newt Gingrich, journalists Dan Rather and Jonathan Alter and other experts (that'd be me!) share what they know about the secret world of the presidency.

My part in the program is a discussion of the Internet and the Presidency, along with White House email, communications, and messaging technologies. You know, the usual.

Kudos to the producers for their patience while they were filming me. I haven't seen the program yet, but they've promised that even though I've got a face for radio, I won't break your TV screen.

Please tune in. I'm sure it'll be a fascinating program. Set your TiVos. And, if you don't happen to catch it Wednesday night, check the History Channel's schedule for future broadcasts.

David Gewirtz is the author of How To Save Jobs and Where Have All The Emails Gone? For more than 20 years, he has analyzed current, historical, and emerging issues relating to technology, competitiveness, and policy. David is the Editor-in-Chief of the ZATZ magazines, is the Cyberterrorism Advisor for the International Association for Counterterrorism and Security Professionals, and is a member of the instructional faculty at the University of California, Berkeley extension. He can be reached at david@zatz.com and you can follow him at http://www.twitter.com/DavidGewirtz.

Ever wondered how to create multiple rows in a SQL Server table using ASP.NET?

You could of course do a loop inside C# code that adds a row at a time by calling a simple INSERT statement. But what if something goes wrong half way through the loop and you want to delete all the rows already added (rollback) so you can try it all again later?

A better way to add many rows at once is to pass a DataTable object directly to a Stored Procedure. Yes, you can do that. And it's fairly easy. Since discovering this approach I've used it in quite a few places.

Let's look at how simple it is.

Last week I mentioned a C# function to create unique voucher codes. Now, let's say we have two tables in a database to store these voucher codes - one called "VoucherRequests" - to store details of who, why and when the codes were requested and one called "VoucherCodes" to store the actual codes generated for each request. The tables are linked together by VoucherRequest row's ID column as a foreign key.

Now, assume our application POSTs the following information to the server:

description=some+vouchers+to+hand+out&quantity=50&value=100&expires=2011-11-23

The server then uses this information to create the right number of vouchers in our tables.

The C# code would look something like this:

requestsTableAdapter rta = new requestsTableAdapter();
vouchersTableAdapter vta = new vouchersTableAdapter(); DataTable vouchers = new DataTable();
vouchers.Columns.Add("VoucherCode");
vouchers.Columns.Add("VoucherValue");
vouchers.Columns.Add("VoucherExpires"); int count = Convert.ToInt32(context.Request["Quantity"]);
int value = Convert.ToInt32(context.Request["Value"]); DateTime validUntil = DateTime.ParseExact(context.Request["Expires"], "dd/MM/yyyy", CultureInfo.InvariantCulture);
validUntil = validUntil.AddDays(1).AddSeconds(-1); //Adjust time to 23:59:59 of the date in question int i=1; do
{ string code = CreateRandomString(8); if (vta.TestCodeUniqueness(code)<1){ DataRow dr = vouchers.NewRow(); dr[0] = code; // System.Guid.NewGuid().ToString("D"); dr[1] = value; dr[2] = validUntil.ToString("yyyy-MM-dd HH:mm:ss"); vouchers.Rows.Add(dr); i++; }
} while (i <= count); rta.AddVouchers(context.Request["Description"], vouchers);

You should be able to see what this is doing. Even if some of it makes no sense - the principal is what's important.

What it does is build a temporary DataTable with 3 columns. Then it keeps trying to create a unique code to add to this table until it has added as many as was requested. Then it passes this table as-is, along with a description of the request, to a Stored Procedure (SP) via a TableAdapter.

The key to making this work is having a User-Defined Table Type in SQL Server, so that it knows how many column to expect and what data type they should be.

Here's the SQL to create this Table Type:

CREATE TYPE [dbo].[VouchersTableType] AS TABLE( [VoucherCode] [nvarchar](36) NOT NULL, [VoucherValue] [int] NOT NULL, [VoucherExpires] [datetime] NULL
)

Once the Table Type is in place we can create a SP that uses it. Here's the SQL for SP to add multiple rows in one go:

CREATE PROCEDURE [dbo].[VoucherRequestADD]
( @description NVARCHAR(255), @TVP VouchersTableType READONLY
) AS SET NOCOUNT ON DECLARE @RequestID INT; BEGIN TRANSACTION INSERT INTO dbo.voucher_requests ([description], [created]) VALUES (@description, GETDATE()); SET @RequestID = SCOPE_IDENTITY(); INSERT INTO dbo.vouchers ([voucher_request_id] ,[voucher_code] ,[valid_until] ,[value]) SELECT @RequestID, VoucherCode, VoucherExpires, VoucherValue FROM @TVP; IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION --Roll back transaction on error SET @RequestID=0; GOTO handler END COMMIT TRANSACTION handler:
SELECT @RequestID;
GO

The key part of this SP is that the INSERT statement uses a SELECT to get its data values. The SELECT statement refers to the Table Type passed in to it.

Notice the INSERT part of the SP is wrapped in a TRANSACTION so that, if anything goes wrong, we can rollback to a previous state and abort the code.

This example is quite specialised, but the principle is the same and can be applied in lots of other scenarios. For example - a shopping cart which has multiple rows per item and one row in the main table to store data about the user and other bits.

I'm posting this here primarily as I hope it will help people Googling in the future, but also as an insight in to a different world for my regular (Domino developer) readers.