RiCo技術農場

最近正在改寫一個舊專案的資料存取方式，

因為某些因素所以舊專案只能使用.NET FrameWork2.0，

理所當然就和EF和Linq說掰掰了，

但以前EF還沒問世時，我相信大多人都自行打造ORM，

今天分享自己轉換資料表並輸出類別的方式。

 

CREATE PROCEDURE [usp_ConvertTableToClass]
@tablename SYSNAME,@namespace varchar(50)
AS
SET NOCOUNT ON
DECLARE @step int,@maxcount int
DECLARE @temp TABLE
(
codeline INT,
code nvarchar(4000)
) 
INSERT INTO @temp
SELECT 1,'using System; '+CHAR(13) + CHAR(10)
union all
SELECT 2,'using System.Collections.Generic; '+CHAR(13) + CHAR(10)
union all
SELECT 3,'using System.Data; '+CHAR(13) + CHAR(10)
union ALL
SELECT 4,''+CHAR(13) + CHAR(10)
union ALL
SELECT 5,'namespace '+@namespace+CHAR(13) + CHAR(10) 
union ALL
SELECT 6,'{ '+CHAR(13) + CHAR(10)
union ALL 
SELECT 7,CHAR(9)+'public class ' + @tablename + CHAR(13) + CHAR(10) 
union ALL
SELECT 8,CHAR(9)+'{ '+CHAR(13) + CHAR(10) 
union ALL
SELECT 9,CHAR(9)+CHAR(9)+'#region ColumnName' + CHAR(13) + CHAR(10)
union ALL
SELECT 10,CHAR(9)+CHAR(9)+CHAR(9)+'public enum ColumnName'+ CHAR(13) + CHAR(10)
union ALL
SELECT 11,CHAR(9)+CHAR(9)+CHAR(9)+'{ '+CHAR(13) + CHAR(10)

set @step=11;
;with mycte(myrow,col)
as
(
SELECT row_number() OVER(ORDER BY ORDINAL_POSITION) as myrow,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+
COLUMN_NAME + ',' +CHAR(13) + CHAR(10) as 'col'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename
)
INSERT INTO @temp
SELECT cast(myrow AS int)+@step ,col
from mycte 

select @maxcount=count(1) from @temp 
update @temp 
SET code=replace(code,',','') 
where codeline=@maxcount 

INSERT INTO @temp
SELECT @maxcount+1,CHAR(9)+CHAR(9)+CHAR(9)+'} '+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+2,CHAR(9)+CHAR(9)+'#endregion End ColumnName' + CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+3,CHAR(9)+CHAR(9)+'#region Constructors' + CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+4,CHAR(9)+CHAR(9)+CHAR(9)+'public ' + @tablename + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9)+ CHAR(9)+ '{'
+ CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9)+ CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+5, CHAR(9)+CHAR(9)+'#endregion' + CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+6,CHAR(9)+CHAR(9)+'#region Private Fields' + CHAR(13) + CHAR(10)

set @step=@maxcount+6;
;with mycte(myrow,col)
as
(
SELECT row_number() OVER(ORDER BY ORDINAL_POSITION) as myrow,CHAR(9)+CHAR(9)+CHAR(9)+'private ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + '_' + COLUMN_NAME + ';' +CHAR(13) + CHAR(10) as 'col'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename
)
INSERT INTO @temp
SELECT cast(myrow AS int)+@step ,col
from mycte 

select @maxcount=count(1) from @temp 

INSERT INTO @temp
SELECT @maxcount+1, CHAR(9)+CHAR(9)+'#endregion' +CHAR(13) + CHAR(10) 
union ALL
SELECT @maxcount+2, CHAR(9)+CHAR(9)+'#region Public Properties' + CHAR(13) + CHAR(10)

