I have been using an ORM called LLBLGen for the last year and a half and am battling internally what the benefits are and whether or not it is a battle I should pick when I start my new job on the 2nd. Should I try to get them to purchase it? (This is a bigger question now that they changed their licensing model to per seat)

I decided to use the Community Server tables and create and example since, well, I don't have any other databases on this server.

First, for ADO.Net as it currently is:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
string query = "select * from cs_posts join cs_posts_incategories on cs_posts.postid = cs_posts_incategories.postid where categoryid=1003";
SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable posts = dataset.Tables["cs_posts"];

foreach (DataRow post in posts.Rows)
{
    Console.WriteLine(post["subject"].ToString());
    Console.WriteLine(post["postdate"].ToString());
    Console.WriteLine(post["formattedbody"].ToString());
}

Then, LLBLGen:

EntityCollection posts = new EntityCollection(new PostsEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(PostsEntity.Relations.PostsInCategoriesEntityUsingPostID);
bucket.PredicateExpression.Add(PredicateFactory.CompareValue(PostsInCategoriesFieldIndex.PostID, ComparisonOperator.Equal, 1003));
Adapter adapter = new Adapter();
adapter.FetchEntityCollection(posts,bucket);

foreach(Posts post in posts)
{
    Console.WriteLine(post.Subject);
    Console.WriteLine(post.PostDate);
    Console.WriteLine(post.FormattedBody);
}

As you can see, they are the same number of code lines, but the true question is, what does the code mean (and yes, I used * which we wouldn't in real life)? At this point, the only thing LLBLGen appears to have gained us is a different syntax and strongly typed objects.

First, the ADO.NET code wouldn't be ran that way.

If we were using ADO.NET directly, we would need to create a business object:

public class Post
{
    private string subject;
    private string postdate;
    private string formattedbody;

    public string Subject
    {
        get { return subject; }
        set { subject = value; }
    }

    public string PostDate
    {
        get { return postdate; }
        set { postdate = value; }
    }

    public string FormattedBody
    {
        get { return formattedbody; }
        set { formattedbody = value; }
    }
}

Then, of course we would need a method to load the objects, we'll keep it simple and pass in a single int for the where clause, and reuse our code from above:

public static Post[] PostsGet(int CategoryID)
{
    SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
    string query = "select * from cs_posts join cs_posts_incategories on cs_posts.postid = cs_posts_incategories.postid where categoryid=@id";
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    adapter.SelectCommand.Parameters["id"].Value = CategoryID.ToString();
    DataSet dataset = new DataSet();
    adapter.Fill(dataset);
    DataTable postTable = dataset.Tables["cs_posts"];
    Post[] posts = new Post[postTable.Rows.Count];
    int i = 0;

    foreach (DataRow post in postTable.Rows)
    {
        posts[i].Subject = post["subject"].ToString();
        posts[i].PostDate = post["subject"].ToString();
        posts[i].FormattedBody = post["subject"].ToString();
        i++;
    }

    return posts;
}

And that doesn't even count the code we were originally writing that prints them out to the console, which is now:

Post[] posts = PostManager.PostsGet(1003);

foreach(Post post in posts)
{
    Console.WriteLine(post.Subject);
    Console.WriteLine(post.PostDate);
    Console.WriteLine(post.FormattedBody);
}

*And* this doesn't even touch on having to save new posts or update existing posts. LLBLGen definitely seems to provide enough value to warrant asking my new company to purchase it for creating new applications.

The only thing that throws murk into this equation is the upcoming additions of ADO.Net Entities and Linq. From what I have seen so far, Linq is still too SQL like for me, I want to work in C#, not C# and Transact SQL, so anything that abstracts the SQL language away and allows me to work more object based is a plus in my book. And for ADO.Net Entities? The team seems very excited about the technology, but from what I have seen so far, they are far from completion and I don't know that they are going to hit that sweet spot of abstracting the SQL away, creating strongly typed entities and still making it usuable enough to really be the new thing. They seem to be running in alot of different directions at once and not getting anywhere fast in the process.

But that's just what I see, they may have made some good progress recently and I am just in the dark.

The other thing to consider when choosing LLBLGen is the learning curve. If the developers you are working with have not used something similar in the past, it can be a pretty steep climb. But once it clicks and you just "get it", the journey is very well worth the trip.

Well, I rambled alot there and I apologize for that, I am not even sure I was making sense the whole time, nor whether or not the post is cohesive. Its really too long for me to read back through again ;)

Disclaimer: I can't guarantee all the code compiles and works as expected. I just formatted (again, its a hobby, really!) and Visual Studio is still installing, hence the massive free time to write such a long post...