0

I'm making a web app that will be querying an existing database. I'm thinking of putting the names of the tables and columns and such into a single static class, and then referencing that when constructing queries. I think this would help with maintainability since if in the future a column gets renamed for some reason, it just needs to be changed in one place.

Is this a good way to do this? Is there a better way?

6
  • 6
    The implication here is that you intend to write bare SQL in your C# code, which is the wrong choice. You'd probably be better off looking for a library designed to help you more easily interact with a database, such as Entity Framework.
    – Magus
    Commented Sep 17, 2014 at 15:19
  • 1
    @Magnus Even then, I would still have to tell the library what tables and columns I want at some point, no?
    – HamHamJ
    Commented Sep 17, 2014 at 15:31
  • If using Entity Framework, the table and column names are the class and property names respectively, meaning you write all your queries in code and don't think about the database. Commented Sep 17, 2014 at 15:56
  • 1
    @razethestray This is an existing database. I don't control the column names.
    – HamHamJ
    Commented Sep 17, 2014 at 16:01
  • 4
    It is possible in EF to generate your classes from an existing database. Commented Sep 17, 2014 at 16:02

4 Answers 4

5

In a properly decoupled architecture, your application should have no knowledge of the physical database model. To put the table and column names in your classes creates a dependency that could break your application if there are major changes to the database beyond simple table and column changes.

For an application of some size and complexity, the better practices are to use an object relational mapper (ORM) such as Entity Framework (as noted by Magus), NHibernate, or other more lightweight options. An ORM handles the mapping between the physical database and your classes so that each are independent.

Another option is to use a web service of some kind to provide that layer of abstraction between the physical database and your application.

These options allow you to make changes within a database or even switch database platforms, while keeping rework to a minimum.

If you are building a very simple application, and you don't anticipate many changes to the database, then an ORM or separate data service might be overkill. There's always a trade-off.

2
  • If I have a class with constants, and a DOA class, wouldn't those basically already be doing the mapping from the database to my actual item class? What additional features would I get from something like Entity Framework?
    – HamHamJ
    Commented Sep 17, 2014 at 16:08
  • @user3038802 you get a complete separation of your application logic and your data structure. With a data access class, if you have a piece of your data that needs to come from a different place in your database, you need to make code changes to the class. With an ORM, you simply change the mapping and leave your application code the same. ORMs also take over some of the heavy lifting in terms of query construction and security that you'd otherwise have to do on your own. There certainly nothing wrong in going the route you've suggested, but you may have to do more rework down the road.
    – mcknz
    Commented Sep 17, 2014 at 16:25
4

I'm thinking of putting the names of the tables and columns and such into a single static class...

Don't do this.

If you want to eliminate magic strings, please do yourself a favor and create one static class per table underneath a shared namespace.

A single static class to manage all table and column names would quickly turn into a big ball of mud.

3
  • I agree with the idea that general 1 class per table is the way to go here. I don't necessary think they need to be static classes, but this is a better idea than a single static class to keep track of all those things. In my current project, we've got 1 class per View or Sproc in our mapping layer, and its pretty easy to get your head around that.
    – GHP
    Commented Sep 17, 2014 at 16:56
  • 2
    If the OP is only using the magic string once in the whole code base, I'd say keep the magic string where it's used. See here programmers.stackexchange.com/questions/305059/… Commented Aug 30, 2016 at 18:49
  • @user2023861: I agree.
    – Jim G.
    Commented Aug 30, 2016 at 19:54
0

I'm trying to get rid of "magic strings" by creating a static class within my model class:

public class Post
{
    public static class Columns
    {
        public static readonly string Id = "Id";
        public static readonly string UserId = "UserId";
        public static readonly string Title = "Title";
        public static readonly string Body = "Body";
    }
    
    public int Id { get; set; }
    public int UserId { get; set; }
    
    [Required]
    public string Title { get; set; }
    
    [Required]
    public string Body { get; set; }
}

and accessing column names like this: Post.Columns.Id, Post.Columns.UserId etc.

0

Some answers are already saying that the database structure has no right leaking into your model classes, and I completely agree.

I suggest that you take this a little further and adopt the repository pattern. A repository is simply a class that exposes business level functions and does all the dirty work required to implement the request in the underlying database. For instance a repository for an online shopping site may expose methods like getCustomer(customerId), changePassword(customerId, passwordHash) and persistOrder(Order).

Typically the repository will use an ORM under the covers to reduce the amount of SQL that needs to be hand coded. The repository is the only class (or group of classes) that know about table names, column names, transactions and SQL in general.

Also it is good practice to define the repository capability using an interface. This reduces the chance of a circular reference (i.e The repository imports model classes and some model classes need to import the repository) and also simplifies mocking the repository during testing.

Not the answer you're looking for? Browse other questions tagged or ask your own question.