You are here

Klotin SqlTable

Klotin SqlTableAbout The Module

SqlTable is a module for DotNetNuke that allows display of custom data in a table.

Admin (or anyone who has Edit rights on the module) can define Sql query and output it as a table for other visitors to see.

The module gives absolute freedom to the editor. Any data that can be accessed with the default DNN Connection String can be output with no limits. It also lets defining custom styles for the outer div, table, essential rows and columns, but no styles are predefined so you have to be a a little bit a developer or web designer to style out the table the way you'd like it to be.

The table also supports sorting and transposing, but those settings are experimental and might have issues. Table is wrapped into an UpdatePanel for the ajax sorting.

Additional feature is handling of input parameters. There are several predefined parameters and query string / page form parameters, cache and session variables, cookies and server variables can also be used:

The module gives you freedom, but it comes along with responsibility. Protect yourself, be careful with the way you use the module.

How to use

1. Add Klotin.SqlTable module to the page and set Page / Module permissions so that noone except people you really trust could edit the module settings (very important). The module itself appears empty.

Empty SqlTable

2. Use menu and select Edit SqlTable Settings

3. Add your own Sql script that would output only one record set.

Sql Table

If your script uses temp tables / updates / inserts, you would need to set NOCOUNT ON in the beginning of your script.

You can also use the following parameters:

  • $(tabId) - current Tab ID
  • $(tabModuleId) - current Tab Module ID
  • $(moduleId) - current Module ID
  • $(userId) - current User ID
  • $(parameter) - Cache, Session, Query String or Form parameter, Cookie value or Server Variable - the string values have single quote symbol escaped and are wrapped into single quotes

String values are wrapped in quotes to try prevent Sql Injection attacks, but regardless of that, please be very careful with both - what data you are showing and what parameters you are using. 

4. Once doe with Sql, you must retrieve columns using Run Sql and Get Column Info button. It will sabstitute all parameters with empty strings and will attempt running Sql. That is why you have to use string variables to read the input parameters.

Klotin SqlTable column settings

5. You can define some settings for each column:

  • Caption for the table
  • Encode - use HtmlEncoding to suppress cross site scripting attacks
  • Css class for the cells in the column
  • Format for the cell lets you slightly transform the data. For example, if it is a datetime, format string 'date: {0:yyyy/MM/dd}' will convert '03/11/2012 07:25 PM' to 'date: 2012/03/11'

6. Transpose - you can transpose the data, but this was not tested thoroughly yet.

7. Sortable - will let user sort the table by all columns.

8. Cache - how long to cache the data on the web server without going to the DB. 0 means data is not cached.

9. Styling - next sections let you define the styles for the table, wrapping div and columns. This lets you customize the look and feel of the table.

Klotin SqlTable styling

10. Once done - click Save and the module will show your data

Filled Klotin SqlTable

Version History

Version Date DNN Notes
01.00.05 5/6/2012 5.x,6.x
  • Increased sql script length to more than 2000 chars
  • Added value formatting support for the transposed tables
  • TabId, moduleId, tabModuleId and userId are inserted as integers into Sql scripts, without quotes around them
  • In transposed mode, first column (rotated header) is not html-encoded
  • UpdatePanel is not used when Sorting is disabled to increase performance
  • Removed unneeded css links
01.00.04 4/5/2012 5.x,6.x
  • Added support for Cache, Session, Server variables, and Cookies
01.00.03 3/24/2012 5.x,6.x
  • Stability update
01.00.02 3/18/2012 5.x,6.x
  • Bugfixes
  • Settings ajaxified
  • Dimming the table on sort
01.00.01 3/11/2012 5.x,6.x
  • Initial public release

Licensing

 

The module is licensed per production DNN instance. Clustered DNN configuration is considered a single instance. Publicly accessible DNN is considered a production.

Download

You can purchase and download the module from the DotNetNuke store.

Related

The module is a part of the Klotin Sql DNN Productivity Kit. The other modules in the set are:

If you have any comments or suggestions, you can either email me or use a form below.

 

Social Buttons: 

Comments

 #

Hi

I've struggled (understatement) to find a solution to showing users there own information. The standard Reports module doesn't (16 Nov 2012) support parameters unless they are passed in a URL

This has made my life so much easier.

I'll be posting a recommendation for this module on dnnhero.com as I'd asked how you achieve precisely that - this module has solved my problem

Many thanks

Dave

 
 #

Exept I can not get my data, witch is in a remote database.

 
 #

You can access remote database by using 'database_name'.'dbo'.'table_name' if it is on the same server or you can use MsSql server links and use it by 'link_name'.'database_name'.'schema'.'table_name', although this method has its drawbacks.

 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.

Get Updates

RSS Twitter

Subscribe by Email:

Recent Comments

Who's online

There are currently 0 users online.