Does one SqlConnection mean one SqlCommand?  
Author Message
Alessandro Camargo





PostPosted: Visual C# Express Edition, Does one SqlConnection mean one SqlCommand? Top

Hi,

This program below works fine; however, I have a question. I am using two different connections one to CommandSqlServer and another to CommandSqlServer1.

Am I right this means to 3 commands I need 3 connections and so on - (of course all of them nested)

Or am I wasting resource in case how can I do (Because if I use just one I got an exception).

//Connection string for SqlServer 2005

ConnectionSqlServer = new SqlConnection();

ConnectionSqlServer1 = new SqlConnection();

...stuff

//Command

CommandSqlServer = new SqlCommand(stringCommandSqlServer, ConnectionSqlServer);

CommandSqlServer1 = new SqlCommand(stringCommandSqlServer, ConnectionSqlServer1);

...stuff

//Command 1

CommandSqlServer.CommandText = "SELECT 1 From dummy ";

// Execute

DtReaderSqlServer = CommandSqlServer.ExecuteReader();

while (DtReaderSqlServer.Read() )

{

// Command 2

CommandSqlServer1.CommandText = "update table x

values(" + DtReaderSqlServer.GetValue(1).ToString() + " ) "

//Execute Command 2

CommandSqlServer1.ExecuteNonQuery();

}

Cheers,




Visual Studio Express Editions1  
 
 
Mike Danes





PostPosted: Visual C# Express Edition, Does one SqlConnection mean one SqlCommand? Top

If you are using SQL Server 2005 and .NET 2.0 you can add MultipleActiveResultSets=true to the connection string and then you can have more that one active command per connection.

However, given the code you posted I wonder if you cannot achieve the same thing using just one command. Update table X values is not a valid SQL command so I assume that you actually wanted to write insert into table x values ... .

You should be able to do something like this:

INSERT INTO TABLE x (Column1) SELECT 1 FROM dummy

If you need to do some complex processing that cannot be in SQL then you have no other way than using code like the one posted but if you just need to move data from one table to another or doing things like adding the values of 2 columns:

INSERT INTO TABLE x (Column1) SELECT Column2 + Column3 FROM dummy

then using INSERT ... SELECT is the way to go.


 
 
Alessandro Camargo





PostPosted: Visual C# Express Edition, Does one SqlConnection mean one SqlCommand? Top

Hi Make,

Yes I posted the wrong command is insert and not update, by the way I can't use insert into...select

Because, I want to execute alter table command

while ( ... )

{
alter table.....

}

In this case can I use MultipleActiveResultSets=true

cheers,



 
 
Mike Danes





PostPosted: Visual C# Express Edition, Does one SqlConnection mean one SqlCommand? Top

Yes, you can use MultipleActiveResultsSets if you wish.

Anyway I suppose you won't have too many data to read since your doing alter table in that while so performance should not be a problem. Somehow I don't expect that someone is going to send 1000 alter tables to the database too often. Or do you


 
 
Alessandro Camargo





PostPosted: Visual C# Express Edition, Does one SqlConnection mean one SqlCommand? Top

Hi Mike,

No, I don't. I'll export all table data from SqlBase to Sql Server, therefore it'll be a standalone application.

Anyway I gonna test this MultipleActiveResultsSets.

Thank you