Accessing External Data Workaround - Part 2 (More examples) This blog follows Luke's blog on 'Accessing External Data' workaround with RAD. Please note that we are saying this is a workaround since you cannot use the RAD Control Panel alone to create it. There are various other modules which request you to use the SQL command from the HOST menu, or the SQL Management Studio, notepad, or any other tool not inside that module as part of their solution - but due to our usability policy we are committed to provide this solution 'out of the box' in version 1.20. Using Luke's workaround one needs to follow these steps: - Check your RAD's Application Id. Check in the table list in SQL management studio for tb_DNM_X_ tables. The X is the Application Id.
- In SQL management studio (or in the SQL command in HOST) create a new view called tb_DNM_X_ViewName, where X is your Application Id and ViewName is the name with which you will see your external data from RAD.
- In DotNetMushroom RAD create a Query in which you write 'SELECT * FROM ViewName'.
- Use this query in your RAD forms.
Let's assume that your Application Id is 7 and we are in Portal 5. If you want to access all the Users of your Portal from RAD you will need to create the following view:
|
SQL View Name
|
tb_DNM_7_PortalUsers |
|
SQL Create View Script
|
CREATE VIEW tb_DNM_7_PortalUsers
AS
SELECT UserId as pk,* FROM dbo.Users INNER JOIN dbo.UserPortals ON dbo.Users.UserID = dbo.UserPortals.UserId WHERE (PortalId = 5)
|
|
RAD Query Text
|
SELECT * FROM PortalUsers |
Let's assume that in the same application you want to access a table called tb_Stock in database W5 (in the same server as DNN) and the DNN user has rights to access this date.
|
SQL View Name
|
tb_DNM_7_Stock |
|
SQL Create View Script
|
CREATE VIEW tb_DNM_7_Stock
AS
SELECT NEWID() as pk, * FROM W5.dbo.tb_Stock
|
|
RAD Query Text
|
SELECT * FROM Stock |
Do not hesitate to contact us in our forums for more information and queries. |