General Tips & Tricks

I was in a meeting yesterday and one of my co-workers had an issue, while remoted into his desktop, with a window popping up on his secondary monitor. Luckily, I’ve used 2 – 4 monitors a long time and I’ve run into that same issue before. The solution?

1. Press Alt+Space
2. Press M
3. Push and hold the relevant arrow key until you see the window you want

So…after telling him how to solve that issue, I thought I’d post some other tips/tricks that I use quite a bit. Here’s another one:

Ever copy over your clipboard in Visual Studio and think, “Dang! Now I have to go copy that again”…or worse you cut it…then you get to Ctrl Z and then re-copy…HUGE pain. Well luckily for us, Visual Studio remembers your clipboard and you can take advantage of it by using Ctrl+Shift+V to cycle through your previous copies. This has saved me MANY times. NOT that you should copy and paste code...as a general rule...this is not a good thing :)

Ever have to minimize all your open windows to get to your desktop? My solution to this problem was to create a group of toolbars to categorize my apps. I leave the toolbar locked, never hidden, and always on top. When I first started using this method back in 2002, my boss at a startup called Icabob (now closed), hated it because I had the auto-hide enabled so everytime he’d try to go to the file menu, my toolbar would open. I was used to it so I didn’t have the same problem :)

Another nice thing about this is that I typically have all the same software installed on all my different machines, so I can copy and paste my root “toolbars” folder on all the machines and the shortcuts still work. Keep in mind, you only would add apps that you use quite a bit. So I don’t put my entire “All Programs” section in the toolbar. Anyhow, here’s my current toolbar setup, which is docked on the top of my screen:

Yes that is a GroupWise icon you see up there…so all you Outlook 2010 people (my pic is already on this page) complaining should remember there are worse things. However, we are going through an Exchange migration right now, so I’ll be joining in on the complaints soon enough I’m sure :)

Here are a couple shortcuts that I don’t see used often, but are handy:

Ctrl + Shift + Esc – Opens Task Manager

Win + Pause Break – Opens Computer Properties

Here’s a couple shortcuts specifically for VS IDE that I use a lot:

Ctrl + K + C – Comment Selected Code

Ctrl + K + U – Uncomment Selected Code

For more VS tips, check out Sara Ford’s book Visual Studio Tips 251 Ways to Improve Your Productivity

Anyhow, I thought I’d share some of the things I use daily that I’ve never seen used. I also want to post my favorite jQuery plugins, favorite Firefox add-ons, and VS plugins. I’m not sure if anyone is interested or not, but if you are, let me know!

Thanks for reading!

Take Control of Your Web.Config

After reading a Paul Sheriff article in CODE Magazine, I ended up creating a base WebConfig class to handle getting values out of my web.config. I’ve hated the ConfigurationManager.AppSettings since they deprecated it from ConfigurationSettings.AppSettings. So now I have my own GetAppSetting so I never have to worry about it again. 

My base class looks like this:

public class BaseWebConfig
{
protected static string GetAppSetting(string appSettingName)
{
    if (ConfigurationManager.AppSettings[appSettingName] == null)
        throw new NullReferenceException(appSettingName 
            + " AppSettings value cannot be null or empty in the web.config.");

    return ConfigurationManager.AppSettings[appSettingName];
}

protected static bool GetBoolAppSetting(string appSettingName)
{
    return Convert.ToBoolean(GetAppSetting(appSettingName));
}

protected static string[] GetStringArraySetting(string appSettingName)
{
    var values = GetAppSetting(appSettingName).Split(new[] { ',' });
    return values;
}

protected static int GetIntAppSetting(string appSettingName)
{
    int i;
    if (!int.TryParse(GetAppSetting(appSettingName), out i))
        throw new InvalidCastException(appSettingName 
            + " AppSettings value is an invalid integer.");

    return i;
}

protected static DateTime GetDateTimeAppSetting(string appSettingName)
{
    DateTime dt;
    if (!DateTime.TryParse(GetAppSetting(appSettingName), out dt))
        throw new InvalidCastException(appSettingName 
            + " AppSettings value is an invalid DateTime.");

    return dt;
}

protected static string GetConnectionString(string connectionStringName)
{
    if (ConfigurationManager.ConnectionStrings[connectionStringName] == null)
        throw new NullReferenceException(connectionStringName 
            + " ConnectionStrings value cannot be null in the web.config.");

    if (String.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString))
        throw new NullReferenceException(connectionStringName 
            + " ConnectionStrings value cannot be null or empty in the web.config.");

