Creating Custom [Client] Tables

Client Schema ([Client])

Database schemas are logical collections of data objects. The Disco Database has the following schemas:

• Client schema ([Client]) - Contains all tables created and maintained in the Client Extension
• DBO schema ([dbo]) - Contains DirectScale tables

You need to create your tables in the [Client] schema, which exists in your Database instance.

Both schema names appear in custom reports and data viewed with the SQL Manager or Data Editor.

---

---

Creating Custom Tables

Using the ClientConnectionString from the _dataService, write a custom API endpoint or DBDeploy script that calls the following sample code. You can use this code as a starting point.

C#

private int CreateMyTable()
        {
            try
            {
                using (var dbConnection = new SqlConnection(_dataService.ClientConnectionString.ConnectionString))
                {
                    var query = @"
                    IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE [name] = N'MyTable' AND [type] = 'U' AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'Client'))
                    BEGIN
                        CREATE TABLE [Client].[MyTable]
                        (
                            [recordnumber] int NOT NULL IDENTITY(1, 1),
                            [last_modified] DATETIME CONSTRAINT DF_MyTable_last_modified DEFAULT (GETDATE()) NOT NULL,
                            [MyUsefulField] varchar(150) NULL
                            CONSTRAINT [MyTable_PrimaryKey] PRIMARY KEY CLUSTERED 
                                (
                                    [recordnumber]
                                ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
                        );
                    END
                    CREATE INDEX ExampleIndex ON [Client].[MyTable] (MyUsefulField);
                    ";
                    dbConnection.Execute(query);
                }
                return 0;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

---

How it Works

C#

using (var dbConnection = new SqlConnection(_dataService.ClientConnectionString.ConnectionString))

ClientConnectionString is a method in the DataService that provides a read-write connection string that can only access [Client] tables.

---

C#

var query = @"

The preceding example function uses query, which is a method defined in Dapper. However, using Dapper is optional. You can use whatever method of data access you want. If you're going to use this example code:

1. Install the Dapper NuGet package.
2. Add using Dapper; at the top of the file.

---

C#

IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE [name] = N'MyTable' AND [type] = 'U' AND [schema_id] = (SELECT [schema_id] FROM sys.schemas WHERE [name] = 'Client'))

We're checking if the table has been previously created. If it hasn't, then we'll create the table. If it has, then we'll update it.

Important: Client Custom tables must have recordnumber (int) Primary Key and last_modified (DateTime) fields.

---

C#

CREATE INDEX ExampleIndex ON [Client].[MyTable] (MyUsefulField);

Indexes are a fast way to retrieve data. If you anticipate your table storing large amounts of data, then it's a good idea to create an Index for your tables.