C# Application to import paradox data to SQL Server

As promised…

When recently tasked with rewriting an antiquated hotel reservation booking system, that uses paradox data, into C#, I was disappointed to find no free tools online that can import the paradox tables into SQL Server. Thus to save others the time and trouble of writing an application to do this, and hopefully save people from wasting their money on rip-off software, I have written one. Mine is, of course, free to use, abuse, modify… whatever you want, and comes with the full source code.

Running the application requires .Net Framework version 4.5. Editing it requires Visual Studio 2012, either a paid-for version or Visual Studio 2012 Express for Desktop. In order for the c# program to read paradox table files, you need to have the Borland Database Engine installed. I have shared the installer for it on my SkyDrive as well, since I struggled to find it.

Downloads
BDEInfoSetup.exe Paradox2Sql.zip

The idea is of course to import the data into an intermediate database in SQL server; then design a proper relational database. You shouldn’t be using the imported data directly. The application is not the fastest it could be, as it tries to import every paradox table it finds in the directory, and validates every single field of every row, using null for any that are invalid. It will also use strings for a lot of data types (anything besides string, int and DateTime); feel free to modify the application’s source if you want to specify more data types in the created SQL tables. (The GenerateSqlCreateTableScript method. The source is not complicated. Any c# developer, even one with little experience, should be able to follow the source code and modify it, should they need to.)

Instructions

First of all, I suggest making a local copy of the paradox data files. With the user of the paradox system logged out, copy all the tables (*.db) to a local directory. Take note of the paradox location where you copied the files.

In the directory where you copied the paradox files, create a subdirectory called LOCKFILES, then alter its security attributes in Windows. Give the NETWORK SERVICE user full control of the directory.

BDE Configuration

  1. If the BDE is not already installed, install it.
  2. Run the BDE Administrator tool. On my Windows 7 environment, it is installed at: “C:\Program Files\Borland\Common Files\BDE\BDEADMIN.EXE”.
  3. Expand Configuration –> Drivers –> Native –> PARADOX and change the NET DIR setting to the path of the LOCKFILES directory you created. For example, if the paradox files are in E:\Paradoxdb, the setting should be E:\Paradoxdb\LOCKFILES.
  4. On the menu, click the Object –> Apply menu item.

Using the Paradox2Sql application

 paradox2Sql

  1. If you extract the Paradox2Sql.zip file, you can launch the executable from bin\Release\Paradox2Sql.exe. Alternatively, open the project in Visual Studio 2012 and run it in the debugger.
  2. Set the Paradox files location to the directory path where your paradox DB files are.
  3. Create an empty database in SQL Server, and set the SQL connection string to a valid connection string to your newly created database. (Obviously using a user that has permissions to create tables and insert data in the database, or just use sa.)
  4. Click the Import button.

Enjoy.

Advertisements

About Jerome

I am a senior C# developer in Johannesburg, South Africa. I am also a recovering addict, who spent nearly eight years using methamphetamine. I write on my recovery blog about my lessons learned and sometimes give advice to others who have made similar mistakes, often from my viewpoint as an atheist, and I also write some C# programming articles on my programming blog.
This entry was posted in Programming and tagged , , , , . Bookmark the permalink.

12 Responses to C# Application to import paradox data to SQL Server

  1. Jerome says:

    I haven’t tried it on 64 bit… Sorry.

    But I don’t see any reason for it not to, if the BDE can install. (My platform target was set to “any CPU”. It can just be set to 64 bit and rebuilt.) I resorted to this because MS Access gave obscure errors, which would involve fixing the DB files, and the files were not under my control.

    If it doesn’t work, try opening the files with MS Access.

    Like

  2. Jerome says:

    Looks like your ODBC data sources are messed up.

    Have a look at: this forum post. Seems you need to run the 32 bit data source manager, even on 64-bit.

    (Of course, I have no idea what you’d have to do if the data source is 16-bit.)

    Like

  3. Luca says:

    Thanks a lot for this work.

    Like

  4. Jacques says:

    Thanks for posting this, it’s proving very useful. Found another program that could read paradox files and output to Excel, but the table had more rows than Excel’s max, so it couldn’t be used and it would have been a 2 step process to get it into sql.

    I just needed to downgrade the project to VS2010 to make a small change so text columns are varchar max (was getting an error on some inserts on varchar 255)

    Like

    • Jacques says:

      btw, found that it was quite slow on one of the tables since a lot of the columns contained null values. Changed the following bit of code and had a big increase in speed.

      private static dynamic ConvertTo(object source, Type dest)
      {
      try
      {
      if (source.GetType() == typeof(DBNull))
      return DBNull.Value;

      /* Sometimes DateTime values that are valid according to the DateTime type
      * can still give out of range errors when we try importing them to SQL. */
      if (dest == typeof(DateTime) && !IsValidSqlDateTimeNative(source.ToString()))
      return DBNull.Value;

      return Convert.ChangeType(source, dest);
      }
      catch
      {
      return DBNull.Value;
      }
      }

      Like

  5. bbcompent1 says:

    I like what I see here so far. It says it can create a table; what I want to know is can I import from Paradox using your code to an existing SQL Compact Edition table?

    Like

  6. I went digging into the legacy application. This thing is using Paradox v4! I’m going to try your solution to see if I can fit it to my application. My goal Paradox V4 > SQL Compact 4. I welcome any and all advice in this endeavor.

    Like

  7. Nick says:

    Just thought to say thanks. I was trying to extract data from a 20yrs old paradox db and your code and bde setup helped me. Cheers

    Like

  8. Jerome says:

    Rahim, I have deleted your comments as requested. I do find it odd that you would request that… ja, blog comments show up on Google, and so does everything else you do online while not surfing anonymously. I quite like that myself. Good thing you didn’t comment on my other blog… 🙂

    Like

  9. Hugo M says:

    Thanks a lot! It worked very well for me after some editing… But I never got a Blob field to show in table.Columns… My paradox table has 8 columns, but when da.Fill(table); is executed, and I check the number of columns inside the GenerateSqlCreateTableScript method, it shows only 7 of them (the blob one is missing).
    Any hints on how to solve it?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s