Revision: 58905
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at August 9, 2012 04:27 by lookitstony
Initial Code
<#+ 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) { DatabaseInfo dbInfo = new DatabaseInfo(); dbInfo.Server = server; dbInfo.Name = database; var connectionString = dbInfo.ConnectionString; // ONLY GENERATE THESE CLASSES var includeTables = new List<string>(); // TABLES TO EXCLUDE FROM GENERATION var excludedTables = new List<string>() {"sysdiagrams"}; DataTable dt; // GENERATE TABLE TYPE LIST var tableNames = new List<string>(); var tables = new List<TableInfo>(); using (var conn = new SqlConnection(connectionString)) { var restrictions = new string[4]; 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 columns = new List<ColumnInfo>(); using (var conn = new SqlConnection(connectionString)) { var cmd = conn.CreateCommand(); cmd.CommandText = "select top 1 * from " + tableName + " where 1 = 2"; conn.Open(); var da = new SqlDataAdapter(cmd); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; var ds = new DataSet(); da.Fill(ds, tableName); dt = ds.Tables[0]; foreach (DataColumn col in dt.Columns) { var column = new ColumnInfo { 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); } } var table = new TableInfo { 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;} } #>
Initial URL
Initial Description
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");
Initial Title
SchemaCollector
Initial Tags
Initial Language
C#