Connecting to same database at the same time

tobin46
tobin46 used Ask the Experts™
on
Hi:  
In a function where I'm gathering claim data per policy, I execute a stored procedure and the result set returned with the following code:
scGet_Claim_Data = New SqlCommand("Get_Claim_Data", scDBConnection)
                    With (scGet_Claim_Data)
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add("@PolNum", SqlDbType.VarChar, 18)
                        .Parameters("@PolNum").Value = sdrBasicPolicyInfo.Item("PolNum")
                        .Parameters.Add("@userdate", SqlDbType.DateTime, 18)
                        .Parameters("@userdate").Value = dteuserdate
                        sdrLossRecordInfo = .ExecuteReader()
                    End With
I have created structures that will assemble the data returned and write it to a text file.

There is one trigger for a particular field that will require another query to be run against the same database to gather additional information.  

IF a claim meets this requirement, I want to pass, Policy Number and Claim Number to another function and a procedure that would be connecting to the same database.  Could I re-use the connection since it would still be open in the other function?  Or would I simply need to create another connection using the same connection string?

For example, the connection string used above is scDBConnection, would I just need to create scDBConnection2?  Can I even do that since there is a live connection against the database?

Trying to plan on how to accomplish this...Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
You can re-use the connection for multiple queries. The connection is available till it is closed.

Create a new SqlCommand object and associate the existing connection object with that. Use the new command object to execute your second SP

~Ajitha
Most Valuable Expert 2012
Top Expert 2014
Commented:
In such scenarios, I declare the connection, command and reader(if needed) objects at the class level. I initialize them in the form load. These can then be used in all the functions. For error handling purposes, I just add a check to see if the connection is open or not. For example

Public Class MyClass
    Dim dbcon as sqlconnection
    Dim dbcmd as sqlcommand
Private Sub Form_Load...
    InitializeObjs()
End Sub

Private Sub LoadData...
    If IsNothing(dbcon) or isnothing(dbcmd) then
       InitializeObjs()
    End Sub
    ...
End Sub

Private Sub InitializeObjs()
    dbcon = new Sqlconnection(constring)
    dbcon.open
    dbcmd = new Sqlcommand
    dbcmd.connection = dbcon
End Sub
End Class

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial