SubString in an ADO.NET DataSet - (DataColumn Expression)

One cool feature that I just love are the expression based columns in ADO.NET. I do not use them often but when I do find cause to use them, they are very useful. (Not to mention very cool.) One good use of expression columns in a DataSet is when you want to load a column into a DataGrid but you don't want the entire text description to appear in the column. This is often found when you have a notes, comments or some sort of very long description column that is 100 or even 8000 characters long. You likely don't want to show the entire text string because it could stretch out your grid, so a common way to deal with it is to only show the first X amount of characters. For example, the code sample below will create an expression column called "Co" and add it to the Customers DataTable. This new column will grab the first 10 characters from the CompanyName (from the same DataRow in the DataTable) and append 3 dots. So instead of showing all of the columns in a grid, you could hide the CompanyName and instead show the Co column.

 

    string cnStr = @"Data Source=mysqlserver;Initial Catalog=northwind;Integrated Security=True";
    SqlConnection cn = new SqlConnection(cnStr);
    string sql = "SELECT CustomerID, CompanyName, City FROM Customers ORDER BY CustomerID";
    SqlCommand cmd = new SqlCommand(sql, cn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds, "Customers");
    ds.Tables["Customers"].Columns.Add("Co.", 
        typeof(string), 
        "Substring(CompanyName, 1, 10) + '...'");

 

There is a WHOLE bunch more that you can do with expression based columns, but I'll leave that for later.

Tweet Post Share Update RSS

Hi, I'm John Papa. I author this blog, create courses for Pluralsight and am a Google Developer Expert and Microsoft Regional Director. I travel speaking at events and train technology thought leaders

Tags:
All