About SQLDataReader

[Tony Johansson]

Hello!

I'm pretty sure that if you haven't an open a database connection when you
want to get something from the database a connection is temporarily open for
you and when you have got the data the connection is closed(returned to the
pool).

This doesn't seem to be right because I get error(no connection is open)
when I remove this statement
"dataConnection.Open();" from the code below.

Can somebody explain why a connection is not opened ?

class Report
{
static void Main(string[] args)
{
SqlConnection dataConnection = new SqlConnection();
try
{
dataConnection.ConnectionString = "Integrated
Security=true;" +
"Initial
Catalog=Northwind;" +
@"Data
Source=hempc\SQLExpress";
//dataConnection.Open();
Console.Write("Please enter a customer ID (% characters);
");
string customerId = Console.ReadLine();

SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText =
"Select OrderID, OrderDate, " +
"ShippedDate, ShipName, ShipAddress, ShipCity, " +
"ShipCountry ";
dataCommand.CommandText +=
"From Orders " +
"where CustomerID='" + customerId + "'";
Console.WriteLine("About to execute {0}\n\n",
dataCommand.CommandText);

SqlDataReader dataReader =
dataCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);


while (dataReader.Read())
{
int orderId = dataReader.GetInt32(0);

if (dataReader.IsDBNull(2))
{
Console.WriteLine("Order {0} not yet shipped\n\n",
orderId);
}
else
{
DateTime orderDate = dataReader.GetDateTime(1);
DateTime shipDate = dataReader.GetDateTime(2);
string shipName = dataReader.GetString(3);
string shipAddress = dataReader.GetString(4);
string shipCity = dataReader.GetString(5);
string shipCountry = dataReader.GetString(6);
Console.WriteLine(
"Order {0}\nPlaced {1}\nShipped {2}\n" +
"ToAdress {3}\n{4}\n{5}\n{6}\n\n", orderId,
orderDate,
shipDate, shipName, shipAddress, shipCity,
shipCountry);
}
}

dataReader.Close();
}
catch (Exception e)
{
Console.WriteLine("Error accessing the database: " +
e.Message);
}

finally
{
System.Data.ConnectionState state = dataConnection.State;
dataConnection.Close();
}
}
}
}

 

[Jon Skeet [C# MVP]]

I'm pretty sure that if you haven't an open a database connection when you
want to get something from the database a connection is temporarily open for
you and when you have got the data the connection is closed(returned to the
pool).

I believe that's the case for SqlDataAdapter, but I can't see any
documentation saying it's the case for calling
SqlCommand.ExecuteReader directly.

Jon

 

[Arne Vajhøj]

I believe that's the case for SqlDataAdapter, but I can't see any
documentation saying it's the case for calling
SqlCommand.ExecuteReader directly.

The reader has to support data sizes that can not be in memory,
so ExecuteReader can not release the connection.

..ExecuteReader(CommandBehavior.CloseConnection) must be the
closest.

Arne

 

[Jon Skeet [C# MVP]]

The reader has to support data sizes that can not be in memory,
so ExecuteReader can not release the connection.

When the reader is closed, I think it's reasonable for it to close the
connection when CommandBehavior.CloseConnection has been specified -
I'd certainly expect to have to retrieve all the data I wanted (even
from blobs/clobs) before that point.

However, I believe the OP is more concerned that it's not *opening*
the connection.

Jon

 

[Arne Vajhøj]

When the reader is closed, I think it's reasonable for it to close the
connection when CommandBehavior.CloseConnection has been specified -
I'd certainly expect to have to retrieve all the data I wanted (even
from blobs/clobs) before that point.

However, I believe the OP is more concerned that it's not *opening*
the connection.

Ah. Then everything makes sense.

Arne

 

[Peter Bromberg [C# MVP]]

Only the DataAdapter class family supports managing the opening / closing of
the Connection object. SqlCommand with ExecuteReader, ExecuteScalar, etc. -
you have to take care of it yourself. As mentioned,
CommandBehavior.CloseConnection on a SqlDataReader command is the closest you
can come to this -- when you close the Reader the connection is also closed
and returned to the Connection Pool.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short Urls & more: http://ittyurl.net