Advanced 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. See Set up your development environment.

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.

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;
                         }
                    }
     }
}