Posted By

lookitstony on 08/09/12

Tagged

Versions (?)

Last Edited at 08/09/12 04:27am

Statistics

Viewed 137 times

Favorited by 0 user(s)

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");

Highlighted TextExpand

<#+	
	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;}
	}
	
#>

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.