    return ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
}
}

Here’s the breakdown:

• GetAppSetting – Gets a string value


• GetBoolAppSetting – Gets a bool value


• GetStringArraySetting – Gets a string[] value


• GetIntAppSetting – Gets an int value


• GetDateTimeAppSetting – Gets a DateTime value


• GetConnectionString – Gets a connection string value

Pretty simple stuff here…well I think it’s self-explanatory. Note they’re all protected so the inheriting class can access them, but nothing else. The reason I do this is because I never want a magic string passed in and risk getting a run-time error with a simple typo.

So with this base class, I inherit it in another WebConfig class in my actual project. Here’s an example of one:

public class WebConfig : BaseWebConfig
{
    public static string ReturnEmail
    {
        get { return GetAppSetting("ReturnEmail"); }
    }

    public static bool IsTestEnvironment
    {
        get { return GetBoolAppSetting("IsTestEnvironment"); }
    }

    public static DateTime ShutOffDate
    {
        get { return GetDateTimeAppSetting("ShutOffDate"); }
    }

    public static string DbConnectionString
    {
        get { return GetConnectionString("DbConnectionString"); }
    }
}

You can see above, I can reference my web.config all day long and never worry about mistyping something. If I need to rename something, I can easily do that as well. If you’re a reader of my blog, you know I’m a huge fan of being strongly-typed. I love for intellisense to help me out :)

I really like the simplicity of this method because it’s easy for beginner team members to use. That’s all I got…let me know if you have comments.

Thanks for reading!

Favorite Extension Methods

I thought I’d share some of my favorite extension methods I use regularly. I’ll go through a few extensions for MailMessage, HtmlHelper, and DirectoryEntry (for Active Directory).

MailMessage Extension (only 1)

    public static class MailMessageExtensions
    {
        public static void Send(this MailMessage mailMessage)
        {
            #if (DEBUG)
            mailMessage.To.Clear();
            mailMessage.To.Add(WebConfig.FromEmail);
            #endif

            var sc = new SmtpClient();
            sc.Send(mailMessage);
        }
    }

HtmlHelper (only 1)

        public static string DivSuccessMessage(this HtmlHelper html, string successMessage)
        {
            if (html.ViewData.ModelState.IsValid && html.ViewData["success"] != null)
                return "<div class=\"success-message\">" + successMessage + "</div>";

            return "";
        }

I created this extension to act similar to the ValidationSummary extension. Basically it checks for ViewData[“success”] and that the model state is valid then returns the sucess message, otherwise it returns nothing.

DirectoryEntry

Need a property? Here you go, just give me the name. (I also use an enum for our AD schema too to pass in the propertyName)

public static string GetProperty(this DirectoryEntry entry, string propertyName)
{
    if (entry.Properties[propertyName] == null || entry.Properties[propertyName].Value == null)
        return null;

    return entry.Properties[propertyName].Value.ToString();
}

Need to add a property? Done…it’ll even commit the changes.

public static void AddProperty(this DirectoryEntry entry, string propertyName, string propertyVal, bool commitChange)
{
    if (entry.Properties[propertyName] == null || propertyVal == null) 
        return;

    entry.Properties[propertyName].Value = propertyVal;
    if (commitChange)
        entry.CommitChanges();
}

I have a couple more for adding properties…one that adds just a property and commits without asking and then one that adds multiple properties at once.

public static void AddProperty(this DirectoryEntry entry, string propertyName, string propertyVal)
{
    AddProperty(entry, propertyName, propertyVal, true);
}

public static void AddProperties(this DirectoryEntry entry, params Func<string, string>[] values)
{
    foreach (var func in values)
        AddProperty(entry, func.Method.GetParameters()[0].Name, func(null), true);
}

