Posts tagged with c - page 11

Parameterising the IN clause of an SQL SELECT in .NET

I’m a fan of parameterized queries with a strong dislike for building SQL (or other magic strings). Encoding, escaping errors, localization formatting problems and injection can run rampant 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

.NET quick samples: Up-times, ages, rounding to n places

Just a few quick .NET samples for performing some common tasks that the .NET Framework doesn’t do for you:

System uptime

using System.Diagnostics;

public TimeSpan GetUptime() {
    var systemUpTime = new PerformanceCounter("System", "System Up Time");
    systemUpTime.NextValue(); // Required to work!
    return TimeSpan.FromSeconds(systemUpTime.NextValue())));
}

Calculating age

public int GetAge(DateTime birthday) { 
    int years = DateTime.Now.Year - birthday.Year;
    return (birthday.DayOfYear >= DateTime.Now.DayOfYear) ? years : years - 1;
}

Rounding to n decimal places

public decimal ArithmeticRound(decimal d, int decimals) {
    var power = (decimal)Math.Pow(10, decimals);
    return (decimal.Floor((Math.Abs(d) * power) + 0.4m) / power) * Math.Sign(d);
}

[)amien

Extending GridView to access generated columns

ASP.NET’s GridView is a useful control and one of it’s best features is it’s ability to generate the columns automatically from the data source given.

The problem however is that these generated columns are not exposed as part of the Columns collection or indeed available at all so you can’t hide or manipulate the selected columns.

One simple scenario might be that you want the first column to be a “View” link to drill down into the row displayed. Whilst you can add the column to the GridView before data binding you can’t actually pull out the information needed from another columns to construct the URL.

By sub-classing GridView you can obtain this functionality with some caveats.

Version 1: Auto generated columns added to the Columns collection… with caveats.

using System;
using System.Data;
using System.Collections;
using System.Web.UI.WebControls;

public class GridViewEx1 : GridView
{
    private DataControlFieldCollection originalColumns;

    public GridViewEx1() : base() {
    }

    public void RecordColumns() {
        originalColumns = new DataControlFieldCollection();
        foreach(DataControlField column in Columns)
            originalColumns.Add(column as DataControlField);
    }

    public void ResetColumns() {
        if (originalColumns == null)
            RecordColumns();
        else {
            Columns.Clear();
            foreach(DataControlField column in originalColumns)
                Columns.Add(column as DataControlField);
        }
    }

    protected override ICollection CreateColumns(PagedDataSource dataSource, bool useDataSource) {
        ResetColumns();
        ICollection generatedColumns = base.CreateColumns(dataSource, useDataSource);
        foreach(DataControlField column in generatedColumns)
            if (!originalColumns.Contains(column))
                Columns.Add(column as DataControlField);
        return Columns;
    }
}

This version provides some compatibility with existing code/expectations in that the auto-generated columns are part of the Columns collection after the DataBind.

Should you call DataBind again however as well as wiping out the changes to the generated columns (they are, after all re-generated) any additional columns added to the collection after the first DataBind will also be lost as it does not track which are added by the programmer and which automatically.

Version 2: All bound columns exposed as BoundColumns, user ones as Columns.

using System;
using System.Data;
using System.Collections;
using System.Web.UI.WebControls;

public class GridViewEx2 : GridView
{
    private DataControlFieldCollection boundColumns = new DataControlFieldCollection();

    public GridViewEx2() : base() {
    }

    public DataControlFieldCollection BoundColumns {
        get { return boundColumns; }
    }

    protected override ICollection CreateColumns(PagedDataSource dataSource, bool useDataSource) {
        ICollection generatedColumns = base.CreateColumns(dataSource, useDataSource);
        BoundColumns.Clear();
        foreach (DataControlField column in generatedColumns)
            BoundColumns.Add(column as DataControlField);
        return BoundColumns;
    }
}

After the DataBind you will have full access to the generated columns as part of the BoundColumns collection.

[)amien

Equatable Weak References

In a previous post I described a [WeakReference](//damieng.com/blog/2006/08/01/ImplementingWeakReferenceT) class for providing strongly-typed [WeakReference](http://msdn2.microsoft.com/en-us/library/system.weakreference.aspx) objects.

GitHub has the latest version of EquatableWeakReference</a> </p> One problem with the previous WeakReference class is being able to use and find it within the various collection classes. This is because one WeakReference is not equal to another WeakReference class. Overriding the Equals method fixes this problem at first glance however also reveals another issue. If you override Equals you should also override the GetHashCode method so that two objects that equal each other return the same hash code. This is because some of the collection classes use hash codes to efficiently lookup items within their collection. Normally a hash code would be calculated from the various data items that comprise the class but in our case we really only have one to go on – the Target object itself. This raises two more issues: 1. The hash code should not change over the objects lifetime – difficult when your Target object can be changed. 2. The hash code should be stored because the Target object might well be collected by the GC – after all that’s what this class is all about. This doesn’t leave us with many choices at all. We must grab the hash code from the Target object within our constructor and store it for subsequent retrieval. Here is EquatableWeakReference with the usual disclaimers as to it’s suitability for any purpose. ```csharp using System; using System.Runtime.InteropServices; public class EquatableWeakReference : IEquatable<EquatableWeakReference>, IDisposable where T : class { protected GCHandle handle; protected int hashCode; public EquatableWeakReference(T target) { if (target == null) throw new ArgumentNullException("target"); hashCode = target.GetHashCode(); InitializeHandle(target); } protected virtual void InitializeHandle(T target) { handle = GCHandle.Alloc(target, GCHandleType.Weak); } ~EquatableWeakReference() { Dispose(); } public void Dispose() { handle.Free(); GC.SuppressFinalize(this); } public virtual bool IsAlive { get { return (handle.Target != null); } } public virtual T Target { get { object o = handle.Target; if ((o == null) || (!(o is T))) return null; else return (T)o; } } public override bool Equals(object other) { if (other is EquatableWeakReference) return Equals((EquatableWeakReference)other); else return false; } public override int GetHashCode() { return hashCode; } public bool Equals(EquatableWeakReference other) { return ReferenceEquals(other.Target, this.Target); } } ``` *[)amien*