Multiple SqlClient.SqlCommand for a single SQLConnection?

[iaw]

Hi,

I've recently returned to VB.net and a lot has changed and I'm looking for someone to explain if it is possible to have multiple commands for a single connection? For example:

Public cn as SqlClient.SqlConnectoin

Public Sub Test()

dim SqlCommand as SqlClient.SqlCommand

cn = New SqlClient.SqlConnection(My Login Info)
SqlCommand = New SqlClient.SqlCommand("Select count(*) from tblCustomers")
SqlCommand.Connection.Open
SqlCommand.ExecuteScalar

....

Is there a way that I can reuse the same cn for another select/insert/update/delete without having to .Close and then .Open again? Is there no way to just change the SqlCommand and Execute Scalar/NonQuery?

Basically I plan on executing multiple statements all the time and I'm trying to be sure I'm doing this the most efficient way.

Thanks,

iaw

 

[jmcilhinney]

If all you want to do is execute different SQL code then you don't need multiple SqlCommands. You can simply change the CommandText property of the one SqlCommand. If you need to change more than that then you probably do want multiple SqlCommands, but you already know how to do it. Creating multiple SqlCommands is just creating one SqlCommand multiple times. You already know how to create one SqlCommand so just do it multiple times. Also, there's not much point doing this:

SqlCommand.Connection.Open

when you can just do this:

cn.Open

 

[cjard]

Yes, you can have multiple commands using one connection - this is what the data access layer built by Visual Studio looks like..

Here's an example pulled out of an open project of mine (this is C# but you'll get the idea, the VB version is virtually identical)

internal global::System.Data.Odbc.OdbcConnection Connection {
            get {
                if ((this._connection == null)) {
                    this.InitConnection();
                }
                return this._connection;
            }
            set {
                this._connection = value;
                if ((this.Adapter.InsertCommand != null)) {
                    this.Adapter.InsertCommand.Connection = value;
                }
                if ((this.Adapter.DeleteCommand != null)) {
                    this.Adapter.DeleteCommand.Connection = value;
                }
                if ((this.Adapter.UpdateCommand != null)) {
                    this.Adapter.UpdateCommand.Connection = value;
                }
                for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1)) {
                    if ((this.CommandCollection[i] != null)) {
                        ((global::System.Data.Odbc.OdbcCommand)(this.CommandCollection[i])).Connection = value;
                    }
                }
            }
        }

Actually, you should investigate how to get Visual Studio to write your data access code for you. It will save you a lot of work, and be higher quality than what a human could turn out in days

Basically I plan on executing multiple statements all the time and I'm trying to be sure I'm doing this the most efficient way.

I'd recommend you actually write the app first, and then look to profile it.. Youre actually optimising something that will save you so little cpu time that it's unlikely to ever repay even the amount of time you spent worrying/posting about it. Efficient means different things to different people: fast, economical, lean; these things are largely incompatible, so efficiency sounds like a nice idea, but it shouldnt be your focus right now.. Just follow higher level recommended practices for writing your app, then sort out problems

Your sample code given can be reduced to the following set of steps:

Add a new dataset to the project
Right click its surface and Add Query
Set up the conenction string
Write the query
Call it something sensible
Click finish

To use in code:

Private x as New MyTableAdapter()

...
  If x.CountPeople() > 10 Then ...

Just 2 lines of code in your program, and VS has written all the database code in the most efficient, secure way supported

 

[cjard]

If all you want to do is execute different SQL code then you don't need multiple SqlCommands. You can simply change the CommandText property of the one SqlCommand.

I disagree; you've then got to mess about clearing and re-populating the .Parameters collection too, change the command type over if needs be etc..

Better off just making another command, and keeping them both cached for later use