Here’s one to enable an account

public static void EnableAccount(this DirectoryEntry entry)
{
    if (!ValidUserAccount(entry))
        return;

    entry.Properties[Attributes.userAccountControl].Value = GetCurrentUserAccountControl(entry) & Flags.ADS_UF_NORMAL_ACCOUNT;
    entry.CommitChanges();
}

private static int GetCurrentUserAccountControl(DirectoryEntry entry)
{
    int currentVal;
    return !int.TryParse(entry.Properties[Attributes.userAccountControl].Value.ToString(), out currentVal) ? 0 : currentVal;
}

private static bool ValidUserAccount(DirectoryEntry entry)
{
    return entry.SchemaClassName.Equals("user", StringComparison.CurrentCultureIgnoreCase);
}

Here’s one to disable an account

public static void DisableAccount(this DirectoryEntry entry)
{
    if (!ValidUserAccount(entry))
        return;

    entry.SetPassword("SomeDefaultPassword");
    entry.Properties[Attributes.userAccountControl].Value = GetCurrentUserAccountControl(entry) | Flags.ADS_UF_ACCOUNTDISABLE;
    entry.CommitChanges();
}

Another one to reset password

public static void SetPassword(this DirectoryEntry entry, string password)
{
    if (!ValidUserAccount(entry))
        return;

    entry.Invoke("SetPassword", new object[] {password});
    entry.CommitChanges();
}

There you go…a few extensions that I use and thought I’d share. I know you all have some good ones so feel free to share.

Thanks for reading!

Getting Started with fluentHtml

I mentioned a couple posts ago that I’d blog about how to get setup with fluentHtml, which is packaged with the MvcContrib project. Let’s get started…

Step 1:
Reference MvcContrib & MvcContrib.FluentHtml in your project after downloading the MvcContrib project.

Step 2:
Create a new base page that inherits from the ModelViewPage<T> in MvcContrib.FluentHtml. It’ll end up looking like this:

    public class BaseViewPage<T> : MvcContrib.FluentHtml.ModelViewPage<T> where T : class
    {
    }

Step 3:
So in your aspx page, you’ll inherit from the BaseViewPage<T> like this:

<%@ Page Language="C#" Inherits="Core.UI.Model.BaseViewPage<PersonView>" %>