set @step=@maxcount+2;
;with mycte2(myrow,col)
as
(
SELECT row_number() OVER(ORDER BY ORDINAL_POSITION) as myrow,CHAR(9)+CHAR(9)+CHAR(9)+'public ' +
CASE
WHEN DATA_TYPE LIKE '%CHAR%' THEN 'string '
WHEN DATA_TYPE LIKE '%INT%' THEN 'int '
WHEN DATA_TYPE LIKE '%DATETIME%' THEN 'DateTime '
WHEN DATA_TYPE LIKE '%BINARY%' THEN 'byte[] '
WHEN DATA_TYPE = 'BIT' THEN 'bool '
WHEN DATA_TYPE LIKE '%TEXT%' THEN 'string '
ELSE 'object '
END + COLUMN_NAME +
CHAR(13) + CHAR(10) + CHAR(9) +CHAR(9)+CHAR(9)+ '{' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +CHAR(9)+CHAR(9)+
'get { return _' + COLUMN_NAME + '; }' +
CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +CHAR(9)+CHAR(9)+
'set { _' + COLUMN_NAME + ' = value; }' +
CHAR(13) + CHAR(10) + CHAR(9) +CHAR(9)+CHAR(9)+ '}'+CHAR(13) + CHAR(10) as 'col'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename
)
INSERT INTO @temp
SELECT cast(myrow AS int)+@step ,col
from mycte2

select @maxcount=count(1) from @temp 

INSERT INTO @temp
SELECT @maxcount+1, CHAR(9)+CHAR(9)+'#endregion' +CHAR(13) + CHAR(10) --16
union ALL
SELECT @maxcount+2, CHAR(9)+CHAR(9)+'#region CRUD Method' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+3, CHAR(9)+CHAR(9)+CHAR(9)+'public virtual void QuerySingle()' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+4,CHAR(9)+CHAR(9)+CHAR(9)+'{'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+5,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+'throw new NotImplementedException();'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+6,CHAR(9)+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+7, CHAR(9)+CHAR(9)+CHAR(9)+'public virtual void QueryAll()' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+8,CHAR(9)+CHAR(9)+CHAR(9)+'{'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+9,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+'throw new NotImplementedException();'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+10,CHAR(9)+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+11, CHAR(9)+CHAR(9)+CHAR(9)+'public virtual void Insert()' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+12,CHAR(9)+CHAR(9)+CHAR(9)+'{'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+13,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+'throw new NotImplementedException();'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+14,CHAR(9)+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+15, CHAR(9)+CHAR(9)+CHAR(9)+'public virtual void Update()' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+16,CHAR(9)+CHAR(9)+CHAR(9)+'{'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+17,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+'throw new NotImplementedException();'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+18,CHAR(9)+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+19, CHAR(9)+CHAR(9)+CHAR(9)+'public virtual void Delete()' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+20,CHAR(9)+CHAR(9)+CHAR(9)+'{'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+21,CHAR(9)+CHAR(9)+CHAR(9)+CHAR(9)+'throw new NotImplementedException();'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+22,CHAR(9)+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+23, CHAR(9)+CHAR(9)+'#endregion CRUD Method' +CHAR(13) + CHAR(10)
union ALL
SELECT @maxcount+24,+CHAR(9)+CHAR(9)+'}'+CHAR(13) + CHAR(10) 
union ALL
SELECT @maxcount+25,'}'+CHAR(13) + CHAR(10) 

declare @cmd nvarchar(4000),@finalcode nvarchar(4000), @currentcode nvarchar(4000)
select @maxcount=count(1) from @temp 
set @step=1 
set @finalcode=''

while(@step<=@maxcount)
BEGIN
SELECT @currentcode=code 
FROM @temp
where codeline=@step

set @step=@step+1
set @finalcode=@finalcode+@currentcode

end

print @finalcode;
GO

 

 

 

執行

exec usp_ConvertTableToClass 'AP_LOG','DAL'

 

擷取部份。

 

如果想把該內容輸出.cs檔案的話，

請參考[SQL SERVER][TSQL]匯出Store Procedure內容並存成檔案

 

參考

[SQL SERVER][TSQL]匯出Store Procedure內容並存成檔案