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

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.