I have an existing database that has GB's of data in it that I need to build web reports from, but still want everything managed & accessed within DNN. I have 2 questions about the best way I should go about this... 1) How can I reference another DB from a custom module and still use the available DNN DB info too? Can I use the same data access class for this or should I make a new one? Example: I want my module to send the current DNN user id to my other DB so it knows to get only that user's info. I plan on keeping a user conversion table in the other DB to make this practical. Examples or detailed instructions would be very helpful as I'm new to custom module building. 2) I will be making dozens of reports that all have very different parameter options but the output is all Crystal Reports reports (which can be called with just a parameter list and name of report), so I need to know if it would make more sense to encapsulate the data access and report call in separate dll's and keep all the different parameter controls in a 3rd module and call each control dynamically somehow as opposed to making complete separate modules for each report? So.. if I put all the controls in one module, any thoughts on how to call each one dynamically?

As far as accessing a separate db than DNN...

Someone correct me if I'm wrong, but to the best of my understanding, DNN, through the methods provided by the module base class (and the sibling methods of the partial class for modules in asp.net 2.0 / dnn 4.x)  uses the DNN SQLDataProvider to access the DNN database.

So the data provider you author for your module can access its own database with no problem at all.

The module you right then can use the base methods for DNN to access DNN information through the DNN data provider while using your own data provider to access a completely separate database all together.

One thing I personally have not investigated is whether or not I can ask for two separate data providers... but I can't see why it wouldn't be possible.

Bill

Thanks for the input.

I did some experimenting and I was able to access the other DB by just changing the connection string. I am currently expoloring the best place to put the DB access code. One thing that I'm considering is dropping the entire seperate SqlDataProvider provider project. As long as I'm only building custom modules for my own use and don't plan to package them for others, I don't see the point is doing what seems to be double work (putting functions in both DataProvider to be overwritten and SqlDataProvider) so I thought I'd simplify things and combine them into the main module project.

Can anyone think of any reason to not ditch the SqlDataProvider project?

I also don't see how the parameters are assigned to the sProcs when using the base class data functions such as "SqlHelper.ExecuteReader". Do they just need to be listed in the same order as they are in the sProc?

If so, this seems kind of scary since it could be easy to mis-order parameters accidentally. Personally, I began writing all sql parameters to a hash table (field name / value) from the BL and have my own DL that loops through it and sends whatever parameters to the sProc by name. What methods of sending parameters do most people use?

 

Suggest you visit this link http://opensource.indyneinc.com/home/DevX/Articles/SingleSignOnforDotNetNuke3/tabid/99/Default.aspx and review the option to use secondConnectionString.  This option allows you to acess DNN db and your own db using the same SQLProvider project.

Should one have a separate SQLDataprovider project?  There are code gen template (ex. codesmith template from http://dnnjungle.vmasanas.net/) that will auto gen code using the the data provider model.  Stick with the model will have less code to write.

That was interesting and helpful. Thanks!

I just want to share something else I learned while looking into this...

The generic data access class that is ised in the DNN template module is actually coming from "Microsoft Application Blocks for .NET". If you do any work outside of DNN, since DNN already uses it, you must check it out. I built my own general purpose class for getting integers, datareaders, datatables, etc. previously, but could have saved a lot of work by just using the classes in this. It is very inclusive. It also has an exception handler too.