Syntax Frequently Asked Questions

How to detect a DataStreamer type of stream on the server side at runtime?

To detect which binary datastreamer was used, anlyze the binary stream header. TDABin2DataStreamer uses the DABIN200 header; TDABinDataStreamer uses the DABIN100 header. Headers can be analyzed after reading the binary parameter of the client's request, which can be found in the handler of the Message.OnReadMessageParameter event. This is an example for a UpdateData call:

procedure TServerDataModule.MessageReadMessageParameter(Sender: TROMessage;
  const aName: string; aTypeInfo: PTypeInfo; const DataRef: Pointer;
  Attributes: TParamAttributes);
var
  signature: TBIN2AdapterSignature;
begin
  if  aName = 'aDelta'  then  begin
    Binary(DataRef).Read(signature, SizeOf(signature));
    // if signature = BIN2AdapterSignature then 
    // TDABin2DataStreamer was used on client
  end;
end;

How to execute stored procedures with parameters?

To execute stored procedures use the following code:

IDbCommand lCommand = this.Connection.NewCommand("dbo.stp_GetBLOB", CommandType.StoredProcedure);
lCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("pID", aID));
IDataReader lReader = lCommand.ExecuteReader(CommandBehavior.SingleRow);
lReader.Read();
                
String lFSPath = (String)lReader[0];
Byte[] lFSContext = (Byte[])lReader[1];
lReader.Close();

It is not recommended to use Connection.NewDataTable to call stored procedures with parameters, because NewDataTable does not allow to use parameters, so you will have to create a SQL query string manually, constructing it from a template like EXEC dbo.ProcedureName @psName and replacing @psName with the actual value. This approach is not secure due to the possibility of SQL Injection attacks.

If you need to receive the entire DataTable you can use the following approach (which is similar to the NewDataTable call, except that here the stored procedure is called with parameters):

DataTable lDataTable;
using (IDbCommand lCommand = this.Connection.NewCommand("dbo.stp_GetBLOB", CommandType.StoredProcedure))
{
  lCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("pID", aID));
  using (IDataReader lReader = lCommand.ExecuteReader())
  {
    lDataTable = new DataTable();
    lDataTable.Load(lReader);
  }
};

How to use FieldExpression object in the Dynamic Where feature?

FieldExpression object has two main properties:

  • FieldName - contains name of the database field.
  • TableName - contains table name of the database

If to use constructor like:

new FieldExpression("Name"), FieldName will be equal to Name and TableName will be empty. If in your SQL statement there is one field with name Name then DynamicWhere statement will work correct. But if you have more then one field with name Name in your SQL statement, you will have error: Ambiguous column name.

So, the right way to avoid application wrong work is to use following constructor:

new FieldExpression("Person","Name"). In this case FieldName property will be equal to Name and TableName property will be equal to Person. And your Dynamic Where statement will work correct.

How to use the MS SQL Server HierarchyId data type?

Unfortunately, the Microsoft.SqlServer.Types.SqlHierarchyId data type is specific to MS SQL 2005/2008 and is not directly supported by Data Abstract for .NET, yet. As a workarouund, you could do the following to be able to work with tables containing SqlHierarchyId columns via DataAbstract:

Add a table to the schema, set its Statement Type to stSQL and setup its SQL property. You need to define a SQL statement where the HierarchyId is converted to VARBINARY(MAX). F.e.:

SELECT
    CONVERT(VARBINARY(MAX), HierarchyId_Column) AS Binary_Column,
    Some_Column
FROM
    [Table]
WHERE {Where}

Please note that you don't have to define custom Insert, Update and Delete commands for such table (where the incoming VARBINARY(MAX) parameter is converted back to HierarchyId), because VARBINARY(MAX) can be implicitly converted to HierarchyId.

In a client applicaton, the Byte[] value can be converted to Microsoft.SqlServer.Types.SqlHierarchyId in the following way:

Byte[] BinaryValue = (Byte[])DataSet.Table[0].Rows[1][0];
Microsoft.SqlServer.Types.SqlHierarchyId HierarchyIdValue = new Microsoft.SqlServer.Types.SqlHierarchyId();
HierarchyIdValue.Read(new System.IO.BinaryReader(new System.IO.MemoryStream(BinaryValue)));