Integrating into theBorland Data Provider (BDP)

Ramesh Theivendran

Borland Software Corporation

Agenda

Borland Data Provider (BDP) Architecture

BDP core Interfaces

BDP metadata services

Helper Classes

Managed to Unmanaged

.NET Data Provider

IDbConnection - Connection to a data source

IDbCommand - Executes a SQL command

IDataReader - Provides a forward only cursor

IDataAdapter - Provide and resolve data

IDbDataAdapter - Specifies SQL Commands

IDbTransaction - Transaction support

IDataParameter - Runtime parameters

IDataParameterCollection

.NET Data Provider Architecture

OLEDBManaged

.NET Client

SQL ServerManaged

COM Interop layer

OleDbProvider

RDBMS

DB client

OleDbProvider

BDPManaged

TDS GDS32

MSSQL IB

DOTNET

COM

ORACLE

OCI

ISQLConnectionISQLCommand

ISQLCursorISQLMetaDataISQLResolver

ISQLSchemaCreateISQLDataSource

DB client wrapper

bdpDatasources.xml

DB

BDP Designers

BDP Components

Data Explorer

bdpConnections.xml

BDP for .NET

Namespace Borland.Data.Provider– BdpConnection– BdpTransaction– BdpCommand– BdpDataReader– BdpDataAdapter– BdpCommandBuilder

Namespace Borland.Data.Design

BDP for .NET

Namespace Borland.Data.Common– BdpParameter, BdpParameterCollection– BdpColumn, BdpColumnCollection– BdpError, BdpErrorCollection– BdpException

– ISQLConnection– ISQLCommand– ISQLCusor

BDP for .NET

Namespace Borland.Data.Schema– ISQLDataSource– ISQLMetaData – ISQLResolver– ISQLSchemaCreate

BDP development tasks

choose the DB Client

Build your unmanaged layer as a DLLCreate a class with static methods exporting all DLL entry pointImplement BDP core interfaces

ISQLConnectionISQLCommandISQLCursor

Implement BDP metadata interfacesISQLMetaDataISQLResolverISQLSchemaCreate

Extend BDPConnectionStringAdd provider assembly info. to BdpDataSources.xml

ISQLConnectionpublic interface ISQLConnection

{

Int32 Connect(String szDatabase, String szUser, String szPasswd, String szHostName);

Int32 Disconnect();

Int32 FreeConnect();

Int32 ChangeDatabase(String szDatabase, String szUser, String szPasswd, bool bConnected);

ISQLCommand GetSQLCommand();

ISQLMetaData GetMetaData();

ISQLResolver GetResolver();

Int32 BeginTransaction(Int32 ilTransID, Int32 iIsoLevel);

Int32 Commit(Int32 ilTransID);

Int32 Rollback(Int32 ilTransID);

Int32 GetErrorMessage(ref StringBuilder ErrorMessage);

Int32 SetOptions(String ConnOptions);

void SetProperty( ConnectionProps eConnProp, Object Value);

void GetProperty( ConnectionProps eConnProp, out Object Value);

}

BdpConnectionEstablishing a Connection:

BdpConnection.Open() - Loads the provider assembly - ISQLConnection.SetOptions()

- ISQLConnection.Connect() - ISQLConnection.GetErrorMessage() - ISQLConnection.Freeconnect()

BdpConnection.Close() - ISQLConnection.Disconnect()

Getting a Command:BdpConnection.CreateCommand() - A new BdpCommand(String.Empty, this) is returned - ISQLConnection.GetSQLCommand() is called from BdpCommand to create

a new ISQLCommand before any SQL request

BdpConnection

Transaction Control:

BdpConnection.BeginTransaction()

- A new BdpTransaction(this) is returned

- ISQLConnection.BeginTransaction() is called from BdpTransaction()

BdpTransaction.Commit()

- ISQLConnection.Commit()

BdpTransaction.Rollback()

- ISQLConnection.Rollback()

Accessing Metadata and Resolver implementation:

BdpConnection.GetMetaData()

- ISQLConnection.GetMetaData()

