/ 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");
Use: DatabaseInfo dbInfo = DataCollector.SchemaCollector("SERVER","DATABASE");
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
<#+ public class DataCollector { // Note: You can call this method from the outside of the template, please add parameters as needed. // To include this template just drag this file on another editor // then call this.TableCollector() public static DatabaseInfo SchemaCollector(string server, string database) { dbInfo.Server = server; dbInfo.Name = database; var connectionString = dbInfo.ConnectionString; // ONLY GENERATE THESE CLASSES // TABLES TO EXCLUDE FROM GENERATION DataTable dt; // GENERATE TABLE TYPE LIST { restrictions[0] = database; // Catalog restrictions[1] = "dbo"; // Owner restrictions[2] = null; // Table - We want all, so null restrictions[3] = "BASE TABLE"; // Table Type - Only tables and not views conn.Open(); var schema = conn.GetSchema("Tables", restrictions); if (includeTables.Count > 0) { foreach (DataRow tbl in schema.Rows) { var tableName = tbl[2].ToString(); if (includeTables.Contains(tableName)) { tableNames.Add(tableName); } } } else { foreach (DataRow tbl in schema.Rows) { var tableName = tbl[2].ToString(); if (excludedTables.Contains(tableName)) continue; tableNames.Add(tableName); } } } foreach (var tableName in tableNames) { { var cmd = conn.CreateCommand(); cmd.CommandText = "select top 1 * from " + tableName + " where 1 = 2"; conn.Open(); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.Fill(ds, tableName); dt = ds.Tables[0]; foreach (DataColumn col in dt.Columns) { { Name = col.ColumnName, PrimaryKey = dt.PrimaryKey.Contains(col), Type = GetColumnType(col.DataType.Name), IsNullable = col.AllowDBNull, Width = col.MaxLength, AutoIncrement = col.AutoIncrement, Order = col.Ordinal }; columns.Add(column); } } { Name = tableName, Columns = columns }; tables.Add(table); } dbInfo.Tables = tables; return dbInfo; } private static string GetColumnType(string dataTypeName) { string dataType; switch (dataTypeName) { case "BigInt": dataType = "Int64"; break; case "SmallInt": dataType = "Int16"; break; case "Int32": dataType = "int"; break; case "TinyInt": dataType = "byte"; break; case "VarBinary": case "Binary": case "Timestamp": dataType = "byte[]"; break; case "Char": dataType = "char[]"; break; case "Real": dataType = "Single"; break; case "Date": case "DateTime": case "DateTime2": dataType = "DateTime"; break; case "DateTimeOffset": dataType = "DateTimeOffset"; break; case "Decimal": case "Money": case "SmallMoney": dataType = "decimal"; break; case "String": case "NChar": case "NVarChar": case "Text": case "NText": dataType = "string"; break; case "Double": dataType = "double"; break; case "Boolean": dataType = "bool"; break; case "Variant": dataType = "object"; break; case "Xml": dataType = "Xml"; break; case "UniqueIdentifier": dataType = "Guid"; break; default: dataType = "object"; //throw new ApplicationException("Invalid field type"); break; } return dataType; } } public class DatabaseInfo { public string Name {get;set;} public string Server {get;set;} public string ConnectionString {get{ return string.Format( @"Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", Server,Name); }} public List<TableInfo> Tables {get;set;} } public class TableInfo { public string Name {get;set;} public List<ColumnInfo> Columns {get;set;} } public class ColumnInfo { public int Order { get; set; } public string Name {get;set;} public bool PrimaryKey {get;set;} public bool AutoIncrement {get;set;} public string Type {get;set;} public bool IsNullable {get;set;} public int Width {get;set;} } #>