creating a dotnetnuke package with easy to install sql database

Question

I am new to dotnetnuke and asp.net altogether. I need to create a module package that is easy to install on a different DNN site. The problem is that SQL tables and other database objects need to be added manually. I would like them to be added automatically when the package is deployed. As I said I am new to all this and a step by step explanation would be very helpful.

Thanks,

Jelena

Answer 1

This is handled by SqlDataProvider files.

Note, that when you create a DotNetNuke Compiled Module project in VS2010 (or VS2008), you end up with three such files, two of which are of concern here (I think)

• 01.00.00.SqlDataProvider is executed upon Module Installation
• Uninstall.SqlDataProvider is run upon Uninstallation

Note in your DNN Manifest file, there are entries pointing to these SqqDataProvider files:

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

Also note, in the manifest file, that the version number corresponds to the prefix on the installer SQL file:

<version>01.00.00</version>

Finally, you will package your DNN Module into a .zip file. The exact structure evades me, but DNNCreative and the book referenced below have plenty of info.

Once you have the deployable .zip file, you install it just like any other module you might buy off SnowCovered.

My Suggestion is to do the following

• Watch some recent DNNCreative training videos on custom module development. 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 fun tweaks to the SQL scripts / manifest to support multiple versions of your module

I used both resources and found them very useful

Answer 2

O.K. I have gotten through this and would like to share with those who may be struggling still. Once you create a package you need to unzip it and edit some files. Take a look at the .dnn file in your package. There, as Hamlin pointed out, you need to add the SCRIPTS (not files) that will execute SQL scripts and create tables, stored procedures and other database objects. Here is the portion of the code I added to the dnn file. It is added to the components 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 put in the paths, file types, file names and versions. Then you need to create the dataprovider files you indicated in the manifest. I used the {databaseOwner} and {objectQualifier} to make sure the new database objects comply with the server they will be installed on. Those are case sensitive so be careful, otherwise you will be getting errors. Here is what my dataprovider files look like:

05.02.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 that the sqlconnections are appropriate for the new site and make changes if necessary in those files that contain the connections (in my case I had them in vb ascx.vb and ascx fle). I used this code to pull the information from the web.config file and make the connection appropriate for any site.

vb file:

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

ascx file:

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

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