Creating a SQL DataSource

Use the Projects Tree or the Project Configuration dialogue to Create or Edit a new SQL DataSource.

Configuring the SQL DataSource

To setup a Microsoft SQL Server DataSource:

• Enter the Server Name\Instance Name (or IP Address,Port), this can be a parameter from the Parameter Store.
• Add the Authentication Modes that you want your app to support

• Enter the name of the Database to connect to or open the drop down menu and select the Database from the available list. The list is refreshed when the drop down first opens if the server and login information provided is correct. The Database name can be a parameter from the Parameter Store.

• Specify a time in seconds for the commands to Time Out.
• Click the Test button to check if the connection can be opened.

Configuring the SQL DataView(s)

DataView have standard and custom configurations areas. This section describes how to setup SQL DataView custom area. Refer to the standard section for information about setting up Parameters, Data Pivotor Calculated Columns.

To add a new DataView click the New DataView button. You can edit existing DataView by selecting it in the grid and clicking Edit, or by double clicking the DataView in the grid.

• A SQL DataView configuration requires a SQL query to pull data from the server. In the Read tab, type the SQL (T-SQL) query to send to the server to pull data. It can be a simple select query, use JOIN or complex T-SQL syntax, be a stored procedure etc.
• Click the SQL Editor link to open the SQL Editor dialogue:
• Supports SQL and parameters syntax highlight
• It shows all tables, views and SPs from the server
• It creates simple select statements and joins by Double Clicking the nodes or Right Click > [Actions], with options to
• append the action text instead of replacing the current content by holding the Ctrl key pressed
• layout the query vertically by holding the Shift key pressed 

• You can click the Test button to check if the query is valid and how many results it returns.

Check out the SQL walk through section for configuration and query examples.

Defining Writing Permissions

If necessary you can also specify how to write data back to the server.

In the Write tab, for each column where you want to allow users to edit the data, you must specify:

• The name of the Column as it is returned by the query defined in the read section.
• Optionally the name of the Column in the target table if different from the name loaded by the query.
• The name of the Table that contains this column, the name must be the 2 parts name if the table does not belong to the dbo schema (i.e. Schema.Table).
• The name(s) of the ID Column(s) in the table, i.e. the column(s) which identify the object on the target table. if there are multiple columns the names need to be delimited with a comma. These columns must also be returned by the query with their original name.

• Under Script you can define a custom Update Script that replace the default automatically generated, to run procedures, post update scripts and other custom T-SQL  

You can find examples of column permissions set-up in the SQL walk through section. 

Defining Allowed Values

By default, when editing a column, the user can enter any value of the proper data type. By defining Allowed Values for a column, you restrict the user input and the editing form will display a drop down with the values you defined only.

To define Allowed Values for a column:

• Click the <Any> link in the Values column
• in the Allowed Values dialogue, select whether you want to explicitly define values, or use a custom Query
• In Explicit mode:
• Select a data type for the values. the type must be consistent with that of the column
• Enter the required values in grid. You can specify a value that needs to be of the proper type in the Value column
• Optionally you can add a different display name in the Display Value column. The user will see the Display Value, but the actual Value will be sent to the database
• In Query mode, the values will be dynamically loaded from the database:
• Select a frequency to reload the Data Validation:

• Once: the values are loaded once, the first time the sheet is refreshed, and kept in cache for the duration of the BimSens session
• OnViewRefreshed: the values are cleared and reloaded every time the view is refreshed
• Always: the values are not cached, and reloaded from the sheet every time they are needed

• Enter a Query to load values; the Query must return a list of values of the proper type, and optionally a second column with an alternative display name.

Create, Delete and other Advanced Operations

Advanced Operations, or Actions, allow you to associate buttons in BimSens's interface to stored procedures or SQL statements on the server.

To create an Action, open the Advanced tab and click the New Action button.

In the Action Configuration dialogue:

• Specify the Action Name as you want it displayed to users
• Set the action to be the default actions if you want to enable execution using keyboard shortcuts
• Choose an Action Type, either Create, Delete or Other.
• Type the Command, either a stored procedure name or a SQL statement
• You can provide a user friendly Description for the action, the default will be the command text

