T
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();
}
}
}
}
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();
}
}
}
}