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! 

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:

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

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!

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!

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.

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.