petapoco是個基於T4模板的輕量級ORM，好用效率高，具體介紹略了
獲取注釋基本原理是調用數據庫::fn_listextendedproperty函數，獲取擴展屬性MS_Description
technet參考資料：sys.fn_listextendedproperty (Transact-SQL)
直接上代碼
首先是PetaPoco.Core.ttinclude
106行原始：
...
public string SequenceName;
public bool Ignore;
...
新建Description字段，這個是表的注釋
...
public string SequenceName;
public bool Ignore;
public string Description;
...
140行原始：
...
public bool IsAutoIncrement;
public bool Ignore;
...
新建Description字段，這個是列的注釋
...
public bool IsAutoIncrement;
public bool Ignore;
public string Description;
...
517行SqlServerSchemaReader.ReadSchema方法，原始：
...
foreach (var tbl in result)
{
tbl.Columns=LoadColumns(tbl);
// Mark the primary key
string PrimaryKey=GetPK(tbl.Name);
var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
if(pkColumn!=null)
{
pkColumn.IsPK=true;
}
}
...
修改後，調用函數獲取表的注釋：
...
foreach (var tbl in result)
{
using(var cmdDesc=_factory.CreateCommand())
{
cmdDesc.Connection=connection;
cmdDesc.CommandText=TABLE_DESC_SQL;
DbParameter p = null;
p = cmdDesc.CreateParameter();
p.ParameterName = "@schema";
p.Value=tbl.Schema;
cmdDesc.Parameters.Add(p);
p = cmdDesc.CreateParameter();
p.ParameterName = "@table";
p.Value=tbl.Name;
cmdDesc.Parameters.Add(p);
using (var rdrDesc=cmdDesc.ExecuteReader())
{
if(rdrDesc.Read())
tbl.Description=rdrDesc["value"].ToString();
}
}
...
572行插入新的代碼，獲取每一列的注釋：
...
foreach (var col in result)
{
using(var cmdDesc=_factory.CreateCommand())
{
cmdDesc.Connection=_connection;
cmdDesc.CommandText=COLUMN_DESC_SQL;
DbParameter pDesc = null;
pDesc = cmdDesc.CreateParameter();
pDesc.ParameterName = "@schema";
pDesc.Value=tbl.Schema;
cmdDesc.Parameters.Add(pDesc);
pDesc = cmdDesc.CreateParameter();
pDesc.ParameterName = "@table";
pDesc.Value=tbl.Name;
cmdDesc.Parameters.Add(pDesc);
pDesc = cmdDesc.CreateParameter();
pDesc.ParameterName = "@column";
pDesc.Value=col.Name;
cmdDesc.Parameters.Add(pDesc);
using (var rdrDesc=cmdDesc.ExecuteReader())
{
if(rdrDesc.Read())
col.Description=rdrDesc["value"].ToString();
}
}
}
...
672行插入新的代碼，存儲調用函數的sql語句：
...
const string TABLE_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, null, null)";
const string COLUMN_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, 'column', @column)";
...
附完整PetaPoco.Core.ttinclude：
1 <#@ template language="C#v3.5" hostspecific="True" #>
2 <#@ assembly name="EnvDTE" #>
3 <#@ assembly name="System.Core.dll" #>
4 <#@ assembly name="System.Data" #>
5 <#@ assembly name="System.Xml" #>
6 <#@ assembly name="System.Configuration" #>
7 <#@ assembly name="System.Windows.Forms" #>
8 <#@ import namespace="System.Collections.Generic" #>
9 <#@ import namespace="System.Data" #>
10 <#@ import namespace="System.Data.SqlClient" #>
11 <#@ import namespace="System.Data.Common" #>
12 <#@ import namespace="System.Diagnostics" #>
13 <#@ import namespace="System.Globalization" #>
14 <#@ import namespace="System.IO" #>
15 <#@ import namespace="System.Linq" #>
16 <#@ import namespace="System.Text" #>
17 <#@ import namespace="System.Text.RegularExpressions" #>
18 <#@ import namespace="System.Configuration" #>
19 <#@ import namespace="System.Windows.Forms" #>
20 <#+
21
22 /*
23 This code is part of the PetaPoco project (http://www.toptensoftware.com/petapoco).
24 It is based on the SubSonic T4 templates but has been considerably re-organized and reduced
25
26 -----------------------------------------------------------------------------------------
27
28 This template can read minimal schema information from the following databases:
29
30 * SQL Server
31 * SQL Server CE
32 * MySQL
33 * PostGreSQL
34 * Oracle
35
36 For connection and provider settings the template will look for the web.config or app.config file of the
37 containing Visual Studio project. It will not however read DbProvider settings from this file.
38
39 In order to work, the appropriate driver must be registered in the system machine.config file. If you're
40 using Visual Studio 2010 the file you want is here:
41
42 C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
43
44 After making changes to machine.config you will also need to restart Visual Studio.
45
46 Here's a typical set of entries that might help if you're stuck:
47
48 <system.data>
49 <DbProviderFactories>
50 <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
51 <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
52 <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
53 <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
54 <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.3.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
55 <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/><add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
56 <add name="Npgsql Data Provider" invariant="Npgsql" support="FF" description=".Net Framework Data Provider for Postgresql Server" type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.11.91, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7" />
57 </DbProviderFactories>
58 </system.data>
59
60 Also, the providers and their dependencies need to be installed to GAC.
61
62 Eg; this is how I installed the drivers for PostgreSQL
63
64 gacutil /i Npgsql.dll
65 gacutil /i Mono.Security.dll
66
67 -----------------------------------------------------------------------------------------
68
69 SubSonic - http://subsonicproject.com
70
71 The contents of this file are subject to the New BSD
72 License (the "License"); you may not use this file
73 except in compliance with the License. You may obtain a copy of
74 the License at http://www.opensource.org/licenses/bsd-license.php
75
76 Software distributed under the License is distributed on an
77 "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
78 implied. See the License for the specific language governing
79 rights and limitations under the License.
80 */
81
82 string ConnectionStringName = "";
83 string Namespace = "";
84 string RepoName = "";
85 string ClassPrefix = "";
86 string ClassSuffix = "";
87 string SchemaName = null;
88 bool IncludeViews = false;
89 bool GenerateOperations = false;
90 bool GenerateCommon = true;
91 bool GeneratePocos = true;
92 bool ExplicitColumns = true;
93 bool TrackModifiedColumns = false;
94 string[] ExcludePrefix = new string[] {};
95
96
97 public class Table
98 {
99 public List<Column> Columns;
100 public string Name;
101 public string Schema;
102 public bool IsView;
103 public string CleanName;
104 public string ClassName;
105 public string SequenceName;
106 public bool Ignore;
107 public string Description;
108
109 public Column PK
110 {
111 get
112 {
113 return this.Columns.SingleOrDefault(x=>x.IsPK);
114 }
115 }
116
117 public Column GetColumn(string columnName)
118 {
119 return Columns.Single(x=>string.Compare(x.Name, columnName, true)==0);
120 }
121
122 public Column this[string columnName]
123 {
124 get
125 {
126 return GetColumn(columnName);
127 }
128 }
129
130 }
131
132 public class Column
133 {
134 public string Name;
135 public string PropertyName;
136 public string PropertyType;
137 public bool IsPK;
138 public bool IsNullable;
139 public bool IsAutoIncrement;
140 public bool Ignore;
141 public string Description;
142 }
143
144 public class Tables : List<Table>
145 {
146 public Tables()
147 {
148 }
149
150 public Table GetTable(string tableName)
151 {
152 return this.Single(x=>string.Compare(x.Name, tableName, true)==0);
153 }
154
155 public Table this[string tableName]
156 {
157 get
158 {
159 return GetTable(tableName);
160 }
161 }
162
163 }
164
165
166 static Regex rxCleanUp = new Regex(@"[^\w\d_]", RegexOptions.Compiled);
167
168 static string[] cs_keywords = { "abstract", "event", "new", "struct", "as", "explicit", "null",
169 "switch", "base", "extern", "object", "this", "bool", "false", "operator", "throw",
170 "break", "finally", "out", "true", "byte", "fixed", "override", "try", "case", "float",
171 "params", "typeof", "catch", "for", "private", "uint", "char", "foreach", "protected",
172 "ulong", "checked", "goto", "public", "unchecked", "class", "if", "readonly", "unsafe",
173 "const", "implicit", "ref", "ushort", "continue", "in", "return", "using", "decimal",
174 "int", "sbyte", "virtual", "default", "interface", "sealed", "volatile", "delegate",
175 "internal", "short", "void", "do", "is", "sizeof", "while", "double", "lock",
176 "stackalloc", "else", "long", "static", "enum", "namespace", "string" };
177
178 static Func<string, string> CleanUp = (str) =>
179 {
180 str = rxCleanUp.Replace(str, "_");
181
182 if (char.IsDigit(str[0]) || cs_keywords.Contains(str))
183 str = "@" + str;
184
185 return str;
186 };
187
188 string CheckNullable(Column col)
189 {
190 string result="";
191 if(col.IsNullable &&
192 col.PropertyType !="byte[]" &&
193 col.PropertyType !="string" &&
194 col.PropertyType !="Microsoft.SqlServer.Types.SqlGeography" &&
195 col.PropertyType !="Microsoft.SqlServer.Types.SqlGeometry"
196 )
197 result="?";
198 return result;
199 }
200
201 string GetConnectionString(ref string connectionStringName, out string providerName)
202 {
203 var _CurrentProject = GetCurrentProject();
204
205 providerName=null;
206
207 string result="";
208 ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
209 configFile.ExeConfigFilename = GetConfigPath();
210
211 if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
212 throw new ArgumentNullException("The project does not contain App.config or Web.config file.");
213
214
215 var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);
216 var connSection=config.ConnectionStrings;
217
218 //if the connectionString is empty - which is the defauls
219 //look for count-1 - this is the last connection string
220 //and takes into account AppServices and LocalSqlServer
221 if(string.IsNullOrEmpty(connectionStringName))
222 {
223 if(connSection.ConnectionStrings.Count>1)
224 {
225 connectionStringName = connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].Name;
226 result=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ConnectionString;
227 providerName=connSection.ConnectionStrings[connSection.ConnectionStrings.Count-1].ProviderName;
228 }
229 }
230 else
231 {
232 try
233 {
234 result=connSection.ConnectionStrings[connectionStringName].ConnectionString;
235 providerName=connSection.ConnectionStrings[connectionStringName].ProviderName;
236 }
237 catch
238 {
239 result="There is no connection string name called '"+connectionStringName+"'";
240 }
241 }
242
243 // if (String.IsNullOrEmpty(providerName))
244 // providerName="System.Data.SqlClient";
245
246 return result;
247 }
248
249 string _connectionString="";
250 string _providerName="";
251
252 void InitConnectionString()
253 {
254 if(String.IsNullOrEmpty(_connectionString))
255 {
256 _connectionString=GetConnectionString(ref ConnectionStringName, out _providerName);
257
258 if(_connectionString.Contains("|DataDirectory|"))
259 {
260 //have to replace it
261 string dataFilePath=GetDataDirectory();
262 _connectionString=_connectionString.Replace("|DataDirectory|",dataFilePath);
263 }
264 }
265 }
266
267 public string ConnectionString
268 {
269 get
270 {
271 InitConnectionString();
272 return _connectionString;
273 }
274 }
275
276 public string ProviderName
277 {
278 get
279 {
280 InitConnectionString();
281 return _providerName;
282 }
283 }
284
285 public EnvDTE.Project GetCurrentProject() {
286
287 IServiceProvider _ServiceProvider = (IServiceProvider)Host;
288 if (_ServiceProvider == null)
289 throw new Exception("Host property returned unexpected value (null)");
290
291 EnvDTE.DTE dte = (EnvDTE.DTE)_ServiceProvider.GetService(typeof(EnvDTE.DTE));
292 if (dte == null)
293 throw new Exception("Unable to retrieve EnvDTE.DTE");
294
295 Array activeSolutionProjects = (Array)dte.ActiveSolutionProjects;
296 if (activeSolutionProjects == null)
297 throw new Exception("DTE.ActiveSolutionProjects returned null");
298
299 EnvDTE.Project dteProject = (EnvDTE.Project)activeSolutionProjects.GetValue(0);
300 if (dteProject == null)
301 throw new Exception("DTE.ActiveSolutionProjects[0] returned null");
302
303 return dteProject;
304
305 }
306
307 private string GetProjectPath()
308 {
309 EnvDTE.Project project = GetCurrentProject();
310 System.IO.FileInfo info = new System.IO.FileInfo(project.FullName);
311 return info.Directory.FullName;
312 }
313
314 private string GetConfigPath()
315 {
316 EnvDTE.Project project = GetCurrentProject();
317 foreach (EnvDTE.ProjectItem item in project.ProjectItems)
318 {
319 // if it is the app.config file, then open it up
320 if (item.Name.Equals("App.config",StringComparison.InvariantCultureIgnoreCase) || item.Name.Equals("Web.config",StringComparison.InvariantCultureIgnoreCase))
321 return GetProjectPath() + "\\" + item.Name;
322 }
323 return String.Empty;
324 }
325
326 public string GetDataDirectory()
327 {
328 EnvDTE.Project project=GetCurrentProject();
329 return System.IO.Path.GetDirectoryName(project.FileName)+"\\App_Data\\";
330 }
331
332 static string zap_password(string connectionString)
333 {
334 var rx = new Regex("password=.*;", RegexOptions.Singleline | RegexOptions.Multiline | RegexOptions.IgnoreCase);
335 return rx.Replace(connectionString, "password=**zapped**;");
336 }
337
338
339
340 Tables LoadTables()
341 {
342 InitConnectionString();
343
344 WriteLine("// This file was automatically generated by the PetaPoco T4 Template");
345 WriteLine("// Do not make changes directly to this file - edit the template instead");
346 WriteLine("// ");
347 WriteLine("// The following connection settings were used to generate this file");
348 WriteLine("// ");
349 WriteLine("// Connection String Name: `{0}`", ConnectionStringName);
350 WriteLine("// Provider: `{0}`", ProviderName);
351 WriteLine("// Connection String: `{0}`", zap_password(ConnectionString));
352 WriteLine("// Schema: `{0}`", SchemaName);
353 WriteLine("// Include Views: `{0}`", IncludeViews);
354 WriteLine("");
355
356 DbProviderFactory _factory;
357 try
358 {
359 _factory = DbProviderFactories.GetFactory(ProviderName);
360 }
361 catch (Exception x)
362 {
363 var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
364 Warning(string.Format("Failed to load provider `{0}` - {1}", ProviderName, error));
365 WriteLine("");
366 WriteLine("// -----------------------------------------------------------------------------------------");
367 WriteLine("// Failed to load provider `{0}` - {1}", ProviderName, error);
368 WriteLine("// -----------------------------------------------------------------------------------------");
369 WriteLine("");
370 return new Tables();
371 }
372
373 try
374 {
375 Tables result;
376 using(var conn=_factory.CreateConnection())
377 {
378 conn.ConnectionString=ConnectionString;
379 conn.Open();
380
381 SchemaReader reader=null;
382
383 if (_factory.GetType().Name == "MySqlClientFactory")
384 {
385 // MySql
386 reader=new MySqlSchemaReader();
387 }
388 else if (_factory.GetType().Name == "SqlCeProviderFactory")
389 {
390 // SQL CE
391 reader=new SqlServerCeSchemaReader();
392 }
393 else if (_factory.GetType().Name == "NpgsqlFactory")
394 {
395 // PostgreSQL
396 reader=new PostGreSqlSchemaReader();
397 }
398 else if (_factory.GetType().Name == "OracleClientFactory")
399 {
400 // Oracle
401 reader=new OracleSchemaReader();
402 }
403 else
404 {
405 // Assume SQL Server
406 reader=new SqlServerSchemaReader();
407 }
408
409 reader.outer=this;
410 result=reader.ReadSchema(conn, _factory);
411
412 // Remove unrequired tables/views
413 for (int i=result.Count-1; i>=0; i--)
414 {
415 if (SchemaName!=null && string.Compare(result[i].Schema, SchemaName, true)!=0)
416 {
417 result.RemoveAt(i);
418 continue;
419 }
420 if (!IncludeViews && result[i].IsView)
421 {
422 result.RemoveAt(i);
423 continue;
424 }
425 if(StartsWithAny(result[i].ClassName, ExcludePrefix)) {
426 result.RemoveAt(i);
427 continue;
428 }
429 }
430
431 conn.Close();
432
433
434 var rxClean = new Regex("^(Equals|GetHashCode|GetType|ToString|repo|Save|IsNew|Insert|Update|Delete|Exists|SingleOrDefault|Single|First|FirstOrDefault|Fetch|Page|Query)$");
435 foreach (var t in result)
436 {
437 t.ClassName = ClassPrefix + t.ClassName + ClassSuffix;
438 foreach (var c in t.Columns)
439 {
440 c.PropertyName = rxClean.Replace(c.PropertyName, "_$1");
441
442 // Make sure property name doesn't clash with class name
443 if (c.PropertyName == t.ClassName)
444 c.PropertyName = "_" + c.PropertyName;
445 }
446 }
447
448 return result;
449 }
450 }
451 catch (Exception x)
452 {
453 var error=x.Message.Replace("\r\n", "\n").Replace("\n", " ");
454 Warning(string.Format("Failed to read database schema - {0}", error));
455 WriteLine("");
456 WriteLine("// -----------------------------------------------------------------------------------------");
457 WriteLine("// Failed to read database schema - {0}", error);
458 WriteLine("// -----------------------------------------------------------------------------------------");
459 WriteLine("");
460 return new Tables();
461 }
462
463
464 }
465
466 bool StartsWithAny(string s, IEnumerable<string> items)
467 {
468 if (s == null)
469 return false;
470
471 return items.Any(i => s.StartsWith(i));
472 }
473
474 abstract class SchemaReader
475 {
476 public abstract Tables ReadSchema(DbConnection connection, DbProviderFactory factory);
477 public GeneratedTextTransformation outer;
478 public void WriteLine(string o)
479 {
480 outer.WriteLine(o);
481 }
482
483 }
484
485 class SqlServerSchemaReader : SchemaReader
486 {
487 // SchemaReader.ReadSchema
488 public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
489 {
490 var result=new Tables();
491
492 _connection=connection;
493 _factory=factory;
494
495
496
497 //pull the tables in a reader
498 using(var cmd=_factory.CreateCommand())
499 {
500 cmd.Connection=connection;
501 cmd.CommandText=TABLE_SQL;
502 using (var rdr=cmd.ExecuteReader())
503 {
504 while(rdr.Read())
505 {
506 Table tbl=new Table();
507 tbl.Name=rdr["TABLE_NAME"].ToString();
508 tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
509 tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
510 tbl.CleanName=CleanUp(tbl.Name);
511 tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
512 result.Add(tbl);
513 }
514 }
515 }
516 foreach (var tbl in result)
517 {
518 using(var cmdDesc=_factory.CreateCommand())
519 {
520 cmdDesc.Connection=connection;
521 cmdDesc.CommandText=TABLE_DESC_SQL;
522
523 DbParameter p = null;
524
525 p = cmdDesc.CreateParameter();
526 p.ParameterName = "@schema";
527 p.Value=tbl.Schema;
528 cmdDesc.Parameters.Add(p);
529
530 p = cmdDesc.CreateParameter();
531 p.ParameterName = "@table";
532 p.Value=tbl.Name;
533 cmdDesc.Parameters.Add(p);
534
535 using (var rdrDesc=cmdDesc.ExecuteReader())
536 {
537 if(rdrDesc.Read())
538 tbl.Description=rdrDesc["value"].ToString();
539 }
540 }
541
542 tbl.Columns=LoadColumns(tbl);
543
544 // Mark the primary key
545 string PrimaryKey=GetPK(tbl.Name);
546 var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
547 if(pkColumn!=null)
548 {
549 pkColumn.IsPK=true;
550 }
551 }
552
553
554 return result;
555 }
556
557 DbConnection _connection;
558 DbProviderFactory _factory;
559
560
561 List<Column> LoadColumns(Table tbl)
562 {
563
564 using (var cmd=_factory.CreateCommand())
565 {
566 cmd.Connection=_connection;
567 cmd.CommandText=COLUMN_SQL;
568
569 var p = cmd.CreateParameter();
570 p.ParameterName = "@tableName";
571 p.Value=tbl.Name;
572 cmd.Parameters.Add(p);
573
574 p = cmd.CreateParameter();
575 p.ParameterName = "@schemaName";
576 p.Value=tbl.Schema;
577 cmd.Parameters.Add(p);
578
579 var result=new List<Column>();
580 using (IDataReader rdr=cmd.ExecuteReader())
581 {
582 while(rdr.Read())
583 {
584 Column col=new Column();
585 col.Name=rdr["ColumnName"].ToString();
586 col.PropertyName=CleanUp(col.Name);
587 col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
588 col.IsNullable=rdr["IsNullable"].ToString()=="YES";
589 col.IsAutoIncrement=((int)rdr["IsIdentity"])==1 ||
590 (!DBNull.Value.Equals(rdr["DefaultSetting"]) && ((string)rdr["DefaultSetting"] == "(newsequentialid())" ||
591 (string)rdr["DefaultSetting"] == "(newid())"));
592 result.Add(col);
593 }
594 }
595 foreach (var col in result)
596 {
597 using(var cmdDesc=_factory.CreateCommand())
598 {
599 cmdDesc.Connection=_connection;
600 cmdDesc.CommandText=COLUMN_DESC_SQL;
601
602 DbParameter pDesc = null;
603
604 pDesc = cmdDesc.CreateParameter();
605 pDesc.ParameterName = "@schema";
606 pDesc.Value=tbl.Schema;
607 cmdDesc.Parameters.Add(pDesc);
608
609 pDesc = cmdDesc.CreateParameter();
610 pDesc.ParameterName = "@table";
611 pDesc.Value=tbl.Name;
612 cmdDesc.Parameters.Add(pDesc);
613
614 pDesc = cmdDesc.CreateParameter();
615 pDesc.ParameterName = "@column";
616 pDesc.Value=col.Name;
617 cmdDesc.Parameters.Add(pDesc);
618 using (var rdrDesc=cmdDesc.ExecuteReader())
619 {
620 if(rdrDesc.Read())
621 col.Description=rdrDesc["value"].ToString();
622 }
623 }
624 }
625 return result;
626 }
627 }
628
629 string GetPK(string table){
630
631 string sql=@"SELECT c.name AS ColumnName
632 FROM sys.indexes AS i
633 INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
634 INNER JOIN sys.objects AS o ON i.object_id = o.object_id
635 LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
636 WHERE (i.is_primary_key = 1) AND (o.name = @tableName)";
637
638 using (var cmd=_factory.CreateCommand())
639 {
640 cmd.Connection=_connection;
641 cmd.CommandText=sql;
642
643 var p = cmd.CreateParameter();
644 p.ParameterName = "@tableName";
645 p.Value=table;
646 cmd.Parameters.Add(p);
647
648 var result=cmd.ExecuteScalar();
649
650 if(result!=null)
651 return result.ToString();
652 }
653
654 return "";
655 }
656
657 string GetPropertyType(string sqlType)
658 {
659 string sysType="string";
660 switch (sqlType)
661 {
662 case "bigint":
663 sysType = "long";
664 break;
665 case "smallint":
666 sysType= "short";
667 break;
668 case "int":
669 sysType= "int";
670 break;
671 case "uniqueidentifier":
672 sysType= "Guid";
673 break;
674 case "smalldatetime":
675 case "datetime":
676 case "datetime2":
677 case "date":
678 case "time":
679 sysType= "DateTime";
680 break;
681 case "datetimeoffset":
682 sysType = "DateTimeOffset";
683 break;
684 case "float":
685 sysType="double";
686 break;
687 case "real":
688 sysType="float";
689 break;
690 case "numeric":
691 case "smallmoney":
692 case "decimal":
693 case "money":
694 sysType= "decimal";
695 break;
696 case "tinyint":
697 sysType = "byte";
698 break;
699 case "bit":
700 sysType= "bool";
701 break;
702 case "image":
703 case "binary":
704 case "varbinary":
705 case "timestamp":
706 sysType= "byte[]";
707 break;
708 case "geography":
709 sysType = "Microsoft.SqlServer.Types.SqlGeography";
710 break;
711 case "geometry":
712 sysType = "Microsoft.SqlServer.Types.SqlGeometry";
713 break;
714 }
715 return sysType;
716 }
717
718
719
720 const string TABLE_SQL=@"SELECT *
721 FROM INFORMATION_SCHEMA.TABLES
722 WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
723 ORDER BY TABLE_SCHEMA,TABLE_TYPE,TABLE_NAME";
724
725 const string TABLE_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, null, null)";
726
727 const string COLUMN_SQL=@"SELECT
728 TABLE_CATALOG AS [Database],
729 TABLE_SCHEMA AS Owner,
730 TABLE_NAME AS TableName,
731 COLUMN_NAME AS ColumnName,
732 ORDINAL_POSITION AS OrdinalPosition,
733 COLUMN_DEFAULT AS DefaultSetting,
734 IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
735 CHARACTER_MAXIMUM_LENGTH AS MaxLength,
736 DATETIME_PRECISION AS DatePrecision,
737 COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
738 COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
739 FROM INFORMATION_SCHEMA.COLUMNS
740 WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA=@schemaName
741 ORDER BY OrdinalPosition ASC";
742
743 const string COLUMN_DESC_SQL=@"SELECT value FROM ::fn_listextendedproperty('MS_Description', 'user', @schema, 'table', @table, 'column', @column)";
744 }
745
746 class SqlServerCeSchemaReader : SchemaReader
747 {
748 // SchemaReader.ReadSchema
749 public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
750 {
751 var result=new Tables();
752
753 _connection=connection;
754 _factory=factory;
755
756 var cmd=_factory.CreateCommand();
757 cmd.Connection=connection;
758 cmd.CommandText=TABLE_SQL;
759
760 //pull the tables in a reader
761 using(cmd)
762 {
763 using (var rdr=cmd.ExecuteReader())
764 {
765 while(rdr.Read())
766 {
767 Table tbl=new Table();
768 tbl.Name=rdr["TABLE_NAME"].ToString();
769 tbl.CleanName=CleanUp(tbl.Name);
770 tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
771 tbl.Schema=null;
772 tbl.IsView=false;
773 result.Add(tbl);
774 }
775 }
776 }
777
778 foreach (var tbl in result)
779 {
780 tbl.Columns=LoadColumns(tbl);
781
782 // Mark the primary key
783 string PrimaryKey=GetPK(tbl.Name);
784 var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
785 if(pkColumn!=null)
786 pkColumn.IsPK=true;
787 }
788
789
790 return result;
791 }
792
793 DbConnection _connection;
794 DbProviderFactory _factory;
795
796
797 List<Column> LoadColumns(Table tbl)
798 {
799
800 using (var cmd=_factory.CreateCommand())
801 {
802 cmd.Connection=_connection;
803 cmd.CommandText=COLUMN_SQL;
804
805 var p = cmd.CreateParameter();
806 p.ParameterName = "@tableName";
807 p.Value=tbl.Name;
808 cmd.Parameters.Add(p);
809
810 var result=new List<Column>();
811 using (IDataReader rdr=cmd.ExecuteReader())
812 {
813 while(rdr.Read())
814 {
815 Column col=new Column();
816 col.Name=rdr["ColumnName"].ToString();
817 col.PropertyName=CleanUp(col.Name);
818 col.PropertyType=GetPropertyType(rdr["DataType"].ToString());
819 col.IsNullable=rdr["IsNullable"].ToString()=="YES";
820 col.IsAutoIncrement=rdr["AUTOINC_INCREMENT"]!=DBNull.Value;
821 result.Add(col);
822 }
823 }
824
825 return result;
826 }
827 }
828
829 string GetPK(string table){
830
831 string sql=@"SELECT KCU.COLUMN_NAME
832 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
833 JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
834 ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
835 WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
836 AND KCU.TABLE_NAME=@tableName";
837
838 using (var cmd=_factory.CreateCommand())
839 {
840 cmd.Connection=_connection;
841 cmd.CommandText=sql;
842
843 var p = cmd.CreateParameter();
844 p.ParameterName = "@tableName";
845 p.Value=table;
846 cmd.Parameters.Add(p);
847
848 var result=cmd.ExecuteScalar();
849
850 if(result!=null)
851 return result.ToString();
852 }
853
854 return "";
855 }
856
857 string GetPropertyType(string sqlType)
858 {
859 string sysType="string";
860 switch (sqlType)
861 {
862 case "bigint":
863 sysType = "long";
864 break;
865 case "smallint":
866 sysType= "short";
867 break;
868 case "int":
869 sysType= "int";
870 break;
871 case "uniqueidentifier":
872 sysType= "Guid";
873 break;
874 case "smalldatetime":
875 case "datetime":
876 case "date":
877 case "time":
878 sysType= "DateTime";
879 break;
880 case "float":
881 sysType="double";
882 break;
883 case "real":
884 sysType="float";
885 break;
886 case "numeric":
887 case "smallmoney":
888 case "decimal":
889 case "money":
890 sysType= "decimal";
891 break;
892 case "tinyint":
893 sysType = "byte";
894 break;
895 case "bit":
896 sysType= "bool";
897 break;
898 case "image":
899 case "binary":
900 case "varbinary":
901 case "timestamp":
902 sysType= "byte[]";
903 break;
904 }
905 return sysType;
906 }
907
908
909
910 const string TABLE_SQL=@"SELECT *
911 FROM INFORMATION_SCHEMA.TABLES
912 WHERE TABLE_TYPE='TABLE'";
913
914 const string COLUMN_SQL=@"SELECT
915 TABLE_CATALOG AS [Database],
916 TABLE_SCHEMA AS Owner,
917 TABLE_NAME AS TableName,
918 COLUMN_NAME AS ColumnName,
919 ORDINAL_POSITION AS OrdinalPosition,
920 COLUMN_DEFAULT AS DefaultSetting,
921 IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType,
922 AUTOINC_INCREMENT,
923 CHARACTER_MAXIMUM_LENGTH AS MaxLength,
924 DATETIME_PRECISION AS DatePrecision
925 FROM INFORMATION_SCHEMA.COLUMNS
926 WHERE TABLE_NAME=@tableName
927 ORDER BY OrdinalPosition ASC";
928
929 }
930
931
932 class PostGreSqlSchemaReader : SchemaReader
933 {
934 // SchemaReader.ReadSchema
935 public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
936 {
937 var result=new Tables();
938
939 _connection=connection;
940 _factory=factory;
941
942 var cmd=_factory.CreateCommand();
943 cmd.Connection=connection;
944 cmd.CommandText=TABLE_SQL;
945
946 //pull the tables in a reader
947 using(cmd)
948 {
949 using (var rdr=cmd.ExecuteReader())
950 {
951 while(rdr.Read())
952 {
953 Table tbl=new Table();
954 tbl.Name=rdr["table_name"].ToString();
955 tbl.Schema=rdr["table_schema"].ToString();
956 tbl.IsView=string.Compare(rdr["table_type"].ToString(), "View", true)==0;
957 tbl.CleanName=CleanUp(tbl.Name);
958 tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
959 result.Add(tbl);
960 }
961 }
962 }
963
964 foreach (var tbl in result)
965 {
966 tbl.Columns=LoadColumns(tbl);
967
968 // Mark the primary key
969 string PrimaryKey=GetPK(tbl.Name);
970 var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
971 if(pkColumn!=null)
972 pkColumn.IsPK=true;
973 }
974
975
976 return result;
977 }
978
979 DbConnection _connection;
980 DbProviderFactory _factory;
981
982
983 List<Column> LoadColumns(Table tbl)
984 {
985
986 using (var cmd=_factory.CreateCommand())
987 {
988 cmd.Connection=_connection;
989 cmd.CommandText=COLUMN_SQL;
990
991 var p = cmd.CreateParameter();
992 p.ParameterName = "@tableName";
993 p.Value=tbl.Name;
994 cmd.Parameters.Add(p);
995
996 var result=new List<Column>();
997 using (IDataReader rdr=cmd.ExecuteReader())
998 {
999 while(rdr.Read())
1000 {
1001 Column col=new Column();
1002 col.Name=rdr["column_name"].ToString();
1003 col.PropertyName=CleanUp(col.Name);
1004 col.PropertyType=GetPropertyType(rdr["udt_name"].ToString());
1005 col.IsNullable=rdr["is_nullable"].ToString()=="YES";
1006 col.IsAutoIncrement = rdr["column_default"].ToString().StartsWith("nextval(");
1007 result.Add(col);
1008 }
1009 }
1010
1011 return result;
1012 }
1013 }
1014
1015 string GetPK(string table){
1016
1017 string sql=@"SELECT kcu.column_name
1018 FROM information_schema.key_column_usage kcu
1019 JOIN information_schema.table_constraints tc
1020 ON kcu.constraint_name=tc.constraint_name
1021 WHERE lower(tc.constraint_type)='primary key'
1022 AND kcu.table_name=@tablename";
1023
1024 using (var cmd=_factory.CreateCommand())
1025 {
1026 cmd.Connection=_connection;
1027 cmd.CommandText=sql;
1028
1029 var p = cmd.CreateParameter();
1030 p.ParameterName = "@tableName";
1031 p.Value=table;
1032 cmd.Parameters.Add(p);
1033
1034 var result=cmd.ExecuteScalar();
1035
1036 if(result!=null)
1037 return result.ToString();
1038 }
1039
1040 return "";
1041 }
1042
1043 string GetPropertyType(string sqlType)
1044 {
1045 switch (sqlType)
1046 {
1047 case "int8":
1048 case "serial8":
1049 return "long";
1050
1051 case "bool":
1052 return "bool";
1053
1054 case "bytea ":
1055 return "byte[]";
1056
1057 case "float8":
1058 return "double";
1059
1060 case "int4":
1061 case "serial4":
1062 return "int";
1063
1064 case "money ":
1065 return "decimal";
1066
1067 case "numeric":
1068 return "decimal";
1069
1070 case "float4":
1071 return "float";
1072
1073 case "int2":
1074 return "short";
1075
1076 case "time":
1077 case "timetz":
1078 case "timestamp":
1079 case "timestamptz":
1080 case "date":
1081 return "DateTime";
1082
1083 case "uuid":
1084 return "Guid";
1085
1086 default:
1087 return "string";
1088 }
1089 }
1090
1091
1092
1093 const string TABLE_SQL=@"
1094 SELECT table_name, table_schema, table_type
1095 FROM information_schema.tables
1096 WHERE (table_type='BASE TABLE' OR table_type='VIEW')
1097 AND table_schema NOT IN ('pg_catalog', 'information_schema');
1098 ";
1099
1100 const string COLUMN_SQL=@"
1101 SELECT column_name, is_nullable, udt_name, column_default
1102 FROM information_schema.columns
1103 WHERE table_name=@tableName;
1104 ";
1105
1106 }
1107
1108 class MySqlSchemaReader : SchemaReader
1109 {
1110 // SchemaReader.ReadSchema
1111 public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1112 {
1113 var result=new Tables();
1114
1115
1116 var cmd=factory.CreateCommand();
1117 cmd.Connection=connection;
1118 cmd.CommandText=TABLE_SQL;
1119
1120 //pull the tables in a reader
1121 using(cmd)
1122 {
1123 using (var rdr=cmd.ExecuteReader())
1124 {
1125 while(rdr.Read())
1126 {
1127 Table tbl=new Table();
1128 tbl.Name=rdr["TABLE_NAME"].ToString();
1129 tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
1130 tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1131 tbl.CleanName=CleanUp(tbl.Name);
1132 tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1133 result.Add(tbl);
1134 }
1135 }
1136 }
1137
1138
1139 //this will return everything for the DB
1140 var schema = connection.GetSchema("COLUMNS");
1141
1142 //loop again - but this time pull by table name
1143 foreach (var item in result)
1144 {
1145 item.Columns=new List<Column>();
1146
1147 //pull the columns from the schema
1148 var columns = schema.Select("TABLE_NAME='" + item.Name + "'");
1149 foreach (var row in columns)
1150 {
1151 Column col=new Column();
1152 col.Name=row["COLUMN_NAME"].ToString();
1153 col.PropertyName=CleanUp(col.Name);
1154 col.PropertyType=GetPropertyType(row);
1155 col.IsNullable=row["IS_NULLABLE"].ToString()=="YES";
1156 col.IsPK=row["COLUMN_KEY"].ToString()=="PRI";
1157 col.IsAutoIncrement=row["extra"].ToString().ToLower().IndexOf("auto_increment")>=0;
1158
1159 item.Columns.Add(col);
1160 }
1161 }
1162
1163 return result;
1164
1165 }
1166
1167 static string GetPropertyType(DataRow row)
1168 {
1169 bool bUnsigned = row["COLUMN_TYPE"].ToString().IndexOf("unsigned")>=0;
1170 string propType="string";
1171 switch (row["DATA_TYPE"].ToString())
1172 {
1173 case "bigint":
1174 propType= bUnsigned ? "ulong" : "long";
1175 break;
1176 case "int":
1177 propType= bUnsigned ? "uint" : "int";
1178 break;
1179 case "smallint":
1180 propType= bUnsigned ? "ushort" : "short";
1181 break;
1182 case "guid":
1183 propType= "Guid";
1184 break;
1185 case "smalldatetime":
1186 case "date":
1187 case "datetime":
1188 case "timestamp":
1189 propType= "DateTime";
1190 break;
1191 case "float":
1192 propType="float";
1193 break;
1194 case "double":
1195 propType="double";
1196 break;
1197 case "numeric":
1198 case "smallmoney":
1199 case "decimal":
1200 case "money":
1201 propType= "decimal";
1202 break;
1203 case "bit":
1204 case "bool":
1205 case "boolean":
1206 propType= "bool";
1207 break;
1208 case "tinyint":
1209 propType = bUnsigned ? "byte" : "sbyte";
1210 break;
1211 case "image":
1212 case "binary":
1213 case "blob":
1214 case "mediumblob":
1215 case "longblob":
1216 case "varbinary":
1217 propType= "byte[]";
1218 break;
1219
1220 }
1221 return propType;
1222 }
1223
1224 const string TABLE_SQL=@"
1225 SELECT *
1226 FROM information_schema.tables
1227 WHERE (table_type='BASE TABLE' OR table_type='VIEW') AND TABLE_SCHEMA=DATABASE()
1228 ";
1229
1230 }
1231
1232 class OracleSchemaReader : SchemaReader
1233 {
1234 // SchemaReader.ReadSchema
1235 public override Tables ReadSchema(DbConnection connection, DbProviderFactory factory)
1236 {
1237 var result=new Tables();
1238
1239 _connection=connection;
1240 _factory=factory;
1241
1242 var cmd=_factory.CreateCommand();
1243 cmd.Connection=connection;
1244 cmd.CommandText=TABLE_SQL;
1245 cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1246 cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1247
1248 //pull the tables in a reader
1249 using(cmd)
1250 {
1251
1252 using (var rdr=cmd.ExecuteReader())
1253 {
1254 while(rdr.Read())
1255 {
1256 Table tbl=new Table();
1257 tbl.Name=rdr["TABLE_NAME"].ToString();
1258 tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
1259 tbl.IsView=string.Compare(rdr["TABLE_TYPE"].ToString(), "View", true)==0;
1260 tbl.CleanName=CleanUp(tbl.Name);
1261 tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
1262 result.Add(tbl);
1263 }
1264 }
1265 }
1266
1267 foreach (var tbl in result)
1268 {
1269 tbl.Columns=LoadColumns(tbl);
1270
1271 // Mark the primary key
1272 string PrimaryKey=GetPK(tbl.Name);
1273 var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==PrimaryKey.ToLower().Trim());
1274 if(pkColumn!=null)
1275 pkColumn.IsPK=true;
1276 }
1277
1278
1279 return result;
1280 }
1281
1282 DbConnection _connection;
1283 DbProviderFactory _factory;
1284
1285
1286 List<Column> LoadColumns(Table tbl)
1287 {
1288
1289 using (var cmd=_factory.CreateCommand())
1290 {
1291 cmd.Connection=_connection;
1292 cmd.CommandText=COLUMN_SQL;
1293 cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1294 cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1295
1296 var p = cmd.CreateParameter();
1297 p.ParameterName = ":tableName";
1298 p.Value=tbl.Name;
1299 cmd.Parameters.Add(p);
1300
1301 var result=new List<Column>();
1302 using (IDataReader rdr=cmd.ExecuteReader())
1303 {
1304 while(rdr.Read())
1305 {
1306 Column col=new Column();
1307 col.Name=rdr["ColumnName"].ToString();
1308 col.PropertyName=CleanUp(col.Name);
1309 col.PropertyType=GetPropertyType(rdr["DataType"].ToString(), (rdr["DataScale"] == DBNull.Value ? null : rdr["DataScale"].ToString()));
1310 col.IsNullable = "YES".Equals(rdr["isnullable"].ToString()) || "Y".Equals(rdr["isnullable"].ToString());
1311 col.IsAutoIncrement=true;
1312 result.Add(col);
1313 }
1314 }
1315
1316 return result;
1317 }
1318 }
1319
1320 string GetPK(string table){
1321
1322 string sql=@"select column_name from USER_CONSTRAINTS uc
1323 inner join USER_CONS_COLUMNS ucc on uc.constraint_name = ucc.constraint_name
1324 where uc.constraint_type = 'P'
1325 and uc.table_name = upper(:tableName)
1326 and ucc.position = 1";
1327
1328 using (var cmd=_factory.CreateCommand())
1329 {
1330 cmd.Connection=_connection;
1331 cmd.CommandText=sql;
1332 cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
1333 cmd.GetType().GetProperty("InitialLONGFetchSize").SetValue(cmd, -1, null);
1334
1335 var p = cmd.CreateParameter();
1336 p.ParameterName = ":tableName";
1337 p.Value=table;
1338 cmd.Parameters.Add(p);
1339
1340 var result=cmd.ExecuteScalar();
1341
1342 if(result!=null)
1343 return result.ToString();
1344 }
1345
1346 return "";
1347 }
1348
1349 string GetPropertyType(string sqlType, string dataScale)
1350 {
1351 string sysType="string";
1352 sqlType = sqlType.ToLower();
1353 switch (sqlType)
1354 {
1355 case "bigint":
1356 sysType = "long";
1357 break;
1358 case "smallint":
1359 sysType= "short";
1360 break;
1361 case "int":
1362 sysType= "int";
1363 break;
1364 case "uniqueidentifier":
1365 sysType= "Guid";
1366 break;
1367 case "smalldatetime":
1368 case "datetime":
1369 case "date":
1370 sysType= "DateTime";
1371 break;
1372 case "float":
1373 sysType="double";
1374 break;
1375 case "real":
1376 case "numeric":
1377 case "smallmoney":
1378 case "decimal":
1379 case "money":
1380 case "number":
1381 sysType= "decimal";
1382 break;
1383 case "tinyint":
1384 sysType = "byte";
1385 break;
1386 case "bit":
1387 sysType= "bool";
1388 break;
1389 case "image":
1390 case "binary":
1391 case "varbinary":
1392 case "timestamp":
1393 sysType= "byte[]";
1394 break;
1395 }
1396
1397 if (sqlType == "number" && dataScale == "0")
1398 return "long";
1399
1400 return sysType;
1401 }
1402
1403
1404
1405 const string TABLE_SQL=@"select TABLE_NAME, 'Table' TABLE_TYPE, USER TABLE_SCHEMA
1406 from USER_TABLES
1407 union all
1408 select VIEW_NAME, 'View', USER
1409 from USER_VIEWS";
1410
1411
1412 const string COLUMN_SQL=@"select table_name TableName,
1413 column_name ColumnName,
1414 data_type DataType,
1415 data_scale DataScale,
1416 nullable IsNullable
1417 from USER_TAB_COLS utc
1418 where table_name = :tableName
1419 and virtual_column = 'NO'
1420 order by column_id";
1421
1422 }
1423
1424
1425
1426
1427 /// <summary>
1428 /// Summary for the Inflector class
1429 /// </summary>
1430 public static class Inflector {
1431 private static readonly List<InflectorRule> _plurals = new List<InflectorRule>();
1432 private static readonly List<InflectorRule> _singulars = new List<InflectorRule>();
1433 private static readonly List<string> _uncountables = new List<string>();
1434
1435 /// <summary>
1436 /// Initializes the <see cref="Inflector"/> class.
1437 /// </summary>
1438 static Inflector() {
1439 AddPluralRule("$", "s");
1440 AddPluralRule("s$", "s");
1441 AddPluralRule("(ax|test)is$", "$1es");
1442 AddPluralRule("(octop|vir)us$", "$1i");
1443 AddPluralRule("(alias|status)$", "$1es");
1444 AddPluralRule("(bu)s$", "$1ses");
1445 AddPluralRule("(buffal|tomat)o$", "$1oes");
1446 AddPluralRule("([ti])um$", "$1a");
1447 AddPluralRule("sis$", "ses");
1448 AddPluralRule("(?:([^f])fe|([lr])f)$", "$1$2ves");
1449 AddPluralRule("(hive)$", "$1s");
1450 AddPluralRule("([^aeiouy]|qu)y$", "$1ies");
1451 AddPluralRule("(x|ch|ss|sh)$", "$1es");
1452 AddPluralRule("(matr|vert|ind)ix|ex$", "$1ices");
1453 AddPluralRule("([m|l])ouse$", "$1ice");
1454 AddPluralRule("^(ox)$", "$1en");
1455 AddPluralRule("(quiz)$", "$1zes");
1456
1457 AddSingularRule("s$", String.Empty);
1458 AddSingularRule("ss$", "ss");
1459 AddSingularRule("(n)ews$", "$1ews");
1460 AddSingularRule("([ti])a$", "$1um");
1461 AddSingularRule("((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$", "$1$2sis");
1462 AddSingularRule("(^analy)ses$", "$1sis");
1463 AddSingularRule("([^f])ves$", "$1fe");
1464 AddSingularRule("(hive)s$", "$1");
1465 AddSingularRule("(tive)s$", "$1");
1466 AddSingularRule("([lr])ves$", "$1f");
1467 AddSingularRule("([^aeiouy]|qu)ies$", "$1y");
1468 AddSingularRule("(s)eries$", "$1eries");
1469 AddSingularRule("(m)ovies$", "$1ovie");
1470 AddSingularRule("(x|ch|ss|sh)es$", "$1");
1471 AddSingularRule("([m|l])ice$", "$1ouse");
1472 AddSingularRule("(bus)es$", "$1");
1473 AddSingularRule("(o)es$", "$1");
1474 AddSingularRule("(shoe)s$", "$1");
1475 AddSingularRule("(cris|ax|test)es$", "$1is");
1476 AddSingularRule("(octop|vir)i$", "$1us");
1477 AddSingularRule("(alias|status)$", "$1");
1478 AddSingularRule("(alias|status)es$", "$1");
1479 AddSingularRule("^(ox)en", "$1");
1480 AddSingularRule("(vert|ind)ices$", "$1ex");
1481 AddSingularRule("(matr)ices$", "$1ix");
1482 AddSingularRule("(quiz)zes$", "$1");
1483
1484 AddIrregularRule("person", "people");
1485 AddIrregularRule("man", "men");
1486 AddIrregularRule("child", "children");
1487 AddIrregularRule("sex", "sexes");
1488 AddIrregularRule("tax", "taxes");
1489 AddIrregularRule("move", "moves");
1490
1491 AddUnknownCountRule("equipment");
1492 AddUnknownCountRule("information");
1493 AddUnknownCountRule("rice");
1494 AddUnknownCountRule("money");
1495 AddUnknownCountRule("species");
1496 AddUnknownCountRule("series");
1497 AddUnknownCountRule("fish");
1498 AddUnknownCountRule("sheep");
1499 }
1500
1501 /// <summary>
1502 /// Adds the irregular rule.
1503 /// </summary>
1504 /// <param name="singular">The singular.</param>
1505 /// <param name="plural">The plural.</param>
1506 private static void AddIrregularRule(string singular, string plural) {
1507 AddPluralRule(String.Concat("(", singular[0], ")", singular.Substring(1), "$"), String.Concat("$1", plural.Substring(1)));
1508 AddSingularRule(String.Concat("(", plural[0], ")", plural.Substring(1), "$"), String.Concat("$1", singular.Substring(1)));
1509 }
1510
1511 /// <summary>
1512 /// Adds the unknown count rule.
1513 /// </summary>
1514 /// <param name="word">The word.</param>
1515 private static void AddUnknownCountRule(string word) {
1516 _uncountables.Add(word.ToLower());
1517 }
1518
1519 /// <summary>
1520 /// Adds the plural rule.
1521 /// </summary>
1522 /// <param name="rule">The rule.</param>
1523 /// <param name="replacement">The replacement.</param>
1524 private static void AddPluralRule(string rule, string replacement) {
1525 _plurals.Add(new InflectorRule(rule, replacement));
1526 }
1527
1528 /// <summary>
1529 /// Adds the singular rule.
1530 /// </summary>
1531 /// <param name="rule">The rule.</param>
1532 /// <param name="replacement">The replacement.</param>
1533 private static void AddSingularRule(string rule, string replacement) {
1534 _singulars.Add(new InflectorRule(rule, replacement));
1535 }
1536
1537 /// <summary>
1538 /// Makes the plural.
1539 /// </summary>
1540 /// <param name="word">The word.</param>
1541 /// <returns></returns>
1542 public static string MakePlural(string word) {
1543 return ApplyRules(_plurals, word);
1544 }
1545
1546 /// <summary>
1547 /// Makes the singular.
1548 /// </summary>
1549 /// <param name="word">The word.</param>
1550 /// <returns></returns>
1551 public static string MakeSingular(string word) {
1552 return ApplyRules(_singulars, word);
1553 }
1554
1555 /// <summary>
1556 /// Applies the rules.
1557 /// </summary>
1558 /// <param name="rules">The rules.</param>
1559 /// <param name="word">The word.</param>
1560 /// <returns></returns>
1561 private static string ApplyRules(IList<InflectorRule> rules, string word) {
1562 string result = word;
1563 if (!_uncountables.Contains(word.ToLower())) {
1564 for (int i = rules.Count - 1; i >= 0; i--) {
1565 string currentPass = rules[i].Apply(word);
1566 if (currentPass != null) {
1567 result = currentPass;
1568 break;
1569 }
1570 }
1571 }
1572 return result;
1573 }
1574
1575 /// <summary>
1576 /// Converts the string to title case.
1577 /// </summary>
1578 /// <param name="word">The word.</param>
1579 /// <returns></returns>
1580 public static string ToTitleCase(string word) {
1581 return Regex.Replace(ToHumanCase(AddUnderscores(word)), @"\b([a-z])",
1582 delegate(Match match) { return match.Captures[0].Value.ToUpper(); });
1583 }
1584
1585 /// <summary>
1586 /// Converts the string to human case.
1587 /// </summary>
1588 /// <param name="lowercaseAndUnderscoredWord">The lowercase and underscored word.</param>
1589 /// <returns></returns>
1590 public static string ToHumanCase(string lowercaseAndUnderscoredWord) {
1591 return MakeInitialCaps(Regex.Replace(lowercaseAndUnderscoredWord, @"_", " "));
1592 }
1593
1594
1595 /// <summary>
1596 /// Adds the underscores.
1597 /// </summary>
1598 /// <param name="pascalCasedWord">The pascal cased word.</param>
1599 /// <returns></returns>
1600 public static string AddUnderscores(string pascalCasedWord) {
1601 return Regex.Replace(Regex.Replace(Regex.Replace(pascalCasedWord, @"([A-Z]+)([A-Z][a-z])", "$1_$2"), @"([a-z\d])([A-Z])", "$1_$2"), @"[-\s]", "_").ToLower();
1602 }
1603
1604 /// <summary>
1605 /// Makes the initial caps.
1606 /// </summary>
1607 /// <param name="word">The word.</param>
1608 /// <returns></returns>
1609 public static string MakeInitialCaps(string word) {
1610 return String.Concat(word.Substring(0, 1).ToUpper(), word.Substring(1).ToLower());
1611 }
1612
1613 /// <summary>
1614 /// Makes the initial lower case.
1615 /// </summary>
1616 /// <param name="word">The word.</param>
1617 /// <returns></returns>
1618 public static string MakeInitialLowerCase(string word) {
1619 return String.Concat(word.Substring(0, 1).ToLower(), word.Substring(1));
1620 }
1621
1622
1623 /// <summary>
1624 /// Determine whether the passed string is numeric, by attempting to parse it to a double
1625 /// </summary>
1626 /// <param name="str">The string to evaluated for numeric conversion</param>
1627 /// <returns>
1628 /// <c>true</c> if the string can be converted to a number; otherwise, <c>false</c>.
1629 /// </returns>
1630 public static bool IsStringNumeric(string str) {
1631 double result;
1632 return (double.TryParse(str, NumberStyles.Float, NumberFormatInfo.CurrentInfo, out result));
1633 }
1634
1635 /// <summary>
1636 /// Adds the ordinal suffix.
1637 /// </summary>
1638 /// <param name="number">The number.</param>
1639 /// <returns></returns>
1640 public static string AddOrdinalSuffix(string number) {
1641 if (IsStringNumeric(number)) {
1642 int n = int.Parse(number);
1643 int nMod100 = n % 100;
1644
1645 if (nMod100 >= 11 && nMod100 <= 13)
1646 return String.Concat(number, "th");
1647
1648 switch (n % 10) {
1649 case 1:
1650 return String.Concat(number, "st");
1651 case 2:
1652 return String.Concat(number, "nd");
1653 case 3:
1654 return String.Concat(number, "rd");
1655 default:
1656 return String.Concat(number, "th");
1657 }
1658 }
1659 return number;
1660 }
1661
1662 /// <summary>
1663 /// Converts the underscores to dashes.
1664 /// </summary>
1665 /// <param name="underscoredWord">The underscored word.</param>
1666 /// <returns></returns>
1667 public static string ConvertUnderscoresToDashes(string underscoredWord) {
1668 return underscoredWord.Replace('_', '-');
1669 }
1670
1671
1672 #region Nested type: InflectorRule
1673
1674 /// <summary>
1675 /// Summary for the InflectorRule class
1676 /// </summary>
1677 private class InflectorRule {
1678 /// <summary>
1679 ///
1680 /// </summary>
1681 public readonly Regex regex;
1682
1683 /// <summary>
1684 ///
1685 /// </summary>
1686 public readonly string replacement;
1687
1688 /// <summary>
1689 /// Initializes a new instance of the <see cref="InflectorRule"/> class.
1690 /// </summary>
1691 /// <param name="regexPattern">The regex pattern.</param>
1692 /// <param name="replacementText">The replacement text.</param>
1693 public InflectorRule(string regexPattern, string replacementText) {
1694 regex = new Regex(regexPattern, RegexOptions.IgnoreCase);
1695 replacement = replacementText;
1696 }
1697
1698 /// <summary>
1699 /// Applies the specified word.
1700 /// </summary>
1701 /// <param name="word">The word.</param>
1702 /// <returns></returns>
1703 public string Apply(string word) {
1704 if (!regex.IsMatch(word))
1705 return null;
1706
1707 string replace = regex.Replace(word, replacement);
1708 if (word == word.ToUpper())
1709 replace = replace.ToUpper();
1710
1711 return replace;
1712 }
1713 }
1714
1715 #endregion
1716 }
1717
1718 #>
PetaPoco.Core.ttinclude
至此已經從數據庫獲取到了注釋，下面需要將注釋插入到T4模板中
修改PetaPoco.Generator.ttinclude
141行原始：
...
<# if (GeneratePocos) { #>
<#
foreach(Table tbl in from t in tables where !t.Ignore select t)
{
#>
<# if (string.IsNullOrEmpty(tbl.Schema)) { #>
[TableName("<#=tbl.Name#>")]
...
添加表的Description注釋：
...
<# if (GeneratePocos) { #>
<#
foreach(Table tbl in from t in tables where !t.Ignore select t)
{
#>
/// <summary>
/// <#=tbl.Description??""#>
/// </summary>
<# if (string.IsNullOrEmpty(tbl.Schema)) { #>
[TableName("<#=tbl.Name#>")]
...
167行原始：
...
public partial class <#=tbl.ClassName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tbl.ClassName#>> <# } #>
{
<#
foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{
// Column bindings
#>
<# if (TrackModifiedColumns) { #>
...
添加列的Description注釋：
...
public partial class <#=tbl.ClassName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tbl.ClassName#>> <# } #>
{
<#
foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{
// Column bindings
#>
/// <summary>
/// <#=col.Description??""#>
/// </summary>
<# if (TrackModifiedColumns) { #>
...
這樣就改完了，打開database.tt按ctrl+s就能更新獲取到的注釋了
另外GetInstance居然不是單例方法，這簡直沒法忍，果斷改掉：
PetaPoco.Generator.ttinclude，38行修改為：
...
public static <#=RepoName#> GetInstance()
{
if (_instance!=null)
return _instance;
if (Factory!=null)
return Factory.GetInstance();
//else
// return new <#=RepoName#>();
return _instance = new <#=RepoName#>();
}
...
