1 / 33

Integrating into the Borland Data Provider (BDP)

Integrating into the Borland 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.

hinda
Download Presentation

Integrating into the Borland Data Provider (BDP)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Integrating into theBorland Data Provider (BDP) Ramesh Theivendran Borland Software Corporation

  2. Agenda • Borland Data Provider (BDP) Architecture • BDP core Interfaces • BDP metadata services • Helper Classes • Managed to Unmanaged

  3. .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

  4. .NETData Provider Architecture .NET Client D O T N E T OLEDB Managed BDP Managed SQL Server Managed COM Interop layer C O M OleDb Provider OleDb Provider TDS GDS32 OCI DB client MSSQL IB ORACLE RDBMS

  5. BDP Components Data Explorer BDP Designers ISQLConnection ISQLCommand ISQLCursor ISQLMetaData ISQLResolver ISQLSchemaCreate ISQLDataSource bdpDatasources.xml bdpConnections.xml DB client wrapper DB

  6. BDP for .NET • Namespace Borland.Data.Provider • BdpConnection • BdpTransaction • BdpCommand • BdpDataReader • BdpDataAdapter • BdpCommandBuilder • Namespace Borland.Data.Design

  7. BDP for .NET • Namespace Borland.Data.Common • BdpParameter, BdpParameterCollection • BdpColumn, BdpColumnCollection • BdpError, BdpErrorCollection • BdpException • ISQLConnection • ISQLCommand • ISQLCusor

  8. BDP for .NET • Namespace Borland.Data.Schema • ISQLDataSource • ISQLMetaData • ISQLResolver • ISQLSchemaCreate

  9. BDP development tasks • choose the DB Client • Build your unmanaged layer as a DLL • Create a class with static methods exporting all DLL entry point • Implement BDP core interfaces • ISQLConnection • ISQLCommand • ISQLCursor • Implement BDP metadata interfaces • ISQLMetaData • ISQLResolver • ISQLSchemaCreate • Extend BDPConnectionString • Add provider assembly info. to BdpDataSources.xml

  10. ISQLConnection • public 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); • }

  11. BdpConnection • Establishing 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

  12. 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

  13. 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); • }

  14. 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 procedure • BdpCommand.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()

  15. BdpCommand • BdpCommand.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.

  16. 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 ); • }

  17. MetaData Services • MetaData retrieval • Schema Creation • Create Table,View,Indices • Alter Table • Drop Table, View, Indices • Data Migration • BdpCopyTable component • SourceCommand • Destination • DestinationTable

  18. MetaData Properties • public enum MetaDataProps • { • CatalogName = 0, • SchemaName = 1, • DatabaseName = 2, • …… • ObjectQuoteChar = 11, • ObjectName = 17, • QualifiedName = 18, • QuotedObjectName = 19, • QuoteObjects = 20, • ObjectQuoteSuffix = 21 • };

  19. 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); • };

  20. 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); • }

  21. 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); • }

  22. Helper classes • BDPMetaDataHelper • GetObjectList() • GetTables() • GetColumns() • GetProcedures() • GetSchemaTable() • ….. • GetDataTypes() • GetCreateTable() • GetSQLTypeMapping() • BDPResolverHelper • BDPSchemaCreationHelper

  23. Data Explorer - ISQLDataSource • public 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

  24. 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

  25. 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); • … • }

  26. PInvoke Type mapping

  27. “unsafe” code is in fact “safe” • Record buffer and passing offsets • Allocate 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);

  28. “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);

  29. “unsafe” code is in fact “safe” • Parameter Binding • Set 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);

  30. 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

  31. 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)

  32. 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…

  33. Ramesh.Theivendran@borland.com

More Related