Generating parameter names in ADO.NET 2.0

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi,
I'm using
DbConnection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation) to
get information from the current data provider. I would like to generate
appropriate parameter names for the current database. This code will be
used with multiple databases (some unknown at this time) and I'm hoping to
come up with a generic way to create parameter names. I've already worked
around the named vs. positional parameter problem. All I need now is to
generate appropriate names when using named parameters. The info returned
by the above statement provides two values that may be useful:
ParameterMarkerFormat and ParameterMarkerPattern. The latter is a regex
pattern. I can see how that can be used to verify a valid name, but not for
generating one, unless there's something about regex I don't know. The
former provides more hope. However, in testing this I found that with SQL
Server this returns "{0}". I would expect it to return "@{0}" (add the
leading "@" character). So I am confused about the usefulness of either of
these and wondering if there is a way to do what I'm doing without having to
write custom code for each database. Along these lines, I'm looking at the
other regex patterns returned like QuotedIdentifierPattern and
CompositeIdentifierSeparatorPattern and wondering if there really is a way
of generating names from all these patterns. I can't believe they'd be
there if they serve no purpose. If anyone can shed a little light on this
I'd really appreciate it.

Thanks,
John
 
Hi John,

If you need to get the parameter collection from stored procedures
automatically, I suggest you use DeriveParameters instead of GetSchema.
Because each database has its own the naming convention. So the GetSchema
might not meet all providers. A customize is required.

The CommandBuilder.DeriveParameters method retrieves parameter information
from the stored procedure specified in the command object and populates the
parameters collection of the specified Command object.

Please check the following link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatasqlclientsqlcommandbuilderclassderiveparameterstopic.asp

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hi Kevin,

Thank you for the reply. Unfortunately this does not help me, as I am not
using stored procedures. I have found work-arounds for most of what I'm
attempting to do using CommandBuilders, but in some cases I need to generate
an appropriate database-specific parameter name. For example, given a
select statement, the builder is able to generate appropriate insert, update
and delete statements along with parameters. But this only works when
selecting based on primary key. If I want to update using a where clause
based on some other column(s) I have to generate these statements--including
parameter names--myself. Even when using primary keys, if the table
contains an identity column and the database supports combining statements,
I'd like to be able to generically generate the equivalent of the following:
INSERT ...; SELECT @Identity = SCOPE_IDENTITY()
This, of course, works for SQL Server, but not every database. Accoring to
the small amout of documentation currently available for the beta, the
DataSourceInformation table returned from GetSchema is supposed to provide
database-specific details in a generic way. If this is not the case and you
know of a better way for me to accomplish this please let me know.

The values returned using GetSchema seem like they can offer a lot of value.
Whether or not they can help in my current situation or not, I'd still like
to understand them better. If you can explain how the ParameterMarkerFormat
can be used (when it simply returns "{0}" for SQL Server) and how the regex
patterns can be used I'd appreciate it. I've already been through all the
MSDN docs I can find as well as a few non-MSDN articles. There's just not a
whole lot out there right now--one of the difficulties with trying to stay
ahead of the technology. But any feedback you can provide is appreciated.

Thank you,
John
 
Hi John,

The GetSchema method returns a schema table that is to each providers, not
to each databases. For oledb provider, we can use GetOleDbSchemaTable to
get the further detailed information.

The schema collection includes common schema collections and
provider-specific schema collections. You can check the following link for
more information about Obtaining schem information from a database.

http://msdn2.microsoft.com/en-us/library/kcax58fh

Sorry, but this is the only doc I can find since the beta version of VS.NET
lack of documentation currently. The complete docs will be available when
it is release officially.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top