The bitboxx bbquery module is a DNN module for querying and editing database tables. Its intention is more for intranet than internet. You can define database querys (selects) to every kind of database (if drivers are installed) and even edit the tables if configured like this. if you or your employees need to fire every morning the same query t…
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
App_LocalResources
Components
Controls
Documentation
Installation
Properties
.gitattributes
.gitignore
BBQuery.csproj
BBQuery.csproj.vspscc
BBQuery.sln
BBQuery.vssscc
EditParameters.ascx
EditParameters.ascx.cs
EditParameters.ascx.designer.cs
Module.css
README.md
Settings.ascx
Settings.ascx.cs
Settings.ascx.designer.cs
View.ascx
View.ascx.cs
View.ascx.designer.cs

README.md

Project Description

The bitboxx bbquery module is a DNN module for querying and editing database tables. Its intention is more for intranet than internet. You can define database querys (selects) to every kind of database (if drivers are installed) and even edit the tables if configured like this. if you or your employees need to fire every morning the same query to the database to check if nightly production went well and every record is on his right place, this is the module for you! The minimum DNN Version for this module is 06.01.00.

These are the main features:

  • Define Database Query per SELECT Statement
  • Query every database type with installed OLEDB Drivers
  • Editing, Inserting and Deleting records
  • Role Based permissions for INSERTs, UPDATEs and DELETEs
  • Define Parameters for search (visually or only per URL-Parameter)
  • Define the fields which should be displayed as links (one-to-many relations)

bitboxx bbquery sample image

Documentation

General settings

After the bitboxx bbquery module is added to a page the settings (via "manage" - "Settings") must be edited first. In addition to the normal module settings,  the special BBQuery module settings are found in the last tab.

Select

Database Provider Please select the database provider for connection to your data source
Connection String Please enter the connection string for connection to your data source
Select Command Enter the SELECT SQL command which selects the data to be shown in result view
Show SQL Shows the SQL statement which leads to the displayed data
Hide module if checked module is only shown if one of the defined search parameters is filled

After saving the settings, the SELECT query is immediately executed and the results are displayed. Columns In the grid could be sorted by clicking the column headers.

Edit Settings

The administrator could define individual roles for adding, editing and deleting permissions to allow users  some users the changing of database content. If editig is enabled, the primary key the and corresponding SQL statements for updating, inserting and deleting should be entered. All fields to be modified must be included in the SQL statement, because otherwise they are not taken to account when you run the command.

Edit Settings

Allow Edits / Inserts / Deletes Check if you want to allow special users to edit / insert / delete records
Primary Key Please enter the primary key of your selection (needed for inserts, updates, deletes)
Edit/ Insert / Update – Role Select the role a user needs to be allowed to edit, insert or delete records

Parameters

Parameters are the “variables” in the database query. They can be defined in the parameters section.  After clicking on "new",  you could specify the name of the field and its data type. The field name (and type) must match one of the fields in the selection.

Parameter Settings

Field Name Please enter the field name of the search field
Datatype Please enter the data type of the search field
Show in search Select if the parameter should be shown as a search field or only be a non visible URL parameter

Parameters that are not “shown in search” could only be used in the URL (eg. http://www.mydomain.de/pagename.aspx?field1=value1&field2=value2). But remark that the advanced query options (“>”,”<”,”contains”, “AND/OR”) could not be used when using URL syntax. If you added one or more parameters to the search fields, they could be easily used to filter the selection. Parameters could be combined using AND or OR. Empty parameters are ignored ! If “Show Sql” is enabled you can see the actually used SQL statement  on any time.

Search

Link Settings

The link settings are used to define one or more fields, which should act as a link to a detailed view of the selected record (e.g. one to many scenario). For this please enter the name of the fields as a comma separated list.

Link Settings

Sample

If you would like to have a master-detail view of orders and order related items, you need two modules for this. The first will display above the orders as in the example. We define the OrderID as the link field and as a parameter field.

The second module (SELECT * FROM BBStore_Orderproducts) needs the OrderID too as a parameter. But we define the module as only displayed if the data is filtered (option "hide module"). If you click now in the orders module on the link of an order, the corresponding position sets are displayed in the positions module.

Sample