Using CommandBehavior.CloseConnection while executing a command [Resolved]

Posted by Thiru under VB.NET on 8/19/2012 | Points: 10 | Views : 4830 | Status : [Member] | Replies : 5
what is the difference between:

(1) objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
(2) objDataReader = objCommand.ExecuteReader()





Responses

Posted by: Vasanthmvp on: 8/21/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
You may loop through the reader and write as:
Here, im selecting AutoId and FileName column values from the database table, which i executed it in the command.

using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
Response.Write(reader["AutoId"] + ", " + reader["FileName"] + "<br />");

(OR)

Response.Write(reader.GetInt32(reader.GetOrdinal("AutoId")) + ", " + reader.GetString(reader.GetOrdinal("FileName")) + "<br />");
}
}

2nd way is the better one.(fastest)


Regards,

Awesome Coding !! :)

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vikash on: 8/19/2012 [Member] Starter | Points: 25

Up
0
Down
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

above line automatically close the connection after objCommand.ExecuteReader() operation is performed.


Below statement keep the connection open from the databasse untill we don't close the connection explicitly.........

objDataReader = objCommand.ExecuteReader()

i hope you got your answer...........


Regards,
Vikash Pathak

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 8/19/2012 [Member] Starter | Points: 25

Up
0
Down
Thanks for your reply Vikash,

objCommand=New MySqlCommand("select * from tbl", mycon)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

as in the first line, assume it will give us 500 records.
if so, as in the 2nd line if connection is closed.
(i hope datareader requires the connection open - am i right? )

how to loop the datareader as:

While objDataReader.Read()
console.write(objDataReader("name"))
End while

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vasanthmvp on: 8/20/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

Generally, we use 1. connectionopen 2. Execute Command 3. close connection.

In case of SqlDataReader, it binds the data unless the connection is alive. Hence,

If you want to control the database connection behavior explicitly, simply call the execute Reader method without any parameter.

just as in (2) objDataReader = objCommand.ExecuteReader()

If you want to set the connection to be closed, as soon as the reader is closed. pass the (CommandBehaviour.CloseConnection())

just as in (1) objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)


Coming to the second thing,

you may attach the data reader as a datasource and populate.

Awesome Coding !! :)

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 8/23/2012 [Member] Starter | Points: 25

Up
0
Down
Now it sounds good.
Thanks for your valuable reply/support.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response