PersonView is the sample view model I’m using for this post and it looks something like this:

    public class PersonView
    {
        public string Name { get; set; }
        public string StreetAddress { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string PostalCode { get; set; }
        public string PasswordSample { get; set; }
        public string TextAreaSample { get; set; }
        public bool CheckboxSample { get; set; }
    }

Just something basic so you can see some of the possibilities.

Step 4:
In the <pages><namespaces> section of the web.config, you can add the namespaces so you don’t have to reference them in your aspx pages. It’ll look like this:

    <add namespace="MvcContrib"/>
    <add namespace="MvcContrib.FluentHtml"/>

Otherwise, you’ll have to use the import at the top of the aspx pages like this:

<%@ Import Namespace="MvcContrib" %>
<%@ Import Namespace="MvcContrib.FluentHtml" %>

Step 5:
Create your form…here are some samples:

   <p><%=this.TextBox(f=>f.Name).Label("Name: ") %></p>
   <p><%=this.TextBox(f=>f.StreetAddress).Label("Street Address: ") %></p>
   <p><%=this.TextBox(f=>f.City).Label("City: ") %></p>
   <p><%=this.Select(f=>f.State).Options(UnitedStates.StateDictionary) %></p>
   <p><%=this.TextBox(f=>f.PostalCode).Label("Postal Code: ").Styles(display=>"block") %></p>
   <p><%=this.Password(f=>f.PasswordSample).Label("Password Sample: ") %></p>
   <p><%=this.CheckBox(f=>f.CheckboxSample).LabelAfter("Checkbox Sample") %></p>

The above form will create this view:

The first 3 are very basic and just have a textbox with a label with the default layout. The other few are examples of how you do certain things with fluentHtml.

A dropdownlist looks like this: <%=this.Select(f=>f.State).Options(UnitedStates.StateDictionary) %>
(See more details on how to add the states in this post)

A Textbox with the label on top looks like this:
<%=this.TextBox(f=>f.PostalCode).Label("Postal Code: ").Styles(display=>"block") %>

A Password looks like this:
<%=this.Password(f=>f.PasswordSample).Label("Password Sample: ") %>

A Checkbox with the label after the box looks like this:
<%=this.CheckBox(f=>f.CheckboxSample).LabelAfter("Checkbox Sample") %>

That’s it for fluentHtml!
Really simple and I love that it’s strongly-typed. I hate this type of code:
<%= Html.TextBox(“Name”, Model.Name) %> However, this will no longer be the case with ASP.NET MVC 2, which is a GREAT thing!

Something else to look into:
Of course if you’re in a rush, you can use something else that the MvcContrib brings to the table and that is Html.InputForm. If you go this route, you’ll need to add all the templates that you want to use from the MvcContrib project’s InputBuilderTemplates into your Shared folder. Then you’d be able to do this in your markup:

  <%=Html.InputForm() %>

And that’ll generate this view:

It’s pretty awesome that you can type one line and generate your whole form from the view model and you can change the way it looks in the templates that they provide. These two namespaces MvcContrib.UI.InputBuilder and MvcContrib.UI.InputBuilder.Views are used with the Html.InputForm.

Linq to Sql Generic Repository with AutoMapper

We, my teammates and I, have been playing with creating a generic repository for Linq to Sql using AutoMapper. We haven’t used it in production or anything, but I figured I’d blog about it and see if anyone has any comments about it. It’s fun just to toy around sometimes.

A couple of posts ago, I blogged about Func<> and that’s going to play an important part in what I’m about to show you. So basically we started off coding what we would do without generics and then converted that into a generic method. The ending interface looks like this:

public interface ILinq2SqlRepository<TDataContext, TDomainModel, TLinqModel>
     where TDataContext : DataContext
{
   IList<TDomainModel> GetAll(Func<TDataContext, IQueryable<TLinqModel>> func);
   TDomainModel GetById(Func<TDataContext, TLinqModel> func);
   void Save(TDomainModel model, Func<TDataContext, TLinqModel> func);
}

So there is the GetAll and the GetById methods. I’m also working on the Save, but it’s a little more tricky because I believe there’s a bug in Linq to Sql Attach(entity, original) when the primary key is a Guid, but that’s just me :) My Save method right now keeps returning a cannot insert duplicate key error and I’ve Googled it, but to no avail. I’m also not sure how to handle the using(DataContext)…just know it’s a work in progress! Anyhow…

The implementation looks like this:

public class Linq2SqlRepository<TDataContext, TDomainModel, TLinqModel> 
    : ILinq2SqlRepository<TDataContext, TDomainModel, TLinqModel>
    where TDataContext: DataContext
{
  private readonly TDataContext DataContext;

  public Linq2SqlRepository(TDataContext dataContext)
  {
      DataContext = dataContext;
  }

  public IList<TDomainModel> GetAll(Func<TDataContext, IQueryable<TLinqModel>> func)
  {
      var list = Mapper.Map<TLinqModel[], TDomainModel[]>(func.Invoke(DataContext).ToArray());
      return list.ToList();
  }

  public TDomainModel GetById(Func<TDataContext, TLinqModel> func)
  {
      return Mapper.Map<TLinqModel, TDomainModel>(func.Invoke(DataContext));
  }

  public void Save(TDomainModel model, Func<TDataContext, TLinqModel> func)
  {
      using (DataContext)
      {
          var original = func.Invoke(DataContext);
          var entity = Mapper.Map<TDomainModel, TLinqModel>(model);
          DataContext.GetTable(typeof(TLinqModel)).Attach(entity, original);
          DataContext.SubmitChanges();
      }
  }
}

Well here’s the base implementation and it looks a lot easier if you have a good understanding of .Net generics. You can see the common AutoMapper Mapper.Map<> method in there, which is what we’re using to do the left to right mapping and basically we’re just passing in simple Linq commands. So we still have to do our AutoMapper configuration, but my hope is that maybe this post will strike up a good idea in someone else’s head…hopefully!

