Calling with custom SQL statements - SmartPlant Foundation - IM Update 44 - Customization & Programming - Hexagon

SmartPlant Foundation Customization

Language
English
Product
SmartPlant Foundation
Search by Category
Customization & Programming
SmartPlant Foundation / SDx Version
10

Follow these instructions to run custom SQL statements built up using the SPFSQL object covering the three main methods on the IDBProvider on SPFRequestContext. There are some examples of the code below, which shows you how this operates.

By using the call this way, the user is responsible for the opening and closing of the connection and disposing of your objects.

We do not support any direct communication with the database through specific custom SQL. Future releases of SmartPlant Foundation may modify the database schema, which can potentially stop any specific custom SQL from running. Users will have modify any custom SQL for them to run again.

Building SQL statements

To build SQL statements you must use the SPFSQL object found in SPF.Common.DataAccessLayer.DataRetrieval. This object allows you to append lines to your SQL command.

Dim lobjSql As New SPFSQL

lobjSql.AppendLine("SELECT * FROM TABLE ")

To add a variable to your SQL statement, use the AppendBindVariable function found on the DBProvider object.

Do not use the BindVariable command. It has been replaced by the AppendBindVariable, which is easier to use.

lobjSql.AppendLine("WHERE ID = ")

SPFRequestContext.Instance.IDBProvider.

AppendBindVariable(lobjSql, "ID", pstrIDValue)

You can also use AppendInListBindValues() to append a list of values to your SQL statement.

To append the effective date to limit your query to data within the effective date, use the AppendDateEffectivity method. In the example, the "o" is the table prefix, which must be replaced with your relevant table prefix.

SPFRequestContext.Instance.IDBProvider.AppendDateEffectivity("o", pobjSQL)

SHARED Tip You can also bind a configuration clause by using the AppendConfiguration function on the IDBProvider.

DataReaders

DataReaders can be twice as fast as DataSets when querying for large amounts of data.

Always map the result onto an object and never return a DataReader out of your method. For example, map IDs into a List<String> and return the List object, and so on.

Try

SPFRequestContext.Instance.IDBProvider.OpenConnection(False)

Using lobjReader As IDataReader = SPFRequestContext.Instance.IDBProvider.ExecuteDataReader(lobjSelectSQL)

While lobjReader.Read()

' Put Mapping code here

End While

End Using

Finally

SPFRequestContext.Instance.IDBProvider.CloseConnection(False)

End Try

NonQuery

This example shows how to run non-query create/read/update/delete (CRUD) style statement.

Try

SPFRequestContext.Instance.IDBProvider.OpenConnection(True)

SPFRequestContext.Instance.IDBProvider.ExecuteNonQuery(lobjSelectSQL)

Finally

SPFRequestContext.Instance.IDBProvider.CloseConnection(True)

End Try

DataSets

You need to ensure you dispose of the data object if you use a data set or data table to return to a calling method, or if you are not mapping to an object.

Try

SPFRequestContext.Instance.IDBProvider.OpenConnection(False)

Using lobjDataSet As DataSet = SPFRequestContext.Instance.IDBProvider.ExecuteDataSet(lobjSelectSQL)

' Put code here

' N.B. If you are returning the dataset/datatable then you will not need the using block here so put it in the calling code

End Using

Finally

SPFRequestContext.Instance.IDBProvider.CloseConnection(False)

End Try