BdpConnection.GetResolver()

- ISQLConnection.GetResolver()

Connection Properties and Options

ISQLCommand public interface ISQLCommand

{

Int32 Prepare(String szSQL, Int16 iParamCount);

Int32 PrepareProc(String szSP, Int16 iParamCount);

Int32 GetStoredProcedureSQL(StringBuilder SQL, ArrayList ParamList);

Int32 Execute(out ISQLCursor SQLCur, ref Int16 nResultCols);

Int32 GetRowsAffected(ref Int32 lRowsAffected);

Int32 GetErrorMessage(ref StringBuilder ErrorMessage);

Int32 Close();

Int32 Release();

Int32 SetParameter(Int16 uParamNumber, Int16 uChildPos,

ParameterDirection eParamDir, BdpType uLogType, BdpType uSubType,

Int32 lMaxPrecision, Int32 lMaxScale,

Int32 Length, Object Value, bool bIsNull );

Int32 GetParameter(Int16 uParamNumber, Int16 uChildPos,

ref Object Value, ref bool bIsNull );

Int32 SetOptions(String[] CommandOptions);

}

BdpCommand Preparing SQL:BdpCommand.Prepare()

- Validates the BdpCommand.CommandText - Gets a ISQLCommand by calling ISQLConnection.GetSQLCommand() - If the BdpCommand.CommandType is Text, ISQLCommand.Prepare() is called.

Binding Parameters and Executing SQL or Stored procedureBdpCommand.ExecuteReader()BdpCommand.ExecuteScalar()BdpCommand.ExecuteNonQuery()

- BdpCommand.CommandType = StoredProcedure- BdpSPParam array list created

- ISQLCommand.GetStoredProcedureSQL() returns native SQL. - ISQLCommand.PrepareProc()

- ISQLCommand.SetParameter() - ISQLCommand.Execute()

- ISQLCommand.GetParameter()

BdpCommandBdpCommand.ExecuteReader()

BdpCommand.ExecuteScalar()

- A ISQLCursor and the number of resultset columns are returned

- A new instance of BdpDataReader is created and each holds a

reference to BdpConnection, BdpCommand and the ISQLCursor

BdpCommand.ExecuteNonQuery()

- ISQLCommand.GetRowsAffected()

Freeing resources

BdpCommand.Close()

- ISQLCommand.Release() to drop statement handle

- ISQLCommand.Close() is called from BdpDataReader.Close() to free

record buffer and to close statement handle.

ISQLCursor

public interface ISQLCursor { Int16 GetColumnCount(); Int32 GetColumnName(Int16 uCol, ref string szColName); Int32 GetColumnTypeName(Int16 uCol, ref string szTypeName); Int32 GetColumnType(Int16 uCol, ref Int16 uType, ref Int16 uSubType ); Int32 GetColumnLength(Int16 uCol, ref Int32 ulLength );

Int32 GetRowsAffected(ref Int32 lRowsAffected); Int32 Next(); Int32 GetNextResult(out ISQLCursor SQLCur, ref Int16 nResultCols);

Int32 GetIsNull(Int16 uColumn, ref Int32 iInd ); Int32 GetString(Int16 uColumn, ref StringBuilder Data, ref Int32 iInd ); Int32 GetShort(Int16 uColumn, ref Int16 Data, ref Int32 iInd ); Int32 GetTimeStamp(Int16 uColumn, ref DateTime Data, ref Int32 iInd ); Int32 GetDecimalAsString(Int16 uColumn, StringBuilder Data, ref Int32 iInd ); Int32 GetBlobSize(Int16 uColumn, ref long lBlobSize, ref Int32 iInd ); Int32 GetBlob(Int16 uColumn, ref byte[] buffer, ref Int32 iInd, Int32 iLength );}

MetaData Services

MetaData retrieval

Schema CreationCreate Table,View,Indices

Alter Table

Drop Table, View, Indices

Data MigrationBdpCopyTable component

SourceCommand

Destination

DestinationTable

MetaData Properties

public enum MetaDataProps

