Learn More





Retrieving and Modifying Data using SQL

Return to previous page

  • 4/7/2015



Retrieving and Modifying Data using SQL

Last updated 4 years ago



(Enter the content of this article below)


Nearly all content and metadata for DNN core framework and modules is stored in a common database, you specify during installation. This data may be retrieved by any database tool or using Host module "SQL". You may even modity data using these tools, but this should always be done with care and usually after performing a database backup, because you may accidently damage the database structure, affecting function or stability of your DotNetNuke site.


If you are using Microsoft SQL Server (most common scenario) you may access the database using either

Note: If you are using a user instanced database (connection strings in web.config contain "database.mdf" file name and "user instance=true" option) instead of an attached database, you cannot access the database using a non-DNN tool the same time and need to detach from database before closing the tool.

Connection Strings and SQL Server user account

DNN is accessing the database using the connection string SiteSQLServer, specified in your web.config. This connection string is specified again in app settings for backwards compatibility, make sure to keep both in sync.
For attached databases, the connection string contains either username and password of a sql server account or use integrated security, i.e. the windows account of the user currently logged on. If you are accessing the database from your windows account, this will be your user name and password, if a web application like DNN is accessing the database, it is using the windows account specified in IIS application pool.

Exploring Database Structure

To explore the structure of an attached database, start SQL Server Management Studio (SSMS), login to your SQL Server and open the database explorer tree on the left. Select your database to see all object trypes inside. One of the first nodes is "tables", listing all the tables of your database. The next one is called views and in Programmability, you will find functions and stored procedures. For security reasons, nearly all database access from DotNetNuke is done using stored procedures.

Right clicking the database node offers options like backup and restore, shrink, run reports and access database properties.

Right clicking a table or view name offers e.g. displaying top 1000 rows, editing top 200 rows or modifying table definition including fields, indexes and relations or edit sql code of the view's definition. E.g. selecting top 1000 items of Tabs table will display data of the first 1000 DNN pages (across all portals of this instance). Retrieve and edit options are available as well in Visual Studio data tools, WebMatrix 2 and web based tools.

Right clicking a function or stored procedure offers to edit its definition or execute the function/procedure. if you execute stored procedure "GetAllTabs" you will get about the full list of all DNN pages in a specific order.

Retrieving Data using SQL

To retrieve data from the database, you may also use SQL statements in SQL Server Management Studio (SSMS) by right clicking the database root node and select "New Query". You will get displayed a blank text sheet, which acts like the text box in Host > SQL menu item of DNN.
You may enter a simple query like
SELECT TabName, TabPath FROM Tabs WHERE PortalId = 0

Results will be displayed in a grid beneath the query window in SSMS or in an HTML table at the bottom of SQL module inside DNN.

Database Owner and Object Qualifier

During installation of DotNetNuke, you may specify an object qualifier, which will be preceeding every name of a database object created by DotNetNuke - this is helpful, if there are other applications using the same database (note: tables of Microsoft Membership Component are always prefixed by ASPNET instead, even if installed with DNN). If you specified a nonempty value, but e.g. "DNN", the query above will not return any value, i.e. you need to run
SELECT TabName, TabPath FROM DNN_Tabs WHERE PortalId = 0

Same situation applies, if your Hosting provider requires you to use a database owner other than dbo, e.g. "mySiteOwner". In this case you'll need to run
SELECT TabName, TabPath FROM MySiteOwner.DNN_Tabs WHERE PortalId = 0

DotNetNuke installer and SQL module uses two placeholders to support those scenarios:
  • {databaseOwner}
  • {objectQualifier}
    Note: names are case sensitive! Placeholders are supported only within DotNetNuke.

I.e. if you don't know the values of database owner and object qualifier you may use
SELECT TabName, TabPath FROM {databaseOwner}.[{objectQualifier}Tabs] WHERE PortalId = 0

Note: and have been added around table name to support special characters being used.

Editing data using SQL

You may use SQL statements as well to modify data, e.g. edit values, add or delete rows.

Warning: Please be very careful, when editing data created by DNN core framework or modules, you might affect stability or function of your websites.
Executing the following statement will add Admin menu to the end of your main menu bar:
@@UPDATE {databaseOwner}.{objectQualifier}Tabs SET isVisible = 1 WHERE TabPath Like '\\admin'
Note: you need to restart your application in order to become database changes effective for your running DNN instance.

Final Notes

Although you may edit values directly in the database, module developers should always use DNN API methods, which will ensure consitent changes being made in the database, which might affect multiple tables (e.g. adding a page will result in additional values of tabsettings and tabpermissions tables as well).

Accessing the database directly might be of big help, if consitency of your database has been affected or modifications are required, which are not supported via UI.
No sections defined
Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
Need More Information?