This is a continuation of the "Best Practices: Development Team Collaboration and DotNetNuke" multi-part series of blogs. If you have not read Part 1: The Development Environment, you may wish to do so before reading this entry. The first installment not only covers the development environment setup, it also covers some target audience things you may wish to review as well before diving into this post. This installment is dedicated to database structure changes using the SqlDataProvider files.

Probably one of the more challenging things with regards to team collaboration and DotNetNuke development is managing the database objects, often referred to as database schema. When multiple developers are collaborating, there needs to be a way that ensures that all database object changes, additions, deletes can be easily made in other developer environments (ex. their workstations, remote servers, etc.). ASP.NET developers have probably done this in the past using T-SQL files. Please note that I am only discussing the database objects, such as tables and stored procedures. I am not discussing the actual data stored in rows of tables. Before going too far into team collaboration, we should first review a few important things about DotNetNuke and T-SQL. 

SqlDataProvider Files

SqlDataProvider files are custom to DotNetNuke. They are basically SQL scripts saved in a text format to be processed by DotNetNuke at time of module installation. The files should be named according to the version of your module they correspond to. For example, on initial module development there is typically a 01.00.00.SqlDataProvider file which builds all tables and stored procedures used by your module. Now if you are working on your module’s next version, 01.00.01, any changes in database objects from the original 01.00.00.SqlDataProvider file should be contained in the 01.00.01.SqlDataProvider file. All SqlDataProvider files must be listed in the <files> section of the .dnn manifest file read by DotNetNuke during the module installation process otherwise they will not be executed at time of installation. Your module’s version, set in AssemblyInfo.vb (or AssemblyInfo.cs) and in the .dnn manifest file, should be equal to or less than your last .SqlDataProvider file.

An example of the end of a .dnn manifest file that includes SqlDataProvider files can be seen in the screen shot below.

A few very important things to keep in mind when creating SqlDataProvider files:

• Be sure that you do NOT create incremental .SqlDataProvider files that have TABLE drops as you will potentially be deleting all data on an existing install. (unless that is your intention, but that is rare)
• Be sure to use the {databaseOwner}{objectQualifier} in all scripts as does DNN core.
  • These should be used for CREATE, DROP, ALTER statements as well as table/view/function names within stored procedures.
• Be careful with the usage of [ and ] brackets. The reason is when using the owner and qualifier objects, you could do something that would break the syntax unknowingly.
  • WRONG - CREATE PROCEDURE {databaseOwner}{objectQualifier}[AT_Module_Get]
  • RIGHT - CREATE PROCEDURE {databaseOwner}[{objectQualifier}AT_Module_Get]
• Be sure to avoid using SQL Server's COLLATE when dealing with text/ntext, varchar/nvarchar, char/nchar (seen when working with any text type column data storage).
  • We do not want this as it breaks localization(or possibly could).
    • WRONG - [Template] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    • RIGHT - [Template] [ntext] NOT NULL
  • If creating temporary tables in stored procedures, be sure that all text type columns (ntext, nvarchar, etc) use the COLLATE database_default statement.
    • ex. Email NVARCHAR(100) COLLATE database_default
• The SqlDataProvider files need to be saved as UTF-8 Format using notepad and Save As from the menu.
  • Standard notepad is ANSI encoding which can cause big installation problems using DotNetNuke.
  • This error normally only shows up when + signs are used in stored procedures, but make sure your new SqlDataProvider files are UTF-8 to avoid issues later.
• At the end of each SqlDataProvider file you complete your last SQL logic with GO.
• Just as important is if it is the very last item in your SqlDataProvider script, you need to hit the Enter/Return key several times at the end leaving at least 3 extra lines of white space in notepad.
  • This is a bug with the DotNetNuke script installation, so please just make sure this is present at the end of every SqlDataProvider file.
• Always drop stored procedures and recreate them, no reason to “ALTER” them.
• SqlDataProvider files by design should NOT (generally) be edited from version release to version release.
  • There are special circumstances where this should occur and we need to agree as a team when those circumstances are. (Highly unlikely)
• All items created (tables, stored procedures, views, constraints, etc) should be contained in the Uninstall.SqlDataProvider file and dropped in proper order
  • ex. stored procedures before tables they depend on, constraints dropped before tables, then finally tables
  • Same objectQualifier & databaseOwner rules apply
