Skip to main content

SQL Standards and Practices

Best Practices for SQL within Business Rules​

There are many cases where you will need to write queries to access data in your application database tables. We’ll provide both requirements that must be followed as well as recommendations for making your code as readable and maintainable as possible.

Check your queries and ensure that they are in a readable format (string interpolation), use appropriate keyword casing, and include the WITH (NOLOCK) syntax, where applicable.

Always ensure that you are executing your SQL queries within a using statement with a database connection. When doing this, be careful not to use the database connection within a loop to avoid creating multiple connections.

OneStream discourages the direct querying of application database tables, and we do not allow direct querying of Framework data tables. We recommend using OneStream APIs when possible. By using direct queries, you risk the queries breaking when you upgrade your OneStream environment.

Parameterizing SQL Queries​

When writing SQL queries, it is important to parameterize them. This protects against any possible injection, which can cause problems with your data. In your Business Rule you can parameterize your SQL queries by creating a list of DBParamInfo objects, referencing them within your query, and executing your query using those parameters, as shown below using a OneStream audit table, not an application table:

private DataTable getAuditMember(SessionInfo si, DashboardDataSetArgs args)
    {
        try
        {
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();

            string strAuditUser = string.Empty;
            string strAuditTime = string.Empty;
            strAuditUser = args.NameValuePairs.GetValueOrDefault("AuditUser");
            strAuditTime = args.NameValuePairs.GetValueOrDefault("AuditTime");

            if (!string.IsNullOrEmpty(strAuditUser) && !string.IsNullOrEmpty(strAuditTime))
            {
                sql.AppendLine("SELECT [AuditUser], [AuditInsUpdateDel], [Name], [Description], [AuditTime] FROM AuditMember WHERE [AuditUser] = @AuditUser AND [AuditTime] >= @AuditTime");

                List<DbParamInfo> parameters = new List<DbParamInfo>()
                {
                    new DbParamInfo("@AuditUser", strAuditUser),
                    new DbParamInfo("@AuditTime", strAuditTime)
                };

                using (DbConnInfo database = BRApi.Database.CreateApplicationDbConnInfo(si))
                {
                    dt = BRApi.Database.ExecuteSql(database,sql.ToString(),parameters, false);
                }
            }
            return dt;
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(si, new XFException(si, ex));
        }
    }

SQL | Method Queries​

It is recommended to use SQL within your Business Rules or assembly files commonly referred to as method queries, instead of using SQL from within a Data Adapter. By using method queries, there is the organization advantage of having an assembly with one or more Business Rules or assembly files, the benefit of being able to parameterize queries, and being able to combine data from an application database with the framework database. When using SQL from within a Data Adapter, you run the risk of piped parameters not resolving at runtime, causing an error when your query runs. This issue can be easily addressed when using method queries for the parameter value and setting its value if needed.

Dynamic SQL​

It is recommended to use Dynamic SQL within your business rules, rather than using SQL from within a Data Adapter. By using Dynamic SQL, you can keep all your queries organized in one place and have the benefit of being able to easily parameterize and debug your queries. When using SQL from within a Data Adapter, you run the risk of piped parameters not resolving at runtime, causing an error when your query runs. This issue can be easily addressed when using Dynamic SQL in business rules by checking for the parameter value and setting its value if needed.

Stored Procedures​

SQL stored procedures offer a powerful way to manage and execute database operations. They encapsulate complex queries and operations into reusable scripts, enhancing efficiency, security, and maintainability. By using stored procedures, developers can streamline database interactions, reduce network traffic, and ensure consistent execution of tasks.

Benefits of SQL Stored Procedures:

  • Improved Performance: Stored procedures are precompiled, which can lead to faster execution compared to individual SQL queries.
  • Enhanced Security: They allow for better control over access permissions, reducing the risk of SQL injection attacks.
  • Code Reusability: Procedures can be reused across different applications, promoting consistency and reducing redundancy.
  • Simplified Maintenance: Changes can be made in one place without altering the application code, making updates easier to manage.
  • Reduced Network Traffic: Executing a stored procedure requires fewer network roundtrips compared to sending multiple individual queries.

