One of the biggest failure points I see with third-party DotNetNuke modules, including some of my first released modules were failures due to the lack of support for ObjectQualifier or DatabaseOwner values within the SqlDataProvider files. Although the process to add support for these two tokens is easy to add, it is very hard for those of us that like to test our database structures outside of DotNetNuke before we actually commit to building our modules. Thankfully, with a bit of research, I have found a way to build SQL Scripts for DotNetNuke using SQL Server Management Studio (SSMS) in a normal fashion and using a simple find/replace operation to bring everything into compliance with DotNetNuke integration. This post will explore the method that I use to accomplish this.
Setup
Depending on your exact scripting needs you might want to configure the SSMS scripting options to "script drops" before the creates of stored procedures, allowing you to do complete scripting run out of the box. You can also work with various options to tailor the generated SQL Scripts to meet your needs. Otherwise, no special setup is needed.
Writing the Scripts
The best part about the way that I write my SQL scripts is that only slight modifications are needed to write the scripts in a manner that can be executed on any database directly from SSMS and then converted easily into DotNetNuke compliant scripts. The only change that you must make is that ALL objects must be qualified with a database owner, and the object name must be surrounded in[]'s. So a stored procedure creation must be similar to "CREATE PROCEDURE dbo.[MyPrefix_InsertMyData]", this rule applies to all objects, including from clauses and joins within the stored procedures.
By doing this you have a consistent form to your scripts that still complies with the needs of a default SQL Server implementation but also allows for a very simple find and replace to prepare objects for DotNetNuke integration. Once you have written the scripts, feel free to run them and insert the objects into a test database, this way you can truly validate that your syntax is correct and perform any other validations needed.
Creating the DotNetNuke Scripts
Once you have successfully created the scripts and validated that they are correct you can use a simple find/replace option to make the scripts DNN compliant. You have two options when it comes to performing this step. The first is to simply use the SQL scripts that you executed, however, those might not yet have the drop statements needed to ensure that scripts are re-usable. The second option is to use the "Script Object As" options within SSMS to have it script the Create or Alter statements for you. The key is to get to a point where all scripts, in their current form, are inside a single document.
Once the items are in a single document simply use find/replace. Search for "dbo.[" without the quotes and specify "dbo.[" without the quotes as the replace with text. This will sub out the hardcoded database owner and will supply the DotNetNuke tokens, making the script fully functional within the DotNetNuke module installation environment.
Conclusion
I hope this posting has helped someone with the creation of DotNetNuke module scripts. Although I do not like the use of an ObjectQualifier as a custom module developer for DotNetNuke it is important to follow the standards set forth to ensure that modules function in a manner that users expect.
Feel free to share your comments below.