Allow multiple active connections to MS SQL database using VS datasets

Question

So I use a Visual Basic DataSet on an ASP.Net website, and when multiple users access the website I get something along the lines of:

ExecuteReader requires an open and available connection. The connection's current state is Open.

Can I create multiple connections to the SQL database?

I know the datasets have normal code behind them so I'm wondering if I could modify something in those files to create a new connection for each user session.

I've tagged c# because if an answer pops up in c# I'll convert it...

A shared connection I believe, I haven't changed anything (apart from adding tables) to the dataset — Jordan Harvey
– Jordan Harvey, Commented Aug 8, 2017 at 12:30

Hobbes · Accepted Answer · 2017-08-08 14:33:36Z

2

In a multi-threaded, multi-user environment like ASP.NET, you have to stay away from shared connections. You're better off creating the connection as needed and then disposing of it. Behind the scenes .NET will use connection pools, and SQL Server won't have any problems handling lots of multiple connections. Pseudo-code:

    ' SQLCommand to fill DataTable
    Dim dt As New DataTable
    Using cnn As New SqlConnection("someconnectionstring")
        Dim cmd As New SqlCommand("SELECT * FROM SomeTable", cnn)
        cnn.Open()
        dt.Load(cmd.ExecuteReader)
        cnn.Close()
    End Using

    ' TableAdapter to fill DataSet
    Dim ds As New DataSet
    Dim ta As New SqlDataAdapter
    ta.Fill(ds)

The [Using statement][1] lets .NET handle the disposal of the connection for you.

edited Aug 8, 2017 at 14:33

answered Aug 8, 2017 at 13:25

Hobbes

14712 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Crowcoder Over a year ago

Yes, but Close() is redundant within using.

Andrew Morton Over a year ago

@Crowcoder And .Open() is redundant when using .Fill() like that.

Jordan Harvey Over a year ago

I've converted existing XSD code into a vb file, can I use the generated classes and functions in the way you have specified above?

Hobbes Over a year ago

You can, although moving the autogenerated code into your own VB file will make updating the code a bit of a hassle. It rather defeats the purpose of having a graphical code-generation tool like the dataset designer...

mason Over a year ago

@Hobbes There is no need to always pair an open with a close. Only do so when you need to do so. Don't leave useless code in.

|

Collectives™ on Stack Overflow

Allow multiple active connections to MS SQL database using VS datasets

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related