First Post

I've been coding for a long, long time. Doesn't mean that I am any good at it but I do have the benefit of almost three decades of screwing up (some people call this experience). In that time I've worked with Microsoft development tools on many occasions. Starting with vb3 and including vb4 (16 bit), vb6, vc/c++ and .asp programming. With the exception of the c/c++ I have not had much positive to say about my experience. Nevertheless, I periodically try the latest and greatest to see what is new and if the promise of easy development is any closer to being realized.

Earlier this week I downloaded the 2008 Visual C# Express Edition with the idea of porting an ancient paradox for dos application to .net. One of the decisions to make is the database. Express Edition comes with something called SQL Server Compact Edition(SQLCE). Apparently this is a Windows CE product ported to the rest of the Windows family. Unfortunately it is not completely compatible with it's larger siblings.

I have a csv like export file from the paradox tables. I was hoping for an easy way to load this into SQLCE . I finally got the data for one table loaded but it wasn't trivial. I'm considering switching to something like mysql just to avoid headaches like this. We'll see.

I made several attempts using the built in data facilities in the IDE. I wrote a quick cpython program to convert the .csv file into properly formatted sql inserts. I also wrote a quick custom c# program to let me execute sql pasted into a textbox and had no luck there for two reasons.

• SQLCE does not like to execute multiple SQL statements at once ie no 'INSERT foo; INSERT bar;' each statement needs to be executed seperately.
• I could not get my statements to insert unless I explicitly put NULL in any field where there was not a value to be inserted

Once I figured these two issues out (and the error messages were not helpful) I was dreading writing all of the file access code in C# to read the sql statements in. Not that I know the code to be difficult but the quickest way to do something is the way you already know. Which leads me to my next adventure.

I've always enjoyed python programming and one of the things I've wanted to check out about .net is IronPython. So, I got the 2.0 Alpha8 release (alpha!) and wrote a quick bit of code in the interactive shell to load the data. Here is the cut and pasted code so no brickbats for bad formatting:

>>> import clr
>>> clr.AddReference('System.Data.SqlServerCe')
>>> from System.Data.SqlServerCe import *
>>> conn = SqlCeConnection()

>>> cmd = SqlCeCommand()
>>> conn = SqlCeConnection.setConnectionString("Data Source=database.sdf")
>>> cmd.Connection = conn
>>> conn.Open()
>>> inp = open("C:\\newMsSqlAccount.sql","r")
>>> for line in inp:
... cmd.CommandText = line
... rows = cmd.ExecuteNonQuery();
... print rows

And that was it, 26k rows loaded in less than a minute. I am somewhat happier now.