The SQL_QueryBuilder class​

If your solution consumes SQL data, whenever possible, try to include and use this provided helper class SQL_QueryBuilder. This may alleviate the heavy lifting associated with your application’s data access.

You can utilize the below helper class in your solutions.

SQL_QueryBuilder Code Examples​

public class SQL_QueryBuilder
{
    private SessionInfo SI { get; set; } =
        null /* TODO Change to default(_) if this is not a reference type */
    ;
    private StringBuilder Query { get; set; } = new System.Text.StringBuilder();
    public List<DbParamInfo> Params { get; set; } = new List<DbParamInfo>();

    public SQL_QueryBuilder(SessionInfo si)
    {
        try
        {
            this.SI = si;
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public void AddLine(string newLine)
    {
        try
        {
            this.Query.AppendLine(newLine);
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public void AddParameter(string paramName, object paramValue)
    {
        try
        {
            this.Params.Add(new DbParamInfo(paramName, paramValue));
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public string CreateInClause(object itemList)
    {
        try
        {
            System.Text.StringBuilder inClause = new System.Text.StringBuilder();
            Type itemListType = itemList.GetType();
            // Validate The Object Is Of List Type
            if (
                itemListType.IsGenericType
                && itemListType.GetGenericTypeDefinition().IsAssignableFrom(typeof(List<>))
            )
            {
                foreach (var i in (dynamic)itemList)
                {
                    int paramCount = this.Params.Count + 1;
                    inClause.Append(inClause.Length == 0 ? $"@{paramCount}" : $", @{paramCount}");
                    this.AddParameter(paramCount.ToString(), i);
                }
            }
            else
            {
                string msg = $"In Clause must be created from type List(Of T), not {itemListType}.";
                throw new XFUserMsgException(SI, null, null, msg);
            }
            return inClause.ToString();
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public void AddParamsToDbCommand(List<DbParamInfo> dbParamInfos, DbCommand dbCommand)
    {
        if (dbParamInfos != null)
        {
            foreach (DbParamInfo dbParamInfo in dbParamInfos)
            {
                DbParameter dbParameter = dbCommand.CreateParameter();
                dbParameter.Direction = dbParamInfo.Direction;
                dbParameter.IsNullable = false;
                dbParameter.ParameterName = dbParamInfo.Name;
                dbParameter.Value = dbParamInfo.Value;
                dbCommand.Parameters.Add(dbParameter);
            }
        }
    }

    public DataTable GetDataTable(
        DbLocation dbLocation = DbLocation.Application,
        bool useCommandTimeoutLarge = false,
        bool includePrimaryKeyInfo = false
    )
    {
        try
        {
            DataTable dt =
                null /* TODO Change to default(_) if this is not a reference type */
            ;
            switch (dbLocation)
            {
                case DbLocation.Application:
                {
                    using (
                        DbConnInfo dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(this.SI)
                    )
                    {
                        using (
                            DbCommand dbCommand = dbConnApp.CreateCommand(useCommandTimeoutLarge)
                        )
                        {
                            dbCommand.CommandText = this.Query.ToString();
                            this.AddParamsToDbCommand(this.Params, dbCommand);
                            dt = DbSql.GetDataTable(
                                dbConnApp,
                                dbCommand,
                                includePrimaryKeyInfo,
                                false
                            );
                        }
                    }
                    break;
                }
                case DbLocation.Framework:
                {
                    using (DbConnInfo dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(this.SI))
                    {
                        using (DbCommand dbCommand = dbConnFW.CreateCommand(useCommandTimeoutLarge))
                        {
                            dbCommand.CommandText = this.Query.ToString();
                            this.AddParamsToDbCommand(this.Params, dbCommand);
                            dt = DbSql.GetDataTable(
                                dbConnFW,
                                dbCommand,
                                includePrimaryKeyInfo,
                                false
                            );
                        }
                    }
                    break;
                }
            }
            return dt;
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public DataTable ExecuteAndReturnDataTable(
        string tableName = null,
        DbLocation dbLocation = DbLocation.Application,
        bool useCommandTimeoutLarge = false
    )
    {
        try
        {
            DataTable dt =
                null /* TODO Change to default(_) if this is not a reference type */
            ;
            switch (dbLocation)
            {
                case DbLocation.Application:
                {
                    using (
                        DbConnInfoApp dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(
                            this.SI
                        )
                    )
                    {
                        dt = BRApi.Database.ExecuteSql(
                            dbConnApp,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge
                        );
                    }
                    break;
                }
                case DbLocation.Framework:
                {
                    using (
                        DbConnInfoFW dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(this.SI)
                    )
                    {
                        dt = BRApi.Database.ExecuteSql(
                            dbConnFW,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge
                        );
                    }
                    break;
                }
                default:
                {
                    break;
                    break;
                }
            }
            if (tableName != null)
                dt.TableName = tableName;
            return dt;
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public DataTable ExecuteAndReturnDataReader(
        string tableName = null,
        DbLocation dbLocation = DbLocation.Application,
        bool useCommandTimeoutLarge = false
    )
    {
        try
        {
            DataTable dt =
                null /* TODO Change to default(_) if this is not a reference type */
            ;
            switch (dbLocation)
            {
                case DbLocation.Application:
                {
                    using (
                        DbConnInfoApp dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(
                            this.SI
                        )
                    )
                    {
                        dt = BRApi.Database.ExecuteSqlUsingReader(
                            dbConnApp,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge
                        );
                    }
                    break;
                }
                case DbLocation.Framework:
                {
                    using (
                        DbConnInfoFW dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(this.SI)
                    )
                    {
                        dt = BRApi.Database.ExecuteSqlUsingReader(
                            dbConnFW,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge
                        );
                    }
                    break;
                }
                default:
                {
                    break;
                    break;
                }
            }
            if (tableName != null)
                dt.TableName = tableName;
            return dt;
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public long ExecuteActionQuery(
        DbLocation dbLocation = DbLocation.Application,
        bool useCommandTimeoutLarge = false
    )
    {
        try
        {
            switch (dbLocation)
            {
                case DbLocation.Application:
                {
                    using (
                        DbConnInfoApp dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(
                            this.SI
                        )
                    )
                    {
                        return BRApi.Database.ExecuteActionQuery(
                            dbConnApp,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge,
                            true
                        );
                    }
                    break;
                }
                case DbLocation.Framework:
                {
                    using (
                        DbConnInfoFW dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(this.SI)
                    )
                    {
                        return BRApi.Database.ExecuteActionQuery(
                            dbConnFW,
                            this.Query.ToString(),
                            this.Params,
                            useCommandTimeoutLarge,
                            true
                        );
                    }
                    break;
                }
                default:
                {
                    return -1;
                }
            }
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public string QueryString()
    {
        return Query.ToString();
    }

    public void WriteQueryToLog()
    {
        try
        {
            System.Text.StringBuilder msg = new System.Text.StringBuilder(this.Query.ToString());
            msg.AppendLine();
            msg.AppendLine("Parameters:");
            foreach (DbParamInfo param in this.Params)
            {
                msg = msg.Replace(
                    string.Format("@{0}", param.Name),
                    string.Format("'{0}'", param.Value.ToString())
                );
                msg.AppendLine(string.Format("{0} - {1}", param.Name, param.Value.ToString()));
            }
            BRApi.ErrorLog.LogMessage(this.SI, msg.ToString());
        }
        catch (Exception ex)
        {
            throw ErrorHandler.LogWrite(this.SI, new XFException(this.SI, ex));
        }
    }

    public void Reset()
    {
        Params.Clear();
        Query.Clear();
    }
}
Last updated on by Jeremy Meiss

Was this page helpful?