StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Retrieve parameter names from stored procedures

Retrieve parameter names from stored procedures

Jeff Pagley — Wed, 26 Sep 2007 09:11:04 GMT

I have some old code where I used the System.Data.SqlClient.SqlConnection object that is dropped onto a form for data access. I use the connection object to retrieve parameter names of stored procedures. I would like to convert it to use Strataframe. Here is an example of what the code looks like now:

cmd = New SqlClient.SqlCommand("StoredProcedureName", Me.SQLConnection99)

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandTimeout = 60

'get the parameters for the stored procedure

SqlCommandBuilder.DeriveParameters(cmd)

For Each prm As SqlParameter In cmd.Parameters

If prm.ParameterName <> "RETURN_VALUE" Then

prm.Value = datarow(prm.ParameterName)

End If

Next prm

How can I use Strataframe's connection layer to eliminate the SQL connection object (SQLConnection99) I am using now to retrieve the parameter names?

I am sure it's simple, but I'm not seeing it. As always, thanks for your expert help!

RE: Retrieve parameter names from stored procedures

StrataFrame Team — Wed, 26 Sep 2007 09:11:04 GMT

You can also use it like this:

Dim conn As SqlConnection = DirectCast(DataBasics.DataSources("").CreateBlankDbConnection(), SqlConnection)

All that does internally is create a new SqlConnection object and set its connection string to the one used by the data source.

RE: Retrieve parameter names from stored procedures

Trent L. Taylor — Tue, 25 Sep 2007 09:25:54 GMT

Yup...you got it. Sorry if I was not more clear. I was answering questions last night with a migraine headache....so I am not surprised that my answer was a little "muddy" :) But you got exactly what I was saying. :)

RE: Retrieve parameter names from stored procedures

Jeff Pagley — Tue, 25 Sep 2007 08:16:59 GMT

Hi Trent,

Sorry about the earlier post. Once I posted my response, it dawn on me what you had said. It was as simple as...

Dim conn As New SqlConnection(DataBasics.DataSources("").ConnectionString)

conn.Open()

cmd = New SqlClient.SqlCommand("stored procedure name", conn)

Thanks for the help!

RE: Retrieve parameter names from stored procedures

Jeff Pagley — Tue, 25 Sep 2007 08:01:08 GMT

Hi Trent,

I guess I need little more help. I have used the DataBasics.DataSources many times to execute stored procedures and it works great. In my case, I believe I need to get a SqlConnection object, so that I can execute the SqlCommandBuilder.DeriveParameters(cmd) to retrieve the stored procedure's parameters.

How do I get a valid SqlConnection object using the DataSources..ConnectionString information to be used in the following statement.

cmd = New SqlClient.SqlCommand("StoredProcedureName", >> SqlConnection Object << )

Thanks!

RE: Retrieve parameter names from stored procedures

Trent L. Taylor — Mon, 24 Sep 2007 20:59:38 GMT

All you have to do is retrieve the connection string through your already defined data sources. We do not "keep-alive" the connections and they are re-created as needed. So if you want to create a temp connection, just pull ther string from the data sources connection:

[codesnippet]MicroFour.StrataFrame.Data.Databasics.DataSources("MyKey").ConnectionString[/codesnippet]

In this case you would just create your SQlConnection variable for testing. You can also exevute queries directly off of this as well:

[codesnippet]MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery("My Statement")[/codesnippet]