I thought it’d work best to implement this into a repository by just setting it as a private field like this:

public class SampleRepository : IRepository<DomainPerson>
{
    private readonly ILinq2SqlRepository<SampleDataContext, DomainPerson, DataPerson> _repository;
    public SampleRepository(string connstring)
    {
        _repository = new Linq2SqlRepository<SampleDataContext, DomainPerson, DataPerson>(
            new SampleDataContext(connstring));
    }

    public IList<DomainPerson> GetAll()
    {
        return _repository.GetAll(x => x.DataPeople);
    }

    public DomainPerson GetById(Guid id)
    {
        return _repository.GetById(x => x.DataPeople.SingleOrDefault(y => y.Id == personId));
    }

    public void Save(DomainPerson person)
    {
        _repository.Save(person, x => x.DataPeople.SingleOrDefault(y => y.Id == person.Id));
    }
}

I went the inheritance route and didn’t really care for it, mainly because it was much uglier in the implementation. So you’ll notice this inherits from a generic IRepository, which could’ve been IDomainPersonRepository that inherits from the generic or was just a plain ole repository. Anyhow, it looks like this:

    public interface IRepository<T>
    {
        IList<T> GetAll();
        T GetById(Guid id);
        void Save(T model);
    }

I wanted to show prettier things in the public side of the API like GetById with just Guid instead of pass me an ugly lambda :) (Even though I love the lambdas, a lot of devs don’t care for them) So the users of this API would just reference the IRepository<T>, but I’d actually probably end up renaming to a friendlier name like this:

    public interface IDomainPersonRepository : IRepository<DomainPerson> {}

Then I’d go ahead and inherit my SampleRepository from IDomainPersonRepository…I should really plan these posts out more than I do before typing them so the post is better structured. As I get more practice in, I’ll try to do that for you!

So that’s my thinking on some type of generic repository for Linq to Sql with AutoMapper. Please keep in mind that this was a fly by thing we came up with this afternoon and I thought it was pretty cool. It could definitely use more testing & refactoring!

Let me know if you have any thoughts, questions, or comments.

Thanks for reading!

Security Questions with ASP.NET MVC & fluentHtml

Have you ever needed to create a screen to ask a user to select security questions? Well, I recently had to create this screen and I thought I’d share the experience.

I guess I’ll start with the view model.

public class CreateSecurityQuestionsView
{
    public IList<SelectListItem> QuestionList 
    { 
        get { return DI.SecurityQuestionService().GenerateSecurityQuestions(); } 
    }

    [ValidateNonEmpty("Username was not found")]
    public string Username { get; set; }




    [ValidateNonEmpty("")]
    [ValidateNotSameAs("SecondQuestionId", "Question 1 = Question 2")]
    [ValidateNotSameAs("ThirdQuestionId", "Question 1 = Question 3")]
    public string FirstQuestionId { get; set; }

    [ValidateNonEmpty("Answer 1 is Required")]
    public string FirstAnswer { get; set; }

    [ValidateNonEmpty("")]
    [ValidateNotSameAs("FirstQuestionId", "Question 2 = Question 1")]
    [ValidateNotSameAs("ThirdQuestionId", "Question 2 = Question 3")]
    public string SecondQuestionId { get; set; }

    [ValidateNonEmpty("Answer 2 is Required")]
    public string SecondAnswer { get; set; }

    [ValidateNonEmpty("")]
    [ValidateNotSameAs("FirstQuestionId", "Question 3 = Question 1")]
    [ValidateNotSameAs("SecondQuestionId", "Question 3 = Question 2")]
    public string ThirdQuestionId { get; set; }

    [ValidateNonEmpty("Answer 3 is Required")]
    public string ThirdAnswer { get; set; }
}

So my view has a QuestionList, which gets all the questions for the end-user to select. I’ll populate the dropdownlists with this property in a bit. I used the goodsecurityquestions.com website to gather my questions and I’m using 15 in the selection. The other properties are pretty standard and the validation attributes are Castle Validators. I’m not a fan of the way the ValidateNotSameAs worked out for me, but it does the job.

Okay, let’s take a look at the HTML.

