同table一直累加(例如紀錄使用者操作的table)，資料到一定數量以上後就算加上索引以空間換時間，還是不免有點"慢"
不論SQL/NoSQL，一般解法就是以日期去拆分Table/Collection
Entity Framework 物件關係對映沒辦法動態產表，就只好搭配Stored Procedure做RESTFul API了
整個流程拆分為三段程式：
1. [Stored Procedure1]：單純透過 SQL 資料定義語言(DDL)，建立Table
2. [Stored Procedure2]：資料操作語言(DML)與資料查詢語言(DQL)，接收後端傳來的查詢/新增/修改條件並回傳固定Class格式的資料，若特殊條件達成再呼叫[Stored Procedure1]建立新的Table
3. [.Net Core]：將封包查詢條件傳到SQL Server上，接收到資料再回傳給前端
下面程式供參考使用，應需求再自行調整
[Stored Procedure1]
USE [TestDB]
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
/*************** DynamicCreateTable **************/
/*功能：依年月產生新Table */
/*參數：@yyyymm 年月 公司別 */
/*************************************/
ALTER PROCEDURE [dbo].[DynamicCreateTable]
@yyyymm CHAR(6)
AS
DECLARE @tableName CHAR(13)
DECLARE @createTableCommand varCHAR(1000)
DECLARE @createClixCommand CHAR(255)
DECLARE @createIxCommand CHAR(255)
DECLARE @clustIndexName CHAR(11)
DECLARE @indexName CHAR(12)
SELECT @tableName='dbo.Test'+@yyyymm
SELECT @clustIndexName='CLIX_'+@yyyymm
SELECT @indexName='IX_'+@yyyymm
SELECT @createTableCommand="CREATE TABLE "+@tableName
+"(A char (10) NOT NULL DEFAULT(''), B varchar (30) NOT NULL DEFAULT(''),"
+"C dec (12,0) NOT NULL DEFAULT(0), D datetime NULL)";
EXEC (@createTableCommand);
SELECT @createClixCommand="CREATE UNIQUE CLUSTERED INDEX "+@clustIndexName+" ON "+@tableName+" (A,B)";
EXEC (@createClixCommand);
SELECT @createIxCommand="CREATE INDEX "+@indexName+" ON "+@tableName+" (C,D)";
EXEC (@createIxCommand);
[Stored Procedure2]
USE [frudat]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Richard
-- 使用範例：
-- exec stk104CRUD @method='post', @tableName='test201902',
-- @A = 'a',
-- @B ='test',
-- @C = 1.23,
-- @D ='1992-07-19 10:00:00.000'
-- =============================================
ALTER PROCEDURE [dbo].[testTableCRUD]
@method varchar(6) = 'GET',
@yyyyMM varchar(13),
@A char(12) = null,
@B varchar(30) = null,
@C decimal(7, 1) = 0,
@D varchar(30) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlComm varchar(1000);
DECLARE @ErrorMessage varchar(1000);
if (UPPER(@method) = 'GET')
begin
set @SqlComm = 'select * from test' + @yyyyMM + ' WHERE 1=1
AND (' + ISNULL('''' + @A + '''', 'null') + ' is null OR A Like ' + ISNULL('''' + @A + '''', 'null') + ')';
end
else if (UPPER(@method) = 'POST')
begin
if (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME='test'+ @yyyyMM))
begin
set @SqlComm = 'exec DynamicCreateTable @yyyymm='+ @yyyyMM;
exec(@SqlComm);
end
set @SqlComm = 'insert into test' + @yyyyMM + ' (A, B, C, D)
values('
+ ISNULL('''' + @A + '''','''''') + ','
+ ISNULL('''' + @B + '''','null') + ','
+ CONVERT(varchar(15), @C) + ','
+ ISNULL('''' + @D + '''','null')
+ ')';
end
else if (UPPER(@method) = 'PUT')
begin
set @SqlComm = 'update test' + @yyyyMM + ' set '
+ 'B = ' + ISNULL('''' + @B + '''','null') + ','
+ 'C = ' + CONVERT(varchar(15), @C) + ','
+ 'D = ' + ISNULL('''' + @D + '''','null')
+ ' WHERE 1=1
AND (' + ISNULL('''' + @A + '''','null') + ' is null OR A Like ' + ISNULL('''' + @A + '''','null') + ')';
end
else if (UPPER(@method) = 'DELETE')
begin
set @SqlComm = 'delete from test' + @yyyyMM + ' WHERE 1=1
AND (' + ISNULL('''' + @A + '''','null') + ' is null OR A Like ' + ISNULL('''' + @A + '''','null') + ')';
end
else
begin;
THROW 51000, 'ERROR: @method must be GET, POST, PUT or DELETE!', 1;
end;
BEGIN TRY
exec(@SqlComm);
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE() ;
THROW 51000, @ErrorMessage, 1;
END CATCH;
END
[.Net Core]
public class Test
{
public string A { get; set; }
public string B { get; set; }
public decimal? C { get; set; }
public DateTime? D { get; set; }
}
public class Request : Test
{
public string Method { get; set; }
public string YyyyMM { get; set; }
public int? Count { get; set; }
}
[HttpPost]
public IActionResult TestTable( [FromBody] Request Request)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
//預設值調整
if (Request.Count == null)
{
Request.Count = 100;
}
try
{
string[] param = new[] {
Request.Method,
Request.YyyyMM,
Request.A, //pk begin
Request.B,
Request.C.ToString(),
Request.D == null ? null : Request.D.GetValueOrDefault().ToString("yyyy-MM-dd HH:mm:ss.fff"),
};
switch (S03Request.Method.ToUpper())
{
case "GET":
//批量查詢(pk = null)
if (Request.A == null)
{
var GetTest = _context.S03YM.FromSql("testTableCRUD @method={0}, @yyyyMM={1}", param).Take(Request.Count.GetValueOrDefault());
return Ok(GetTest);
}
//條件查詢
else
{
IQueryable<Test> GetTest = _context.Test.FromSql("testTableCRUD @method={0}, @yyyyMM={1}, " +
"@A={2}", param).Take(Request.Count.GetValueOrDefault());
return Ok(GetTest);
}
case "POST":
int PostTest = _context.Database.ExecuteSqlCommand("EXEC testTableCRUD @method={0}, @yyyyMM={1}, " +
"@A= {2}, @B= {3}, @C= {4}, @D= {5}", param);
break;
case "PUT":
int PutTest = _context.Database.ExecuteSqlCommand("EXEC testTableCRUD @method={0}, @yyyyMM={1}, " +
"@A= {2}, @B= {3}, @C= {4}, @D= {5}", param);
break;
case "DELETE":
int DeleteTest = _context.Database.ExecuteSqlCommand("EXEC testTableCRUD @method={0}, @yyyyMM={1}, " +
"@A={2}", param);
break;
default:
return BadRequest("ERROR: @method must be GET, POST, PUT or DELETE!");
}
}
catch(Exception ex)
{
return BadRequest(ex.Message);
}
return Ok();
}
