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