0

I have a dataset - ImageDataSet.xsd - that has a datatable in it - Colour - with it's relating tableadapter - ColourTableAdapter -. This ColourTableAdapter has a custom query added to it - FillByColourID - that is linked to the database stored procedure - Colour_SelectByColourID -.

If I only have access to the table adapter and the name of the query, so ColourTableAdapter and FillByColourID, how could I possibly get the stored procedure name? I have gotten as far as retrieving the correct MethodInfo to invoke but I need to know the stored procedure name as well.

Looking at the query in the xsd file, I see there are properties that list the FillMethodName and the CommandText, which is the method I need to invoke on the adapter and the stored procedure name that I am looking for, but I don't know how to get to those properties. I have looked through the adapter using reflection but I can't seem to find the correct properties to give me this information.

Edit:

I need the stored procedure name as I have a running SqlConnection and SqlTransaction that spans across multiple table adapters that inherit from a base and classes so I need to set the connection and the transactions manually to all the commands. A transaction scope doesn't work as it requires something in Windows to be running that our environments don't have (really can't remember what it was).

Currently, I have to set all the commands in the table adapter base as I don't actually know which command is going to be called. With the stored procedure name, I can set just that command as some of the table adapters have like 50 queries and I am worried about the performance impact.

Metal Paw
  • 112
  • 1
  • 13
  • I added the tags xml and xsd to the posting. Why do you need the procudure name? Since you have an xsd the tag names in the xml are a constant (not variable) so it will not be changing. If they do change you should be able to extract the names directly from the xml and not have to go back to the database to get the names. – jdweng May 30 '16 at 12:37
  • Updated description. – Metal Paw May 30 '16 at 14:08
  • Why don't you add the procedure name to one of the database tables so it can be returned in the query. – jdweng May 30 '16 at 15:32
  • That isn't exactly feasible.... If I need to go and create a database table to hold all the stored procedure names and their relating data table adapter call, not to mention that either of these names could change over time, that would require going through over 4000 stored procedures and then trying to match them to their code calls. Not to mention scalar and table functions. – Metal Paw May 31 '16 at 07:21
  • If you can't create a table manually then why do you think you can do it programmable? The table should be built when the data is stored in the database. You always have to consider in a software design the impact of future changes and having the database forward and backwards compatible. – jdweng May 31 '16 at 08:28
  • I'm not trying to create a table. I am trying to get the name of an existing stored procedure from the data table adapter method. The table you are suggesting I build is a link between all the stored procedure names and the data table adapter methods. **That** isn't feasible. – Metal Paw May 31 '16 at 14:55
  • You can get the procedure names using the schma of the SQL Server. But how will you know which tables were built with each procedure? – jdweng May 31 '16 at 18:13

0 Answers0