Archive for SQL tag

DLookup for Excel

February 28th 2008 • Microsoft (, , ) • 3,063 views • 2 responses

I had to do a couple of ad-hoc Excel jobs today and found that whilst Excel has a VLookup function for spreadsheet/ranges it doesn’t have one for databases.

It’s been a while since I touched VBA and Access but the DLookup function was quite useful so here it is for Excel. Read the warnings below before you use it!


Public Function DLookup(expression As String, domain As String, criteria As String, Optional connectionString As String)

    Dim con As ADODB.connection
    Set con = New ADODB.connection
    If IsMissing(connectionString) Then
        con.Open "Driver={SQL Server};Server=abc;Database=def;Trusted_Connection=Yes;"
    Else
        con.Open connectionString
    End If

    Dim rs As ADODB.Recordset
    Dim sql As String
    sql = "SELECT TOP 1 " + expression + " FROM " + domain + " WHERE " + criteria
    Set rs = con.Execute(sql)
    If Not rs.EOF Then DLookup = rs(0)
    rs.Close
    Set rs = Nothing

    con.Close
    Set con = Nothing

End Function

Copy and paste the code above into an Excel module and replace the “Driver=… with what you want to be your default connection.

Some examples of use:

To select the Name column from the Customers table where the numeric ID is held in column 2.


=DLookup("Name","Customers","ID=" & A2)

To return Forename and Surname columns joined together with a space from the Contacts table in the crm database using the postcode from column 5


=DLookup("Forname + ' ' + Surname","crm..Contact","PostCode='" & A5 & "'")

To return Description from a Products table held in another connection using a partial like match from column 1:


=DLookup("Description","Products","OrderCode LIKE '" & A1 & "%'", "DSN=SecondaryDSN")

I think you get the idea!

There are a few caveats you need to be aware of using this function:

  • It only returns the first value of the first row
  • DLookup’s are not efficient opening a connection each time it is referenced. This could put a lot of strain on your server.

  • DLookup syntax is open-ended and has some attack vectors.

If that doesn’t scare you then give it a shot. As usual no warranty expressed or implied!

[)amien

Parameterising the IN clause of an SQL SELECT in .NET

October 25th 2006 • .NET (, ) • 1,109 views • 5 responses

I’m a fan of parameterized queries with a strong dislike for building SQL (or other magic strings). Eencoding /escaping errors, localization formatting problems and injection can run rumpant when you think everything is a string.

Problem

Today I found myself a quandary as I needed to SELECT records based on a list of values I had. e.g.

SELECT * FROM Products WHERE ProductCode IN ('ABC123', 'DEF456', 'GHI789')

At first glance the relevant parameterized version might look like:

SELECT * FROM Products WHERE ProductCode IN (@productlist)

The problem here however is that if you put a comma-separate list of items into a string parameter named @productlist then it sends this to the database server:

SELECT * FROM Products WHERE ProductCode IN ('ABC123, DEF456, GHI789')

That’s not what we want at all.

This hack isn’t pretty and it has some limitations:

  • Only works with named parameters
  • Could upset some DB providers that don’t like having command parameters removed or command text modified
  • Parameter name to replace must be totally unique – i.e. not exist as a subset of another parameter name
  • Only preserves the basic IDbCommand declared properties

It should however work across DB providers and types.

Usage

The previous example would mean we use exactly the expected parameterized version with @productlist in place.

Add the parameter as you’d expect but instead of assigning a string/numeric to it assign something IEnumerable.

Finally call this method against the command and parameter before you execute it for the ‘magic’ to happen:

The ‘magic’

public void ExpandDbArrayParameter(IDbCommand cmd, IDbDataParameter parameter) {
    if (parameter.Value is IEnumerable) {
        int index = 0;
        StringBuilder newParameterSQL = new StringBuilder();
        foreach(Object value in (IEnumerable) parameter.Value) {
            String valueParameterName = String.Format("{0}{1}", parameter.ParameterName, ++index);
            IDataParameter valueParameter = cmd.CreateParameter();
            valueParameter.DbType = parameter.DbType;
            valueParameter.Direction = parameter.Direction;
            valueParameter.ParameterName = valueParameterName;
            valueParameter.SourceColumn = parameter.SourceColumn;
            valueParameter.SourceVersion = parameter.SourceVersion;
            valueParameter.Value = value;
            cmd.Parameters.Add(valueParameter);

            if (index == 1)
                newParameterSQL.Append(valueParameterName);
            else
                 newParameterSQL.Append("," + valueParameterName);
        }
        cmd.Parameters.Remove(parameter);
        cmd.CommandText = cmd.CommandText.Replace(parameter.ParameterName, newParameterSQL.ToString());
    }
}

[)amien