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.