<%=Html.DivValidationSummary("All three questions are required and should be unique")%>
<form action="/Account/CreateSecurityQuestions/" method="post" id="step5form">
<fieldset>
    <legend>Create Security Questions</legend>
    <p><%=this.Select(f => f.FirstQuestionId)
           .Options(Model.QuestionList)
           .Label("Security Question 1: ")
           .Selected(Model.FirstQuestionId)%></p>
           
    <p><%=this.TextBox(f => f.FirstAnswer).Label("Answer 1: ")%></p>
    
    <p><%=this.Select(f => f.SecondQuestionId)
           .Options(Model.QuestionList)
           .Label("Security Question 2: ")
           .Selected(Model.SecondQuestionId)%></p>
    
    <p><%=this.TextBox(f => f.SecondAnswer).Label("Answer 2: ")%></p>
    
    <p><%=this.Select(f => f.ThirdQuestionId)
           .Options(Model.QuestionList)
           .Label("Security Question 3: ")
           .Selected(Model.ThirdQuestionId)%></p>
    
    <p><%=this.TextBox(f=>f.ThirdAnswer).Label("Answer 3: ") %></p>
    <%=this.Hidden(f=>f.Username) %>
    <%=Html.AntiForgeryToken() %>
    <div style="text-align: right"><%=Html.SubmitButton("Finish", "sb", "S") %></div>
</fieldset>
</form>

So you can see here, I’m using fluentHtml and  you can see the view model from above spread all through the markup. You should notice the Model.QuestionList three different times in the above markup in the Options() for the Select(). In order to use fluentHtml, you’ll need to inherit from the MvcContrib.FluentHtml.ModelViewPage<TModel>. I plan on doing a post on fluentHtml soon, which will go through the configuration and usage…etc.

The controller is really basic and looks like this:

[AcceptVerbs(HttpVerbs.Post), ValidateModel(typeof(CreateSecurityQuestionsView)), ValidateAntiForgeryToken]
public ActionResult CreateSecurityQuestions(CreateSecurityQuestionsView securityQuestionsView)
{
    if (!ModelState.IsValid)
        return View("Step5", securityQuestionsView);

    _registrationService.CreateSecurityQuestions(Mapper.Map<CreateSecurityQuestionsView, AccountInformation>(securityQuestionsView));

    return RedirectToAction("Summary");
}

Alrighty, so the CreateSecurityQuestions action is post only, it uses the ValidateModel filter that I got from CodeCampServer (I think), and it uses the ValidateAntiForgeryToken. You can read more about the ValidateAntiForgeryToken on Phil Haack’s post. Obviously the action accepts the CreateSecurityQuestionsView and then it checks to verify the ModelState is valid, if not, it returns the view. Then I’m using AutoMapper to map the view model to my AccountInformation object and then I redirect to the summary action. The _registrationService is passed into the controller via the constructor.

I did consider doing a loop on the security questions and answers, but I thought the most I’d ever have is 5 in the view and as long as it’s not a set number in my domain I’m alright with it. Basically, what I’m saying is don’t put securityQuestion1, 2, 3 etc in your domain model, allow it to grow to how many ever security questions your end-user wants to answer. The reason we’re using three is because we require our registered users to answer three and then they can add more later once they’ve completed the registration process.

I think that about covers everything. Please comment if you’d like more details on anything.

Thanks for reading!

Playing with C# Func<T, TResult> and ADO.NET

I’ve always liked the look of the generic Func and I think the possibilities behind it are awesome. However, it’s usually difficult for me to find a good place to use it. I’m probably just not that used to using it yet and I am missing out on some huge advantages. Ignorance is not bliss sometimes :)

So anyhow, I was playing with the good ole ExecuteScalar & ExecuteNonQuery commands on the IDbCommand interface and came up with something kinda cool. At least I thought so…

I tend to use a DbHelper when I’m doing easy things instead of using NHibernate, Linq2Sql, etc, etc. Well here’s what I started with:

public class DatabaseHelper
{
public static T ExecuteScalar<T>(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            return (T) oCmd.ExecuteScalar();
        }
    }
}

