Wednesday, December 17, 2008

Encrypted Free Tables and Visual Studio

I discussed working with encrypted free tables in my November FAQ, however, the topic deserves a bit more detail. The best way to work with encrypted tables with any of the Advantage supported development environments is to use a data dictionary. However, this is not always possible or even desirable.

To use encrypted tables with the Advantage .NET data provider you must specify the encryption password in the connection string and open the table using a TableDirect command type. To open an encrypted table using SQL with the Advantage .NET data provider you must use a database connection. The following code snippet demonstrates opening an encrypted free table.

   1: AdsConnection cn;
   2: AdsCommand cmd;
   3: AdsDataAdapter da;
   4: DataSet ds;
   5:  
   6: string sConnect = "Data Source=C:\\Data";
   7: sConnect += ";ServerType=Remote";
   8: sConnect += ";EncryptionPassword=password";
   9:  
  10: cn = new AdsConnection(sConnect);
  11: cmd = cn.CreateCommand();
  12: cmd.CommandType = CommandType.TableDirect;
  13: cmd.CommandText = "Customer";
  14:  
  15: cn.Open();
  16:  
  17: da = new AdsDataAdapter(cmd);
  18: ds = new DataSet();
  19:  
  20: da.Fill (ds);

You can encrypt and decrypt tables using an Advantage Extended Data Reader which has EncryptTable and DecryptTable methods. To use the extended data reader you must use a CommandType of TableDirect and the ExectueExtendedReader method. To use EncryptTable and DecryptTable you must open the table exclusively by setting the Shared connection string keyword to False.

There is an example application available on the Advantage DevZone which demonstrates these methods. It is listed in Code Central under C#/VB.NET and is called Free Table Encryption Demo. It is a Visual Studio 2005 project which contains C# and VB.NET code.

No comments: