Devart’s SQL Complete–A Fantastic Tool!

| No TrackBacks

Blah, blah, blah.. I haven’t been blogging in forever I hear you say. Well, as is with just about everyone – I’ve been busy.  The new job panned out extremely well and I’m very happy and otherwise have been tied up working hard.  Life is good.

So I discovered a new tool recently called SQL Complete by Devart.  Now, if you know anything about them.. they make pretty good products and also have reasonable prices.  I’ve been using their free version of CodeCompare for quite awhile now with Visual Studio and it’s miles above others that I’ve tried (esp. the built on TFS comparison tool!).  So their software quality is definitely high!

And that brings us to SQL Complete – I’ve been trying out this application and it’s definitely a metric ton better than the built in IntelliSense in SSMS – not only does it update and improve SSMS, it also integrates into Visual Studio (2010, though others are supported).

One feature in particular that I find absolutely terrific – you can configure document formatting!  I work with a lot of ugly stored procedures – and with a simple command, it pretties up my SQL.

The only complaint that I have so far (and I haven’t reported this to support) is that it appends “dbo.'” in front of everything, regardless of the option I select.  Otherwise, I don’t have a single complaint.  It’s really nice having a complete list of columns/tables/etc. popup while I’m typing, with field types and lengths.  SQL Complete beats the built in SSMS IntelliSense, hands down.

All-in-all, SQL Complete is a great tool and I whole heartedly recommend it.  Their free version doesn’t have all of the bells and whistles of the paid ($99) version, but definitely assists.  One of my favorite features – you can configure the time to display its IntelliSense !  Without, SSMS would freqently pop-up the wrong thing at the wrong time immediately after typing something.  With the configuration settings in SQL Complete you’re able to control that.

I’m definitely adding this to my useful tools list, and I wish I could afford all of their other tools (Query Builder, Schema/Data Compare, Data Studio, I’m looking at you guys!).


- Matthew

LINQBugging - Using LINQPad For WinForms Testing

| No TrackBacks

Did you know that you can use LINQPad to test your WinForms app while you're doing development, and easily ?  I don't think that this is a feature that's widely used (and in fact, I don't think that the author himself thought of that idea!  ::grins, hi Joe!::) while using LINQPad. 

So, how do you do it ?

Right click in your query window and select Advanced Properties.  On the Additional References tab select "Add" and then "Browse".  Find the assembly that you wish to test.  Also make sure you add any other necessary assemblies, and especially System.Windows.Forms.dll.  You'll then want to switch to the Additional Namespace Imports tab and type in the Namespace of your assembly that you wish to test, so that you'll have IntelliSense. 
Important Note:  Make sure you list your namespaces in the "most dependant ones last."  i.e., start with System.Windows.Forms so that you'll get proper IntelliSense.