{

CatalogName = 0,

SchemaName = 1,

DatabaseName = 2,

……

ObjectQuoteChar = 11,

ObjectName = 17,

QualifiedName = 18,

QuotedObjectName = 19,

QuoteObjects = 20,

ObjectQuoteSuffix = 21

};

Metadata Retrieval - ISQLMetaData

public interface ISQLMetaData

{ DataTable GetObjectList ( ObjectType eObj); DataTable GetTables ( String szTableName, TableType eTable); DataTable GetProcedures ( String szProcName, ProcedureType eProc); DataTable GetColumns ( String szTableName, String szColumnName, ColumnType eCol); DataTable GetProcedureParams ( String szProcName, String szParamName); DataTable GetIndices ( String szTableName, IndexType uIndexType); DataTable GetSchemaTable( IDataReader reader, IDbCommand command);

  ISQLSchemaCreate GetSchemaCreate(); void SetProperty( MetaDataProps eMetaProp, Object Value); void GetProperty( MetaDataProps eMetaProp, out Object Value); };

Schema Creation - ISQLSchemaCreate public interface ISQLSchemaCreate { String SchemaName { get; set;} Boolean BatchExecute { get;} Boolean AutoCommitDDL { get; } DataTable GetDataTypes();

  String GetDDL (ObjectType ObjType, String ObjectName, String BaseName, BdpColumnCollection BdpColumns);  String[] GetDDL (ObjectType ObjType, String ObjectName, String BaseName, DataTable table);  void CreateObject (ObjectType ObjType, String ObjectName, String BaseName, BdpColumnCollection BdpColumns);  void CreateObject (ObjectType ObjType, String ObjectName, String BaseName, DataTable table);  void DropObject (ObjectType ObjType, String ObjectName, String BaseName); 

DataTable FillSQLTypeMapping(String Destination, bool bDefault); }

SQL Generation - ISQLResolver

public interface ISQLResolver { String QuotePrefix { get; set;} String QuoteSuffix { get; set;} String[] ReadOnly { get; set;} String[] ExcludeFilter { get; set;} DataRow Row { get; set; } 

String GetSelectSQL(IDbConnection Conn, DataRowCollection columns, String TableName); 

String GetInsertSQL(IDbConnection Conn, IDbCommand Command, DataRowCollection columns, String TableName); 

String GetUpdateSQL(IDbConnection Conn, IDbCommand Command, DataRowCollection columns, String TableName, BdpUpdateMode UpdateMode);  String GetDeleteSQL(IDbConnection Conn, IDbCommand Command, DataRowCollection columns, String TableName, BdpUpdateMode UpdateMode); }

Helper classes

BDPMetaDataHelperGetObjectList()

GetTables()

GetColumns()

GetProcedures()

GetSchemaTable()

…..

GetDataTypes()

GetCreateTable()

GetSQLTypeMapping()

BDPResolverHelper

BDPSchemaCreationHelper

Data Explorer - ISQLDataSourcepublic interface ISQLDataSource { String[] GetProviders (); String[] GetConnections (String ProviderName); String[] GetDbObjectTypes (String ProviderName); }

BdpDataSources.xml<?xml version="1.0" standalone="yes"?><DataSource xmlns="http://www.borland.com/schemas/bds/3.0/bdpdatasources.xsd"><provider name="Interbase" connectionStringType="Borland.Data.Interbase.IBConnectionString,

Borland.Data.Interbase, Version=2.0.0.0, Culture=neutral, PublicKeyToken=91d62ebb5b0d1b1b"> <objectTypes> <objectType>Tables</objectType> <objectType>Procedures</objectType> <objectType>Views</objectType> </objectTypes></provider></DataSource>

BdpConnections.xml

Managed to Unmanaged

PInvoke– Load the unmanaged DLL in memory– Locates the function and Marshals arguments– Calls the functions

Com Interop– TlbImp.exe – Import Com types to Managed– RegAsm.exe – Export Managed to Com types

Framework classes– System.Runtime.InteropServices

• DllImportAttribute• Marshal

– System.BitConverter– System.Text.Encoding

DLLImportAttribute

