DbConnectionDataProvider Class

In This Topic

Abstract base class for data providers accessing the data of an IDbConnection. The concrete implementations in this assembly are the OleDbConnectionDataProvider Class, the OracleConnectionDataProvider Class and the SqlConnectionDataProvider Class. You can add your own custom IDbConnection consuming descendants if you can access the database schema information (tables and relations) for your .NET data provider. This class is serializable.

In order to write your own descendant, override the Init() method and pass all tables and relations to the protected Provider member. See the example below for how this is done in the SqlConnectionDataProvider Class. As long as your provider conforms to ISO SQL, you shouldn't need to change anything else, the base class takes care of the entire SQL handling automatically.

Override the OnTranslateFilterSyntax method in order to change the default handling for filter syntax translation. See IAdvancedFiltering Interface for details and see the example below for how this method is overridden in the OracleConnectionDataProvider Class. In this case, just the handling of the SUBSTRING SQL function is changed, as the name of this function is SUBSTR in Oracle.

Object Model

Syntax

Visual Basic
C#
C++/CLI

'Declaration
 
Public MustInherit Class DbConnectionDataProvider 
   Implements ICanHandleUsedIdentifiers, IDataProvider, combit.Reporting.ISupportsLogger

public abstract class DbConnectionDataProvider : ICanHandleUsedIdentifiers, IDataProvider, combit.Reporting.ISupportsLogger

public ref class DbConnectionDataProvider abstract : public ICanHandleUsedIdentifiers, IDataProvider, combit.Reporting.ISupportsLogger

Example

protected override void Init()
{
    if (Initialized)
        return;

    List<String> passedRelationNames = new List<string>();

    Connection.Open();
    try
    {
        DataTable dt = (Connection as SqlConnection).GetSchema("Tables");

        foreach (DataRow dr in dt.Rows)
        {
            string tableSchema = dr["TABLE_SCHEMA"].ToString();
            string tableType = dr["TABLE_TYPE"].ToString();
            string parentTableName = dr["TABLE_NAME"].ToString();          
            switch (tableType)
            {
                case "BASE TABLE":
                    if ((SupportedElementTypes & DbConnectionElementTypes.Table) == 0)
                        continue;
                    break;
                case "VIEW":
                    if ((SupportedElementTypes & DbConnectionElementTypes.View) == 0)
                        continue;
                    break;
                default:
                    continue;
            }

            // pass table
            SqlConnection newConnection = DbCommandSetDataProviderHelper.CloneConnection(Connection) as SqlConnection;
            SqlCommand cmd = new SqlCommand("Select * From \"" + (String.IsNullOrEmpty(tableSchema) ? parentTableName+"\"" : tableSchema + "\".\"" + parentTableName) + "\"", newConnection);
            Provider.AddCommand(cmd as IDbCommand, parentTableName);

            // pass relations
            string commandText = "sp_fkeys @pktable_name = '" + parentTableName + "'";
            if (!String.IsNullOrEmpty(tableSchema))
            {
                commandText+= ", @pktable_owner = '"+tableSchema+ "'";
            }
            cmd = new SqlCommand(commandText, Connection as SqlConnection);
            SqlDataReader reader = cmd.ExecuteReader();
            
            while (reader.Read())
            {
                string childColumnName = reader.GetSqlString(7).ToString();
                string parentColumnName = reader.GetSqlString(3).ToString();

                // combined primary key, add key field to last relation on stack
                if (reader.GetInt16(8) > 1)
                {
                    IDataProvider providerInterface = (Provider as IDataProvider);
                    DbCommandTableRelation lastRelation = providerInterface.Relations[providerInterface.Relations.Count - 1] as DbCommandTableRelation;
                    lastRelation.ChildColumnName += '\t' + childColumnName;
                    lastRelation.ParentColumnName += '\t' + parentColumnName;
                    continue;
                }
                string childTableName = reader.GetSqlString(6).ToString();
                string relName = reader.GetSqlString(2).ToString() + "2" + childTableName;
                int relationIndex = 1;
                string formatString = relName+"{0}";

                while (passedRelationNames.Contains(relName))
                {
                    relName = String.Format(formatString, relationIndex);
                    relationIndex++;
                }
                passedRelationNames.Add(relName);
                Provider.AddRelation(relName, parentTableName, childTableName, parentColumnName, childColumnName);                        
            }
            reader.Close();
        }
    }
    finally
    {
        Connection.Close();
        Initialized = true;
    }
}

VB.NET

