RSS

A workaround to use SQL IN clause in TableAdapters

22 May

This article is a simple implementation on how to use parameterized SQL query with IN clause or unknown number of parameters in TableAdapter.

When modifying those ancient database access codes, SQL query strings appear in the form of concatenated string very often. These codes are hard to read, maintain and prone to SQL Injection. Using DataSet Designer can easily improve it by accessing database with parameterized commands. But DataSet Designer does not support automatically generating commands with parameterized IN clause.

The idea of this article is originated from Extending TableAdapters for Dynamic SQL on The Code Project. The article demonstrate a way to change the command text programmatically by extending the TableAdapter which is defined as a partial class from the DataSet Designer. What’s not covered is how to parameterized the command.

The parameterized approach I’m going to demonstrate overloads the Fill method which is created automatically by DataSet Designer. The steps are as follows:
1. Define the TableAdapter in the DataSet Designer like what we do normally without the IN clause in the command text. Here is the command text for demonstration:

SELECT P.*
  FROM PRODUCTS P
 WHERE (PRODUCTNAME = :ProductName)
   AND (PRICE = :Price)

2. Double click on the ProductsTableAdapter and it will create the DataSetTest.vb automatically.

3. Find the automatically generated Fill method in the DataSetTest.Designer.vb. Copy it into the Partial Class ProductsTableAdapter we just created.

Public Overridable Overloads Function Fill(ByVal dataTable As DataSetTest.ProductsDataTable, ByVal ProductName As Decimal, ByVal Price As Decimal) As Integer
    Me.Adapter.SelectCommand = Me.CommandCollection(0)
    Me.Adapter.SelectCommand.Parameters(0).Value = CType(ProductName, Decimal)
    Me.Adapter.SelectCommand.Parameters(1).Value = CType(Price, Decimal)
    If (Me.ClearBeforeFill = True) Then
        dataTable.Clear()
    End If
    Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
    Return returnValue
End Function

4. Now we work on this overloaded Fill method. First add a string parameter “columnName” for the table column name which we want to compare in the SQL IN clause, a string List “constraintList” of column values. Next modify the method as follows:

Partial Public Class ProductsTableAdapter

    Public Overridable Overloads Function Fill(ByVal dataTable As DataSetTest.ProductsDataTable, _
                                               ByVal ProductName As Decimal, _
                                               ByVal Price As Decimal, _
                                               ByVal columnName As String, _
                                               ByVal constraintList As List(Of String)) As Integer

        ' Me.CommandCollection(0) --> Me.CommandCollection(0).Clone()
        ' To retain the original command
        Me.Adapter.SelectCommand = Me.CommandCollection(0).Clone()
        ' -----------------------------------------------------------

        Me.Adapter.SelectCommand.Parameters(0).Value = CType(ProductName, Decimal)
        Me.Adapter.SelectCommand.Parameters(1).Value = CType(Price, Decimal)

        ' Modify command text and add parameters for IN clause
        Dim inClause As String = " AND " & columnName & " IN ("
        Dim i As Integer = 0
        For Each constraint As String In constraintList
            inClause = inClause & "ID" & i & ", "
            Me.Adapter.SelectCommand.Parameters.Add(New OracleClient.OracleParameter("ID" & i, constraint))
            i = i + 1
        Next
        inClause = inClause.Remove(inClause.Length - 2) & ")"

        Me.Adapter.SelectCommand.CommandText = Me.Adapter.SelectCommand.CommandText & inClause
        ' ----------------------------------------------------

        If (Me.ClearBeforeFill = True) Then
            dataTable.Clear()
        End If
        Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
        Return returnValue

    End Function

End Class

Here let’s have a look at the modifications:

Me.Adapter.SelectCommand = Me.CommandCollection(0).Clone()

The purpose of changing this line is to retain the original command which is used for the original Fill method. Notice that command is reference type. Since we’re going to modify the command text and command parameters, refer the Me.Adapter.SelectCommand to Me.CommandCollection(0) without cloning it will result in changing the Me.CommandCollection(0) and make the original Fill method unable to be used. Also we want to retain the original command since the number of parameters in the IN clause may change when we call this overloaded method. In this approach we modify the original command for the overloaded Fill method every time we call it.

Dim inClause As String = " AND " & columnName & " IN ("
Dim i As Integer = 0
For Each constraint As String In constraintList
    inClause = inClause & "ID" & i & ", "
    Me.Adapter.SelectCommand.Parameters.Add(New OracleClient.OracleParameter("ID" & i, constraint))
    i = i + 1
Next
inClause = inClause.Remove(inClause.Length - 2) & ")"

Me.Adapter.SelectCommand.CommandText = Me.Adapter.SelectCommand.CommandText & inClause

This code segment append the IN clause to the command text and add parameters to the command.

After all the modification finished, we can now use this overloaded Fill method as follows:

Dim dataTableTest As New DataSetTest.ProductsDataTable
Dim productsAdapter As New DataSetTestTableAdapters.ProductsTableAdapter

Dim productName As String = "Goggle"
Dim price As Decimal = 299
Dim categoryList As New List(Of String)
categoryList.Add("Motor Sports")
categoryList.Add("Ski")

productsAdapter.Fill(dataTableTest, productName, price, "Category", categoryList)

References:
Extending TableAdapters for Dynamic SQL
How can I get tableadapter to process dynamic sql ‘select’ queries?
How to pass unknown number of parameters to sql query from C#?
Parameterizing an SQL IN clause?

Advertisements
 
Leave a comment

Posted by on May 22, 2012 in .NET, ADO.NET, Oracle, SQL, VB

 

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: