Creating custom Big Table

Vote:
 

I'm working on creating a custom table for storing log data for a module we have, and have ran into an issue with the create routine. I've been following this tutorial, and I've implemented it as shown in the example. The issue that I'm running into is, the database is coming back with an error stating that varchar isn't supported, while the generated SQL that I've created does not have a varchar included:

if OBJECT_ID('dbo.tblLoggerErrorsDataStoreBigTable', 'U') is null 
            create table [dbo].[tblLoggerErrorsDataStoreBigTable] 
            ([pkId] bigint not null, 
            [Row] int not null default(1) constraint CH_tblLoggerErrorsDataStoreBigTable check ([Row]>=1), 
            [StoreName] nvarchar(128) not null, 
            [ItemType] nvarchar(512) not null, 
            [LogId] int not null,  
             [Created] datetime null,
             [Author] nvarchar(100) null,
             [LogInfo] nvarchar(max) null, 
            constraint [PK_tblLoggerErrorsDataStoreBigTable] primary key clustered([pkId],[Row]), 
            constraint [FK_tblLoggerErrorsDataStoreBigTable_tblBigTableIdentity] foreign key ([pkId])
            references [tblBigTableIdentity]([pkId]));  IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_tblLoggerErrorsDataStoreBigTable_LogId')
                    CREATE NONCLUSTERED INDEX [IDX_tblLoggerErrorsDataStoreBigTable_LogId] 
                    ON [dbo].[tblLoggerErrorsDataStoreBigTable]([LogId]) 
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);  IF NOT EXISTS(SELECT * FROM sys.indexes WHERE Name = 'IDX_tblLoggerErrorsDataStoreBigTable_Author')
                    CREATE NONCLUSTERED INDEX [IDX_tblLoggerErrorsDataStoreBigTable_Author] 
                    ON [dbo].[tblLoggerErrorsDataStoreBigTable]([Author]) 
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); 

The goal here is to be able to insert log records into this table as needed for reporting purposes. I'm trying to create a four column table with reference to the DDS so that I can leverage a cleaner query call vs storing data directly in the primary BigTable. The above fails out when I call GetOrCreateStore() on my IDynamicData object.

Any direction or info on what I may be doing wrong here, would be great. Thanks.

#205638
Jul 17, 2019 21:23
Vote:
 

Hi Sean

May I ask why you want to build a custom BigTable, rather than making a custom table and interact with it using Dapper or EntityFramework?

If you will only be using your custom BigTable for a single purpose (logging) I believe that there will much trouble than it is worth.

#205640
Jul 18, 2019 7:00
Sean Haddy - Jul 18, 2019 15:59
Hey Stafan,
I am open to making a custom table and interacting with it with EntityFramework - is there somewhere I can find an example of creating/using a table within Epi in this fashion? Would I just generate the database within an initialization routine and then connect via EF like a standard .NET project, or what?
Sean Haddy - Jul 18, 2019 19:13
I went ahead and code-first created a EF database and it seems to be working good. Thanks for the recommendation Stefan.
Stefan Holm Olsen - Jul 19, 2019 18:01
You are very welcome, Sean. Let me know if there is anything else.
Vote:
 

Another thing, Sean.

I don't know which kind of logs you are going to store, or how you are going to use or retrieve them.

But if you are going to store many raw log entries in that table, performance may not be as good as you expect. Maybe you would be interested in logging to Azure Application Insights instead of a custom log table. If you need info and sample code have a look at my blog post on the topic.

In short, it is free (or very cheap), cloud-based, very fast at logging and its querying/analytics part is very powerful.

#205707
Jul 19, 2019 18:25