If LINQ sounds like a foreign language, this might not be your most immediately productive option, but Entity Framework isn't that new, and you'd be astonished at how neatly it abstracts away all this boilerplate code. This isn't really a review, because it's a completely different approach, but for what it's worth I find the first listing perfectly readable and much better than casting anything to Object, even if AddWithValue actually takes an Object - I prefer these conversions implicit.
So here it goes - say you have an entity like this:
public class Client
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; } // this one is nullable, DateTime? is shorthand for Nullable<DateTime>
public string Address { get; set; }
}
You can easily find lots of tutorials online about using Entity Framework, but for the sake of displaying your case, you'll need a class that derives from DbContext to hold all your DbSets, which hold your entities:
public class MyContext : DbContext
{
public MyContext(string connectionString)
: base(connectionString)
{ }
public IDbSet<Client> Clients { get; set; }
// ...
public override void OnModelCreating(DbModelBuilder modelBuilder)
{
// customize entity mappings here
base.OnModelCreating(modelBuilder);
}
}
There are multiple ways to do this, but whatever you do EF needs to know how entities map to tables and properties map to columns - hence Object/Relational Mapper, and if you don't do anything about it EF will try to make it work by convention, and this means you only write the mapping/ModelBuilder code you need to write.
Now that you've got your entity and a context (you'll need proper configuration in app.config, and the connection string to use can be also inferred from config/context), you're ready to use it.
First thing you'll need is to decouple you UI from your data. This may seem redundant, but you'll want a ViewModel for use in your UI, rather than directly using the entity type (although that would still work). The idea is to not expose stuff that doesn't belong on the UI, notably the Id:
public class ClientViewModel
{
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public string Address { get; set; }
}
Now this is nice if all you need is to create new clients or display existing ones, but at one point you'll want to update an existing client and you'll want that Id back. I've posted my idea for converting between data and presentation types here on CR, and got good feedback - it was meant for use with WPF, but it can't hurt to take a look ;)
So, assuming we don't care about the Id of an existing Client yet, and only want to create a new one. What your UI code will do is fill up a ClientViewModel instance, with all the required and missing optional values.
I'm not very familiar with web dev (I know, I really should get into that), but I'd try to databind my ViewModel with the form fields (I'm sure it's supported), again to minimize boilerplate code.
Then you can have a "Model" class that works with your DbContext to do all the gruntwork - which is nowhere near the kind of gruntwork you're :
public class MyModel
{
private readonly MyContext _context;
public MyModel(MyContext context)
{
_context = context;
}
public void AddClientAndSave(ClientViewModel client)
{
var entity = new Client
{
FirstName = client.FirstName,
LastName = client.LastName,
DateOfBirth = client.DateOfBirth,
Address = client.Address
};
_context.Clients.Add(entity);
_context.SaveChanges();
}
}
Just an example, but you get the point: no one gives a rat's ass about which field maps to a nullable or required column, except the mapping code. When you save your new client, give it a null value if that's what you need, provide an actual value if that's what you want. That's all! No SqlCommand, no parameters, no inline sql or anything. Just the code that gets it done.
