How to access columns and data of a Sql parameter of SqlDbType as Structured in C#?

Question

In C#, for Table-valued parameter I add a SqlParameter with 'SqlDbType' as 'Structured' and 'Value' as a C# DataTable. I want to extract this data later in my code.

I want to verify if the SqlDbType/DbType is 'Structured'.
If yes, and if the 'Value' is a 'DataTable', I want fetch the columnNames of its Columns and the data in the DataRows.

Below is the code for SqlParameter.

DataTable memoIdDt = new DataTable();
SqlParameter param = new SqlParameter ("memos", SqlDbType.Structured) { Value = memoIdDt, TypeName = "Table_Type_In_DB" };

Later I want to do something like the below (this is not the exact code).

//I am not able to use param.SqlDbType. I can use the param.DbType property.
//But it returns Object. So, not able to get the if clause right.
If(param.DbType == SqlDbType.Structued)
{
    //foreach column in param.Value.Columns, print columnNames
    //foreach DataRow in param.Value, print the array
}

Please help if you know how this can be achieved.

Does param.DbType == SqlDbType.Structured work? If it does, the first part of your question is already answered, but you have to test it to see if it does. — Robert Harvey
– Robert Harvey, Commented May 17, 2021 at 14:47
@RobertHarvey This doesn't work. That was just a pseudo code, I have found one workaround to fetch the value, then type, then name, but can't get to use the DbType or SqlDbType. — Sini
– Sini, Commented May 17, 2021 at 15:29

umberto-petrov · Accepted Answer · 2021-05-17 14:51:59Z

0

I think you can simply cast param.Value back to a DataTable:

if (param.SqlDbType == SqlDbType.Structured)
{
    var table = param.Value as DataTable;

    foreach (DataColumn column in table.Columns) Console.WriteLine(column.ColumnName);
    foreach (DataRow row in table.Rows) Console.WriteLine(row.ItemArray.Length);
}

answered May 17, 2021 at 14:51

umberto-petrov

7434 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sini Over a year ago

Thanks, this works, only concern is the if clause validation. I can't use SqlDbType. I can use the DbType property, but it returns Object.

pinkfloydx33 Over a year ago

Why can't you use the SqlDbType property? If it's because you have something with a static type of IDbParameter or DbParameter then just cast that to SqlParameter first which is almost certain to be a valid cast if you are creating it originally as said type

umberto-petrov Over a year ago

@Sini why can't you use SqlDbType? If you define param as SqlParameter (as in your code snippet), then it should be available for you?

Collectives™ on Stack Overflow

How to access columns and data of a Sql parameter of SqlDbType as Structured in C#?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related