Archive for February, 2010

The IQToolkit provides you with a framework for building your own LINQ providers. The toolkit comes with providers for Microsoft SQL, Microsoft SQL CE, Access, MySQL, and SQLite.

This post will show you how to use the toolkit to talk to Microsoft SQL. Later, I’ll be attempting to write my own providers for DB2 and Interbase 7.1.

Download the toolkit
First thing to do is download the IQToolkit (version 0.16a as of this posting).

Reference the toolkit
I setup a console project to test the toolkit. Within your project, create a reference to the IQToolkit.Data.dll and the IQToolkit.dll. Then, add a reference to the dll for the provider you wish to use. In this case, we are using the IQToolkit.Data.SqlClient.dll for Microsoft SQL.

Table objects
Next, you will need to create a class object for each table you need to work with in the database. I have a table that has two fields: PLCID and SummaryProdMode. Create a class object to hold these properties and then use the Column attribute to bind the property to the table column.

Imports IQToolkit.Data.Mapping

Public Class Machine

    Private _id As Integer
    Private _mode As Boolean

    <Column(IsPrimaryKey:=True)> _
    Public Property PLCID() As Integer
        Get
            Return _id
        End Get
        Set(ByVal value As Integer)
            _id = value
        End Set
    End Property

    <Column()> _
    Public Property SummaryProdMode() As Boolean
        Get
            Return _mode
        End Get
        Set(ByVal value As Boolean)
            _mode = value
        End Set
    End Property

End Class

DataContext
The data context contains read only properties for each database table being used. In this case, I’m dealing with the DCM_Machines table.

Imports IQToolkit
Imports IQToolkit.Data.Mapping
Imports System.Linq

Public Class MachineDataContext

    Private _provider As IEntityProvider

    Public Property EntityProvider() As IEntityProvider
        Get
            Return _provider
        End Get
        Set(ByVal value As IEntityProvider)
            _provider = value
        End Set
    End Property

    <Table()> _
    Public ReadOnly Property Machines() As IEntityTable(Of Machine)
        Get
            Return EntityProvider.GetTable(Of Machine)("DCM_Machines")
        End Get
    End Property

    Public Sub New(ByVal provider As IEntityProvider)

        _provider = provider

    End Sub

End Class

Using the provider
Use the following code to setup and use the provider with LINQ.

Imports IQToolkit.Data

Module Module1

    Sub Main()

        Dim provider = DbEntityProvider.From( _
            "IQToolkit.Data.SqlClient", _
            "YOUR_CONNECTION_STRING", _
            "Machine")
        Dim db = New MachineDataContext(provider)

        Dim machs = (From m In db.Machines _
                    Select m).ToList()

        For Each m In machs

            Console.WriteLine("id=" & m.PLCID)

        Next

        Console.ReadLine()

    End Sub

End Module