Bits.Bytes.

A large word document plonked into my outlook inbox with the specs for my latest project. After reading through I came across the database schema from hell.

It starts like this:

 

And ends merrily like this:

The fields are of a revealing nature so these are just substitutes.

The problem is that these are columns of a database table, and the mission, should you choose to accept it, is convert this 500 field monster into:

1. A SQL Server Table
2. A stored procedure
3. Code in a code behind page that will call the procedure pass it parameters

As my head spun with the implications of all the grunt work, a small elf appeared at my shoulder and urged me to apply my grey matter to the problem.

Reading further and making a few inquiries I found out:

1. All fields were integers
2. Each  was to have  a default of 1
3. The data entry form was to have all 500 fields for entry (Yes, the client insisted)

Armed with this information, the grey matter was applied to the problem

I didn’t have to sit naked in a bath and watch an apple fall to have an eureka moment.

I would have to somehow generate the code. And there is a very cheap way to do it with a budget of ZERO

Regular expressions.

Regular expressions are the salt of the earth. I cannot enumerate just how many times they have saved my bacon (and ham, and cheese!)

My first instinct was to write some code to parse the file but sanity prevailed. All good text editors support regular expressions. Those I have occasioned to use are Textpad, UltraEdit and my current darling, Notepad++, which has syntax highlighting and code folding. Yeah baby!

Let’s get cracking

Generating the SQL Table

Since we have the field names, all we need to do is generate the DDL.

This nifty regular expression does just that

The ^(.*)$ captures every line. The \1 returns what the regular expression captured. So the output of this little number is as follows:

After adding the create table statement and a couple of brackets, we can run it in SQL Server Management Studio

 

And Bob’s your uncle. Table done. Time taken? Negligible.

Generating the Stored Procedure

Next thing is to generate the stored procedure to update this monster. Adding is a no brainier because the last column we add is a primary key, generated elsewhere. Inserting that one column will generate default values for all the others.

Now we work on the update procedure.

Again we apply our friend the regular expression

In plain English, ^(.*)$ captures every line and @\1 int, prepends @ and appends the data type to what we captured.

The result is as follows:

This is just the parameter definition. The next bit is to generate the update statements. Again we employ our new best friend

^(.*)$ captures every line and \1 = @\1, outputs the captured value and prepends the @ and appends a comma

With a few tweaks to add the name, we get our procedure and hey presto

Awesome!

Generating The Data Access Code

Next order of business is to write the code in the data access class.

Before we do this we make use of the knowledge that we are going to use a DataList to view all this data. And since each textbox will have some custom JavaScript attached, we’ll have to use templates, in which we will put our textboxes. Automating the generation of these textboxes we can think about later.

Right now let us assume they have somehow been generated and we need to generate the code for the update.

This corker is the regular expression we will use

cmd.Parameters.Add(new SqlParameter(”@\1″,Convert.ToInt32(((TextBox)e.Item.FindControl(”txt\1″)).Text)));

This generates the following (I’m sure you get the drift of what \1 is by now)

Does it get any cooler?

And with those few expressions the bulk of the code is done!

This message will self destruct in five seconds…