Protected Overloads Overrides Sub Init()
    If Initialized Then
        Return
    End If

    Dim passedRelationNames As List(Of [String]) = New List(Of String)()

    Connection.Open()
    Try
        Dim dt As DataTable = (TryCast(Connection, SqlConnection)).GetSchema("Tables")

        For Each dr As DataRow In dt.Rows
            Dim tableSchema As String = dr("TABLE_SCHEMA").ToString()
            Dim tableType As String = dr("TABLE_TYPE").ToString()
            Dim parentTableName As String = dr("TABLE_NAME").ToString()

            Select Case tableType
                Case "BASE TABLE"
                    If (SupportedElementTypes And DbConnectionElementTypes.Table) = 0 Then
                        Continue Select
                    End If
                    Exit Select
                Case "VIEW"
                    If (SupportedElementTypes And DbConnectionElementTypes.View) = 0 Then
                        Continue Select
                    End If
                    Exit Select
                Case Else
                    Continue Select
            End Select

            ' pass table
            Dim newConnection As SqlConnection = TryCast(DbCommandSetDataProviderHelper.CloneConnection(Connection), SqlConnection)
            Dim cmd As New SqlCommand("Select * From """ + (If([String].IsNullOrEmpty(tableSchema), parentTableName + """", tableSchema + """.""" + parentTableName)) + """", newConnection)
            Provider.AddCommand(TryCast(cmd, IDbCommand), parentTableName)

            ' pass relations
            Dim commandText As String = "sp_fkeys @pktable_name = '" + parentTableName + "'"
            If Not [String].IsNullOrEmpty(tableSchema) Then
                commandText += ", @pktable_owner = '" + tableSchema + "'"
            End If

            cmd = New SqlCommand(commandText, TryCast(Connection, SqlConnection))
            Dim reader As SqlDataReader = cmd.ExecuteReader()

            While reader.Read()
                Dim childColumnName As String = reader.GetSqlString(7).ToString()
                Dim parentColumnName As String = reader.GetSqlString(3).ToString()

                ' combined primary key, add key field to last relation on stack
                If reader.GetInt16(8) > 1 Then
                    Dim providerInterface As IDataProvider = (TryCast(Provider, IDataProvider))
                    Dim lastRelation As DbCommandTableRelation = TryCast(providerInterface.Relations(providerInterface.Relations.Count - 1), DbCommandTableRelation)
                    lastRelation.ChildColumnName += ControlChars.Tab + childColumnName
                    lastRelation.ParentColumnName += ControlChars.Tab + parentColumnName
                    Continue While
                End If
                Dim childTableName As String = reader.GetSqlString(6).ToString()
                Dim relName As String = reader.GetSqlString(2).ToString() + "2" + childTableName
                Dim relationIndex As Integer = 1
                Dim formatString As String = relName + "{0}"

                While passedRelationNames.Contains(relName)
                    relName = [String].Format(formatString, relationIndex)
                    System.Math.Max(System.Threading.Interlocked.Increment(relationIndex),relationIndex - 1)
                End While
                passedRelationNames.Add(relName)
                Provider.AddRelation(relName, parentTableName, childTableName, parentColumnName, childColumnName)
            End While
            reader.Close()
        Next
    Finally
        Connection.Close()
        Initialized = True
    End Try
End Sub

protected override void OnTranslateFilterSyntax(object sender, TranslateFilterSyntaxEventArgs e)
{
    switch (e.ArgumentType)
    {
        case LlExpressionArgumentType.Function:
            switch (e.Name.ToString().ToUpper())
            {
                case "MID$":
                    if (e.ArgumentCount == 2)
                        e.Result = (String.Format("(SUBSTR({0},{1}))", e.Argument1.ToString(), e.Argument2.ToString() + "+1"));
                    else
                        e.Result = (String.Format("(SUBSTR({0},{1},{2}))", e.Argument1.ToString(), e.Argument2.ToString() + "+1", e.Argument3.ToString()));
                    break;
                case "LEFT$":
                    e.Result = (String.Format("(SUBSTR({0},0,{1}))", e.Argument1.ToString(), e.Argument2.ToString()));
                    break;
                case "RIGHT$":
                    e.Result = (String.Format("(SUBSTR({0},-{1},{1}))", e.Argument1.ToString(), e.Argument2.ToString()));
                    break;
                default:
                    e.Result = null;
                    break;
            }
            break;
        default:
            e.Result = null;
            break;
    }
}

Inheritance Hierarchy

System.Object
   combit.Reporting.DataProviders.DbConnectionDataProvider
      combit.Reporting.DataProviders.CDataDataProvider
      combit.Reporting.DataProviders.DB2ConnectionDataProvider
      combit.Reporting.DataProviders.OdbcConnectionDataProvider
      combit.Reporting.DataProviders.OleDbConnectionDataProvider
      combit.Reporting.DataProviders.OracleConnectionDataProvider
      combit.Reporting.DataProviders.SqlConnectionDataProvider
      combit.Reporting.DataProviders.SQLiteConnectionDataProvider

Requirements

Reference

DbConnectionDataProvider Members
combit.Reporting.DataProviders Namespace

Send Feedback