I know I said I would stop beating this issue to death but I think I have fleshed it out a bit.

Instead of just complaining I think some of us could "contribute" to the project in regards to the DAL.

The approach I hope is considered is that a second database connection key be included in the web.config file like we had in 2.0.

Current modules that use the current DAL would continue to work, however new modules could have the opportunity to support the new connection.

This new connection would use the new DAAB. The new DAAB would allow Dotnetnuke to support SQl, Oracle, and DB2.

The problems with this idea (and the reason the core may reject it):

* The sql install scripts. How would the stored procedures be created?
A) The module install would only run the install script for the currently configured data source (if the module indicated that it was using the new data provider structure).
B) If an administrator changed the data source then Dotnetnuke would try to run the install scripts for all the currently configured modules that have an install script for that particular data source otherwise the module would be disabled (this would require a new field in the module configuration table to track the data source that the module was configured for and whether it was configured for the "legacy" (current) data provider or the new one. The install scripts would have the ".resource" extension to prevent them from being accessed directly).

* The new DAAB is rather large and does a lot more than database access. The other code in the .dll would be totally wasted.

I do understand why the core would say "listen guys you are trying to fix something that already works". However, I think that when the module developers, including the core, are only making sql providers then they are all saying that the extra coding needed to support the DAL is going to waste because the extra coding only make sense if you actually code more than one provider.

Telling us to "roll our own" provider is not the best way to keep the community on the same page. The module developers want to "stay in the fold" and do it the "official way". I am currently coding the DAL the official way. I keep wanting to "roll my own" but I always break down and code it according to Shaun's document (ok I really just use DAL builder pro :)

I understand that this may be a great idea but falls under the "who has time for this?". If the core is not working on this (so I know I would not be wasting my time) I would be willing to head up a group of volunteers to create a "proof of concept" to present to the core. I really feel there is support out there and that a lot of the module developers would code to the new method if it were implemented.

1. You would still use the "controller" and the CBO to hydrate your "info" object but you would simply call your stored procedure (or sql statement) directly from the "controller". This would eliminate a lot of code. Using the "src=" directive in your .ascx file, you could code a module using notepad that talks to the database. I think people would actually start giving away more free modules when they are able to create them quickly and easily.

2. When the administrator changes the data source:
* All modules that have an install script for the new provider would continue to work.
* The legacy modules that support that provider would also continue to work.
 (However what I think will happen is everyone would continue to only make SQL providers and that the only people who would change providers would be the people who are writing their own modules. However even they would appreciate the fact that they simply need to create a install script rather then code and compile a "provider").

In the end what I am REALLY trying to get at is I want to allow a person who WANTS to make dotnetnuke module the ability to make one. Right now a person has to have the ABILITY to make modules and the fact that you have to have Visual Studio and know how to use it to code the DAL means it is not easy.

My "Creating a DotNetNuke Module using notepad" gets a lot of hits. The problem is without the ability to connect to the database it's usefulness is limited.

The easier it is to make modules the more modules we will have. The more modules we have the "stronger" we become. We could have thousands of modules when we make it easy for the average person to make a module to get to their data. Right now too many potentially good programmers get frustrated and quit. Yes I agree they should "roll their own" provider but instead they quit. How many "roll your own" provider modules are there on snowcovered? Even I wouldn't want to buy a module that had a "roll your own" provider. If the core doesn't bless it then I really don't want a part of it.

I am asking the core if it would be worth my time to gather a group to pursue a proof of concept.

IMO, this is one of the few things missing from DNN. The ultimate solution, IMO, is complete abstraction of the backend for custom module developers.

Unfortunetely, this is an enormous task. You have to provide a 'SQL scripting' engine that will create/modify/delete a backend db regardless of db engine.

I don't pretend to have any solution, but you're right, 9 times out of 10, developers aren't going to create providers for their cutom modules to other dbs except SQLServer.

This is the biggest missing link in the portal to date, IMO.

I'm curious - how different, really, is an Access DAL provider from an SQL DAL provider? Or an Oracle or MySQL provider? Can't some "automated" mechanism be made to generate other providers off of a completed SQL provider? Would a tool like that make life easier for module developers?

Just curious, as I haven't dived into non SQL DAL providers, myself...

The real difference here, is the programmer! ;-)

An SQL Server/Oracle/DB2 programmer would and should use all the functionality of the database - views, store procedures, user defined functions-types, etc.

An Access programmer or MySQL programmer, on the other side, he has no such luxuries; An MySQL programmer doesn't even have views to use.

So a programmer that is used to have these luxuries, he can not change his place back to not having them, if he really doesn't have to, and he can not understand the others that don't want to use them and benefit from their speed, easiness to program, etc.

The application is very different, according the database it's designed to use. An application that is designed to work with Access or MySQL is doomed not to scale, not to perform in it's maximum, even it is well designed, user friendly and whatever. On the other hand an application that is designed to run on SQL Server/Oracle/DB2 is doomed to desire such an environment; to scale, perform at maximum, etc.

Designing and partitioning on Microsoft windows platform, teaches programmers to do that. To architect and develop for enterprise environments, to have in mind that may be today we have a 1000 users, but tomorrow we may have 100000 users to service. We can not rewrite the application in one night, so we have to partition our application in order to be able to scale up, in seconds. This is why we use, so many store procedures, views, user defined types, etc.

Maybe this approach makes the programmers very appealing to enterprise computing, but the makes them not appealing when coming to medium to small size computing. Then the cost becomes the first issue and when you have to service with low or no cost, the enterprise environment is a problem; you can not have an SQL Server/Oracle/DB2 database, and if you have windows platform it is a luxury.

I can not decide which is good and which is bad, I think both of the worlds are needed. We can “downgrade” our applications, or “upgrade” our environments.

DotNetNuke is a big application because it is ready to scale out-of-the box. PHP forums are not, nor they will ever be. The choice which to use, it must be the environment and not the desire what we would like to have.

George J.

For the most part I agree with you George. But I am not certain that what you say is the full truth. Ok I can see why Access is not a part of Dnn, cause of the scaling. But others scales well. And postgreSQL, MySQL and Oracle are all good examples. I am sure there must be a way to get the benefits of backend of choice without reducing the scalability or even performance on Dnn. There are other apps that gives you a choosing for your liking. And pls remember MySQL have a new major release coming soon.

I am not saying this issue is mandatory, but in my world this is one that will put Dnn even further in the lead.
When DAL is seperated from buisnesslogic it should really be a matter of writing the DB provider. Think of the main feature of Dnn, module based. So in theory we can have different modules running different DB providers. Kind of like with the language. App in VB but modules in C#. This could even be an argument for a borader audiance. More DB providers, more easy to plug in legacy apps or modules. And without using webservices.

I will not argue how much work will need to go into this, but I am certain DB provider of choice will not ruin Dnn's performance, nor will it take away it's ability to scale. And if something like this could be achieved I am sure we all would see an increase in members to our community, and heck we might even get more free modules or custom module.

Well I hope this make sense, it's 5 in the morning and I just got home from a late poolgame

The abstraction you are seeking could be accomplished with Enterprise Library from MS

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/entlib.asp

Michael,

I've just seen this post and I'm not quite sure why do you need ALL that extra work just to do what you need.
You want a simpler approach to create modules? Then why a new provider? You can do what you need without any extra provider. Or am I missing something?

You would still use the "controller" and the CBO to hydrate your "info" object but you would simply call your stored procedure (or sql statement) directly from the "controller". This would eliminate a lot of code. Using the "src=" directive in your .ascx file, you could code a module using notepad that talks to the database. I think people would actually start giving away more free modules when they are able to create them quickly and easily.

You can do this just include this code in your controller and you're done:

vmasanas wrote:

Michael, You can do this just include this code in your controller and you're done: return SqlHelper.ExecuteXXX(ConnectionString, DatabaseOwner & ObjectQualifier & "spname", params) Add the same code in the sqldataprovider constructor to your Controller constructor (to instantiate a providerconfiguration object to get the values for the db), a pair of fixes and this will be working!

As I see here, you will be ADDING the DB not REPLACING. What I mean is, let say, you are doing this for MySql, you will then need MySql as well as the MS Sql, don't you ?

dagilleland wrote:

I'm curious - how different, really, is an Access DAL provider from an SQL DAL provider? Or an Oracle or MySQL provider?

As I see, the differance is not that much as long as the DB uses Stored Procedures. That means Access has a bit more issues, but MySql 5.0 does have Store Procedures support.

gcapnias wrote:

The real difference here, is the programmer! ;-) An SQL Server/Oracle/DB2 programmer would and should use all the functionality of the database - views, store procedures, user defined functions-types, etc. An Access programmer or MySQL programmer, on the other side, he has no such luxuries; An MySQL programmer doesn't even have views to use.

Please pardon my ignorance, but are we using any DB specific features other than Stored Procedures, in DNN ?

Are we using Views ?

DocHoliday wrote:

Unfortunately, on a practical level this is true. I wish that DNN will become more independent of a given DB backend in the future.

In a practical way, I have zero live installations because cause no one has an SQL server on a hosted enviroment for free.

MySQL server has a wild beauty, but till MySQL v5 is out, we will be waiting. His new license schema made php core team take him out of their distribution. We don't know if the ISPs are going to provide it for free as the previous versions, and how soon after its release.

On the other hand, what will happen with SQL Express? Is it going to be everybody's favorite because it will be provided for free?

The time will tell...

George J.

IndianGuru wrote:

Please pardon my ignorance, but are we using any DB specific features other than Stored Procedures, in DNN ? Are we using Views ?

The membership module does use views.

I agree, that there is the possibility to overcome views, hard coding them to a new DB provider, or go a step even further, include them to web.config.

George J.

vmasanas wrote:

I've just seen this post and I'm not quite sure why do you need ALL that extra work just to do what you need. You can do this just include this code in your controller and you're done: return SqlHelper.ExecuteXXX(ConnectionString, DatabaseOwner & ObjectQualifier & "spname", params) Add the same code in the sqldataprovider constructor to your Controller constructor (to instantiate a providerconfiguration object to get the values for the db), a pair of fixes and this will be working!

"SqlHelper.Execute" as I am sure you know will only work with SQL server. the new DAAB uses ExecuteReader that will work with multiple data sources. (see this link).

At the time the DAL was written this new DAAB did not exist. The core would have had to create this functionality and it would have been a huge project so the DAL was a good solution.

However the new DAAB now exists. It will allow DNN to support multiple data sources and the module programmer would:

1.Create a stored procedure

2. Use code like this:

 Public Function GetContacts(ByVal ModuleId As Integer) As ArrayList
            Return CBO.FillCollection(db.ExecuteReader("GetContacts",ModuleId),GetType(ContactInfo))
 End Function

 3. Then simply bind to your class:

grdContacts.DataSource = objContacts.GetContacts(ModuleId)
grdContacts.DataBind()

 And you're done!

Every piece of