Note from the Editor:

The following is a workaround article relevant to DotNetMushroom RAD version 1.1X.XX. Since our priorities for version 1.1X.XX were usability and security, as well as due to the facility to package a whole application and unpackage it on another installation, resulted in this version does not have 'direct' access to external data sources.

It is a HIGH priority in our road map to add external data sources for version 1.2X.XX 'out of the box' - and at the same time keep the main priorities of usability and security, as well as a solution for unpackaging applications with external data sources. In the meantime Luke's blog will give you an insight of a temporary workaround. This blog is part 1 of 3 planned articles. Part 1 (this article) is 'the theory' of how to create a workaround and an example. Part 2 will give more examples. Part 3 will extend the spectrum and more insight into this hack.

Please use the Forums to ask more information on this issue. Thanks.

The Problem

The deeper one delves in the realm of DNM RAD development, the higher the chances for one to require access to data which resides within tables, or sources outside the 'official' reach of DNM RAD.

One similar scenario involved me having been tasked to retrieve data which resides in a DNM RAD Table (so far so good). However, the records needed to be filtered by the creator's group (and here we're off). The crux of it all was so that all those users who are part of the DNN Group of the Creator's would have read access to the record. This feat would normally be considered 'illegal' by DNM RAD rules and regulations.

Fortunately, for those of us who want to take it to the next level, it is possible to go underground and use clandestine methods in order to reach that data which lies 'outside'. And by outside I mean inside the SQL Server where the DNM RAD resides. By making use of Views, Functions and Stored Procedures, one can gain access to a wider range of data.

The Setup

Let's start off from the basics...

DNM RAD uses a simple prefix for the tables it creates. This prefix is the key for our 'backdoor', which allows us to 'trick' the RAD into doing our bidding. If you open up the SQL Server Management Studio, and view all the Tables which were created by DNM RAD, you'll notice a simple pattern...

As you can see above, those Tables which were created by DNM RAD have a similar pattern. Each Table which was created by DNM RAD has the 'tb_DNM_' prefix. However, one should note that another pattern exists which includes the previously mentioned prefix. The 'tb_DNM_1_' prefix indicates that the table in question is not only DNM RAD related, but also, makes part of Application Number 1. Should you have multiple Applications installed within the same DNM RAD Installation, this prefix will change and adapt according to the Application it represents. This means that the second application that you create will have the standard 'tb_DNM_' prefix, and then it will append the Application Number (i.e. 2) to it, and the resultant prefix will be 'tb_DNM_2_'.

Now all that is required of us is to create a View which retrieves the data from whichever source we desire.

It is important to note, that in order for DNM to make use of the retrieved data, the View needs to have a column named pk and it must be a unique identifier and it must be in LOWER CASE. If the Table from where the data is being retrieved contains a Unique Field, just return that field, making sure to use the 'AS pk' SQL in order to rename the Column. Should a Unique field not be available, just make use of the NEWID() SQL function and rename the Column to 'pk'

I usually have a habit of creating an underlying Function which usually allows me to perform more complicated data manipulation, and return its results through the View, however, as I like to say, one is only limited by his own Imagination.*

Once the View is created, save it, and make sure to mimic the Prefix which DNM RAD Assigns.

In the above image, we have entered the default 'tb_DNM_' prefix, and then appended the Application Number to it 'X_'. Of course X represents the Application Number in question. Finally, we have entered the name we wanted 'vw_SampleView'.

Once the view is ready and it's name mimics the names which DNM RAD assigns to the tables it creates, we can then proceed to the DNM RAD, and continue our 'illegal activities' there.

The Scam

In order to access the data which is made available in the views, we need to perform one final step to trick the DNM RAD into thinking the View is one of its tables. So, let’s proceed to the DNM RAD Queries, and create a new query which will accommodate the View which we have created previously.

Once in the Query form, enter the Name of the query, and then write the SQL Statement which will retrieve the data from the said query.

SELECT * FROM tb_DNM_X_vw_SampleView

Now, if you have ever attempted to run queries before on the DNM RAD tables, you would have noticed that to reference a DNM RAD table, you only need to enter the 'friendly' name which is visible through DNM RAD, and not it's Full Name. This logic needs to be implemented here as well.

Since we have mimicked the DNM RAD Prefix, our View has a prefix similar to the Tables. We can now con the DNM RAD into accessing the View which we have created beforehand, just as if we are accessing a DNM RAD Table. As such, the above SQL Statement should be shortened to:

SELECT * FROM vw_SampleView

Execute the query and see with your own eyes. The result set of the View is now being loaded by the query. Thus, the data is now available throughout DNM RAD. Make sure to SAVE the Query, otherwise you won't have anything to work with. Now, all that is left for us to do is to bind our forms or controls to the Query we have just created.

This method may be a rather manipulative, Machiavellian, and a crude way of achieving this, however it gets the job done. Although we are currently working on a more user friendly approach, which will allow the user to carry out this 'query' directly from DNM RAD, but for the time being, this can satisfy most of the data retrieval requirements of every user.

Also, we will later on post further details on how to take this 'con' to the next level by making use of Linked Servers, but for the time being, enjoy this cute little hack.

* Note that the Views which return the data, should not be filtered out already, but should return a generic dataset which will then be filtered by DNM RAD. This logic will provide us with a higher degree of flexibility later on in the development stage.