Home » DotNetNuke - Module Development » DotNetNuke – Modules – Creating Stored Procs

DotNetNuke – Modules – Creating Stored Procs

image 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.


Other post in DotNetNuke - Module Development

Like this Article? Subscribe to get every article sent to your email.

Related Post

  • DotNetNuke Modules – Creating Base ModulesDotNetNuke Modules – Creating Base Modules Now that we have DotNetNuke installed into Visual Studio we can go ahead and create our first modules. Actually, creating the modules is pretty simple. But it is even easier to do it […]
  • DotNetNuke Modules – Data Access LayerDotNetNuke Modules – Data Access Layer Now that we've created our stored procedures, we are going to use some of the other CodeSmith templates to generate our data access layer (DAL).Keep in mind that the templates you are […]
  • DotNetNuke Modules – Where Stuff Shows UpDotNetNuke Modules – Where Stuff Shows Up We continue our series on creating DotNetNuke modules today by showing you where the various components of the module we created last week show up in DotNetNuke.Remember, last […]
  • DotNetNuke Modules – Module SettingsDotNetNuke Modules – Module Settings Since each instance of a module that we put on a page should be able to have it's own configuration information, it is necessary to have some place that will allow us to configure […]
  • Creating DotNetNuke ModulesCreating DotNetNuke Modules I've decided to start a series on how to create DNN Modules since I have several clients who need to know this information and I don't know of one good place I can send them that has it […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer. His commitment to quality through test driven development, vast knowledge of C#, HTML, CSS and JavaScript as well as his ability to mentor younger programmers and his passion for Agile/Scrum as defined by the Agile Manifesto and the Scrum Alliance will certainly be an asset to your organization.

  • kolors

    GOOD JOB!!!

    Without this i would need much more to create my module and even then it would be crap!!!