存储过程生成POCO
下面我们演示使用T-SQL写的一个存储过程，生成C#的POCO代码：
CREATE PROCEDURE usp_TableToClass
/*
Generates C# class code for a table
and fields/properties for each column.
Run as "Results to Text" or "Results to File" (not Grid)
Example: EXEC usp_TableToClass 'MyTable'
*/
@table_name SYSNAME
AS
SET NOCOUNT ON
DECLARE @temp TABLE
(
sort INT,
code TEXT
)
INSERT INTO @temp
SELECT 1, 'public class ' + @table_name + CHAR(13) + CHAR(10) + '{' INSERT INTO @temp
SELECT 2, CHAR(13) + CHAR(10) + '#region Constructors' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 3, CHAR(9) + 'public ' + @table_name + '()'
+ CHAR(13) + CHAR(10) + CHAR(9) + '{' + CHAR(13) + CHAR(10) + CHAR(9) + '}'
INSERT INTO @temp
SELECT 4, '#endregion' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 5, '#region Private Fields' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 6, 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(9)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 7, '#endregion' +
CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 8, '#region Public Properties' + CHAR(13) + CHAR(10)
INSERT INTO @temp
SELECT 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(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'get { return _' + COLUMN_NAME + '; }' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(9) +
'set { _' + COLUMN_NAME + ' = value; }' + CHAR(13) + CHAR(10) + CHAR(9) + '}'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
ORDER BY ORDINAL_POSITION
INSERT INTO @temp
SELECT 10, '#endregion' +
CHAR(13) + CHAR(10) + '}'
SELECT code FROM @temp
ORDER BY sort
在DB中创建好后，怎么用呢？例如下面我们以 aspnet_Membership表为例：
EXEC usp_TableToClass aspnet_Membership
接着生成这样的CSharp代码，不包含行号：
1: public class aspnet_Membership
2: { 3:
4: #region Constructors
5: public aspnet_Membership()
6: { 7: }
8: #endregion
9: #region Private Fields
10: private object _ApplicationId;
11: private object _UserId;
12: private string _Password;
13: private int _PasswordFormat;
14: private string _PasswordSalt;
15: private string _MobilePIN;
16: private string _Email;
17: private string _LoweredEmail;
18: private string _PasswordQuestion;
19: private string _PasswordAnswer;
20: private bool _IsApproved;
21: private bool _IsLockedOut;
22: private DateTime _CreateDate;
23: private DateTime _LastLoginDate;
24: private DateTime _LastPasswordChangedDate;
25: private DateTime _LastLockoutDate;
26: private int _FailedPasswordAttemptCount;
27: private DateTime _FailedPasswordAttemptWindowStart;
28: private int _FailedPasswordAnswerAttemptCount;
29: private DateTime _FailedPasswordAnswerAttemptWindowStart;
30: private string _Comment;
31: #endregion
32: #region Public Properties
33: public object ApplicationId
34: { 35: get { return _ApplicationId; } 36: set { _ApplicationId = value; } 37: }
38: public object UserId
39: { 40: get { return _UserId; } 41: set { _UserId = value; } 42: }
43: public string Password
44: { 45: get { return _Password; } 46: set { _Password = value; } 47: }
48: public int PasswordFormat
49: { 50: get { return _PasswordFormat; } 51: set { _PasswordFormat = value; } 52: }
53: public string PasswordSalt
54: { 55: get { return _PasswordSalt; } 56: set { _PasswordSalt = value; } 57: }
58: public string MobilePIN
59: { 60: get { return _MobilePIN; } 61: set { _MobilePIN = value; } 62: }
63: public string Email
64: { 65: get { return _Email; } 66: set { _Email = value; } 67: }
68: public string LoweredEmail
69: { 70: get { return _LoweredEmail; } 71: set { _LoweredEmail = value; } 72: }
73: public string PasswordQuestion
74: { 75: get { return _PasswordQuestion; } 76: set { _PasswordQuestion = value; } 77: }
78: public string PasswordAnswer
79: { 80: get { return _PasswordAnswer; } 81: set { _PasswordAnswer = value; } 82: }
83: public bool IsApproved
84: { 85: get { return _IsApproved; } 86: set { _IsApproved = value; } 87: }
88: public bool IsLockedOut
89: { 90: get { return _IsLockedOut; } 91: set { _IsLockedOut = value; } 92: }
93: public DateTime CreateDate
94: { 95: get { return _CreateDate; } 96: set { _CreateDate = value; } 97: }
98: public DateTime LastLoginDate
99: { 100: get { return _LastLoginDate; } 101: set { _LastLoginDate = value; } 102: }
103: public DateTime LastPasswordChangedDate
104: { 105: get { return _LastPasswordChangedDate; } 106: set { _LastPasswordChangedDate = value; } 107: }
108: public DateTime LastLockoutDate
109: { 110: get { return _LastLockoutDate; } 111: set { _LastLockoutDate = value; } 112: }
113: public int FailedPasswordAttemptCount
114: { 115: get { return _FailedPasswordAttemptCount; } 116: set { _FailedPasswordAttemptCount = value; } 117: }
118: public DateTime FailedPasswordAttemptWindowStart
119: { 120: get { return _FailedPasswordAttemptWindowStart; } 121: set { _FailedPasswordAttemptWindowStart = value; } 122: }
123: public int FailedPasswordAnswerAttemptCount
124: { 125: get { return _FailedPasswordAnswerAttemptCount; } 126: set { _FailedPasswordAnswerAttemptCount = value; } 127: }
128: public DateTime FailedPasswordAnswerAttemptWindowStart
129: { 130: get { return _FailedPasswordAnswerAttemptWindowStart; } 131: set { _FailedPasswordAnswerAttemptWindowStart = value; } 132: }
133: public string Comment
134: { 135: get { return _Comment; } 136: set { _Comment = value; } 137: }
138: #endregion
139: }
140:
这篇文章只是抛砖引玉，你可以创建更强大的功能SP, 或许这是一个思路。
希望对您开发有帮助。
作者：Petter Liu
出处：http://www.cnblogs.com/wintersun/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
该文章也同时发布在我的独立博客中-Petter Liu Blog。
TAG:
