Getting error for HierarchyId while fetching the data from another server and inserting it into my server c#

Question

Error is

The type of column 'MemberHId' is not supported. The type is 'SqlHierarchyId'.

In both the server the datatype is same "HierarchyId".

I am just using the ADO.Net.

The assembly is Microsoft.SqlServer.ManagedDTS version- 13.0.0.0 We are using Azure Sql.

Tried with different versions of sql assemblies

 DataTable dt = new DataTable();
 
 dt.Columns.Add("Code", typeof(string));
 dt.Columns.Add("Description", typeof(string));
 dt.Columns.Add("NodeId", typeof(int));
 dt.Columns.Add("MemberHId", typeof(SqlHierarchyId));
 dt.Columns.Add("Level", typeof(int));
                   
//Getting the data from serverAPI which is returning the data in the columns without null records.
 dt=apicall();

 SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
 returnParameter.Direction = ParameterDirection.ReturnValue;
                
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = spName;
 cmd.Parameters.AddWithValue("@MemberH", dt);

 cmd.CommandTimeout = 0;
 cmd.Connection.Open();
 cmd.ExecuteNonQuery();// Its failing here

My Sp consists of table type param @MemberHType and the procedure returns rows with these columns:

    Name        | Type
    ------------+----------
    Code        | varchar
    Description | varchar
    NodeID      | smallint
    MemberHId   | hierarchyid
    Level       | smallint

This is the procedure:

    alter PROCEDURE [dbo].[InsertHierarchyData]  
    (  
      @MemberHType MemberH READONLY  
    )  
    AS  
    
    truncate table test
    BEGIN TRY   
     
       insert into test values ('start select')
      
     --create table test (errormessage varchar(1000))
    
     IF EXISTS (SELECT TOP 1 1 FROM @MemberHType)  
     BEGIN  
    
     insert into test values ('Inside first Insert start')
      INSERT INTO HierarchyStaging  
      (  
       Code  
       ,[Description]  
       ,NodeID  
       ,MemberhHId  
       ,[Level]  
      )  
      SELECT    
        Code  
        ,[Description]  
        ,NodeId  
        ,MemberhHId  
        ,[Level]  
      FROM @MemberHType  
      insert into test values ('Inside first Insert end')
     END  
     END TRY    
    BEGIN CATCH    
     SELECT   @comment = ERROR_MESSAGE()    
       ,@Status = 'Error'    
     SET   @comment = CONVERT(NVARCHAR(3000),@Comment) + ' Error Severity: ' + CAST(ERROR_SEVERITY() AS varchar(25)) + ' Error state: 30'    
     GOTO ErrorHandler    
    END CATCH    
    The Column in c# is SqlHierarchyId.

Stack-trace:

at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

Answer 1

If I understand the documentation correctly, you can't use SqlHierarchyId as the data type of a DataColumn.

It doesn't appear in the System.Data.SqlTypes Namespace, and it also doesn't appear in the SQL Server Data Type Mappings table, which means there's no documented built in conversion between SQL Server's HierarchyId and a built in type in the .Net framework. (of course, undocumented features have been known to exist in SQL Server, but I wouldn't recommend relying on that).

What you can do, however, is to convert the HierarchyId to nvarchar using a simple cast in T-SQL when you read it into your c# application (which internally is calling the ToString() method):

CAST(MemberhHId  AS nvarchar(4000)) as MemberHId

and convert it back using cast when you insert the data (which internally is calling the Parse() method):

CAST(MemberHId AS hierarchyid)