0

SqlCommandBuilder.DeriveParameters is not available in .net core (even in .NETCoreApp 2.0), so is there any way to retrieve the parameter list of a stored procedure in .net core?

Nhat Hong Ly
  • 618
  • 4
  • 15

1 Answers1

0

You could always just inspect the SQL Server catalog views to get this information - for example, with this query:

SELECT  
    pr.name,     -- Procedure name
    pa.name,     -- Parameter name
    pa.parameter_id,
    ty.name,     -- Parameter datatype name 
    pa.max_length,    -- Max length (of string parameters)
    pa.precision,     -- Precision (total num of digits) for numeric parameters
    pa.scale,         -- Scale (num of digits after decimal point) for numeric parameters
    pa.is_output,            
    pa.has_default_value,    
    pa.is_readonly,
    pa.is_nullable
FROM 
    sys.procedures pr
INNER JOIN 
    sys.parameters pa ON pa.object_id = pr.object_id
INNER JOIN 
    sys.types ty ON ty.user_type_id = pa.user_type_id

This can be extended - there are quite a few more bits of information around, if you're interested in those.

See SQL Server catalog views for a great deal more information and detail about the indispensible catalog views in SQL Server

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Due to the policies in our company, the sql-account, which is used in web code, does not have permission to access those system's objects (procedures, parameters, types, etc.). And we have many database servers, so that it's not a good choice to create a stored procedure with your suggested t-sql and deploy to all DB server. Then do we have any way to do it in web code (cry) – Nhat Hong Ly Jun 01 '17 at 08:38
  • *Then do we have any way to do it in web code (cry)* - no, unfortunately, this information is stored **inside SQL Server** and accessible via these catalog views. If you cannot inspect those, you're pretty much out of luck, I'm afraid..... – marc_s Jun 01 '17 at 08:44
  • I just wonder how `SqlCommandBuilder.DeriveParameters` (full .net framewok) deals with it. And is there any problem to port it to .net standard? We are porting some of our libraries to .net core, but seem it's not an easy work :( – Nhat Hong Ly Jun 01 '17 at 08:49