C# auto generate SQL "user-defined table type"

Question

I have a question in regards to stored procedures taking a user defined table type as parameter. I know that I would just need to create a DataTable in c# code corresponding to the user defined table type in the SQL DB. Just like here How to pass User Defined Table Type as Stored Procedured parameter in C#

However, what I would like to avoid is creating the DataTable manually in the code, but instead creating the DataTable automatically. Is it somehow possible to get this from the database through a query ?

If this isn't possible then another possibility is to get the definition of the user defined table type and then use this to automatically generate the DataTable. But then the question is how do I get the definition of the type ?

Anybody got any solution to this problem, all examples I have found is generating the user-defined data type manually as DataTable in the code.

score 5 · Accepted Answer · answered Aug 18 '16 at 14:41

5

If you know the name of the table type, you should be able to execute the following piece of SQL:

declare @a dbo.TT
select * from @a

(Where dbo.TT is the name of the table type)

This will generate an empty result set with all of the appropriate schema information (column names and types). If you use that with a DataAdapter to populate a DataTable, you should be all set.

answered Aug 18 '16 at 14:41

Damien_The_Unbeliever

234,701
27
340
448

Thanks, just what I'm looking for as there are multiple types with different number of columns. which it handles automatically. – dennis_ler Aug 19 '16 at 06:55

Charles Bretana · Answer 2 · 2016-08-18T14:53:47.127

Try this (This is useful only for sending a stored proc a dataset of one column of values.)

public void AddSQLTableParm<T>(string parmName, 
        IEnumerable<T> values, 
        string typeName = "dbo.keyIds")  // <== here put SQL Server UDT Type neame
    {
        var parm = new SqlParameter(parmName, 
                          DbParamList.CreateDataTable(values))
        {
            SqlDbType = SqlDbType.Structured,
            TypeName = typeName
        };
        Add(parmName, parm);
    }

If you want a multi-column dataset (a multi-Column UDT in SQL Server) you will have to extend this, the signature would become:

for three column dataset:

public void AddSQLTableParm<T1, T2, T3>(
        string parmName, string typeName = "dbo.keyIds",
        IEnumerable<T1> value1s, 
        IEnumerable<T1> value2s, 
        IEnumerable<T1> value3s)
{ .... }

dbParamList defined :

public class DbParamList : List<IDbDataParameter>
{
    private DbParamList() {}
    public static DbParamList Make(IEnumerable<SqlParameter> parms)
    {
        var prmLst = new DbParamList();
        prmLst.AddRange(parms);
        return prmLst;
    }

    public static DbParamList Make(params SqlParameter[] parms)
    {
        var prmLst = new DbParamList();
        prmLst.AddRange(parms);
        return prmLst;
    }

    public void AddSQLParm(string parmName, bool value)
    { Add(new SqlParameter(parmName, value ? "1" : "0")); }

    public void AddSQLParm(string parmName, bool? value)
    {
        if (!value.HasValue)
        {
            throw new ArgumentNullException(
                "Null value passed to AddSQLParm<>()");
        }
        Add(new SqlParameter(parmName, value.Value ? "1" : "0"));
    } 

    public void AddSQLParm<T>(string parmName, T value)
    {
        var type = typeof(T);
        if (type.IsEnum) Add(new SqlParameter(parmName, 
            Convert.ChangeType(value, Enum.GetUnderlyingType(type))));

        else Add(new SqlParameter(parmName, value));
    } 

    public void AddSQLParm<T>(string parmName, T? value,
        bool ignoreNull = false) where T : struct
    {
        var type = typeof(T);

        if (!value.HasValue)
        {
            if (ignoreNull) return;
            throw new ArgumentNullException(
                "Null value passed to AddSQLParm<>()");
        }
        // ---------------------------------------

        if (type.IsEnum) Add(new SqlParameter(parmName, 
            Convert.ChangeType(value.Value, Enum.GetUnderlyingType(type))));
        else Add(new SqlParameter(parmName, value.Value));
    }

    public void AddSQLTableParm<T>(string parmName, IEnumerable<T> values)
    {
        var parm = new SqlParameter(parmName, CreateDataTable(values))
        {
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.keyIds"
        };
        Add(parm);
    }

    internal static DataTable CreateDataTable<T>(IEnumerable<T> values)
    {
        var dt = new DataTable();
        var props = typeof (T).GetProperties();
        if (props.Length > 0)
        {
            foreach (var col in props)
                dt.Columns.Add(col.Name, col.PropertyType);
            foreach (var id in values)
            {
                var newRow = dt.NewRow();
                foreach (var prop in id.GetType().GetProperties())
                    newRow[prop.Name] = prop.GetValue(id, null);
                dt.Rows.Add(newRow);
            }
        }
        else
        {
            dt.Columns.Add("ids");
            foreach (var id in values) dt.Rows.Add(id);
        }
        return dt;
    }

}

Just had quick look at above, looks like a usable solution. For this to be usable the values needs to have the correct type, correct ? — dennis_ler, Aug 18 '16 at 14:47
right, that's why its a generic, the type parameter T is how you tell it the type. This is useful only for sending a stored proc a dataset of one column of values, however. If you want a multi-column dataset (a multi-Column UDT in SQL Server) you will have to extend this. — Charles Bretana, Aug 18 '16 at 14:49

C# auto generate SQL "user-defined table type"

2 Answers2