.NET Answers

Now that we have our tables created, we are going to use the CodeSmith product that we installed last week to create our stored procedures.

We will also end up creating the SQL scripts that we will need to install this module into another DNN installation.

So, the first thing you need to do is to fire up CodeSmith. Under the DotNetNuke folder that we attached when we installed CodeSmith, there should be a template named “StoredProcedures.cst”. Double click that. This will bring up the wizard window that will allow us to create the stored procedures.

At the top of the windows, you will see “MultiSourceTable.” Put your cursor in that field and click the ellipse (…) button to bring up another dialog that will allow you to specify which tables you want to attach to.

One of the first things you’ll need to do is to attach to your DotNetNuke database. Click the ellipse button on the database dialog to add a new connection to CodeSmith. Click the Add button and fill in the form on the resulting screen. The easiest way to get the connection string is to go into the web.config file of your DotNetNuke installation and grab it from there. If your database is in the app_data directory of your DotNetNuke installation, remember to replace |DataDirectory| with the full directory path where your DotNetNuke database is located.

Once you have your connection established, you can select it from the list and select the database you want to create the stored procedures for.

I generally leave all the default options. You will need to fill in the ObjectQualifier with the prefix you used for your table names. Remember last week I told you to prefix your table names with something about your company? There are three reasons for this. First, it reduces the chance that some other company will use the same table name you decided to use. Kind of like name spacing your classes. Second, the wizard wants the object qualifier. If it doesn’t find the string you put in for the object qualifier at the beginning of your table names, it will prefix the code it generates with that code and you’ll have to fix it up. This is more of a problem with the other wizards we will be using, but it is still a problem. Third, when you go into the database to look for one of the tables you are working with, it will be a lot easier to find the group than to go looking for one out of a hundred.

At the bottom of the window is the “Generate” button. Click this to generate the stored procedure script which will show up already selected and ready to copy to the clipboard on the right. The first thing you’ll want to do with this script is to copy it into the 01.00.00.SqlDataProvider file that the DotNetNuke module creation wizard placed in your DeskTopModules/moduleName directory. The other thing you’ll want to do is to run this script in DotNetNuke.

Notice, you can not run this script anywhere other than in DotNetNuke because it has two markers in it that will get replaced by DotNetNuke when it is run.

The first marker is {databaseOwner}. In most systems this will get replaced by “dbo.” But, DotNetNuke allows you to specify another dbo user in the web.config file and if it is specified, that string will be used instead of “dbo”.

The second marker that will get replaced is {objectQualifier}. This allows multiple DotNetNuke installations to share the same database, or to share a database with another application with tables of the same name. Again, this is something that can be specified in the web.config file. If the string exists, {objectQualifier} will be replaced with that string.

Keep this in mind as you create other stored procedures that you may need. Anywhere you would normally use “dbo.”, you should use {databaseOwner} and anyplace you would specify a table name, index or key you’ll want to include {objectQualifier}. For example, tableName would become {objectQualifier}tableName and FK_Keyname would become FK_{objectQualifier}KeyName.

If you are using tools like Microsoft SQL Studio Express to create your scripts, this can be done with a simple search and replace operation.

To run this script we created, run your DotNetNuke development installation, log in as a “host” user and navigate to the “Host” > “SQL” window. Paste the SQL script into that window, check the “run as script” check box and click the “Execute” link.

You now have about 80% of your stored procedures in your database.

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

Most Commented Post

• Object Oriented Programming has Failed Us
• Microsoft’s Biggest Mistake with ASP.NET
• WordPress w/ Forms Authentication on IIS6

Bookmark to:

Hide Sites