Creating a SQL DataSource

Use the Sources Manager 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)
  • Activate Windows Authentication or enter your Login and Password if the server supports SQL logins
  • Enter the name of the Database to connect to (cross Database queries are not supported) 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
  • 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)

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.

 TIPS


Use the Object Browser to get a list of the objects (table, views, columns etc.) that you currently have access to on the server and add them to the query from a selection. This reduces the risk of misspelling.
  • 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.


 NOTE


The DataView is now configured and can be used in read only mode.

SQL Server and Azure DataViews also support Write operations, custom Actions and change Notifications. If you want to configure this functionalities, read on.

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. The name must be the original name from the server.
    • 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.

 TIPS


You can click the See Permissions... button to open the Object Browser and get a list of columns with parent table, table key(s) and permissions from the server. These columns can be added to the list from the Object Browser directly. This reduces the risk of misspelling and errors, and already includes the table key(s) defined for the table.

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


 warning


Do not use generic SQL Logins with high permissions for all the users or they will be able to grant themselves permissions on other columns by modifying this section of the configuration.

This setup cannot override the permissions defined on the server. If information is provided for a column that a login is not authorised to update, the logged in user will receive an error message when trying to edit that column from the grid.

It is therefore highly recommended to restrict logins permissions as much as possible on the server.


 NOTE


It is not imperative for the ID Column(s) to be the primary key(s) of the table, it doesn't even need to uniquely identify records. But all records that have values matching those of the selected rows for the ID Column(s) will be updated as well.

It is not necessary, but good practice.


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 <None> 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
    • You can provide a user friendly description for the action, the default will be the command text
    • Choose an Action Type, either Create, Delete or Other.
    • Type the Command, either a stored procedure name or a SQL statement

 TIPS


You can click the Standard Commands... button to open the Object Browser and automatically get SQL query for standard INSERT and DELETE statements for all tables on the database. This reduces the risk of misspelling and errors, and already includes the table key(s) defined for the table.

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 Infer 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.


 warning


Do not use generic SQL Logins with high permissions for all the users or they will be able to execute potentially harmful code by modifying this section of the configuration.

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

 warning


These are a lot of permissions to give to a user that is not trust worthy, consider enclosing them into a schema to reduce the damage the user could do.


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]


 TIPS


Alternatively if you don't really give a damn about security, you can simply make the database user a member of the db_owner role. But that's not very secured...

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

 warning


There are rules and limitations for Queries to be eligible to change Notifications:

  • the command must use 2 parts names e.g. dbo.TableName instead of TableName
  • the command cannot use * for fields selection e.g. SELECT C1, C2 FROM dbo.TableName instead of SELECT * FROM dbo.TableName
  • it cannot use Views and has restrictions on Stored Procedures
  • it cannot use aggregate functions (SUM, AVG, COUNT etc.)

See the complete list of restrictions: http://msdn.microsoft.com/en-us/library/aewzkxxh.aspx


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.