• Maintain Case in T-SQL
  • It is very possible that your modules/providers/installations could be used in conjunction with a SQL Server database that is case sensitive.
  • ex. Use GO instead of go, SELECT instead of Select, etc.

As you probably noticed, there is mention of the file Uninstall.SqlDataProvider. This should always be updated any time developers make database object additions or deletes. The majority of the outlined items above still apply for these specific SqlDataProvider files. Unfortunately, the quickest way to test if all SqlDataProvider files are successful or not is to install a module and then uninstall it using a DotNetNuke portal locally or on a test server. The errors returned during module installation should almost always provide enough information to correct the majority of errors in your SqlDataProvider files.

With the basics of SqlDataProvider files now covered, which are a very fundamental part to DotNetNuke development, I should mention that if you are using EntitySpaces, this usually becomes much easier to maintain and reduces the need for most stored procedures. It also reduces the amount of code that has to be written by hand for developers, which is monotonous. Tools such as the ATGen SDK for DotNetNuke can also write the SqlDataProvider files for you and can be regenerated at the click of a button along with user interface items thus reducing the hand written code even more. If you add in some unit tests into your build process, you have a very quick and efficient way to make database object changes with confidence. Ultimately, it would be nice if the build process was automated enough that the built modules were pushed to a test server and installed, with the installation message returned back to the build server and then emailed out to developers. Even though this is not completely automated, I feel this is something every developer should be doing locally anyway. It never hurts to have a single DotNetNuke installation that you just install modules on locally outside of your development installation. This allows you to install and uninstall at will without worry of data loss, and iron out any errors found during the installation process.

In addition to the versioned SqlDataProvider files used during module installation discussed thus far, there is usually another SqlDataProvider file in most DotNetNuke projects. This is the SqlDataProvider.vb or SqlDataProvider.cs files used as the concrete data provider class. If you use this file, which is used for most core modules and those that use Info/Controller classes (typically one corresponding Info/Controller set of classes per table) you will likely need the abstract data provider class typically named DataProvider.vb or DataProvider.cs. I don't want to go too in depth here, but I felt it was important to mention these files since they do directly relate to the versioned SqlDataProvider files.

Beyond the SqlDataProvider Files

Something that should be considered is the target database you are working against for your production site. If you are working on a module for multiple sites, thus multiple SQL server installations/types, you soon will have to choose if you want to support SQL 2000 or SQL 2008. SQL 2005 can coexist with either 2000 or 2008, however some data types are now extinct in 2008 such as ntext. Your choice here will also determine what T-SQL Syntax is permitted in your database. We haven't made the move to SQL 2008 yet, so we currently support 2000 and 2005. If you need some good examples of SqlDataProvider files, you can download the core DotNetNuke forums module, it should cover almost anything you would need to reference.

As SqlDataProvider files are added, it is very important that they are added to source control. Often I advocate adding SqlDataProvider files to the project so if they are missing it will fail the build process if you have a build server or CI setup. Just for reference, I have included a screen shot of the forum project loaded up in Visual Studio 2008. This screen shot provides a good example of where to place the SqlDataProvider files within your project.

One notable item developers may sometimes feel they need is a module's definition ID, ModuleDefID. This is stored in the DesktopModules table of the core. If there is a reason you want to use version, you should not execute any T-SQL in your SqlDataProvider files for one main reason: If it is a new installation, the ModuleDefID will not exist until AFTER all the SqlDataProvider files are executed by the DotNetNuke installer. To avoid the need for this, users can use IUpgradeable to execute any T-SQL that requires the ModuleDefID variable. Please keep in mind that for IUpgradeable to fire for a specific version, a corresponding SqlDataProvider file of the same version must exist and be included in the .dnn manifest file. Based on my personal experience, I have only needed the ModuleDefID variable for T-SQL when creating custom permissions within the DotNetNuke permissions grid.

While I know this post doesn't cover everything about the SqlDataProvider, it should certainly server as a good starting point for understanding these files. Once all developers understand these files, it should be a fairly straight forward process to keep database objects synchronized for multiple module developers collaborating on the same project. As long as the above is kept in mind and all database object changes are immediately added to a SqlDataProvider file (and likewise, source control) maintaining these across multiple developers should be a much more simplified process for your organization. Before diving into more in depth topics specific to DotNetNuke, the next blog post in this series will discuss the various environments team developers will deal with such as test, qa and production.