return OleDbDataReader from static function

[Sharon]

Hi all
I want to return OleDbDataReader from a static function,
So I will have DB interface for my Entire project,
Is it possible?
How do I close the connection ??????

Thank you
Sharon

/////////////////////////////////////////////////////////////
//Example of use :
/////////////////////////////////////////////////////////////
OleDbDataReader reader = myDbObj.Select("SELECT CustomerID, ContactName,
FROM Customers")
While (reader.Read()){
System.diagnostics.Trace.WriteLine(reader("CustomerID").ToString()
+ " : " + reader("ContactName").ToString() + "<br>")
}

/////////////////////////////////////////////////////////////
//select function :
/////////////////////////////////////////////////////////////
Public static OleDbDataReader Select (string selectString)
{
'Use a string variable to hold the ConnectionString property.
String connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\\Program
Files\\Microsoft Visual Studio\\VB98\\NWIND.MDB"

OleDbConnection cn = New OleDbConnection(connectString)

'Open the connection.
cn.Open()

'Use a variable to hold the SQL statement.
String As String =

OleDbCommand cmd = New OleDbCommand(selectString, cn)

Return cmd.ExecuteReader()

'Close the reader and the related connection.
// reader.Close()
//??????????????????????????????????????????????????
cn.Close()

}

 

[Marina]

If you are returning the reader, you have to wait for the user to close the
reader. In order for closing the reader to close the connection, you have to
pass in CommandBehavior.CloseConnection into ExecuteReader.

However, this still means the user of this function has to remember to close
it.

None of the code after the Return statement in your function will execute,
the 'Return' statement immediately exits the function.

In general, the rule of thumb is don't do this. Return datatables.

 

[Sharon]

Hi marina



Thank you for your answer

so it will be like :



What is the differences between datatables and OleDbDataReader



Thank you again

Sharon

 

[Sushil Chordia]

Sharon, DataTable is a disconnected data cache. In your case, if the data is
small, it make sense to cache it and then pass it to the user.

HTH,
Sushil.

 

[J L]

Hi Marina,
Why do you say not to return a DataReader. If she uses the
CommandBehavior.CloseConnection, I can see no reason not to use it.
What am I missing?

TIA
John

 

[Marina]

Missing the fact that you are relying on the consumer of the function to
remember to close the reader when they are done.

Someone forgets to close it in one place, and before you know it you are
tracking down a connection pool out of connections leak in your program. You
would be surprised how often that happens.

 

[J L]

Ahhh...very good point. Thanks.

John

Missing the fact that you are relying on the consumer of the function to
remember to close the reader when they are done.

Someone forgets to close it in one place, and before you know it you are
tracking down a connection pool out of connections leak in your program. You
would be surprised how often that happens.

 

[J L]

One more question....if the DataReader goes out of scope before it is
closed....what happens?

John

 

[Marina]

The connection stays open until the GC decides to collect it. In which case,
the Dispose would get called on the connection, which closes it.

However, it is almost never the case that the GC does this before the pool
is out of connections. So in essense you are guaranteed to get the
connection pool is out of connections error.

 

[Sharon]

How about Dataset ?

Is this a cashed too?

The connection stays open until the GC decides to collect it. In which
case, the Dispose would get called on the connection, which closes it.

However, it is almost never the case that the GC does this before the pool
is out of connections. So in essense you are guaranteed to get the
connection pool is out of connections error.

 

[Marina]

Nothing is getting cached. I am not sure what you mean. Caching was not at
all discussed in this thread.

 

[J L]

Hi Marina,
I hope I am not trying your patience but I have one more question...

I have a DAL that I implemented as follows (psecudo code)

try
if dbProvider = "OleDb" then
dim cn as new OleDbConnection(myconnectstring)
<I then use this connection to fill a dataset>

elseif dbProvider = "SqlDb" then
dim cn as new SqlConnection(myconnectstring)
<I then use this connection to fill a dataset>

end if
catch ex as Exception
Throw New SystemException("some info here", ex)
<for example a bad SQL statement provide to my command object>
end try

My question is what happens if there is an exception. I can not
reference the connection in the catch block to see if it is open and
close it. So what happens to the connection in this case? And how best
to handle this situation?

TIA,
John

 

[JiangZemin]

You do not need to worry about disposing DataSet unless you explicitly
create command/connection objects when u create the DataSet, which u
shouldnt need to do in most cases.

HTH,
Premier JiangZemin

 

[Marina]

You need to declare the variables outside the try block. This is just a
scoping issue.

 

[J L]

I know it is a scoping issue but I am doing it for provider
independence. So what I guess I need to do is this

dim cnOleDb as new OleDbConnection
dim cnSQL as new SqlConnection

Try
if dbProvider = "OleDb" then
<use cnOleDb in my command object>
elseif dbProvider ="SqlDb" then
<use cnSQL in my command object>
else
<throw an error for invalid provider type>
endif
Catch
<handle the errors>
Finally
if cnOleDb.State = ConnectionState.Open then
cnOledDb.close()
end if
if cnSql.State = ConnectionState.Open then
cnSql.close()
end if
End Try

Is that a good way to go? I did read about using interfaces and
casting etc. but seemed very complicated. Are there any reasons not to
do it this way? ("it" means provider independent)

Thanks again,
John

 

[Sharon]

Hi marina

Well in Sushil's replay she says :



"DataTable is a disconnected data cache. In your case, if the data is
small, it makes sense to cache it and then pass it to the user. "





so what i'm asking is DataSet is the same thing ?

can i write :

Public static DataSet Select (string selectString)
{

"Data Source=C:\\Program Files\\Microsoft Visual
Studio\\VB98\\NWIND.MDB"
OleDbConnection cn = New OleDbConnection(connectString)
cn.Open()
dAdapter = new OleDbDataAdapter(sqlStr,myConn);
DataSet dset = new DataSet();
dAdapter.Fill(dset);
cn.Close()



return dset;

}



And run hundred’s of calls to this function even on Access db, without
worrying??

Thank you

Sharon

 

[Marina]

Cache was used to mean a local copy of the data in memory.

If you call that function 100 times, you will 100 copies of the datasets in
memory.

The .NET runtime would have no way of knowing the same exact dataset exists
elsewhere, that it was created by running the same query on the same
database. Not to mention, it would not know that you indeed want to reuse
the one instance.

 

[Marina]

If you were to use some provider specific API's, like the one for oracle,
etc, you could potentially end up with a large number of these. Which is why
they all implement the same interfaces, so you could talk to the object
without caring which specific provider it was.

It's really not hard to just cast the object. And it means you don't need If
statements everywhere, and you need just one variable for the connection,
etc. It should simplify your code if anything.