To learn more about Actions and Action Types, refer to the Actions section, or check the SQL walk through section to see some action examples.

If the Command requires parameters you can specify them in the Command Parameters grid.

You can either enter Parameters manually, or click the Detect Parameters button to try getting automatic configuration.

For each parameter you must specify:

• the Parameter Name as needed by the stored procedure or SQL statement (usually of the form @ParameterName)
• the Parameter Type to limit the the user input to the right data type
• the Display Name to show to the user in the prompt dialogue when executing the action
• an Editing mode to make the parameter Locked, Required, Optional or Reference
• a Default Mode to specify how the default value will be calculated (Null, Value or RowValue)
• a Default Value for Default Mode Value or a Row Field Name for Default Mode RowValue.

To learn more about Parameters, Editing & Default Modes, refer to the Actions section, or check the SQL walk through section to see some action examples.

Set up the SQL Server for Notifications

In addition to read and write access, SQL DataSources support change notifications: when a modification is made to the database, a notification is sent to BimSens to refresh its data. Reaction to notifications include Automatic Refresh of the data and re-applying the Auto Colour Filter.

The Notifications system for SQL DataSources relies on SqlDependencies which requires some options to be activated on the database and elevated permissions for the user:

• The database must have the Service Broker activated. You can either activate it using a SQL command or via SQL Server Management Studio > right click the database > Properties > Options > Broker Enabled.
• Additionally, the user need permissions to:

• Create Procedures
• Create Queues
• Create Service
• Reference the Contract [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
• Subscribe to Query Notifications
• Receive Notification Errors
• See View Definition
• Run Select statement on the table(s) being monitored

The SQL command below can be executed on a SQL database to do the complete set up, for the database called NOTIFICATION DEMO and the SQL Login DependencyUser.

Change the database name, user, role and schema as required for your configuration.

(Source: http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen)

USE master

--Enable the Service Broker

ALTER DATABASE [NOTIFICATION DEMO] SET ENABLE_BROKER

GO

--Create the Server Login

--(Only if you are not using an existing login)

CREATE LOGIN [DependencyUser] WITH PASSWORD=N'BimSensRocks', 

DEFAULT_DATABASE=[NOTIFICATION DEMO], 

            CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [NOTIFICATION DEMO]

--Create the Database User for the server Login

--(Only if you are not using an existing user)

CREATE USER [DependencyUser] FOR LOGIN [DependencyUser]

GO

--Create a default schema for the user rather than giving him ownership of the dbo

CREATE SCHEMA [DependencyUserSchema] AUTHORIZATION [DependencyUser]

ALTER USER [DependencyUser] WITH DEFAULT_SCHEMA = [DependencyUserSchema]

--Create a role and assign permissions

EXEC sp_addrole 'sql_dependency_role'

EXEC sp_addrolemember 'sql_dependency_role', 'DependencyUser'

GRANT CREATE PROCEDURE TO [sql_dependency_role]

GRANT CREATE QUEUE TO [sql_dependency_role]

GRANT CREATE SERVICE TO [sql_dependency_role]

GRANT REFERENCES ON

CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]

TO [sql_dependency_role] 

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_role]

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_role]

GRANT VIEW DEFINITION TO [sql_dependency_role] 

GRANT SELECT to [sql_dependency_role]

Activate Notifications

Once the database is set up, the notification needs to be configure for the DataView.

• Go to the Notifications tab and check the Subscribe to Notifications box
• Select whether to listen to notification on the query used to read the data in the Read tab or on another query or queries
• If you choose to specify different queries, type each query in the grid. You can test the subscription process using the Try Subscription(s) button

This completes the configuration on the DataView side, but for BimSens to react to notifications from the DataView the corresponding option needs to be activated. Close the view configuration dialogue, and from the Options dialogue go to the Refresh tab and active the option Refresh when the View notifies of Changes.

Check out the Set up Notifications on SQL Server Source walk through for an example.