public IList<TableTypeColumn> GetTableTypeColumns(TableType tableType) { var retValue = new List<TableTypeColumn>(); using (var conn = new SqlConnection(_designerConnectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_executesql"; const string stmt = "SELECT " + "[c].[name] AS ColumnName, " + "SCHEMA_NAME(ISNULL([st].[schema_id], [ut].[schema_id])) AS SchemaName, " + "ISNULL([st].[name], [ut].[name]) AS DataTypeName " + "FROM [sys].[table_types] tt " + "INNER JOIN [sys].[columns] c " + "ON [c].[object_id] = [tt].[type_table_object_id] " + "INNER JOIN [sys].[types] ut " + "ON [ut].[user_type_id] = [c].[user_type_id] " + "LEFT OUTER JOIN [sys].[types] st " + "ON [st].[user_type_id] = [c].[system_type_id]" + "WHERE " + "[tt].[schema_id] = SCHEMA_ID(@SchemaName) " + "AND [tt].[name] = @ObjectName;"; cmd.Parameters.AddWithValue("@stmt", stmt); cmd.Parameters.AddWithValue("@params", "@SchemaName sysname, @ObjectName sysname"); cmd.Parameters.AddWithValue("@SchemaName", tableType.SchemaName); cmd.Parameters.AddWithValue("@ObjectName", tableType.Name); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var schemaName = reader.GetSqlString(1).Value; var sqlTypeName = reader.GetSqlString(2).Value; var clrTypeName = schemaName == "sys" ? Tools.ClrTypeName(sqlTypeName) : "System.Object"; retValue.Add(new TableTypeColumn(reader.GetSqlString(0).Value, clrTypeName)); } } } } return retValue; }
private string GetCodeForTableTypeColumns(TableType tableType, IList<TableTypeColumn> columns) { var code = string.Join("\r", columns.Select( column => $"public global::{column.ClrTypeName} {Tools.ValidIdentifier(column.ColumnName)} {{ get; private set; }}\r")); code += String.Format("public {0}Row({1}) {{{2}}}\r", Tools.ValidIdentifier(tableType.Name), string.Join(", ", columns.Select( column => $"global::{column.ClrTypeName} {column.ColumnName.Substring(0, 1).ToLower() + column.ColumnName.Substring(1)}")), string.Join("\r", columns.Select( column => $"this.{column.ColumnName} = {column.ColumnName.Substring(0, 1).ToLower() + column.ColumnName.Substring(1)};"))); return code; }