Hi,

If I wanted to create a separate SQL 2005 database to contain tables that are specific to a particular client, how difficult would that be? I assume I would have to create a separate set of providers along with a separate xml config file. Where would I look for guidance and documentation regarding best practices? Has anyone done this? Would you be willing to give me some sample code?

Thanks,

GM

 

Hi,

Thanks for the quick reply. I have a portal on a DNN installation for a particular client (customer). I need to create a DNN module that handles a number of customer tables that are connected by referential integrity constraints in another SQL Server 2005 database. Rather than creating duplicate tables in the DNN database for this specific customer I wondered how difficult it would be to create a DNN module and access these tables in a database that is not the DNN database but a completely separate one. What I would actually do is refresh the tables periodically from customer data. All of this data is highly specific to this customer so I thought keeping it completely separate might be a good idea. The standard Table Module is nice but for this particular case won't work very well for the data in all these interrelated tables.   

I do this all the time.  If you are building a module that is truly custom to you only, you can really do whatever you want.  The way I handle this is by developing my module as you normally would.  Then, in your stored proc, just reference the other database.  This assumes the other data you need is on the same server as your dnn database, but it does not need to be the same database.  Something like this...

SELECT
    'username' = u.username,
    'firstname' = u.firstname,
    'lastname' = u.lastname,
    'hiredate' = e.hiredate,
    'department' = e.department,
    'jobcode' = j.jobcode
FROM dotnetnuke.dbo.users U
JOIN companydb.dbo.employees E ON e.employeeid = u.username
JOIN hrdb.dbo.jobcodes J ON j.employeeid = e.employeeid
WHERE u.username = @username

The magic here is finding some way to map users from one databse to another.  There are lots of ways to do this.  The way we handle it is the DNN username is always the employee's employeeid.