DotNetNuke – Modules – Creating The Tables

On Tuesday we installed the free version of CodeSmith to help us create the database access code. Before we can create the access code, we need some tables in our system. Even if you think you know all about creating tables in a database, don’t skip this lesson. There are some very specific tips related to working with DotNetNuke that you will miss if you skip it.

Here are some helpful tips for creating tables in the DotNetNuke database:

  • Prefix your table names with some unique identifier related to your organization and/or project. (For example, I prefix all of my table names with dmbcllc.) This will be useful when using CodeSmith to generate your scripts. (Trust me.) I’ll tell you more about this when we run the scripts.
  • Create an identity field call ID that is an integer and auto-incrementing for each table.
    This makes the identity field clear to everyone looking at the code and ensures that you have at least one field that is unique. Again, this will be important as we write our access code.Some people like naming this field TableNameID. That will work, too. I just find naming it ID clearer.
  • Make the ID field the primary key.
    Again, this is critical to making the data access code work properly.
  • When creating a field in a table that points to the ID field in a second table, name the field TableNameID.
    For example, if you have an Address table with an ID field and you have an Employee table that needs to point to the ID field for entity relationship purposes, name the field in Employee AddressID. This makes it clear to everyone that the field is pointing to the ID field in the Address table.
  • PascalCase all of your field names.
    I know that historically words in database field names have been Underscore_Separated, but we are going to use the tables and field names to create DataTables in our .NET code. If you create a DataSet against a table that has Underscore_Separated words, you will end up with properties in your DataSet with Underscore_Separated words. Since the code you will spend most of your time working with will be the DataSet, we want to ensure that we end up with PascalCased properties. To do that, we need PascalCased field names. (Of course, you should make your table names PascalCased as well.)
  • If you have entity relationships between the tables, set them up now.
    By doing so, these relationships will remain established when you create your datasets, allowing your datasets to maintain the referential integrity of the tables prior to sending the data down to the database.

     

    Other post in DotNetNuke - Module Development

    Related Post

    2 Responses to “DotNetNuke – Modules – Creating The Tables”

    • Nick:

      I find your video tutorial extremely helpful. As a complete beginner, I would appreciate a video for this step of the tutorial. Do we create the table from within the site Host – SQL paste create procedure, or do we create the table from within Visual Studio Server explorer? Or SQL Server Management express?

    • Dave:

      Whatever tool works for you.

      I would recommend using either Visual Studio, SQL Server Management Studio or SQL Server Management Studio Express.

    Leave a Reply

    Comment Policy:

    • You must verify your comment by responding to the automated email that is sent to your email address. Unverified comments will never show.Leave a good comment that adds to the conversation and I'll leave your link in.
    • Leave me pure spam and I'll delete it.
    • Leave a general comment and I'll remove the link but keep the comment.

    Notify me of followup comments via e-mail

    Bear