Creating DotNetNuke SqlDataProvider Files Using SSMS

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 a 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 "{databaseOwner}[{objectQualifier}" without the quotes as the replace with text. This will sub out the hard coded 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.

Posted by Mitchel on Monday, January 26, 2009

Tags: DotNetNuke, Tutorials, SQL, DotNetNuke Development

Comments

In the past I have been using SMSS to create the stored procedures as you explain. However, when doing this, I cannot run local DNN installs since it does not close the connection to the database as expected. I need to manually terminate all the sql process and restart them before running. Anyone else?

By Dario Rossa on Tuesday, January 27, 2009 at 12:58 AM

I have never had that issue....now typically when generating scripts I am working on a different DB than my DNN test one...but even when I have that open I don't loose connection.

Which version of SQL Server are you using?

By mitchel.sellers@gmail.com on Tuesday, January 27, 2009 at 3:44 AM

Good post. One of the things I routinely do to minimize the sheer number of replacements is to judiciously use table aliases in my procs.

For instance when joining a couple tables like modules and tabmodules I'll use something like this:

dbo.myqualifier_Modules M inner join dbo.myqualifier_TabModules TM ON M.ModuleId = TM.ModuleId

I also find it improves the readability of the procs I write as well.

By Richard Edwards on Friday, March 13, 2009 at 4:58 AM

Thanks, great post. Would be interesting to know how the {objectqualifier} token is intended to be used though, as the VS starter kit inserts it and a hard-coded MyCompany_ prefix on all object names. I assume that the starter kit is misleading, that the objectqualifier is intended for company and the hard-coded prefix should read ModuleName_