Monday, January 30, 2012

Best Extension Methods: Extensions for Datatable and Datareader

Best Extension Methods for DataTable and DataReader
public static class DataTableReaderExtension
{
    #region Select Distinct
    /// 
    /// "SELECT DISTINCT" over a DataTable
    /// 
    /// Input DataTable
    /// Fields to select (distinct)
    /// 
    public static DataTable SelectDistinct(this DataTable SourceTable, String FieldName)
    {
        return SelectDistinct(SourceTable, FieldName, String.Empty);
    }

    /// 
    ///"SELECT DISTINCT" over a DataTable
    /// 
    /// Input DataTable
    /// Fields to select (distinct)
    /// Optional filter to be applied to the selection
    /// 
    public static DataTable SelectDistinct(this DataTable SourceTable, String FieldNames, String Filter)
    {
        DataTable dt = new DataTable();
        String[] arrFieldNames = FieldNames.Replace(" ", "").Split(',');
        foreach (String s in arrFieldNames)
        {
            if (SourceTable.Columns.Contains(s))
                dt.Columns.Add(s, SourceTable.Columns[s].DataType);
            else
                throw new Exception(String.Format("The column {0} does not exist.", s));
        }

        Object[] LastValues = null;
        foreach (DataRow dr in SourceTable.Select(Filter, FieldNames))
        {
            Object[] NewValues = GetRowFields(dr, arrFieldNames);
            if (LastValues == null || !(ObjectComparison(LastValues, NewValues)))
            {
                LastValues = NewValues;
                dt.Rows.Add(LastValues);
            }
        }

        return dt;
    }
    #endregion

    #region Private Methods
    private static Object[] GetRowFields(DataRow dr, String[] arrFieldNames)
    {
        if (arrFieldNames.Length == 1)
            return new Object[] { dr[arrFieldNames[0]] };
        else
        {
            ArrayList itemArray = new ArrayList();
            foreach (String field in arrFieldNames)
                itemArray.Add(dr[field]);

            return itemArray.ToArray();
        }
    }

    /// 
    /// Compares two values to see if they are equal. Also compares DBNULL.Value.
    /// 
    /// Object A
    /// Object B
    /// 
    private static Boolean ObjectComparison(Object a, Object b)
    {
        if (a == DBNull.Value && b == DBNull.Value) //  both are DBNull.Value
            return true;
        if (a == DBNull.Value || b == DBNull.Value) //  only one is DBNull.Value
            return false;
        return (a.Equals(b));  // value type standard comparison
    }

    /// 
    /// Compares two value arrays to see if they are equal. Also compares DBNULL.Value.
    /// 
    /// Object Array A
    /// Object Array B
    /// 
    private static Boolean ObjectComparison(Object[] a, Object[] b)
    {
        Boolean retValue = true;
        Boolean singleCheck = false;

        if (a.Length == b.Length)
            for (Int32 i = 0; i < a.Length; i++)
            {
                if (!(singleCheck = ObjectComparison(a[i], b[i])))
                {
                    retValue = false;
                    break;
                }
                retValue = retValue && singleCheck;
            }

        return retValue;
    }
    #endregion
    /// 
    /// Checks if a column exists in the DataReader
    /// 
    /// DataReader
    /// Name of the column to find
    /// Returns true if the column exists in the DataReader, else returns false
    public static Boolean ColumnExists(this IDataReader dr, String ColumnName)
    {
        for (Int32 i = 0; i < dr.FieldCount; i++)
            if (dr.GetName(i).Equals(ColumnName, StringComparison.OrdinalIgnoreCase))
                return true;

        return false;
    }




}

Compiled By: Rajesh Rolen

Share This!


No comments:

Powered By Blogger · Designed By Seo Blogger Templates