none
ADO.NET SqlCommand.ExecuteReader() with TVP returns 0 rows if tvp is IEnumerable<SqlDataRecord> but returns 66 if tvp is DataTable RRS feed

  • Question

  • Hi.  I'll try to make this quick: I'm calling a stored procedure via an ADO.Net SqlCommand.  The stored procedure requires a table valued parameter, and when I call command.ExecuteReader() the reader contains no rows.  When I convert my logic to a SQL Script and execute directly in SSMS, I receive data.  

    The table type is defined similar to the following:

    CREATE TYPE [dbo].[BusinessTableType] AS TABLE(
    	[id] [int] NULL,
    	[Code] [varchar](1000) NULL,
    	[ServiceType] [varchar](50) NULL,
    	[AccountNumber] [varchar](50) NULL
    )
    GO

    The Sql script that returns looks something like the following:

    DECLARE @Accounts as dbo.BusinessTableType
    insert into @Accounts(id,Code,ServiceType,AccountNumber)
    select 3,'Code','Service','1234567890'
    
    exec MyDatabase.MySchema.GetData @Accounts

    I have mocked out a single row for testing.  This execution returns 66 rows.

    ...now, the offending code (only the relevant parts)

    var request= GetRequest(connection2);
    var data = GetData(connection, connection2, request);

    private RequestList GetRequest(SqlConnection connection) { //DataTable accounts = GetAccounts(connection); DataTable accounts = GetAccountMock(); return GetRequest(connection, accounts); } private DataTable GetAccountMock() { DataTable mock = new DataTable("Accounts"); mock.Columns.Add("Id"); mock.Columns.Add("Code"); mock.Columns.Add("AccountNumber"); mock.Columns.Add("ServiceType"); mock.Rows.Add( 3 , "Code" , "1234567890" , "Service" ); return mock; }

    private AccountRequestList GetRequest(SqlConnection connection, DataTable accounts)
            {
                AccountRequestList request = new AccountRequestList();
    
                foreach (DataRow row in accounts.Rows)
                {
                    if ((row["Code"] != null && !row["Code"].Equals(String.Empty)) && (row["AccountNumber"] != null && !row["AccountNumber"].Equals(String.Empty)))
                        request.Add(new AccountRequest()
                            {
                                 Code = row["Code"].ToString()
                                ,AccountNumber = row["AccountNumber"].ToString()
                                ,ServiceType = row["Service"].ToString()
                                ,Id = string.IsNullOrEmpty(row["Id"].ToString()) ? (int?)null : Convert.ToInt32(row["Id"].ToString())
                            }
                      );
                }
                return request;
            }


    public class AccountRequestList: List<AccountRequest>, IEnumerable<SqlDataRecord>
        {
            IEnumerator<SqlDataRecord> IEnumerable<SqlDataRecord>.GetEnumerator()
            {
                AccountRequestToSqlDataRecordConverter converter = new AccountRequestToSqlDataRecordConverter();
                foreach (AccountRequest accountRequest in this)
                {
                    yield return converter.Convert(accountRequest, typeof(SqlDataRecord)) as SqlDataRecord;
                }
            }
            
        }

    public class AccountRequestToSqlDataRecordConverter
        {
            public object Convert(object value, Type targetType)
            {
                if (value.GetType() != typeof(AccountRequest)) return null;
                if (targetType != typeof(SqlDataRecord)) return null;
                AccountRequest request = value as AccountRequest;
                
                SqlDataRecord record = new SqlDataRecord(
                                                            new SqlMetaData("Id",SqlDbType.Int),
                                                            new SqlMetaData("Code",SqlDbType.VarChar,1000),
                                                            new SqlMetaData("AccountNumber",SqlDbType.VarChar,50),
                                                            new SqlMetaData("ServiceType",SqlDbType.VarChar,50)
                                                                       
                                                     );
                record.SetSqlInt32(0, request.Id.HasValue ? request.Id.Value : SqlInt32.Null); 
                record.SetString(1, request.Code);
                record.SetString(2, request.AccountNumber);
                record.SetString(3, request.ServiceType);
                return record;
            }
        }

    private DataTable GetData(SqlConnection connection1, SqlConnection connection2, AccountRequestList request)
            {
                try
                {
                    SqlDataReader reader;
                    int lastUsedId;
                    lastUsedId = GetLastUsedId(connection2);
                    
                    SqlCommand command = new SqlCommand("[schema].[GetData]", connection1) { CommandType = CommandType.StoredProcedure };
                    if (connection1.State != ConnectionState.Open)
                    {
                        connection1.Open();
                    }
                    if (Dts.Variables["DatabaseTimeout"].Value is int)
                    {
                        command.CommandTimeout = (int)Dts.Variables["DatabaseTimeout"].Value;
                    }
    
                    SqlParameter minId = command.Parameters.AddWithValue("@MinId", lastUsedId);
                    minUsage.SqlDbType = SqlDbType.Int;
    
                    SqlParameter maxId = command.Parameters.AddWithValue("@MaxUsedId", Int32.MaxValue);
                    maxUsage.SqlDbType = SqlDbType.Int;
    
                    SqlParameter AccountRequest = command.Parameters.AddWithValue("@StoredProcParameterName", request);
                    AccountRequest.SqlDbType = SqlDbType.Structured;
                    AccountRequest.TypeName = "dbo.BusinessTableType";
    
                    reader = command.ExecuteReader();
    
                    while (reader.Read())//has no records

    My thought is that it has something to do with the SqlMetaData on the "Id" field that I am using in my Converter, but that's just a guess at this point.  I'm still actively trying to figure this out for myself, but I wanted to spin up this post in case someone more experienced with passing Table Valued Parameters inside a SqlCommand sees something obvious that I just am not seeing.  









    • Edited by K. Alan Bates Thursday, March 21, 2013 10:26 PM
    • Moved by Bob Shen Friday, March 22, 2013 5:52 AM
    Thursday, March 21, 2013 3:50 PM

All replies

  • It looks like you have a variety of issues here.  I would start out with just one issue first -- like being able to retrieve data

    How about this?  Instead of declaring @Accounts like the following

    DECLARE @Accounts as dbo.BusinessTableType
    insert into @Accounts(id,Code,ServiceType,AccountNumber)
    select 3,'Code','Service','1234567890'

    try declaring it like this

    DECLARE @Accounts TABLE(id int NULL, Code varchar(1000) NULL,
     ServiceType varchar(50) NULL, AccountNumber varchar(50) NULL)

    and then insert some data into @Accounts and try to return data into your app at this point.  Once you know you have the data part working then step it up to your other procedures.  If the other procedures don't work then you can investigate this one step at a time


    Rich P

    Thursday, March 21, 2013 4:32 PM
  • It looks like you have a variety of issues here.  I would start out with just one issue first -- like being able to retrieve data

    How about this?  Instead of declaring @Accounts like the following

    DECLARE @Accounts as dbo.BusinessTableType
    insert into @Accounts(id,Code,ServiceType,AccountNumber)
    select 3,'Code','Service','1234567890'

    try declaring it like this

    DECLARE @Accounts TABLE(id int NULL, Code varchar(1000) NULL,
     ServiceType varchar(50) NULL, AccountNumber varchar(50) NULL)

    Rich P

    I'd appreciate if you would enumerate the "variety of issues" I am having.

    In your suggestion, you're misunderstanding the problem.  The snippet that you have quoted works as designed. It is when I attempt to interface with the stored procedure through ADO.Net that the issue arises.  In the database, I can instantiate a variable of my user defined table type, populate it with a row and execute the stored procedure with that variable passed as a TVP (stored procedure returns 66 rows...I noted as such in my initial post)

    However, when I am in my application and call command.ExecuteReader() after filling in the table-valued parameter with my account requests (in the example I have provided, with a mock that contains hard coded values that match the request that returned 66 rows from within SSMS), the reader returns an empty recordset.

    The first area of concern I had focuses on the SqlMetaData for column0 in my SqlDataRecord. The "Id" field is a nullable int in the table valued type, and I am concerned that I might be passing the incorrect version of Null in its place. However, I pushed in a proper Id value and the command still failed to return a result set.  

    So now, I don't really know what the problem is.








    Thursday, March 21, 2013 5:16 PM
  • Ok...so I switched my tvp implementation inside my code from a custom implementation of IEnumerable<SqlDataRecord> to a DataTable *mimes shooting self in head*  ...and rows are returned.

    DataTable requestMock = new DataTable("[dbo].[BusinessTableType]");
                    requestMock.Columns.Add("Id", typeof(int));
                    requestMock.Columns.Add("Code", typeof(string));
                    requestMock.Columns.Add("ServiceType", typeof(string));
                    requestMock.Columns.Add("AccountNumber", typeof(string));
    
                    requestMock.Rows.Add(DBNull.Value, "Code", "Service", "1234567890");
    
    SqlParameter AccountRequest = command.Parameters.AddWithValue("@AccountFilterTable", requestMock);
                    AccountRequest.SqlDbType = SqlDbType.Structured;
                    AccountRequest.TypeName = "dbo.BusinessTableType";
    
                    reader = command.ExecuteReader();

    The reader returns....but that begs the question: What was wrong with my custom implementation of IEnumerable<SqlDataRecord>.GetEnumerator()?



    Thursday, March 21, 2013 7:29 PM
  • I thought DECLARE @Accounts as dbo.BusinessTableType ... was happening in your SP.   What version of VS are you using?

    Rich P

    Thursday, March 21, 2013 9:42 PM
  • No, I never included any of the logic inside the stored procedure (it isn't germane to my problem)  The script that I did include was invoking the stored procedure. It declared and fills a variable of the UDTT that the stored procedure accepts and then executes the stored procedure that I am trying to interface with via ADO.NET.

    The SQL Script executing the stored procedure directly within SSMS returned 66 rows where command.ExecuteReader() -using the same record inside its tvp- was returning 0.  I modified the tvp assignment to a DataTable from my custom Metadata and ExecuteReader() returned my 66 rows.

    At this point, my question is more focused: what was wrong with my custom implementation of IEnumerable<SqlDataRecord>.GetEnumerator() that precluded the SqlCommand from returning my stuffs?

    VS 2008 9.0.35191 on .NET 3.5 SP1



    Thursday, March 21, 2013 9:59 PM
  • I use VS2008 also -- we are (kind of) on the same page.  But I am still not completely clear what is happening with your issue.  Question: are the following operations taking place in your VS app (would these be strings you are executing from like a sqlDataAdapter)?  or is this happening on the sql server?

    CREATE TYPE [dbo].[BusinessTableType] AS TABLE(
     [id] [int] NULL,
     [Code] [varchar](1000) NULL,
     [ServiceType] [varchar](50) NULL,
     [AccountNumber] [varchar](50) NULL
    )
    GO

    ---------------------------------------------------

    DECLARE @Accounts as dbo.BusinessTableType
    insert into @Accounts(id,Code,ServiceType,AccountNumber)
    select 3,'Code','Service','1234567890'

    exec MyDatabase.MySchema.GetData @Accounts

    --also -- is your app a web app? winform app?  console app?


    Rich P

    Friday, March 22, 2013 3:57 PM
  • Type dbo.BusinessTableType is a user defined table type that already exists in SQL Server. I was merely including it to show documentation of its interface.

    DECLARE @Accounts as dbo.BusinessTableType
    insert into @Accounts(id,Code,ServiceType,AccountNumber)
    select 3,'Code','Service','1234567890'
    
    exec MyDatabase.MySchema.GetData @Accounts

    was my proof.  I wrote and executed that sql script to prove that the table type was set up properly, it was being populated, the stored procedure accepted it, inspected it correctly, and returned results.  

    The problem arises when I attempt to perform the same machinations through System.Data.SqlCommand calls to the procedure.  Documentation on interfacing with table valued parameters via ADO.NET state that a tvp may be passed to a command parameter as an IEnumerable<SqlDataRecord>().  I wrote a converter to transform my business object into a SqlDataRecord and passed the IEnumerable into the command.  The parameters list accepted my IEnumerable as a valid value for the argument and command.ExecuteReader() ran to completion, but it did not contain any rows.  

    The documentation for tvps also state that you may pass one via DataTable.  When I modify my implementation to treat the tvp as a DataTable, the command returns the same result set as my SQL Script.  

    This code is executing as a Script Task within an SSIS package.

    Friday, March 22, 2013 8:20 PM
  • Hi,

    Have you tried to use Datatable to check whether or not you could receive the data?

    Monday, March 25, 2013 3:16 PM
  • So I apparently missed that I had originally posted this in the language forum.  Perhaps that's why I experienced a bit of trouble finding an explanation to my liking for what is going on with my code.  Thanks, Bob, for moving this over to the ADO.NET Adapters forum for me.  

    Perhaps you guys might be able to shed light on the problem I am having.


    Thursday, March 28, 2013 3:52 PM