A question about sql connection in C# code.

[Wing]

Hi all,

I am writing the C# code function that take 2 parameters and access
the data in the MS SQL database and return a SqlDataReader object. In
my funtion, a SQL connection is established and open the connnection
before reading the data. Everything seem to be fine, but once I add the
close() function to close the sql connection, error come out. So I
leave out the close() function in my function. My code is showing below

-------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication3
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string found = "c553a15f-15bd-4081-bd5d-89cfa2cc0505";
int photoId = 3;

SqlDataReader answer = ReadQuantity(found, photoId);
while(answer.Read())
{
int photoQuantity = Convert.ToInt16(answer["Quantity"]);
Console.WriteLine("\t{0}", photoQuantity);
}
}

static SqlDataReader ReadQuantity(string cartId, int photoId)
{
SqlConnection thisConnection = new SqlConnection
(@"Data Source=(local);Integrated Security=SSPI;" +
"Initial Catalog=PhotoDB");
thisConnection.Open();
SqlCommand ReadQuantity = thisConnection.CreateCommand();
ReadQuantity.CommandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+"'";
SqlDataReader thisReader = ReadQuantity.ExecuteReader();
// thisConnection.Close();
return thisReader;
}

}
}

------------------------------------------------------------------------

Could everyone tell me what happen to the sql connection if I don't
include the close function in my code. Would it close automatically
after timeout and would it be security threat???

Thanks for your time

Wing

 

[Peter Rilling]

I think that DataReader is connection-oriented, meaning that you must
maintain the connection for the data to be returned. Basically a DataReader
returns on record at a time. This is different from a DataSet/DataAdapter
which loads all the records in memory.

 

[Bjorn Abelli]

...

I am writing the C# code function that take 2 parameters and access
the data in the MS SQL database and return a SqlDataReader object. In
my funtion, a SQL connection is established and open the connnection
before reading the data. Everything seem to be fine, but once I add the
close() function to close the sql connection, error come out. So I
leave out the close() function in my function. My code is showing below

You didn't close the Reader before you closed the Connection...

There are several different solutions to your problem, and it comes down to
what design you really want for your system. There are basically two
different approaches for how to deal with connections, readers, etc, which
can be combined into several different solutions.

1. Open, deal with the data, and close it as fast as you can.

2. Keep it open during the whole session

Both have advantages and drawbacks depending on what tasks the application
should do, but I'll try to sketch two basic examples on how you could deal
with it.

----------------------------------------------------

1.
static ArrayList ReadQuantity(string cartId, int photoId)
{

ArrayList quantities = new ArrayList();

SqlConnection thisConnection = new SqlConnection
(@"Data Source=(local);Integrated Security=SSPI;" +
"Initial Catalog=PhotoDB");
thisConnection.Open();
SqlCommand ReadQuantity = thisConnection.CreateCommand();
ReadQuantity.CommandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+"'";
SqlDataReader thisReader = ReadQuantity.ExecuteReader();

while(thisReader.Read())
{
quantities.Add(answer["Quantity"]);
}

thisReader.Close();
thisConnection.Close();
return quantities;
}

----------------------------------------------------
1 + 2.

In this scenario you open the connection beforehand, and can keep it open
the whole session. Note that you then pass the connection to the method.
(Not really Recommended!)

static ArrayList ReadQuantity(string cartId, int photoId, SqlConnection
con)
{

ArrayList quantities = new ArrayList();

SqlCommand ReadQuantity = thisConnection.CreateCommand();
ReadQuantity.CommandText = "SELECT Quantity FROM ShoppingCart WHERE
CartID = '"+cartId+"' AND PhotoID = '"+photoId+"'";
SqlDataReader thisReader = ReadQuantity.ExecuteReader();

while(thisReader.Read())
{
quantities.Add(answer["Quantity"]);
}

thisReader.Close();
return quantities;
}

----------------------------------------------------

Note that both examples deals with the actual data before returning.

Hmm, but I wonder if you really need a DataReader at all? Does the statement
really return more than one row, or could you suffice with using only the
ExecuteScalar method?

Could everyone tell me what happen to the sql connection
if I don't include the close function in my code. Would
it close automatically after timeout and would it be
security threat???

That is depending on many things, but it will logically not close, even if
it times out from the server, so it's good practice in all cases to close
the connection as soon as possible when you're done with it.

I wouldn't see it as a bigger "security threat" to keep it open than to just
open and close it, but rather a possible misuse of resources. If the number
of simultaneous connections are limited, an unnecessary open connection can
block out other users, or even yourself, if you keep opening connections,
but never close them.

// Bjorn A

 

[Wing]

Thanks very much for your suggestion, help and time. very appreciated!

What I want the function ReadQuantity to do is to return 2 values in
the particular row (of caurse I need to change the SELECT statemant) by
return the SqlDataReader object. Now your exmaple remind me that I can
assign the value in SqlDataReader object into array and return the
array instead, and I can close the connection which no longer need.

Thanks again.

Wing