Posted By

lookitstony on 08/09/12


Tagged

T4 T4Include


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

danfinrud55


SchemaCollector


 / Published in: C#
 

Use this to gather database schema for creating templates that generate SPROCS and models. Save as SchemaCollector.ttinclude.

Use: DatabaseInfo dbInfo = DataCollector.SchemaCollector("SERVER","DATABASE");

  1. <#+
  2. public class DataCollector
  3. {
  4. // Note: You can call this method from the outside of the template, please add parameters as needed.
  5. // To include this template just drag this file on another editor
  6. // then call this.TableCollector()
  7. public static DatabaseInfo SchemaCollector(string server, string database)
  8. {
  9.  
  10. DatabaseInfo dbInfo = new DatabaseInfo();
  11. dbInfo.Server = server;
  12. dbInfo.Name = database;
  13.  
  14. var connectionString = dbInfo.ConnectionString;
  15.  
  16. // ONLY GENERATE THESE CLASSES
  17. var includeTables = new List<string>();
  18.  
  19. // TABLES TO EXCLUDE FROM GENERATION
  20. var excludedTables = new List<string>() {"sysdiagrams"};
  21.  
  22. DataTable dt;
  23.  
  24. // GENERATE TABLE TYPE LIST
  25. var tableNames = new List<string>();
  26. var tables = new List<TableInfo>();
  27.  
  28. using (var conn = new SqlConnection(connectionString))
  29. {
  30. var restrictions = new string[4];
  31.  
  32. restrictions[0] = database; // Catalog
  33. restrictions[1] = "dbo"; // Owner
  34. restrictions[2] = null; // Table - We want all, so null
  35. restrictions[3] = "BASE TABLE"; // Table Type - Only tables and not views
  36.  
  37. conn.Open();
  38. var schema = conn.GetSchema("Tables", restrictions);
  39.  
  40. if (includeTables.Count > 0)
  41. {
  42. foreach (DataRow tbl in schema.Rows)
  43. {
  44. var tableName = tbl[2].ToString();
  45. if (includeTables.Contains(tableName))
  46. {
  47. tableNames.Add(tableName);
  48. }
  49. }
  50. }
  51. else
  52. {
  53. foreach (DataRow tbl in schema.Rows)
  54. {
  55. var tableName = tbl[2].ToString();
  56. if (excludedTables.Contains(tableName)) continue;
  57. tableNames.Add(tableName);
  58. }
  59. }
  60. }
  61.  
  62. foreach (var tableName in tableNames)
  63. {
  64. var columns = new List<ColumnInfo>();
  65.  
  66. using (var conn = new SqlConnection(connectionString))
  67. {
  68. var cmd = conn.CreateCommand();
  69. cmd.CommandText = "select top 1 * from " + tableName + " where 1 = 2";
  70.  
  71. conn.Open();
  72.  
  73. var da = new SqlDataAdapter(cmd);
  74. da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
  75.  
  76. var ds = new DataSet();
  77. da.Fill(ds, tableName);
  78. dt = ds.Tables[0];
  79.  
  80. foreach (DataColumn col in dt.Columns)
  81. {
  82. var column = new ColumnInfo
  83. {
  84. Name = col.ColumnName,
  85. PrimaryKey = dt.PrimaryKey.Contains(col),
  86. Type = GetColumnType(col.DataType.Name),
  87. IsNullable = col.AllowDBNull,
  88. Width = col.MaxLength,
  89. AutoIncrement = col.AutoIncrement,
  90. Order = col.Ordinal
  91. };
  92.  
  93. columns.Add(column);
  94. }
  95. }
  96.  
  97. var table = new TableInfo
  98. {
  99. Name = tableName,
  100. Columns = columns
  101. };
  102.  
  103. tables.Add(table);
  104. }
  105. dbInfo.Tables = tables;
  106. return dbInfo;
  107. }
  108.  
  109. private static string GetColumnType(string dataTypeName)
  110. {
  111. string dataType;
  112. switch (dataTypeName)
  113. {
  114. case "BigInt":
  115. dataType = "Int64";
  116. break;
  117. case "SmallInt":
  118. dataType = "Int16";
  119. break;
  120. case "Int32":
  121. dataType = "int";
  122. break;
  123. case "TinyInt":
  124. dataType = "byte";
  125. break;
  126. case "VarBinary":
  127. case "Binary":
  128. case "Timestamp":
  129. dataType = "byte[]";
  130. break;
  131. case "Char":
  132. dataType = "char[]";
  133. break;
  134. case "Real":
  135. dataType = "Single";
  136. break;
  137. case "Date":
  138. case "DateTime":
  139. case "DateTime2":
  140. dataType = "DateTime";
  141. break;
  142. case "DateTimeOffset":
  143. dataType = "DateTimeOffset";
  144. break;
  145. case "Decimal":
  146. case "Money":
  147. case "SmallMoney":
  148. dataType = "decimal";
  149. break;
  150. case "String":
  151. case "NChar":
  152. case "NVarChar":
  153. case "Text":
  154. case "NText":
  155. dataType = "string";
  156. break;
  157. case "Double":
  158. dataType = "double";
  159. break;
  160. case "Boolean":
  161. dataType = "bool";
  162. break;
  163. case "Variant":
  164. dataType = "object";
  165. break;
  166. case "Xml":
  167. dataType = "Xml";
  168. break;
  169. case "UniqueIdentifier":
  170. dataType = "Guid";
  171. break;
  172. default:
  173. dataType = "object";
  174. //throw new ApplicationException("Invalid field type");
  175. break;
  176. }
  177. return dataType;
  178. }
  179. }
  180. public class DatabaseInfo
  181. {
  182. public string Name {get;set;}
  183. public string Server {get;set;}
  184. public string ConnectionString {get{ return string.Format( @"Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", Server,Name); }}
  185. public List<TableInfo> Tables {get;set;}
  186. }
  187.  
  188. public class TableInfo
  189. {
  190. public string Name {get;set;}
  191. public List<ColumnInfo> Columns {get;set;}
  192. }
  193.  
  194. public class ColumnInfo
  195. {
  196. public int Order { get; set; }
  197. public string Name {get;set;}
  198. public bool PrimaryKey {get;set;}
  199. public bool AutoIncrement {get;set;}
  200. public string Type {get;set;}
  201. public bool IsNullable {get;set;}
  202. public int Width {get;set;}
  203. }
  204.  
  205. #>

Report this snippet  

You need to login to post a comment.