Create a dotnetnuke package with an easy-to-install SQL database

Question

I am new to dotnetnuke and asp.net in general. I need to create a module package that is easy to install on another DNN site. The problem is that SQL tables and other database objects must be added manually. I would like them to be automatically added when the package is deployed. As I said, I'm new to all of this, and a step-by-step explanation will be very helpful.

Thanks,

Jelena

Answer 1

OK. I went through this and would like to share with those who can still resist. After creating the package, you need to unzip it and edit some files. See the .dnn file in your package. There, as Hamlin pointed out, you need to add SCRIPTS (not files) that will execute SQL scripts and create tables, stored procedures, and other database objects. Here is the part of the code that I added to the dnn file. It is added to the component tag.

<components> <component type="Script"> <scripts> <basePath>DesktopModules\UserComments</basePath> <script type="Install"> <name>05.02.05.SqlDataProvider</name> <version>05.02.05</version> </script> <script type="uninstall"> <name>uninstall.sqldataprovider</name> <version>05.02.05</version> </script> </scripts> </component> <component type="Module"> 

There you need to specify the paths, file types, file names and versions. Then you need to create the data files that you specify in the manifest. I used {databaseOwner} and {objectQualifier} to make sure the new database objects match the server on which they will be installed. They are case sensitive, so be careful, otherwise you will receive errors. This is what my dataprovider files look like:

02/05/05.sqldataprovider

 ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE {databaseOwner}{objectQualifier}usercomments( [moduleid] [int] NULL, [comment] [text] NULL, [date] [datetime] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO create procedure {databaseOwner}sp_viewcomments @moduleid int AS BEGIN SET NOCOUNT ON SELECT moduleid, comment, date from {objectQualifier}usercomments where moduleid=@moduleid end go create PROCEDURE {databaseOwner}sp_usercommentsinsert @moduleid int, @comment text, @commentdate datetime AS BEGIN SET NOCOUNT ON; insert into {databaseOwner}{objectQualifier}usercomments (moduleid, comment, date) values (@moduleid, @comment, @commentdate) END go create PROCEDURE {databasOwner}sp_countcomments @moduleid int As begin SELECT count(*) from {databaseOwner}{objectQualifier}usercomments where moduleid=@moduleid end go 

uninstall.sqldataprovider

 DROP TABLE {databaseOwner}{objectQualifier}usercomments GO drop procedure {databaseOwner}sp_usercommentsinsert GO drop procedure {databaseOwner}sp_viewcomments GO drop procedure {databaseOwner}sp_countcomments go 

Make sure sqlconnections are suitable for the new site and, if necessary, make changes to the files containing the connections (in my case, I had them in vb ascx.vb and ascx fle). I used this code to pull information from the web.config file and make the connection suitable for any site.

vb file:

  Dim cn As New SqlConnection(ConfigurationManager.ConnectionStrings("sitesqlserver").ConnectionString) 

ascx file:

 ConnectionString="<%$ ConnectionStrings:SiteSqlServer %>" 

Then pack all the files, including the new dataprovider files, into a zip file, and you should be good to go.

Answer 2

This is handled by SqlDataProvider files.

Please note that when you create the DotNetNuke Compiled Module project in VS2010 (or VS2008) you get three of these files, two of which are of concern here (I think)

• 01.00.00.SqlDataProvider is executed when the module is installed
• Uninstall.SqlDataProvider starts when uninstalling

Note that there are entries in your DNN manifest file pointing to these SqqDataProvider files:

  <file> <name>01.00.00.SqlDataProvider</name> </file> <file> <name>Uninstall.SqlDataProvider</name> </file> 

Also note that in the manifest file, the version number matches the prefix in the installer SQL file:

 <version>01.00.00</version> 

Finally, you will pack your DNN module into a ZIP file. The exact structure evades me, but DNNCreative and the book referenced below contain a lot of information.

Once you have the .zip file to be deployed, you will install it just like any other module that you can buy with SnowCovered.

My suggestion is to do the following

• Stay tuned for recent DNNCreative custom module development tutorials. They explain this process in great detail.
• Read Mitchel Seller’s book: http://www.amazon.com/Professional-DotNetNuke-Programming-Mitchel-Sellers/dp/0470171162 - It explains all the funny tweaks to the SQL scripts / manifest to support multiple versions of your module.

I used both resources and found them very useful.