I've seen this:
How can you dynamically select a table with entity framework 4.x?
However I cannot use a base class in my situation. I am looking to create a user group and week sensitive table for auditing, so I cannot know what these will be called prior to them being created at runtime.
So if I login first week of the year, it would be:
Group1_01_2014
Is this possible? I've tried to simply change the database name at creation, but I get the standard exception about database migrations.
Thanks.
Edit: Before someone says splitting the database is silly, know that I'm auditing ALOT. The idea is to be able to audit most transactions, store them as long as needed, without severely affecting performance.
Edit2: That said if someone has a better solution I'm all ears.
Solution
Ended up using a stored procedure:.
CREATE FUNCTION GetAuditTableName
(
@db_code CHAR (4)
)
RETURNS CHAR (12)
BEGIN
DECLARE @wkNo CHAR (2)
DECLARE @year CHAR (4)
SELECT @wkNo = REPLACE(STR(DATEPART(WEEK, GETDATE()), 2), SPACE(1), '0')
SELECT @year = STR(DATEPART(YEAR, GETDATE()), 4)
RETURN @db_code + '_' + @year + '_' + @wkNo
END
GO
CREATE PROCEDURE [dbo].[Audit_Insert]
@audi_id_first NVARCHAR (20),
@audi_id_second NVARCHAR (20),
@audi_by NVARCHAR (100),
@audi_on DATETIME,
@audi_details XML,
@tabn_code CHAR (4),
@audt_id INT,
@audi_db_code CHAR (4)
AS
BEGIN
DECLARE @tableName CHAR (12)
SET @tableName = [dbo].GetAuditTableName(@audi_db_code)
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=@tableName and xtype='U')
BEGIN
DECLARE @createSql NVARCHAR(MAX);
SELECT @createSql = 'CREATE TABLE [dbo].' + QUOTENAME(@tableName) + ' (
[audi_id] INT IDENTITY (1, 1) NOT NULL,
[audi_id_first] NVARCHAR (20) NULL,
[audi_id_second] NVARCHAR (20) NULL,
[audi_by] NVARCHAR (100) NOT NULL,
[audi_on] DATETIME NOT NULL,
[audi_details] XML NULL,
[tabn_code] CHAR (4) NULL,
[audt_id] INT NOT NULL,
CONSTRAINT [PK_dbo.' + @tableName + '] PRIMARY KEY CLUSTERED ([audi_id] ASC),
CONSTRAINT [FK_dbo.' + @tableName + '_dbo.tabn_table_name_tabn_code] FOREIGN KEY ([tabn_code]) REFERENCES [dbo].[tabn_table_name] ([tabn_code]),
CONSTRAINT [FK_dbo.' + @tableName + '_dbo.audt_audit_type_audt_id] FOREIGN KEY ([audt_id]) REFERENCES [dbo].[audt_audit_type] ([audt_id])
)'
EXEC sp_executesql @createSql
END
DECLARE @insertSql NVARCHAR(MAX);
SELECT @insertSql = N'INSERT [dbo].' + QUOTENAME(@tableName) + ' ([audi_id_first], [audi_id_second], [audi_by], [audi_on], [audi_details], [tabn_code], [audt_id])
VALUES (@audi_id_first, @audi_id_second, @audi_by, @audi_on, @audi_details, @tabn_code, @audt_id)'
EXEC sp_executesql @insertSql, N'@audi_id_first NVARCHAR (20), @audi_id_second NVARCHAR (20), @audi_by NVARCHAR (100), @audi_on DATETIME, @audi_details XML, @tabn_code CHAR (4), @audt_id INT', @audi_id_first, @audi_id_second, @audi_by, @audi_on, @audi_details, @tabn_code, @audt_id
DECLARE @idSql NVARCHAR(MAX);
SELECT @idSql = 'DECLARE @audi_id INT
SELECT @audi_id = [audi_id]
FROM [dbo].' + QUOTENAME(@tableName) + '
WHERE @@ROWCOUNT > 0 AND [audi_id] = scope_identity()
SELECT t0.[audi_id]
FROM [dbo].' + QUOTENAME(@tableName) + ' AS t0
WHERE @@ROWCOUNT > 0 AND t0.[audi_id] = @audi_id'
EXEC sp_executesql @idSql
END
GO
then calling this from C#:
public void AddRecord(Audit record)
{
var Id1 = new SqlParameter("@audi_id_first", SqlDbType.NVarChar);
Id1.Value = (object)record.Id1 ?? System.DBNull.Value;
var Id2 = new SqlParameter("@audi_id_second", SqlDbType.NVarChar);
Id2.Value = (object)record.Id2 ?? System.DBNull.Value;
var UserName = new SqlParameter("@audi_by", SqlDbType.NVarChar);
UserName.Value = record.UserName;
var Stamp = new SqlParameter("@audi_on", SqlDbType.DateTime);
Stamp.Value = record.Stamp;
var Details = new SqlParameter("@audi_details", SqlDbType.Xml);
Details.Value = (object)record.Details ?? System.DBNull.Value;
var TableCode = new SqlParameter("@tabn_code", SqlDbType.Char);
TableCode.Value = record.TableCode;
var TypeId = new SqlParameter("@audt_id", SqlDbType.Int);
TypeId.Value = record.TypeId;
var DatabaseCode = new SqlParameter("@audi_db_code", SqlDbType.Char);
DatabaseCode.Value = CallingPrefix;
this.Database.ExecuteSqlCommand("EXEC Audit_Insert @audi_id_first, @audi_id_second, @audi_by, @audi_on, @audi_details, @tabn_code, @audt_id, @audi_db_code",
Id1, Id2, UserName, Stamp, Details, TableCode, TypeId, DatabaseCode);
}
My next struggle is getting it to work with IQueryable OData requests.
Solution 2
CREATE PROCEDURE [dbo].[CreateAuditTableIfNoneExists]
@tableName CHAR (12)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=@tableName and xtype='U')
BEGIN
DECLARE @createSql NVARCHAR(MAX);
SELECT @createSql = 'CREATE TABLE [dbo].' + QUOTENAME(@tableName) + ' (
[audi_id] INT IDENTITY (1, 1) NOT NULL,
[audi_id_first] NVARCHAR (20) NULL,
[audi_id_second] NVARCHAR (20) NULL,
[audi_by] NVARCHAR (100) NOT NULL,
[audi_on] DATETIME NOT NULL,
[audi_details] XML NULL,
[tabn_code] CHAR (4) NULL,
[audt_id] INT NOT NULL,
CONSTRAINT [PK_dbo.' + @tableName + '] PRIMARY KEY CLUSTERED ([audi_id] ASC),
CONSTRAINT [FK_dbo.' + @tableName + '_dbo.tabn_table_name_tabn_code] FOREIGN KEY ([tabn_code]) REFERENCES [dbo].[tabn_table_name] ([tabn_code]),
CONSTRAINT [FK_dbo.' + @tableName + '_dbo.audt_audit_type_audt_id] FOREIGN KEY ([audt_id]) REFERENCES [dbo].[audt_audit_type] ([audt_id])
)'
EXEC sp_executesql @createSql
END
END
and on database initialisation make sure the table is present:
public class AuditInitialiser : IDatabaseInitializer<AuditContext>
{
public void InitializeDatabase(AuditContext context)
{
if (context.Database.CreateIfNotExists())
{
Seed(context);
}
var tableName = new SqlParameter("@tableName", SqlDbType.Char);
tableName.Value = context.AuditTableName;
context.Database.ExecuteSqlCommand("EXEC CreateAuditTableIfNoneExists @tableName", tableName);
}
However this only works for me because I am using an API, so is stateless and constructs the DbContext every time. This ensures the table is always present. Wouldn't work if I was doing my working out on a stated system.