public static void ExecuteNonQuery(string connstring, string sql, params IDataParameter[] parameters)
{
    using (var oConn = new SqlConnection(connstring))
    {
        oConn.Open();
        using (var oCmd = new SqlCommand(sql, oConn))
        {
            oCmd.Parameters.AddRange(parameters);
            oCmd.ExecuteNonQuery();
        }
    }
}
}

Obviously not pretty and a TON of duplicated code. VERY SMELLY!

So the first thing I did was create a DatabaseCriteria class to help with the number of params I’m passing and it looks like this:

public class DatabaseCriteria
{
    public DatabaseCriteria(string connstring, string sqlStatement, params IDataParameter[] parameters)
    {
        ConnectionString = connstring;
        SqlStatement = sqlStatement;
        Parameters = parameters;
    }
    public string ConnectionString { get; set; }
    public string SqlStatement { get; set; }
    public IDataParameter[] Parameters { get; set; }
}

That’s a little better and it’s easy to modify the existing code that calls my helpers. The next thing I did was create small factories for my connection & command so I wasn’t bound to the Sql Server version and I could easily swap out databases. The builders look like this:

private static IDbConnection BuildConnection(string connstring)
{
    return new SqlConnection(connstring);
}

private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
{
    var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
    command.Parameters.AddRange(parameters);
    return command;
}

Obviously I’m using some SqlCommand features like the AddRange and I’m casting straight to the SqlConnection, but that code is only in one spot and it’d be easy to setup an IoC tool or something. I want to keep this blog simple though, so I’ll leave it as is for now.

Okay, so now let’s get to the fun stuff! Here’s my new ExecuteSql generic method:

private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
{
    using (var oConn = BuildConnection(dbCriteria.ConnectionString))
    {
        oConn.Open();
        using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
        {
            return func.Invoke(oCmd);
        }
    }
}

Okay, so this accepts a DatabaseCriteria object and a generic Func that invokes an action on IDbCommand and returns T. It also takes advantage of the two factories I made and obviously the using statement.

Updated: 1/26/2010 8:16PM
Thanks to the comments of Thilak Nathen, I’ve made an improvement to the ExecuteScalar<T> method. Before him bringing it to my attention, my code could’ve thrown the dreaded “Object reference not set to an instance of an object” message. I think we all have grown to hate that message. Sadly it’s easy to check for and we sometimes just look over it. If you’re interested, the ExecuteScalar before the update was just one line and it was: return ExecuteSql(dbCriteria, x => (T) x.ExecuteScalar());

Thanks Thilak!

Now my DatabaseHelper looks like this:

public class DatabaseHelper
{
    public static T ExecuteScalar<T>(DatabaseCriteria dbCriteria)
    {
        var result = ExecuteSql(dbCriteria, x => x.ExecuteScalar());
        return (result is T) ? (T)result : default(T);
    }

    public static void ExecuteNonQuery(DatabaseCriteria dbCriteria)
    {
        ExecuteSql(dbCriteria, x => x.ExecuteNonQuery());
    }

    private static T ExecuteSql<T>(DatabaseCriteria dbCriteria, Func<IDbCommand, T> func)
    {
        using (var oConn = BuildConnection(dbCriteria.ConnectionString))
        {
            oConn.Open();
            using (var oCmd = BuildCommand(dbCriteria.SqlStatement, dbCriteria.Parameters, oConn))
            {
                return func.Invoke(oCmd);
            }
        }
    }

    private static IDbConnection BuildConnection(string connstring)
    {
        return new SqlConnection(connstring);
    }

    private static IDbCommand BuildCommand(string sqlStatement, IDataParameter[] parameters, IDbConnection connection)
    {
        var command = new SqlCommand(sqlStatement, (SqlConnection)connection);
        command.Parameters.AddRange(parameters);
        return command;
    }
}

I think there could be a little more clean up here, but basically I think it’s pretty clean. There is pretty much zero code duplication and no dependencies on implementations other than in my two builders. You should also notice that everything is private other than the ExecuteScalar and the ExecuteNonQuery because I only want people to use those two methods. Anyhow, I like it! Hopefully you do too.

Thanks for reading!