Software Twist – Adrian Tosca Blog

DBF Data Export Library

Posted in Software Development by Adrian Tosca on 2009, May 10

Recently I’ve written a small library for exporting data to DBF and other format files for an internal application at my current employer. This library is now open source and can be downloaded from codeplex.

The application required data exported for integration with several 3’rd party applications we do not control. These applications require the old DBF file format or CSV format. We also needed export in excel files, this time as a strategic feature. We had a couple of attempts for DBF/CSV files using existing methods but we found several problems.

  • JET using Microsoft.Jet.OLEDB was plain unusable because of wrong formatting in some cases or data files not always read by our target applications
  • Using OLE DB Provider for Visual FoxPro was a lot better, we didn’t have any problem with it, until we needed to move the application to a 64 bit system. VPFOleDB does not work in a 64bit environment and there are no plans to do so. This was a show stopper for us.

At this point we considered using a plain c# solution without any other dependency. This solution will allow us to fix future problems without totally replacing the underlying technology like we had to do with OLEDB external drivers. We believe this solution will pay its initial development time on the long run.

While there are several other solutions out there for writing DBF/CSV files, none did satisfy our requirements. The libraries we found were either of poorly quality or oriented toward data manipulation for specific file formats. We needed a library for exporting data from an existing data source to several file formats (DBF and CSV mainly, but also others to come). Mashing up existing libraries and trying to adapt them for our requirements looked daunting, so we decided to be better of writing a library form scratch. Heavy integration systems like BizTalk were also a no go.

The result was TeamNet Data File Export, a small c# library that can take data from existing source (DataSet and IList are currently implemented) and write files to different formats (DBF and CSV are currently implemented). The library was designed for extensibility so adding other formats should be pretty easy. Office XML (Excel 2003) support is planned and will probably be added the next days.

The library is very easy to use and has a fluent interface. This is an example of using a data set as data source to export data to a DBF file:

        .AddCharacterField("AString", 30)
        .AddNumericField("ANumber", 10, 0)

While DBF files are certainly not trilling to work with, there are times when you need them. I hope to find this library handy then.


Comments Off on DBF Data Export Library

Backup Database in SqlServer with Date and Time Information in the File Name

Posted in Software Development by Adrian Tosca on 2009, May 5

The build-in back-up mechanism of SqlServer allows to either override the back-up file or append to it. I usually prefer to have each back-up in it’s file, this way I can easily choose which older back-ups to delete, and immediately see the last one. The following script makes a back-up with a file name that contains date and time information and can be used in a job step:

declare @currentDate datetime
set @currentDate = GetDate()

declare @fileName varchar(255)
set @fileName = 'C:\BKP\MyDatabase' 
	+ cast(Year(@currentDate) as varchar(4)) 
	+ Replicate('0', 2 - Len(cast(Month(@currentDate) as varchar(2)))) 
            + cast(Month(@currentDate) as varchar(2)) 
	+ Replicate('0', 2 - Len(cast(Day(@currentDate) as varchar(2)))) 
            + cast(Day(@currentDate) as varchar(2)) 
	+ '_' +
	+ Replicate('0', 2 - Len(cast(DatePart(hour, @currentDate) as varchar(2)))) 
            + cast(DatePart(hour, @currentDate) as varchar(2))
	+ Replicate('0', 2 - Len(cast(DatePart(minute, @currentDate) as varchar(2)))) 
            + cast(DatePart(minute, @currentDate) as varchar(2)) + '.bak'

backup database [MyDatabase] to disk = @fileName with NOFORMAT, NOINIT, 
    name = N'MyDatabase-Full Database Backup', 

The back-up will be written to the file with a name like C:\BKP\MyDatabase_20090505_1346.bak.

Hope it helps.

Tagged with: