Tuesday, March 24, 2009

Create .NET Objects Directly from any DbDataReader

Recently I was using the JSON.NET library to deserialize JSON objects into my own .NET class instances. I love serialization and was having way too much fun.

It occurred to me that even though every database I use does not support XML or JSON result sets, that doesn't mean I can't write a small wrapper that facilitates deserialization of a DbDataReader into my own custom classes.

I started out with a small library that took a DbDataReader as input and produced JSON text I then fed to JSON.NET and deserialized. That worked, but was a bit of a hack. I figured others had tried similar approaches so I started searching the web for a similar implementation. Rick Strahl's article DataTable JSON Serialization in JSON.NET and JavaScriptSerializer was exactly what I was looking for. Rick had already posted code that could deserialize DataSet, DataRow and DataTable objects. Using that code as a template, I created my own library that deserializes DbDataReader objects.

This approach differs from Rick's as it skips the step of moving data into an ADO.NET data structure of any sort. I simply query the database and use a DbDataReader to generate my own class instances.

Take the following class as an example:

   1:  class Employee
   2:     {
   3:        public int empid { get; set; }
   4:        public string firstname { get; set; }
   5:        public string lastname { get; set; }
   6:        public int deptnum { get; set; }
   7:        public string phone { get; set; }
   8:        public DateTime doh { get; set; }
  10:        public string ShortFormat()
  11:        {
  12:           return string.Format( "{0} {1} {2}", this.empid, this.firstname, this.lastname );
  13:        }
  15:     }   // class Employee


I can now query the database and create a new Employee object in one simple call (line 5):

   1:              cmd.CommandText = "select * from demo10";            
   3:              // create a single instance of the Employee class using GetOneObject
   4:              AdsDataReader r = cmd.ExecuteReader();               
   5:              Employee e = (Employee)DbSerialize.GetOneObject( r, typeof(Employee) );


Or even better, create a generic list of Employee objects (line 5 again):

   1:              cmd.CommandText = "select * from demo10";            
   3:              // create a generic list of Employee objects using GetObjectList
   4:              AdsDataReader r = cmd.ExecuteReader();               

5: List<Employee> AllEmployees =

(List<Employee>)DbSerialize.GetObjectList(r, typeof(List<Employee>));


DbSerialize Library

There isn't much to the library. I've made it available for download here. I've included a JSON.NET binary I built based on the latest code (the binary on the JSON.NET site had a few bugs that had been fixed in the trunk, so I downloaded the trunk and built it).

The library and example are Visual Studio 2008 projects. I'm sure the code will still work in Visual Studio 2005, but you might have to create your own project files.

The example uses the Advantage Database Server, but the DbSerialize library would work with any ADO.NET provider.

What's Next?

There are quite a few possibilities, most depending on your feedback.

The dependency on JSON.NET could be removed and these functions could be incorporated directly into the Advantage .NET Data Provider.

If Delphi adds better RTTI support, we could deserialize directly into Delphi objects too, which would be cool.

We could facilitate serialization so changes could be posted back to the database. A poor man's ORM solution of sorts. This could be rather nice when you want some of the benefits of ORM, but don't want the overhead of a model and another layer of complexity.

We could modify the Advantage server to return JSON directly. The JSON could then be used by web clients (think JavaScript) as well as .NET and other clients.

Sunday, March 22, 2009

New Advantage Database Server Book

Advantage Presales Engineer Joachim Duerr has written a book about Advantage in German.

In addition to his new book, Joachim will also be joining us in the U.S. at the upcoming Delphi Live 2009 conference in May, where he will be teaching a class on database notifications.

From Joachim:

Ich möchte auf diesem Wege die Verfügbarkeit meines neuen ADS Buches bekanntgeben. Es ist das erste deutschsprachige Buch zu ADS überhaupt.

Das Advantage Database Server 9 Programmierhandbuch
Autor: Dipl.-Ing. (FH) Joachim Dürr
Preis: 35€
380 Seiten, Paperback
weitere Informationen über meine Homepage

Wednesday, March 18, 2009

Advantage Data Architect Management Screen

I'm in changing the grids in the Remote Management Utility in ARC to use DevExpress grids instead of the ancient (think Delphi 2/3) TStringGrid objects.

You can now rearrange columns, sort and filter. You can also multi-select users to disconnect, and I added the option to disconnect users to the "Open Files" tab so you can see all users with a particular file open and disconnect all of them.

What other changes would you like to see while I'm familiar with this form?



Thursday, March 12, 2009

Encrypting Visual FoxPro Tables

This screencast (just over 10 minutes) is a continuation of my series explaining how to extend the life of your Visual FoxPro DBF tables using Advantage. In this screencast I show unencrypted data from DBF tables being transferred over the wire. I then create a data dictionary, reference the tables from that dictionary, and encrypt the tables and all database communications. The only change necessary for the client application is the need to provide credentials when connecting to the database.

Additional notes about Advantage encryption can be found in the online help file topics Advantage Encryption and The Advantage Data Dictionary.

If you have not watched the initial screencasts in this series, I've included the viewing order below. The topics stand on their own fairly well, but may be more meaningful if watched in order.

  1. Getting Started With Visual FoxPro and Advantage
  2. Use Visual FoxPro and Visual Studio - and Share Your Existing DBF Tables 
  3. Using Tables Over 2GB in Visual FoxPro
  4. Hiding DBF Tables

Watch the screencast directly

Download the screencast (open fox_encryption.html after unzipping): 40MB