Code sample for database interactions

You can use the IDBContext interface in the Relativity Helpers API to run queries, manage database transactions, and perform common database operations. You can reference this context from agents, custom pages, and event handlers. You must add a reference to the Relativity.API.dll to your Visual Studio project to use this the IDBContext interface.

Note: The DefaultSqlCommandTimeout instance configuration setting can determine the value of the int timeoutValue parameter in Execute calls (such as ExecuteNonQuerySQLStatement and ExecuteSqlStatementAsDataSet). If your code passes 0 or less as the timeoutValue parameter, the timeout value will revert to using the value set for the instance configuration setting for DefaultSqlCommandTimeout. Should this configuration setting not exist in your environment, it is possible that the Timeout value will be set to 0.

You can use the advanced functionality available through this interface. The JobQueries class demonstrates how to query for data, modify it, and then modify database transactions. For a basic code sample, see Basic concepts for Relativity API Helpers.

Copy 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Relativity.API;
 
namespace Relativity.Samples.Core
{
 
     public static class JobQueries
     {
          public static Boolean JobExists(IDBContext dbContext, Int32 workspaceArtifactID, Int32 artifactID)
          {
               Boolean retVal = false;
               String sql = "SELECT COUNT(*) AS JobCount 
                    FROM [SamplesApp] 
                    WHERE [WorkspaceArtifactID] = @WorkspaceArtifactID AND [InstanceArtifactID] = @InstanceArtifactID";
                
               SqlParameter workspaceArtifactIDParam = new SqlParameter("@WorkspaceArtifactID", SqlDbType.Int);
               workspaceArtifactIDParam.Value = workspaceArtifactID;
                
               SqlParameter instanceArtifactIDParam = new SqlParameter("@InstanceArtifactID", SqlDbType.Int);
               instanceArtifactIDParam.Value = artifactID;
                
               retVal = (dbContext.ExecuteSqlStatementAsScalar<Int32>(sql, new SqlParameter[] { workspaceArtifactIDParam, instanceArtifactIDParam }) > 0);
               return retVal;
          }
 
          public static void InsertJob(IDBContext dbContext, Int32 workspaceArtifactID, Int32 artifactID)
          {
               String sql = "IF NOT EXISTS(SELECT TOP 1 * FROM [SamplesApp] 
                         WHERE [WorkspaceArtifactID] = @WorkspaceArtifactID AND [InstanceArtifactID] = @InstanceArtifactID)"
                    + " BEGIN"
                    + " INSERT INTO [SamplesApp] (WorkspaceArtifactID, InstanceArtifactID, Status)"
                    + " Values (@WorkspaceArtifactID, @InstanceArtifactID, 0)"
                    + " END";
                
               SqlParameter workspaceArtifactIDParam = new SqlParameter("@WorkspaceArtifactID", SqlDbType.Int);
               workspaceArtifactIDParam.Value = workspaceArtifactID;
                
               SqlParameter instanceArtifactIDParam = new SqlParameter("@InstanceArtifactID", SqlDbType.Int);
               instanceArtifactIDParam.Value = artifactID;
                
               dbContext.BeginTransaction();
               try
               {
                    dbContext.ExecuteNonQuerySQLStatement(sql, new SqlParameter[] { workspaceArtifactIDParam, instanceArtifactIDParam });
                    dbContext.CommitTransaction();
               }
               catch (Exception ex)
               {
                    dbContext.RollbackTransaction();
                    throw;
               }
          }
                    public static void CreateTableIfNotExists(IDBContext dbContext) {
                         String sql = "IF NOT EXISTS(SELECT 'true' FROM [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] = 'SamplesApp')" 
                         + " BEGIN " 
                         + " CREATE TABLE SamplesApp ([WorkspaceArtifactID] INT NOT NULL, [InstanceArtifactID] INT NOT NULL, 
                                   [Status] INT NOT NULL)" 
                         + " END";
                          
                         dbContext.BeginTransaction();
                         try {
                              dbContext.ExecuteNonQuerySQLStatement(sql);
                              dbContext.CommitTransaction();
                         }
                         catch (System.Exception ex) {
                              dbContext.RollbackTransaction();
                              throw;
                         }
                    }
     }
}