Using system tables in VB.NET

Question

I want to use the following SQL statements in a VB.NET application:

select * from information_schema.parameters where specific_name='GetTaskEvents' SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME= 'GetTaskEvents' 

Today I talked with the database administrator, and he seemed invisible, but did not give a reason. Is this a bad practice?

I want to do something like this

 public sub Delete() 'loop around about ten databases 'Call the delete function in each of the databases (check that the stored procedure exists first) 'Each database has different output parameters. Deal with them differently eg if there is an email parameter, then email the person to say the record was deleted. End Sub 

The reason for this is that I can process each database in the same way.

Answer 1

If you know the stored procedure, but are not sure which parameters are the best way to use the SqlCommandBuilder.DeriveParameters method to get the number of parameters, as well as their types and names.

UPDATE Here is an example usage in VB.NET

 Dim oCommand As New SqlCommand("SpName", oConnObject) oCommand.CommandType = CommandType.StoredProcedure SqlCommandBuilder.DeriveParameters(oCommand) 

At this point, the oCommand.Parameters collection will be populated with parameters for "SpName"

Answer 2

Best practice should come down to what kind of risk you are willing to expose your company to obtain the required data type. Risk / Benefit Analysis.

In this case, I see that you are making your code depend on Microsoft system tables and schemas behind the scenes. They tend to vary from version to version. Thus, switching from sql 2018 to 2021, for example, can break your compiled code. Besides the risks outlined in the comments about calling procs, you don't understand the effect.

So, to reduce this risk, you can put your code in a saved proc so that it can be easily updated without having to recompile the code if and when the system diagram changes. There are other ways to reduce other identified risks, but that’s how I analyze “best practice.”

Answer 3

Use the System.Data namespace:

DbConnection + DbCommand + IDataReader (SqlConnection + SqlCommand for SQL Server)