Now, with something similar to the following code (make sure to switch the language to C# Program and put this in the Main {} method):

using(MyFormThatIWantToTest myForm = new MyFormThatIWantToTest()) {
  myForm.ShowInTaskbar = true;

It's important to set your form to show in the taskbar because if you open it as a Dialog without, it will be very easy for you to lose you form.  The reason for opening it as a Dialog.. if you don't, the form will immediately close.

You can use this technique (I call it "LINQBugging") to test a lot of different scenarios and in some cases, save yourself a lot of time.  Why does this save you time ?  You can jump straight to your form/dialog without having to go through any unnecessary steps that you might otherwise if you launch your application directly, or from Visual Studio.  I use this technique all the time.  Couple this with Hawkeye to quickly prototype and test forms within your application and bypass the tedious navigation and security of your application to get there.

Also, make sure you open up your project in Visual Studio!  You can make changes and recompile your application and then execute (something similar to the above code) your LINQPad "Query" again.  One note, you need to close you form if you have it opened with LINQPad, otherwise the executable will become locked.  After that, once you make changes to your source code and recompile, LINQPad will execute the new code the next time you run the "query".

There are a lot of other things that you can do with this technique.. testing libraries, writing test procedures, etc.  Now that you know how to do this, what other tips and tricks do you have that I haven't thought of ?

* Note:  Yes, you can have a lot of fun with applications that aren't your own.. a little Reflector, and a little LINQPad can go a long way.  Don't abuse it. :)

Thanks for reading,

Matthew MacSuga

Reflection based IEnumerable<T> TrimAll() function

| No TrackBacks

Sorry for the too-long delay in updating my blog.  As usual, I’ve been pretty busy with a lot of things lately!! … you know, with the new job, summer activities, reading books.. stuff you probably don’t care a lot about. 

Today’s entry is going to deal with a pretty snazzy function I wrote as an Extension method called TrimAll().  This extension method is used when I’m gathering data from a database that has a lot of CHAR(x) fields in it and I’d rather not deal with space padding cluttering my code or UI’s.

/// <summary>
/// Dictionary that serves as a cache for cached PropertyInfo[] arrays
/// </summary>
static Dictionary<Type, PropertyInfo[]> trimCache;
/// <summary>
/// This function trims all public properties on an Enumerable object 
/// except those specified as optional parameters
/// </summary>
/// <typeparam name="T">The type of object</typeparam>
/// <param name="sourceList">A list of items</param>
/// <param name="ignoredFields">Props that should not be trimmed</param>
/// <returns>A cleaned up list with all string padding removed</returns>
public static List<T> TrimAll<T>(this IEnumerable<T> sourceList, 
params string[] ignoredFields)
  if (trimCache == null)
    trimCache = new Dictionary<Type, PropertyInfo[]>();
  //  The list that we will be returning
  List<T> rList = new List<T>();
  //  Added for performance
  PropertyInfo[] props = null;
  //  Try to get a value from the dictionary if it exists
  if (!trimCache.TryGetValue(typeof(T), out props)) 
    //  Get the properties for the type
    props = typeof(T)
              .Where(c => !ignoredFields.Contains(c.Name) && 
                c.CanWrite && 
                c.PropertyType == typeof(System.String))
    //  Add it to the array
    trimCache.Add(typeof(T), props);
  //  Iterate over each item in the source list
  foreach (T obj in sourceList) 
    //  Iterate over each of the filtered properties
    for (int i = 0; i < props.Length; i++) 
      //  Get the value 
      string o = (string)props[i].GetValue(obj, null);
      //  If it's not null, .Trim() it, otherwise leave it alone
      if (o != null)
        (props[i]).SetValue(obj, o.TrimEnd(
                                   ' ', 
                                   '\r'), null);
    //  Add to the list
  //  Return the list!
  return rList;


So, there you have it.  I did do a bit of editing to make this fit within the constraints of my blog theme, but you should get the idea.


1.  I created a Dictionary of type Dictionary<Type,PropertyInfo[]> to improve performance of the TrimAll function on subsequent calls.  The first call will grab all of the public string properties of the input enumerable object and cache the reflection for future uses.

2.  When the function is called, it checks to see if the type of the current object <T> has already been entered into the cache.  For this, I use the TryGetValue object on the Dictionary class.  This is a very handy function that will return true/false if the Key is present in the dictionary.  In this case, the Type of the object is the key.  If the value is not present in the dictionary, then it will be created and added to the dictionary.

3.  You’ll notice that I’m using LINQ to search through all of the public properties of the source type.  I am also filtering the list if there are any optional string parameters passed in for properties that you would not want filtered.. as well as making sure that the property is writable, and that it is a string.

4.  Once the PropertyInfo[] props object is populated it is added to the cache for future use.

5.  With the new array of PropertyInfo objects for the source list, I start iterating over each object in the list.  For each object, I iterate over each item in the props[] array.  If the value is not null I update the Property in the object with a Trimmed version of the string.  You’ll notice that I am using “TrimEnd” with the specific fields I want to have trimmed explicitly specified.  I’m doing this to save a little bit of processing time and make the function slightly more efficient.  If you look at the TrimEnd method in Reflector, you’ll notice that it would cause several more iterations over each character.. this way I am saving those extra cycles.

6.  Finally, the updated object is added to the storage List<T> (rList) and returned to the calling function.

I hope you find this as useful as I have.. it’s saved me quite a few headaches and it’s nice having an easy way to Trim all of the values from my database in one fell swoop.


using(…DataContext dc = new …DataContext()) {
  // With a Stored Procedure
  var dataList = dc.spSomeSproc().TrimAll();
  var dataList2 = myTable.Where(c => c.SomeVal == “SearchVal”).TrimAll();

One of the nice features of this function is that it saves you the extra step of converting your returned Enumerable sequence to a List using .ToList().  I don’t know about you, but I quite often find myself doing that.  Of course, if your database is “normal” and isn’t full of CHAR fields (vs. VARCHAR) then you won’t have to worry about this at all. 

Knowing that most people probably don’t worry about CHAR fields, I still thought this made a nice blog post given that I am a huge fan of Reflection and finding neat ways to solve problems using it.  As usual, YMMV.

Question:  Do you think there is anyway to optimize this method further ?

Thanks for reading!

P.S.  You can now find me on Twitter.  Link:

Matthew MacSuga

LINQ-To-SQL: Updating Disconnected Entity Objects

| 2 Comments | No TrackBacks

Here’s the skinny:  Don’t get into a situation where you have to update an object from a disconnected DataContext.  You will fail, and miserably.

Sure, you can do some Googling and find some resources on the subject, and even find some slightly tolerable solutions.. but even those require some nasty stuff, some hard coding of methods in the DataContext class itself, and/or adding versioning field to your database table.

Google:  “linq disconnected updates” and “LINQ Update Disconnected Entity Objects”

The best solution that I found, that worked for me – was a bit more of tightly coupling what was happening, and passing around an open DataContext from start-to-finish through the various methods.

It seems to me that the best way would be something hypothetical like this:


Of course, that “Update” method doesn’t exist, only the “Attach” method that seems like it should do what we want, but doesn’t really.  The “Update” method of course, would update using only the primary key of the entity to perform the update.  I suppose I see why this might not exist due to complications with child entity objects.

The other alternative AFAIK is to create a CopyFromEntityToEntity extension method or class method to copy from the object you wish to update, to an object acquired from the database during the update method.  Something like the following:

//Warning, this is pseduo-code and will not work for various reasons and should be a generic method.  This also assumes the PK is a GUID[uniqueidentifier] field.  There is no error checking, etc.
void Update(orders_mainTable updateEntity, Guid identifier) {
  myDataContext dc = new myDataContext();
  var original = dc.Table.Where(o => o.Guid == identifier).SingleOrDefault();
  if (original == null)
    throw new Exception(“Original Entry in Database Not Found.”);

  // Some Extension Method?

  // Update the database

The CopyFrom method would iterate over the reflected values of the objects and copy the data from the “updateEntity” object to the original object.  This way, you’re not going to run into any issues issuing an update to a 'disconnected’ object.

Good luck!  This one sure makes me mad though.. LINQ-to-SQL should be smart enough to take care of this for [us] automatically in my opinion.  This is going to be a huge issue for n-tier disconnected architectures.  Instead of being able to fully take advantage of X,Y,Z those portions that do the updating will have to go through hoops like the example above to be able to update something.

Note:  My example above does not address child objects that may be stored along with the entity.

P.S.  I absolutely love LINQ [with the exception of this!].  I had to re-write a substantial part of the application I am working on after some major database changes for features requested by the client.. well, I made a decision to replace portions of code that were already written that needed to be update with LINQ-To-SQL stuff.. and wow!  I was able to write the new LINQ-based code with more features than the original code had.. and implement it faster than I would have been able to modify the existing code to update it to the new requirements. 


Further Reading: 

Rick Strahl has some interesting information on this topic as well but offers some more detail in the suggestions such as adding a timestamp field, etc.
Link:  LINQ to SQL and Disconnected Entities Follow-up

Omar Al Zabir has some ideas as well.
LINK:  Linq to SQL: How to Attach object to a different data context

Here are some other generic Google results that I found during research:


Matthew MacSuga

LINQ-to-SQL – Love it, or Hate It

| 1 Comment | No TrackBacks

I choose to love it.

Here is a post that I posted on Stack Over Flow, asking for some assistance in generating the following query seen below: 

Complicated query with aggregate data for a report from multiple tables for an ordering system

from pmt in products_mainTable
join opt in orders_productsTable on pmt.guid equals opt.products_mainTableGUID into tempProducts
from orderedProducts in tempProducts.DefaultIfEmpty()
join omt in orders_mainTable on orderedProducts.orders_mainTableGUID equals omt.guid into tempOrders
from ordersMain in tempOrders.DefaultIfEmpty()
group pmt by new { pmt.sku, orderedProducts.color, orderedProducts.size } into g
orderby g.FirstOrDefault().sku
select new {
    QTY = g.FirstOrDefault().orders_productsTable.Sum(c => c.qty),
    SUM = g.FirstOrDefault().orders_productsTable.Sum(c => c.itemprice * c.qty),
    AVG = g.FirstOrDefault().orders_productsTable.Average(c => c.itemprice * c.qty),
    Some = g.FirstOrDefault().orders_productsTable.Average(p => p.qty).GetValueOrDefault(0),

I’ve got to say that working with LINQ-TO-SQL has been an absolute pleasure.  I even fired up LINQPad and have really been enjoying the use of that program.  The $19 for the IntelliSense was definitely worth it.  What a great tool.

I can now take the query above into my code, and render it into reports, grids, etc. and whatever else I need it for using my DevExpress framework.  Exciting stuff, to be sure.

I’ll write some more on this topic when I have some additional time, but since I had a difficult time Googling for the answer on my Stackoverflow question, I thought I’d write about it here.

The above represents how to do a full LEFT OUTER JOIN in LINQ and C# while performing GROUPING and AGGREGATE functions.  Hopefully this will be a good starting place for someone else looking to do the same thing.

The important bits here are DefaultIfEmpty() which helps to perform the LEFT OUTER JOIN and FirstOrDetaulf() which allows you access to the underlying type.  While I knew about DefaultIfEmpty() I did not know about FirstOrDefault().. using FoD was the real key to making this query work the way that I wanted it to.

Now I am able to quickly and easily query my sales data.  This query for instance queries the products table, finds products that have sold and generates a quick report that I can use as a DataSource for other components.

Viva la LINQ!

Matthew MacSuga

Automatically Generating Dynamic Enumerations

| No TrackBacks

I use a product from Developer Express called XtraGrid.  It has some pretty remarkable features, one of which is a “flags” editor.

Say for instance that I have a bit flag enumeration:

public enum ColorFlags {
    Red = 1,
    Green = 2,
    Blue = 4

Now, if I want that to show up in my grid with a popup for a field like the following:


I have to do something very simple..


The DevExpress tools will enumerate over the enumeration (ha!) and gather all of the appropriate values, create the checked list edit, and display it to the user.  When the user finishes their edit, they are left with the numerical value of the bitwise operation.

That’s all and well, but what if my enumerations are stored in a database ?  I simply can’t use the .SetFlags method by itself, because there is no hard-coded enumeration to pull from!

What I needed to do was to create the enumeration dynamically, at runtime, to pull the information from the database and construct a dynamic enum.

Here is the code I created:

public enum FlagsType {
    Color = 1,
    Size = 2
private Type GenerateEnumerations(FlagsType f) {
    string asmNameString = String.Empty;
    switch(f) {
        case FlagsType.Color:
            asmNameString = "flags_color";
        case FlagsType.Size:
            asmNameString = "flags_size";
    //    Create Base Assembly Objects
    AppDomain appDomain = AppDomain.CurrentDomain;
    AssemblyName asmName = new AssemblyName(asmNameString);
    AssemblyBuilder asmBuilder = appDomain.
DefineDynamicAssembly(asmName, AssemblyBuilderAccess.Run); // Create Module and Enumeration Builder Objects ModuleBuilder modBuilder = asmBuilder.
DefineDynamicModule(asmNameString + "_module"); EnumBuilder enumBuilder = modBuilder.
DefineEnum(asmNameString, TypeAttributes.Public, typeof(int)); // Query the database for the value of the flags enumerations using (lsDataContext dbObj = new lsDataContext()) { var enumerations = (from j in dbObj.flags_mainTable where j.type == (int)f orderby select j).ToList(); foreach (flags_mainTable fmtObj in enumerations) enumBuilder.DefineLiteral(, fmtObj.flag); } return enumBuilder.CreateType(); }


Now I simply use the .SetFlags method, but this time like this:


I doubt this sort of scenario crops up often, but if it does I

hope this helps!


Matthew MacSuga


| No TrackBacks

Today, I finally achieved one of my many little dreams as a software developer.

Throughout the years, I have wanted one thing.. one tiny, small, insignificant little thing that would make things just a little easier on me.. allow me to have some fun.. be completely legal [not that I wasn’t already!].. and have access to some of the greatest software tools and systems known to man.

I got my MSDN Premium Subscription today with Visual Studio Team Suite !!

I’ve been downloading all of the neatest applications all day, and planning how, what, and where I am going to install things to make the perfect development environment!  I can’t believe after all of these years of wishing someone would buy it for me.. of wishing I had access to the latest operating systems.. today, I finally have it all!  I absolutely can’t wait to dig into all of the features of VSTS Edition! 

So dear reader, you might ask, how did I get this subscription ?  Did I steal it ?  Did I kill someone ?  Did I lie, or cheat ?  Did I bribe someone at Microsoft ?  No.

Microsoft has this incredible program called BizSpark

With BizSpark, a startup company can acquire all of the tools necessary to develop software using the best Microsoft tools, applications, and operating systems.  The requirements are not steep (less than 3 years, make less than $1,000,000 dollars, be privately held, and be developing software that forms the core of your business) and all you have to do is find a Network partner to help you join the program.  *Note:  The network partner should not charge you for anything, so if someone wants too.. find someone else!  Oh, and one other thing.  You may remain in the program for 3 years, and upon your exit, you are required to pay a $100 exit fee.  Cake.

All in all, I highly recommend this program to anyone looking to have the best development tools that Microsoft has to offer.  I can only assume that I will also get Visual Studio 2010 Team Suite Edition as well, when it’s released.  Let me just tell you something, I absolutely can not wait.  You also get PRODUCTION licenses for SQL Server, and Windows 2008 Server [up to 3 years].

Oh, and did I mention these licenses are perpetual ?  Even after you leave the program, your licenses are valid and will remain so.. with the exception that the server products (SQL Server, Server 2008, etc.) will no longer be licensed. 

All I have to say is this:  Thank you Microsoft, for making my little dream come true. 

I am so happy to finally have access to all of their tools and operating systems.  I am going to have so much fun learning all of the new features in VSTS, and Team Foundation Server.  I can only hope that this will increase my output and make me an even better developer. 

Sounds like fun to me. :)

P.S.  Original Blog Article That I Read to Find Out About This:
Thanks Jason[, and Bob!]!!!

Matthew MacSuga

Core Programming Exercises

| No TrackBacks

During the course of researching something.. I came across an excellent free e-book:  “Data Structures and Algorithms” ( written by Granville Barnett and Luca Del Tongo.  While it is a little difficult to follow in places, it is an excellent resource for programmers who want to “get back to basics”.

It’s been a long time since I’ve written things like Linked Lists and Sorting algorithms from scratch. This book really does a good job of laying out pseduo code for what you need to do to implement a particular algorithm. 

Programming in C# has really made me lazy when it comes down to fundamental programming principals!  I would be willing to bed that a lot of programmers today don’t know how to write a lot of these things from scratch anymore.. so much is provided to us to use by others.. that we forget how they’re actually built. 

Using the .NET framework I have hardly had a need to do anything like write a Bubble Sort or a Linked List from scratch.  I suppose that I’m fortunate as well in that I started on the .NET bandwagon late.. a lot of the functionality that is in this book is built into the framework now.

I’m currently working through each of the examples and having quite a bit of fun doing it.  I created my own Singly and Doubly Linked List, from scratch.  While not as powerful or efficient perhaps as the built in Linked Lists and others that are built into the framework.. It’s about learning something old, and applying it to something new. I dug up some old C code that I had written years and years ago that implemented a LL.. now I’ve done it in C# as well.

So, I’m working on getting back to basics.. relearning stuff that I learned a long time ago, and having fun implementing the algorithms in C#.  It’s good exercise for the mind and is making me re-think some of the things that I’ve been writing lately.  Creating these in C# is really making me think more in the OOP world.

Check out the book!

- Matthew 

How to ORDER BY a Bitwise Field in SQL

| No TrackBacks

So, I had an interesting problem crop up a little while ago.  If you are storing a bitwise “flags” field in your database, and you need to order by a particular flag or flags of that field, how do you do it ?

It turns out the answer is relatively simple.

Table Structure: id, name, flags
Flag Values:  1 = Enemy, 2 = Friend, 4 = Owe Money To, 8 = Owes Me Money

Sample Data:
1, Matthew, 2 // Friend
2, Josh, 6 // Friend, I Owe Money
3, Thomas, 10 // Friend, Owes Me Money
4, John, 9 // Enemy, Owes Me Money
5, Abe, 2
6, Becky, 6
7, Jimmy, 10
8, Jason, 9
9, Dean, 1
10,Joseph, 5
11,Frank, 6
12,Julie, 11 // Frenemy, Owes me Money
13,Hannah, 10
14,Sam, 5
15,Q, 9

So we have a list of people in the database that are fall under a few different categories [flags] as referenced above.  Now I want to query from this table to find out various things about the people contained within.

Now I want to see all of the people in the table that are my friends, ordered by those that owe me money, followed by those that I owe money to, followed by any others.

SELECT *, (flags & 4 = 4) AS owesme, (flags & 8 = 8) AS iowethem FROM temp WHERE (flags & 2 = 2) ORDER BY owesme DESC, iowethem DESC, name


I’m pretty happy with that.  I had a need, and figured out how to scratch it.  I am a little surprised that I had never needed this before, but it certainly does work!  I hope this helps those that might be trying to Google for this.. I did not find a single entry, luckily my hunch paid off.  I wonder if not a lot of people do this sort of thing ?  I love storing flags, and now I can use those for easy ordering as well.

VistaDB Rocks:
I wasn’t completely sure that I was going to be able to pull this off in VisatDB, but sure enough, it worked like a charm and the first time.  I’m quite impressed!  Yet something else that VistaDB does well.  I also tried it in MySQL to make sure I wasn’t on drugs either. 

Experiment, play around with it, see if you can find a use for something like this.

Happy programming!

Matthew MacSuga

A Move to a New Host

| No TrackBacks

I moved the site to a new hosting facility, and had a few problems along the way!  It’s up and running now and should be in good shape.  If anyone notices any problems, please let me know :P

P.S.  I’m going to try and write a few more articles this week and post some pretty cool code.  While this is called “C# By Design”, I’ll probably be writing a bit about PHP and how to consume .NET web services!  I had a lot of issues with Google while developing my solutions so I think they’ll be of use.


Matthew MacSuga

Recent Comments

  • Alsbury: Almost a couple years later and this little tidbit is read more
  • Marc: Hi all, this is ONE way to do it (if read more
  • Anonymous: This is really very useful. Do try this also read more
  • Mohan: Thanks a lot for sharing. Simple, to the point, and read more
  • Tyler: Have you given this a try? I just found read more
  • Christian Schiffer: Dude here is the solution to your problem, it drove read more
  • Matt Warren: You don't need FirstOrDefault to access the key values after read more
  • Aros Attila: Very useful article thanks for sharing :). read more
  • Trevor Sullivan: Good article. Thanks for posting for us .NET people :) read more
  • Jeffrey A. Reyes: Hi, good day. Thanks for the information you have here. read more

Recent Assets

Find recent content on the main index or look in the archives to find all content.