StrataFrame Forum

Retrieve parameter names from stored procedures

http://forum.strataframe.net/Topic11656.aspx

By Jeff Pagley - 9/24/2007

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! 

By Trent L. Taylor - 9/24/2007

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:

MicroFour.StrataFrame.Data.Databasics.DataSources("MyKey").ConnectionString

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

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteNonQuery("My Statement")
By Jeff Pagley - 9/25/2007

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!

By Jeff Pagley - 9/25/2007

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!
By Trent L. Taylor - 9/25/2007

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" Smile  But you got exactly what I was saying. Smile
By StrataFrame Team - 9/26/2007

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.