Retrieving data from table value parameter c#

Question

I haven't used TVP or stored procedures before and I'm hoping I can get some clarification on how to retrieve the equivalent of the first query as a dataset in c#. I created a custom type and procedure that seem to work and whenever I declare a variable and exec the stored procedure in SSMS it works, I'm just not sure how to replicate that in code.

SELECT ServLoc.ID, Meters.MeterID, MeterHistory.Stuff
FROM ServLoc 
INNER JOIN Meters ON ServLoc.ID = Meters.ServLocID
INNER JOIN MeterHistory ON Meters.MeterID = MeterHistory.MeterID
WHERE ServLoc.ID IN (1,2,3...40,000)



CREATE TYPE dbo.ServLocMeters 
AS TABLE (ServLocID int NOT NULL)


CREATE PROCEDURE [dbo].[sp_ServLocMeters]
    @newServLocMeters ServLocMeters READONLY
AS
    SELECT ServLocID, Meters.MeterID, MeterHistory.Stuff
    FROM Meters 
    INNER JOIN MeterHistory ON Meters.MeterID = MeterHistory.MeterID
    WHERE ServLocID IN (SELECT ServLocID FROM @newServLocMeters)
    ORDER BY ServLocID


declare @table as ServlocMeters
insert into @table values (1)
insert into @table values (2)
insert into @table values (31)
exec sp_servlocmeters @table

Have you read the TVP [documentation](https://stackoverflow.com/questions/8928378/using-regex-in-sql-server)? It has C# code examples. — Dan Guzman, Jun 17 '21 at 20:04
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Jun 17 '21 at 20:04
gotcha, thanks. and yes I have looked at the documentation but I've been struggling to find how to use the query with a dataadapter in c# (also link was incorrect) @DanGuzman — Ryan, Jun 17 '21 at 20:08
Sorry, this is the link I meant: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters — Dan Guzman, Jun 17 '21 at 21:06
You would probably get better performance if your Table Type had a primary key `CREATE TYPE dbo.ServLocMeters AS TABLE (ServLocID int PRIMARY KEY);` — Charlieface, Jun 20 '21 at 23:07

score 0 · Accepted Answer · edited Jun 17 '21 at 23:23

I found an example shortly after, lol. Thanks marc for the prefix heads up.

DataTable table = new DataTable("ServLoc");
table.Columns.Add("ServLocID", typeof(int));
for (int i = 0; i < 3000; i++)
{
    table.Rows.Add(1 + i);
}

DataSet dbDatasets = new DataSet();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("sp_ServLocMeters", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter sqlParam = cmd.Parameters.AddWithValue("@newServLocMeters", table);
    cmd.CommandTimeout = 0;
    sqlParam.SqlDbType = SqlDbType.Structured; //tells ADO.NET we are passing TVP  
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);

    adapter.Fill(dbDatasets);
}

You need to specify the table type name, also the `ParameterDirection`. Better to add the parameter like this `cmd.Parameters.Add(new SqlParameter("@newServLocMeters", SqlDbType.Structured) {TypeName = "dbo.ServLocMeters", Direction = ParameterDirection.Input, Value = table});` You also need to dispose your adapter with `using` — Charlieface, Jun 20 '21 at 23:06

Retrieving data from table value parameter c#

1 Answers1