EntryPoint

CharSet (Ansi,Unicode,Auto)

CallingConvention (StdCall, Cdecl)

[CLSCompliantAttribute(false)]

public unsafe class BdpMYDB

{

[DllImport("bdpmydb.dll", EntryPoint=“MYDBGetConnection”)]

public static extern int SQLGetConnection( void* phdbc, String szDatabase, String szUser, String szPasswd);

[DllImport("bdpmydb.dll", EntryPoint=“MYDBGetString")]

public static extern int SQLGetString(void* pRecBuffer, StringBuilder pDest, int ulLength);

…

}

PInvoke Type mapping

UnManaged Managed /C# Pointer types

void * IntPtr / void *

char* String

StringBuilder

int

int*

System.Int32

IntPtr / int *

short

short*

System.Int16

IntPtr / short*

“unsafe” code is in fact “safe”

Record buffer and passing offsetsAllocate Global :byte [] m_RecordBuffer = new byte[uRecordBufferSize);

IntPtr m_pRecordBuffer = Marshal.AllocHGlobal((int)uRecordBufferSize);

Bind Offsets : fixed (byte* pData = &m_RecordBuffer[uOffset])

fixed (byte* pNullInd = &m_RecordBuffer[uNullOffset])

{

//Bind offsets

}

byte* pData = ((byte*)m_pRecordBuffer.ToPointer()+uOffset);

byte* pNullInd = ((byte*)m_pRecordBuffer.ToPointer() + uNullOffset);

“unsafe” code is in fact “safe”

Copy and access data: Marshal.Copy(m_pRecordBuffer, m_RecordBuffer, (int)0, (int)m_uRecordBufferSize);

fixed (byte* ptr = &RecordBuffer[Col.uNullOffset] )

Ind = *(Int32*)ptr;

if ( Ind != -1 )

fixed (byte *pRecBuffer = &RecordBuffer[Col.uOffset])

retcode = BdpMYDB.SQLGetString(pRecBuffer, Data, Ind);

Free Global: Marshal.FreeHGlobal(m_pRecordBuffer);

“unsafe” code is in fact “safe”

Parameter BindingSet Parameter :

newParam.pData = Marshal.AllocHGlobal(uLen + sizeof(Int32) );

//Get Length as an array of bytes

byte[] len = BitConverter.GetBytes((Int32)uLen);

//Copy Length

Marshal.Copy(len, 0, newParam.pData, len.Length);

//Copy data

Marshal.Copy(Src, 0, (IntPtr)((byte*)newParam.pData.ToPointer()+len.Length), Src.Length);

Get Parameter :byte* ptr = (byte*)newParam.pData.ToPointer();

Len = *(Int32*)ptr;

byte *pDataBuffer = (byte*)newParam.pData.ToPointer()+sizeof(Int32);

Naming convention

PrvProvider.cs– PrvConnection: ISQLConnection– PrvCommand: ISQLCommand– PrvCusor: ISQLCursor– PrvConnectionString : BdpConnectionString

PrvMetaData.cs– PrvMetaData : ISQLMetadata– BdpMetaDataHelper

PrvResolver.cs– PrvResolver : ISQLResolver– BDPResolverHelper

PrvSchemaCreate.cs– PrvSchemaCreate: ISQLSchemaCreate

Why BDP.NET

Open Architecture: Lets you add support to more DB’s easily

Portable code : Write ones and connect to all DB’s

Logical Data types mapped to .NET Native types

Consistent data type mapping across DB’s

Unlike OLEDB .NET Provider need not go through a COM interop layer.

Support for Database specific features

Supports metadata, schema creation and data migration services

Cross platform availability (may be)

BDP supported RDBMS

INTERBASE 7, 7.5

ORACLE 9i,10g

DB2 V 7.2, 8.x

MSSQL 2000 / MSDE

MSAccess

Sybase 12.5

.NET DataStore (EBU)

MySQL (3rd Party)

DB2 AS400 (3rd Party)

Advantage Database (3rd Party)

and more to follow…

Ramesh.Theivendran@borland.com