Jun 18 Tommy | CodeBits

CodeBits: Object to SqlParameters Extension with Reflection

Don't get me wrong, code generators are a good thing.  However sometimes they generate pages and pages of code that are really redundant thanks to reflection.

Especially for generating a DAL for you application.  Consider the following extension:

 

public static List<SqlParamter> BuildParameters(this object obj)
{
    List<SqlParameter> collection = new List<SqlParameter>();

    foreach (PropertyInfo propertyInfo in obj.GetType().GetProperties())
    {
        if (propertyInfo.GetValue(obj, null) = null)
        {
            collection.Add(new SqlParameter(propertyInfo.Name, DBNull.Value));
        }
        else
        {
            collection.Add(new SqlParameter(propertyInfo.Name, propertyInfo.GetValue(obj, null)));
        }
    }

    return collection;
}

This will take any object, and create a list of sql parameters from all properties. This might sound a little overboard, but consider the following function.

 

public static void ExecuteNonQuery(string query, string connection, List<SqlParameter> parameters)
{
    using (SqlConnection connection = new SqlConnection(connection))
    {
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.CommandType = CommandType.Text;
            for (int i = 0; i < parameters.Count; i++)
            {
                command.Parameters.Add(parameters[i]);
            }

            command.Connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }
}

Now I've wrapped the sqlcommand class into a static helper. What I have done is basically removed any need for generated classes (or manually coded... ugh!) functions to create parameters for a sqlcommand. It would probably work for any DBCommand. The point is and DAL generated code is pointless because we can boil it down to a few functions thanks to reflection. Then usage would be something as follows :

 

 
public function Main()
{
    // Read object from Database or create new object
    // Then manipulate said object
    // Update or Insert object into the Database
    
    DalWrapper.ExecuteNonQuery(queryUpdateOrInsert, connection, obj.BuildParameters());
}

Of course my query should really be a store procedure name. At least my BLL / DAL has been condensed by about 75% of what the auto-generated code was, and makes adding new tables and records a breeze since all I really have to do is strong type my object and define the stored procedures.

Jun 16 Tommy | CodeBits

CodeBits: Export ICollection To Excel Compatable Format

I've expanded the generic collection handler to include allowing export to excel without the need for the gridview hack.

 

Little background: The gridview hack is to save a collection to the page first in a gridview. From there it is pretty easy to post the data back to the page as a cvs file. However that requires the gridview to be loaded first with all the data and a second post to occur. It is also not user friendly to display the gridview on a page if it has more then 15 columns or so. This function will help avoid that. Just add it to any class that inherits the CollectionBase class.

 

Side Note: A full postback must occur. So if you're going to do the export from a button within an AJAX update panel, make sure the panel has the button set for a full postback.

      
    public void ExportToXls(string fileName)
    {
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Clear();

        // Force as inline, otherwise browsers will think this is an .aspx file or not give it a filename
        HttpContext.Current.Response.AddHeader("content-disposition", "inline; filename=" + fileName);
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.Charset = string.Empty;

        HttpContext.Current.Response.Flush();

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                Table table = new Table();
                table.GridLines = GridLines.Both;

                TableRow header = new TableRow();

                // Use refection to give us a header row with the property names
                foreach (PropertyInfo propertyInfo in this[0].GetType().GetProperties())
                {
                    TableCell cell = new TableCell();
                    cell.Text = propertyInfo.Name;
                    header.Cells.Add(cell);                       
                }

                // Add some style to the header
                header.Style.Add(HtmlTextWriterStyle.BackgroundColor, "#008080");
                header.Style.Add(HtmlTextWriterStyle.Color, "#ffffff");
                header.Style.Add(HtmlTextWriterStyle.TextAlign, "center");

                table.Rows.Add(header);

                for (int i = 0; i < this.Count; i++)
                {
                    TableRow dataRow = new TableRow();

                    foreach (PropertyInfo propertyInfo in this[i].GetType().GetProperties())
                    {
                        TableCell cell = new TableCell();

                        // Error check incase the property is null
                        if (propertyInfo.GetValue(this[i], null) == null)
                        {
                            cell.Text = string.Empty;
                        }
                        else
                        {
                            cell.Text = propertyInfo.GetValue(this[i], null).ToString();
                        }

                        dataRow.Cells.Add(cell);
                       
                    }

                    table.Rows.Add(dataRow);
                }

                table.RenderControl(htw);
                HttpContext.Current.Response.Write(sw.ToString());
            }
        }

        HttpContext.Current.Response.End();
    }  

