How can i return the names of a stored procedures input parameters?
-
Either using SQL or C#/ADO.NET is there a way for me to "probe" a stored procedure to return the names of its input parameters to my code? Thanks.
-
Either using SQL or C#/ADO.NET is there a way for me to "probe" a stored procedure to return the names of its input parameters to my code? Thanks.
Interseting!!! but I dont think its possible. :doh:
-
Either using SQL or C#/ADO.NET is there a way for me to "probe" a stored procedure to return the names of its input parameters to my code? Thanks.
I assume your talking about sql server? If it's 2005 you want to use the System Views: http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en[^] If it's 2000 you want the system tables: http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx[^] Unfortunatly I dont have a link handy on how to use them, but they are pretty straight forward and will provide all you need :) Edit: just spotted this article. It looks old but relivent: http://www.devx.com/vb2themax/Tip/18282[^] -- modified at 6:16 Tuesday 26th June, 2007 Edit: I also forgot to tell you about DMO and SMO ;) SMO (Sql Management Objects) in 2005, is a set of objects you can use in .Net that retrive a lot of information about Sql Server and allows you to perform admin operations. They definatly allow you to list the stored procs and all the details about them. A little warning when used with a complex database the SMO's can require a fair bit of tweaking to perform well but it's well worth it if you need that much control. DMO is similar and used in 2000. I've never personally used DMO so you'dh ave to look into what it can achive.
-
Interseting!!! but I dont think its possible. :doh:
-
I assume your talking about sql server? If it's 2005 you want to use the System Views: http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en[^] If it's 2000 you want the system tables: http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx[^] Unfortunatly I dont have a link handy on how to use them, but they are pretty straight forward and will provide all you need :) Edit: just spotted this article. It looks old but relivent: http://www.devx.com/vb2themax/Tip/18282[^] -- modified at 6:16 Tuesday 26th June, 2007 Edit: I also forgot to tell you about DMO and SMO ;) SMO (Sql Management Objects) in 2005, is a set of objects you can use in .Net that retrive a lot of information about Sql Server and allows you to perform admin operations. They definatly allow you to list the stored procs and all the details about them. A little warning when used with a complex database the SMO's can require a fair bit of tweaking to perform well but it's well worth it if you need that much control. DMO is similar and used in 2000. I've never personally used DMO so you'dh ave to look into what it can achive.
-
Except the phrase is "We live and learn" :P We live and learn : (phrase used when sb has heard sthg new, unexpected, and surprising.) -- modified at 7:31 Tuesday 26th June, 2007
-
Either using SQL or C#/ADO.NET is there a way for me to "probe" a stored procedure to return the names of its input parameters to my code? Thanks.
I'm not sure I can think of a valid business case as to why you'd want to do that. From a programmatic perspective, you'd still need the type and, depending upon the type, the size of the field. If you know both sides of the system, there are myriad better methods to perform what you're asking.
-
I'm not sure I can think of a valid business case as to why you'd want to do that. From a programmatic perspective, you'd still need the type and, depending upon the type, the size of the field. If you know both sides of the system, there are myriad better methods to perform what you're asking.
Thanks guys I managed to do it in with .NET by using SqlCommandBuilder.DeriveParameters(MySqlCommand) It was used to create a generic method to loop through values from a DTO and place them into input parameters for a given Stored procedure on the mapper layer of our code. If I cache the results (for speed) it saves us lots of time rewriting very similar stuff inside each mapper to take the dto and asign its values to known paramaters in different procedures. So what i have is DataAdapter myMethod(string SProcName, DTOType inputParamsDTO) { DataAdapter da = new DataAdapter. [psudo] open connection to sql SqlCommandBuilder.DeriveParameters(SProcName) [psudo] loop [psudo] if SProcName.Param.Direction == input OR input/Output [psudo] if inputParamsDTO.Tables[0].Rows[0][i].Contains(SProcName.Param[i].ParamaterName) [psudo] SProcName.Param[i] = inputParamsDTO.Tables[0].Rows[0]["SProcName.Param[i].ParamaterName"] [psudo] end loop [psudo] close connection to sql [psudo] return da } So we pass it a SProc and a DTO (or dataset) and it gives us back a data adapter we can just calla Fill() on. But it needs to cache some where to stop excessive round trips
-
Thanks guys I managed to do it in with .NET by using SqlCommandBuilder.DeriveParameters(MySqlCommand) It was used to create a generic method to loop through values from a DTO and place them into input parameters for a given Stored procedure on the mapper layer of our code. If I cache the results (for speed) it saves us lots of time rewriting very similar stuff inside each mapper to take the dto and asign its values to known paramaters in different procedures. So what i have is DataAdapter myMethod(string SProcName, DTOType inputParamsDTO) { DataAdapter da = new DataAdapter. [psudo] open connection to sql SqlCommandBuilder.DeriveParameters(SProcName) [psudo] loop [psudo] if SProcName.Param.Direction == input OR input/Output [psudo] if inputParamsDTO.Tables[0].Rows[0][i].Contains(SProcName.Param[i].ParamaterName) [psudo] SProcName.Param[i] = inputParamsDTO.Tables[0].Rows[0]["SProcName.Param[i].ParamaterName"] [psudo] end loop [psudo] close connection to sql [psudo] return da } So we pass it a SProc and a DTO (or dataset) and it gives us back a data adapter we can just calla Fill() on. But it needs to cache some where to stop excessive round trips