DataCollectionV2.cs

Jul 23 Tommy | CodeBits

CodeBits: Using IComparer with Generics Collection

.Net is a really cool language. Two things I love about them are Generics and Collections. Now I will combine them and enhance it with IComparer and reflection to create the collection to end all collections. First we will create a generic collections class. Here is our collection:

public class DataCollection : CollectionBase
{
    public virtual T this[int index]
    {
        get { return (T)this.List[index]; }
        set { this.List[index] = value; }
    } 

    public virtual int IndexOf(T item)
    {
        return this.List.IndexOf(item);
    }

    public virtual int Add(T item)
    {
        return this.List.Add(item);
    }

    public virtual void Remove(T item)
    {
        this.List.Remove(item);
    }
 
    public virtual void CopyTo(Array array, int index)
    {
        this.List.CopyTo(array, index);
    }

    public virtual void AddRange(DataCollection collection)
    {
        this.InnerList.AddRange(collection);
    } 

    public virtual void AddRange(T[] collection)
    {
        this.InnerList.AddRange(collection);
    }

    public virtual bool Contains(T item)
    {
        return this.List.Contains(item);
    }

    public virtual void Insert(int index, T item)
    {
        this.List.Insert(index, item);
    }
}

Next we will add a general function to use for sorting objects in our collection.

*Note that this code is part of the collection class.

public void Sort(string sortExpression, SortDirection sortDirection)
{            
    if (_SortField == sortExpression)
    {                
        if (_SortDirection == SortDirection.Ascending)
        {
            _SortDirection = SortDirection.Descending;
        }
        else
        {
            _SortDirection = SortDirection.Ascending;
        }
    }
    else
    {
        _SortDirection = sortDirection;
        _SortField = sortExpression;
    }

    InnerList.Sort(new Comparer(_SortField, _SortDirection));
}

public void Sort(string sortExpression)
{
    _SortDirection = SortDirection.Ascending;            
    _SortField = sortExpression;
    InnerList.Sort(new Comparer(sortExpression));
}

private SortDirection _SortDirection = SortDirection.Ascending;
private String _SortField = string.Empty;

public SortDirection GetSortDirection { get { return _SortDirection; } }
public String GetSortField { get { return _SortField; } }

 

I have seen many sites have a collection like this. Then they implement an IComparer for each object in a collection so that you can sort the collection based on an member of the object in the collection. This is good if you were building something like a Data Access Layer for your application. However these require strong typed collections and objects. That would require a lot of coping and pasting if we wanted to implement an IComparer for each member of the object class. Or we can just use reflection and have one IComparer for all class members.

* Note that this code is a seperate class

public class Comparer : IComparer
{
    string m_SortPropertyName;
    SortDirection m_SortDirection;
    int direction;

    public Comparer(string sortPropertyName)
    {
        this.m_SortPropertyName = sortPropertyName;
        this.m_SortDirection = SortDirection.Ascending;
        direction = -1;
        // default to ascending order
    }

    public Comparer(string sortPropertyName, SortDirection sortDirection)
    {
        this.m_SortPropertyName = sortPropertyName;
        this.m_SortDirection = sortDirection;
        direction = -1;

        if (sortDirection == SortDirection.Descending) { direction = 1; }
    }
 
    public int Compare(object x, object y)
    {

        // Get the values of the relevant property on the x and y objects

        object valueOfX = x.GetType().GetProperty(m_SortPropertyName).GetValue(x, null);
        object valueOfY = y.GetType().GetProperty(m_SortPropertyName).GetValue(y, null);

        IComparable comp = valueOfY as IComparable;

        // Flip the value from whatever it was to the opposite so it sorts correctly.

        return Flip(comp.CompareTo(valueOfX));
    }

 
    private int Flip(int i)
    {
        return (i * direction);
    }
}

 

Now I know whare you are thinking. What would you do with this? Simple:

public void Test
{

    DataCollection collection = new DataCollection();
    //fill collection with dataset here.

    collection.Sort((fieldNameString), SortDirection)
}

 

We can now sort any collection of objects on any member of that object. There is something I like about not having to ever write another collection or sort function ever again, period.

DataCollection.cs

About

Mooglegiant.net is a site maintained by me (mooglegiant).  I occasionally put together blogengine.net themes, and random posts about tech/geek things.  If you like the site, or my work, don't forget to support me.  I'm sure you know where to click.