Need More ADO Suggestions

[Jonathan Wood]

I'm still trying to get a handle on ASP.NET and ADO.NET.

Since there is a bit of code involved in executing a procedure, I thought
I'd stick it in it's own routine.

So I tried the following. I was able to determine this does not work
because, apparently, the reader requires that the connection remains open
while the reader is being used.

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
return null;
}

So then how would you avoid having to rewrite this code everytime you want
to execute a stored procedure?

I'm also troubled by the fact that ASP.NET does not have deterministic
finalization in the destructors. That means I can get into trouble if I
don't use using statements. That seems to make it all the harder to do what
I want to do in a clear and reliable way, and with less typing.

Thanks for any tips.

 

[Scott M.]

See inline....

I'm still trying to get a handle on ASP.NET and ADO.NET.

Since there is a bit of code involved in executing a procedure, I thought
I'd stick it in it's own routine.

So I tried the following. I was able to determine this does not work
because, apparently, the reader requires that the connection remains open
while the reader is being used.

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{

//You can get rid of the "using" statement with your command since a
command does not
//hold on to any unmanaged resources and does not need to be disposed.

using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
return null;
}

So then how would you avoid having to rewrite this code everytime you want
to execute a stored procedure?

You would capture the data you need and store it (in a DataSet or DataTable)
and return that to your calling code, not the DataReader. You are correct
that the reader must stay open as long as you are iterating through the data
(much like a firehose cursor), so you want to get in and get out as quick as
you can. ADO .NET (unlike ADO) is all about "diconnected" access to your
data. Either use a DataAdapter to automatically execute your command and
manage the opening and closing of your connection (so you won't have to
worry about using "using" on the connection obejct or loop through your
DataReader data as soon as you get it and copy it over to a DataTable. Then
just close your reader/connection and return the container.

I'm also troubled by the fact that ASP.NET does not have deterministic
finalization in the destructors. That means I can get into trouble if I
don't use using statements. That seems to make it all the harder to do
what I want to do in a clear and reliable way, and with less typing.

Using is a nice feature, but hardly required. If you simply call .close()
on your DataReader and your Connection (actually, if you set up your
DataReader properly, it will close your connection when you close the
reader) or use a DataAdapter, which opens and closes these objects for you
automatically, then "using" is irrelevant.

You can look at deterministic finalization another way too: You can get
into trouble with it, if you simply forget to destroy an object reference,
so in either case, you have to be a good programmer.

Good luck!

-Scott

 

[Jonathan Wood]

Scott,

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{

//You can get rid of the "using" statement with your command since a
command does not
//hold on to any unmanaged resources and does not need to be disposed.

Okay, I'll look at that.

You would capture the data you need and store it (in a DataSet or
DataTable) and return that to your calling code, not the DataReader. You
are correct that the reader must stay open as long as you are iterating
through the data (much like a firehose cursor), so you want to get in and
get out as quick as you can. ADO .NET (unlike ADO) is all about
"diconnected" access to your data.

Yes, but isn't a DataReader more efficient in that it doesn't load the
entire results in memory? I'm only going to display some data based on the
results. So don't I lose that advantage by using an approach that loads all
the data into RAM?

Either use a DataAdapter to automatically execute your command and manage
the opening and closing of your connection (so you won't have to worry
about using "using" on the connection obejct or loop through your
DataReader data as soon as you get it and copy it over to a DataTable.
Then just close your reader/connection and return the container.

I'm still not clear on what a DataAdapter is. I'll save your message though
and do a bit more digging.

Using is a nice feature, but hardly required. If you simply call .close()
on your DataReader and your Connection (actually, if you set up your
DataReader properly, it will close your connection when you close the
reader) or use a DataAdapter, which opens and closes these objects for you
automatically, then "using" is irrelevant.

But doesn't that approach break down if there's an exception. I'd love to
see you reproduce the code I posted without using that was rock solid and
would clean up quickly even if exceptions were thrown.

You can look at deterministic finalization another way too: You can get
into trouble with it, if you simply forget to destroy an object reference,
so in either case, you have to be a good programmer.

Well, that's garbage collection and not deterministic finalization.
Unfortunately, it just happens to appear that we can only get one or the
other. I understand the benefits of automatically disposed objects, but I
find the lack of deterministic finalization a step away from encapsulation,
the main point of OOP. And I keep running into this as a hard issue for me
in C#.

Thanks.

 

[Cowboy \(Gregory A. Beamer\)]

If you want a static routine, as you have written, pass back out some
construct. The easiest, code wise, is to pass back a DataSet. Do not pass
out the reader, even if you keep the connection open, as there are just too
many issues there.

Here is a basic pattern you can use:

public static DataSet ExecuteProcedure(string procName)
{
//Consider strong typing here
DataSet ds = new DataSet();

using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter();

//As late as possible
cmd.Connection.Open();

da.Fill(ds);

}

return ds;
}

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

 

[Jonathan Wood]

Thanks, but doesn't this kind of confirm some of my concerns? How many times
more efficient is a DataReader over a DataSet? Yes, if I want
well-constructed code, I'm better off using the less efficient method? Or am
I the only one concerned about performance these days?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

If you want a static routine, as you have written, pass back out some
construct. The easiest, code wise, is to pass back a DataSet. Do not pass
out the reader, even if you keep the connection open, as there are just
too many issues there.

Here is a basic pattern you can use:

public static DataSet ExecuteProcedure(string procName)
{
//Consider strong typing here
DataSet ds = new DataSet();

using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter();

//As late as possible
cmd.Connection.Open();

da.Fill(ds);

}

return ds;
}

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

I'm still trying to get a handle on ASP.NET and ADO.NET.

Since there is a bit of code involved in executing a procedure, I thought
I'd stick it in it's own routine.

So I tried the following. I was able to determine this does not work
because, apparently, the reader requires that the connection remains open
while the reader is being used.

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
return null;
}

So then how would you avoid having to rewrite this code everytime you
want to execute a stored procedure?

I'm also troubled by the fact that ASP.NET does not have deterministic
finalization in the destructors. That means I can get into trouble if I
don't use using statements. That seems to make it all the harder to do
what I want to do in a clear and reliable way, and with less typing.

Thanks for any tips.

 

[Scott M.]

Jonathan,

Servers have reached (and passed) the point where holding data in memory is
viable and preferable to being connected to the underlying data source all
the time. Given that distributed architectures are so prevalent these days,
the load on the database becomes more of an issue of short-lived objects in
RAM.

This is what ADO .NET is all about (disconnected access to your data). Test
after test and benchmark after benchmark have shown it to be a more
efficient way of modeling the entire application than the older connected
ways of ADO. When dealing with large amounts of data (1,000's of records),
you don't grab and store it all at once, you make several trips to the
database, fetching the data you happen to need at that time (perhaps
hundreds of records on each call). This just isn't the issue that you think
it is.

A DataAdapter is really a form of composite class. It uses information from
your connection in conjunction with 4 child command objects to perform your
CRUD actions against the database. In the case of a read operation, it
turns out that a DataAdapter uses a DataReader internally to iterate through
the data and copy it to a DataTable so that the connection can then be
closed and the data can be used in memory only. The DataSet keeps track of
any edits that are done to its underlying DataTable data and when the time
comes to propagate the changes back to the original data source the
DataAdapter simply says to the DataSet "give me your changed data only and
I'll push those changes back to the source". It does this by using the
internal command object it has that is set up for doing Creates, Updates or
Deletes (based on what has changed to the data, the DataAdapter invokes the
appropriate command to perform the correct action). The other nice thing
that the DataAdapter does is that is automatically opens and closes the
connection for you, ensuring that the connection is only open for the
shortest possible time. So you get the benefit of an automated copy from
the database / make changes to the database, with the efficiency of
DataReaders for the select operations.

Now, DataReaders have their place. For situations where a read-only,
forward-only firehose-type cursor is needed (like searching a data source
for a matching record), you can't beat a DataReader. But, even in this
case, once you've found the data you are looking for, you should copy it to
a local container, close your DataReader and your Connection.

You asked for a "rock solid" example that wouldn't break down (without
using). Here's the VB way (sorry, I'm not efficient enough to show you the
C# translation):


Public Class DataLayer
Dim con As New
SQLClient.SQLConnection(ConfigurationManager.ConnectionStrings("ASPNETDB").ToString())

Public Function GetProductData() As DataSet
Dim cmd As New SQLClient.SQLCommand(con)
cmd.CommandType = StoredProcedure
cmd.CommandText = "storedProcName"
Dim da As new SQLCLient.SQLDataAdapter(cmd, con)
Dim ds As New DataSet()
Try
da.Fill(ds)
Catch e As SQLClient.SQLException
'DataBase thrown exception handling here

Catch e As Exception
'All other exceptions handled here

Finally
'A DataAdapter will close the connection for you, but if there
was an
'exception before it gets the chance this will cover us and it
won't cause
'any problems if the connection has already been closed.
con.close()
con.Dispose()
End Try

Return ds
End Function

End Class

Within this data layer class, you could add other methods for doing other
CRUD related operations, each method reusing the connection object. This is
a good example of abstracting data logic from business logic as well.

As to non-deterministic finalization, I don't see why you think this breaks
encapsulation. "When" the object is cleaned up does not have anything to do
with object's functionality being encapsulated within it. After all, we
aren't doing any database cleanup in the Finalize method, nor the Dispose
method. These methods simply prepare the object for destruction, but even
still their functionality is encapsulated within the object.

Scott,

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{

//You can get rid of the "using" statement with your command since a
command does not
//hold on to any unmanaged resources and does not need to be
disposed.

Okay, I'll look at that.

You would capture the data you need and store it (in a DataSet or
DataTable) and return that to your calling code, not the DataReader. You
are correct that the reader must stay open as long as you are iterating
through the data (much like a firehose cursor), so you want to get in and
get out as quick as you can. ADO .NET (unlike ADO) is all about
"diconnected" access to your data.

Yes, but isn't a DataReader more efficient in that it doesn't load the
entire results in memory? I'm only going to display some data based on the
results. So don't I lose that advantage by using an approach that loads
all the data into RAM?

Either use a DataAdapter to automatically execute your command and manage
the opening and closing of your connection (so you won't have to worry
about using "using" on the connection obejct or loop through your
DataReader data as soon as you get it and copy it over to a DataTable.
Then just close your reader/connection and return the container.

I'm still not clear on what a DataAdapter is. I'll save your message
though and do a bit more digging.

Using is a nice feature, but hardly required. If you simply call
.close() on your DataReader and your Connection (actually, if you set up
your DataReader properly, it will close your connection when you close
the reader) or use a DataAdapter, which opens and closes these objects
for you automatically, then "using" is irrelevant.

But doesn't that approach break down if there's an exception. I'd love to
see you reproduce the code I posted without using that was rock solid and
would clean up quickly even if exceptions were thrown.

You can look at deterministic finalization another way too: You can get
into trouble with it, if you simply forget to destroy an object
reference, so in either case, you have to be a good programmer.

Well, that's garbage collection and not deterministic finalization.
Unfortunately, it just happens to appear that we can only get one or the
other. I understand the benefits of automatically disposed objects, but I
find the lack of deterministic finalization a step away from
encapsulation, the main point of OOP. And I keep running into this as a
hard issue for me in C#.

Thanks.

 

[Jonathan Wood]

Scott,

This is what ADO .NET is all about (disconnected access to your data).
Test after test and benchmark after benchmark have shown it to be a more
efficient way of modeling the entire application than the older connected
ways of ADO. When dealing with large amounts of data (1,000's of
records), you don't grab and store it all at once, you make several trips
to the database, fetching the data you happen to need at that time
(perhaps hundreds of records on each call). This just isn't the issue
that you think it is.

Well, I'm not certain what you think I think it is. Obviously, Microsoft
thought it worthwhile to create a DataReader class.

A DataAdapter is really a form of composite class. It uses information
from your connection in conjunction with 4 child command objects to
perform your CRUD actions against the database. In the case of a read
operation, it turns out that a DataAdapter uses a DataReader internally to
iterate through the data and copy it to a DataTable so that the connection
can then be closed and the data can be used in memory only.

So we're comparing using a DataReader with using a DataReader *AND* a
DataSet? Why wouldn't I think using just one of them would be more efficient
(assuming I am not changing the data)?

Public Class DataLayer
Dim con As New
SQLClient.SQLConnection(ConfigurationManager.ConnectionStrings("ASPNETDB").ToString())

Public Function GetProductData() As DataSet
Dim cmd As New SQLClient.SQLCommand(con)
cmd.CommandType = StoredProcedure
cmd.CommandText = "storedProcName"
Dim da As new SQLCLient.SQLDataAdapter(cmd, con)
Dim ds As New DataSet()
Try
da.Fill(ds)
Catch e As SQLClient.SQLException
'DataBase thrown exception handling here

Catch e As Exception
'All other exceptions handled here

Finally
'A DataAdapter will close the connection for you, but if there
was an
'exception before it gets the chance this will cover us and it
won't cause
'any problems if the connection has already been closed.
con.close()
con.Dispose()
End Try

Return ds
End Function

End Class

I appreciate that but, as expected, this raises questions for me.

You retrieve a connection string, allocate an SQLConnection object, allocate
several other objects, and I'm suspecting that the call to the
SqlDataAdapter constructor even connects to the database. And all of that
occurs outside of any exception handling. I realized these errors may be
unlikely, but if being "rock solid" was the goal, wouldn't they be wrapped
in a try statement?

I know that part of this is that I don't know, for example, exactly what the
SqlDataAdapter constructor does. But I'm guessing some of the items
mentioned above could potentially raise an exception.

Within this data layer class, you could add other methods for doing other
CRUD related operations, each method reusing the connection object. This
is a good example of abstracting data logic from business logic as well.

Yes, that's the approach I'm trying to take.

As to non-deterministic finalization, I don't see why you think this
breaks encapsulation. "When" the object is cleaned up does not have
anything to do with object's functionality being encapsulated within it.
After all, we aren't doing any database cleanup in the Finalize method,
nor the Dispose method. These methods simply prepare the object for
destruction, but even still their functionality is encapsulated within the
object.

I don't believe I said it "breaks" encapsulation, it reduces it. The reason
encapsulation is considered a good thing, is because it means you only have
to understand an objects public interface in order to use it. But, here, you
really need to know something more about it's implementation because you
must know whether or not it must be disposed. I never had to even think
about that in languages like C++.

I can see we won't agree on all these issues but I appreciate your input.

 

[Scott M.]

Hi Jonathan,

See inline...

Scott,


Well, I'm not certain what you think I think it is. Obviously, Microsoft
thought it worthwhile to create a DataReader class.

Yes, and as I said it has it's place - when you need forward only, read only
firehose cursor behavior. Like searching for a specific record. Sure,
that's a good use for it. But, becuase that is just one reason people use
data and more often than not, they need to modify or get it in chunks (100
records at a time, say), the DataReader won't cut it.

So we're comparing using a DataReader with using a DataReader *AND* a
DataSet? Why wouldn't I think using just one of them would be more
efficient (assuming I am not changing the data)?

I think you are comparing apples and watermelons here. Just using a
DataReader (as you did) will allocate less memory on the heap, but because
you've got to keep your DataReader open while you want to see the data, you
end up with your initial problem you posted about and you tie up resources
on the DataBase longer than needed.

Using the DataSet/DataAdapter approach you allocate more resources in the
heap, but tie up less database resources. And, the amount of memory
allocated for a DataAdapter is inconsequential (it doesn't store any data
within it). RAM is cheap these days and the GC will manage our heap
resources for us. This is the part I think you are putting to much emphasis
on. Sure, as programmers we don't want to waste resources like RAM, but on
the other hand we have these classes that have been optimized for the jobs
they do and they do them well. Couple that with the .NET Framework managing
memory for us on another thread and it (as I said before) is not really
impactfull on the application.

I appreciate that but, as expected, this raises questions for me.

You retrieve a connection string, allocate an SQLConnection object,
allocate several other objects, and I'm suspecting that the call to the
SqlDataAdapter constructor even connects to the database.

No, the connection doesn't occur until the DataAdapter fires its Fill()
method.

And all of that occurs outside of any exception handling. I realized these
errors may be unlikely, but if being "rock solid" was the goal, wouldn't
they be wrapped in a try statement?

The only thing that *could* cause an exception is the call for the
connection string (if the string didn't exist in the web.config file). The
declaration/instantiation of the other objects would not/could not fail
(allocating memory for a DataAdapter isn't going to fail under any
circumstance, for example. You'd need to use it improperly for it to fail
and that's why my Try doesn't begin until I'm actually using the DA). The
connection is not opened until the DataAdapter calls it's Fill method, hence
that's where my try statement begins. But, if you are still worried, just
move the Try up to just after the Sub begins, but it's really not necessary.
And, if you did move the opening Try up further, then what's the problem?

I know that part of this is that I don't know, for example, exactly what
the SqlDataAdapter constructor does. But I'm guessing some of the items
mentioned above could potentially raise an exception.
Nope.


Yes, that's the approach I'm trying to take.


I don't believe I said it "breaks" encapsulation, it reduces it. The
reason encapsulation is considered a good thing, is because it means you
only have to understand an objects public interface in order to use it.
But, here, you really need to know something more about it's
implementation because you must know whether or not it must be disposed. I
never had to even think about that in languages like C++.

Perhaps, but if it is a reduction, I think it's very minor. But, you did
have to destroy your object references in C++, which really is more of a
maintenance task that is not related to the purpose of the code you were
writing. By having Garbage Collection, I think we get to a more pure way of
building classes, because we only have to worry about the business logic
that class should perform and we can let the .NET Framework do the menial
management of the details.

I can see we won't agree on all these issues but I appreciate your input.

I do understand the points you are making and when I started with .NET in
2001, I had many of the same concerns. But reading the white papers and
writing the applications has shown me that using a disconnected data model
is a much better way to go and that non-deterministic finalization is also a
much more memory efficient and less labor intensive way of working.

Good luck!

 

[Jonathan Wood]

Scott,

I think you are comparing apples and watermelons here. Just using a
DataReader (as you did) will allocate less memory on the heap, but because
you've got to keep your DataReader open while you want to see the data,
you end up with your initial problem you posted about and you tie up
resources on the DataBase longer than needed.

Yes, I understand what you are saying, and that there are trade offs. I'm
only just studying how to use data objects to avoid loading entire result
sets in the ASP.NET list controls and instead loading part at a time.

Using the DataSet/DataAdapter approach you allocate more resources in the
heap, but tie up less database resources. And, the amount of memory
allocated for a DataAdapter is inconsequential (it doesn't store any data
within it). RAM is cheap these days and the GC will manage our heap
resources for us. This is the part I think you are putting to much
emphasis on. Sure, as programmers we don't want to waste resources like
RAM, but on the other hand we have these classes that have been optimized
for the jobs they do and they do them well. Couple that with the .NET
Framework managing memory for us on another thread and it (as I said
before) is not really impactfull on the application.

No, the connection doesn't occur until the DataAdapter fires its Fill()
method.

Okay, that's good to know.

The only thing that *could* cause an exception is the call for the
connection string (if the string didn't exist in the web.config file).
The declaration/instantiation of the other objects would not/could not
fail (allocating memory for a DataAdapter isn't going to fail under any
circumstance, for example.

Eh? You're saying New could never fail under any circumstance?

You'd need to use it improperly for it to fail and that's why my Try
doesn't begin until I'm actually using the DA). The connection is not
opened until the DataAdapter calls it's Fill method, hence that's where my
try statement begins. But, if you are still worried, just move the Try up
to just after the Sub begins, but it's really not necessary. And, if you
did move the opening Try up further, then what's the problem?

Then the Finally block could fail if you attempt to close and dispose of
objects that have not yet been allocated.

Perhaps, but if it is a reduction, I think it's very minor. But, you did
have to destroy your object references in C++, which really is more of a
maintenance task that is not related to the purpose of the code you were
writing. By having Garbage Collection, I think we get to a more pure way
of building classes, because we only have to worry about the business
logic that class should perform and we can let the .NET Framework do the
menial management of the details.

It's a trade-off. But most C++ objects are declared on the stack. These
don't need to be manually deleted or disposed, and I don't need to know
anything about what the destructor does. I'm having a hard time with this.

I do understand the points you are making and when I started with .NET in
2001, I had many of the same concerns. But reading the white papers and
writing the applications has shown me that using a disconnected data model
is a much better way to go and that non-deterministic finalization is also
a much more memory efficient and less labor intensive way of working.

I'm trying to understand as much of what you say as possible. As always,
software development is a system of trade offs. Although I first looked at
..NET back when it was new, I haven't been able to seriously dedicate myself
to it until now. With HTML, CSS, ASP.NET, ADO.NET, SQL, stored procedures,
C#, etc., etc., it's got to be one of the hardest changes I've made. I've
always been a careful programmer, and I know a big part of the problem for
me is knowing which constructs are best because I don't yet fully know
enough about what the underlying methods are doing.

 

[Cor Ligthert[MVP]]

Jonathan,

As soon as you use static, you are not using OOP anymore. It is the same as
using modules in VB.Net or the way VB6 did things.

You can use your procedure everywhere in the project by constructing it when
you need it. Don't think to much what happens, when I had to do that I
should have to think about how the register is using the bits, that was the
way I started with. As Scott in a way wrote, that is senseless now, trust
your managed code.

To give the answer in code.

C#
ThClassTheMethodIsIn().JonExecuteProcedure(procName);

VB.Net
ThClassTheMethodIsIn().JonExecuteProcedure(procName)

(assuming that it is in the same project or that a referense and using is
set).

I assume that you not are working on a computer from the 80' by the way
however on a modern computer.

Cor

 

[Jonathan Wood]

You can have static objects. Unfortunately, I'm not able to correlate your
response to anything I was asking. A quote of what you are responding to
would be helpful.

 

[Scott M.]

The only thing that *could* cause an exception is the call for the

Eh? You're saying New could never fail under any circumstance?

That's correct. As I say, a connection instantiation could fail if the
connection string referenced in the constructor argument can't be resolved.
But, a DataAdapter instantiation will never fail, as no actions are
performed as a result of its instantiation, just configuration is performed.
Likewise for a Command object, a DataSet, a DataTable, and many many more.

Then the Finally block could fail if you attempt to close and dispose of
objects that have not yet been allocated.

But since there is no chance that the objects won't get allocated, then
there's no chance the .close or .dispose will fail.

It's a trade-off. But most C++ objects are declared on the stack. These
don't need to be manually deleted or disposed, and I don't need to know
anything about what the destructor does. I'm having a hard time with this.

You don't need to know anything about them here either because, although we
have .Dispose and .Finalize, we don't have destructors, per se in a GC
environment. Finalize isn't going to (and shouldn't) have destructor-type
code in it because we don't know when (or if) the object is going to be
collected. So don't worry about Finalize. Dispose (on Framework types that
have it) will take care of any cleanup needed by the type. You don't need
to know the details of what it does (encapsulation). When you build your
own types, if they use unmanaged resources, you should implement the
IDisposable interface on your class and code any clean up there. Then
others who use it will see your Dispose method, but know that its probably
there for a reason and call it.

I'm trying to understand as much of what you say as possible. As always,
software development is a system of trade offs. Although I first looked at
.NET back when it was new, I haven't been able to seriously dedicate
myself to it until now. With HTML, CSS, ASP.NET, ADO.NET, SQL, stored
procedures, C#, etc., etc., it's got to be one of the hardest changes I've
made. I've always been a careful programmer, and I know a big part of the
problem for me is knowing which constructs are best because I don't yet
fully know enough about what the underlying methods are doing.

I, myself work with all those technologies you mention and I am a technology
trainer. It's been my experience that folks coming to .NET from C/C++ have
a harder time in some respects because while they know about OO concepts,
they are used to getting very involved with all aspects of what is happening
at a very low level. .NET in many respects is about abstracting all that
very low level stuff away from the programmer by providing a CLR to manage
the low level stuff for us.


-Scott

 

[Jonathan Wood]

Scott,

That's correct.

Any possibility you could elaborate? Or point me to additional info? What if
you run out of memory?

As I say, a connection instantiation could fail if the connection string
referenced in the constructor argument can't be resolved.
But, a DataAdapter instantiation will never fail, as no actions are
performed as a result of its instantiation, just configuration is
performed.
Likewise for a Command object, a DataSet, a DataTable, and many many more.

I have been rolling this issue around in my mind since we've been discussing
this. The more I think about it, some of these errors would indicate a
serious problem with the site, to the point where recovery may not even be
desirable. The case where it's most important to dispose of objects to free
resources is when the site is running without error. So my thinking may be
changing a bit on that.

You don't need to know anything about them here either because, although
we have .Dispose and .Finalize, we don't have destructors, per se in a GC
environment.

Here, I strongly disagree. I need to know if the object allocates resources
in a manner that needs to be disposed, and call Dispose if that is the case.
That was the point of my paragraph above. This requires me to know more
about what the object does in order to use it correctly.

Finalize isn't going to (and shouldn't) have destructor-type code in it
because we don't know when (or if) the object is going to be collected.

We don't know when. I'm not sure why you say we don't know *if*.

 

[Scott M.]

Scott,


Any possibility you could elaborate? Or point me to additional info? What
if you run out of memory?

Ok, well if we're going to think in those terms, then every variable that we
ever use should be in a try catch. Or, we could work in an environment
where memory is managed for us by a autonomous black box that monitors
memory and throws away what we don't need when we would need it to...Wait a
moment....we've got that.

If we were to run out of memory by simply allocating such a small amount of
memory needed for any of the objects we've been discussing then, it would
most likely not be because of the program we were writing - something else
would be the culprit (something unmanaged), in which case no matter what we
do in our program would really matter.

As I say, a connection instantiation could fail if the connection string

I have been rolling this issue around in my mind since we've been
discussing this. The more I think about it, some of these errors would
indicate a serious problem with the site, to the point where recovery may
not even be desirable. The case where it's most important to dispose of
objects to free resources is when the site is running without error. So my
thinking may be changing a bit on that.

Some of what errors? As I say, you'r not going to get errors from
instantiating these objects using correct syntax.

Here, I strongly disagree. I need to know if the object allocates
resources in a manner that needs to be disposed, and call Dispose if that
is the case. That was the point of my paragraph above. This requires me to
know more about what the object does in order to use it correctly.

Not all objects have a dispose method. If the object has a dispose method,
it is an indication to you that calling dispose may be necessary. But, as
with any object, you do need to know something about its general behavior.
In the case of the Command object (that I initially mentioned you don't have
to worry about disposing), you don't need to dispose it because you should
explicitly close your DataReader (which is the real hook into the database).
By calling the ExecuteReader method of the Command object, you are
implicitly opening the DataReader and in ADO .NET, anything that can be
opened is going to expose a .close() method, which should be called. Again,
this does not break encapsulation, because we don't need to know or
understand what happens when we call close, the object's interface gives us
the clue as to how to use it. When the DataReader object's interface is
examined closely, it becomes apparent that it is the object using the
unmanged resource, not the connection that spawned/returned it and once we
know that, we know we don't need to call the command object's Dispose
method. Again, this is not a violation of encapsulation, it's information
we get from examining the interface. Just like knowing not to call or worry
about dispose on the many objects that don't have such a member. If there
is no dispose method, then the interface gives us a clue as to inner
workings of the object. Encapsulation doesn't mean that we don't have to
know anything about an object in order to use it correctly. It says we
don't have to be concerned with how the object does what it does internally,
but we do need to know and understand the interface.

We don't know when. I'm not sure why you say we don't know *if*.

No, we don't know *if* an object will be finalized. If we have a 24/7 app.,
running on a powerful server, with ample resources, then theoretically, an
ojbect could sit on the heap indefinately becaue the GC doesn't collect
garbarge at regular inervals, it collects when it needs to collect. If
there is no need, then the objects sit on the heap and don't get finalized.
This is why we don't put clean up code in the finalizer and why finalziers
in .NET are not the same thing as destructors in C/C++.

 

[Cowboy \(Gregory A. Beamer\)]

Performance is one metric in development, not the only metric.

Let's take the DataReader. You end up with a couple of choices.

1. You keep the connection open until you use the reader (expensive)
2. You pack the reader into objects or something else

I would shy away from #1, as it will ultimately kill you. If you do #2, you
lose performance. The question is: Whose algorithm is faster, yours or
Microsoft's? Can you pack objects quicker than a DataSet? If so, you may
have more performant code, but you do so at the cost of a loss of
maintainability. Is it worth it? It depends on the application.

In general, I find maintainability costs companies far more than
performance. If I err, I will generally err on the side of maintainability.
Now, there are times you need to focus on performanc up front, but I find it
more common that you can find out if the application is below the bar using
testing tools. If so, you are fine and there is no need to squeeze out a
cycle or two. If not, you focus on the inner loop or other bits that are
called over and over again in "normal" application use.

When you look at it, performance can be the enemy of scalability,
maintainability and even security. Are you willing to throw all of these
away for a few cycles? Possibly on some applications, but, in general, the
answer is no.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************

Thanks, but doesn't this kind of confirm some of my concerns? How many
times more efficient is a DataReader over a DataSet? Yes, if I want
well-constructed code, I'm better off using the less efficient method? Or
am I the only one concerned about performance these days?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

If you want a static routine, as you have written, pass back out some
construct. The easiest, code wise, is to pass back a DataSet. Do not pass
out the reader, even if you keep the connection open, as there are just
too many issues there.

Here is a basic pattern you can use:

public static DataSet ExecuteProcedure(string procName)
{
//Consider strong typing here
DataSet ds = new DataSet();

using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter();

//As late as possible
cmd.Connection.Open();

da.Fill(ds);

}

return ds;
}

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

I'm still trying to get a handle on ASP.NET and ADO.NET.

Since there is a bit of code involved in executing a procedure, I
thought I'd stick it in it's own routine.

So I tried the following. I was able to determine this does not work
because, apparently, the reader requires that the connection remains
open while the reader is being used.

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
return null;
}

So then how would you avoid having to rewrite this code everytime you
want to execute a stored procedure?

I'm also troubled by the fact that ASP.NET does not have deterministic
finalization in the destructors. That means I can get into trouble if I
don't use using statements. That seems to make it all the harder to do
what I want to do in a clear and reliable way, and with less typing.

Thanks for any tips.

 

[Jonathan Wood]

Scott,

Ok, well if we're going to think in those terms, then every variable that
we ever use should be in a try catch. Or, we could work in an environment
where memory is managed for us by a autonomous black box that monitors
memory and throws away what we don't need when we would need it to...Wait
a moment....we've got that.

How does the second part of that address the first part? If we can run out
of memory, we could get an error. Unless I'm missing something, the only
question that remains is whether or not we want to catch and respond to such
errors. I'm not getting how GC changes that.

Some of what errors? As I say, you'r not going to get errors from
instantiating these objects using correct syntax.

I thought you just got through agreeing with me that, in fact, it is
possible to get such errors.

Nevermind. I was addressing a larger issue and then we went backwards.

Not all objects have a dispose method. If the object has a dispose
method, it is an indication to you that calling dispose may be necessary.
But, as with any object, you do need to know something about its general
behavior.

I thought I just pointed out how this is not the case with C++ objects.

Oh well.

In the case of the Command object (that I initially mentioned you don't
have to worry about disposing), you don't need to dispose it because you
should explicitly close your DataReader (which is the real hook into the
database).

And there you have it. That fact that the Command object has a Dispose
method is not enough information. You really need to know even more about
how these objects work. This is exactly the point I've been trying to make.

At any rate, I appreciate your input. But there's not much point in trying
to reach agreement on all these issues.

 

[Jonathan Wood]

Cowboy,

Let's take the DataReader. You end up with a couple of choices.

1. You keep the connection open until you use the reader (expensive)
2. You pack the reader into objects or something else

I would shy away from #1, as it will ultimately kill you. If you do #2,
you lose performance. The question is: Whose algorithm is faster, yours or
Microsoft's? Can you pack objects quicker than a DataSet? If so, you may
have more performant code, but you do so at the cost of a loss of
maintainability. Is it worth it? It depends on the application.

Your point is well taken and I'm incorporating that into my thinking. Note
that it's not that my algorithm is quicker than Microsoft's. Rather, my
algorithm might not need all the functionality that Microsoft's more generic
DataSet algorithm might employ. But, again, that's another dimension for me
to consider.

In general, I find maintainability costs companies far more than
performance. If I err, I will generally err on the side of
maintainability. Now, there are times you need to focus on performanc up
front, but I find it more common that you can find out if the application
is below the bar using testing tools. If so, you are fine and there is no
need to squeeze out a cycle or two. If not, you focus on the inner loop or
other bits that are called over and over again in "normal" application
use.

Well, before I used C++, I used C. And before that, I used assembler.
Obviously, we all have different levels at where we are comfortable
developing software. But, here, I think things are a little different
because a successful site has the potential to slow down due to heavy usage.
So, for a Website, I think performance might just be more important than
ever before even when it doesn't seem to matter while under development.
YMMV.

When you look at it, performance can be the enemy of scalability,
maintainability and even security. Are you willing to throw all of these
away for a few cycles? Possibly on some applications, but, in general, the
answer is no.

I'm not interested in losing any of those. Hopefully, if I can understand
the technology well enough, I won't have to.

 

[Cor Ligthert[MVP]]

George (and not Jonathan),

I think that we agree that if you don't understand the principle of the
througput through the smallest part of a pipe, then optimalizing your
product is very hard to understand.

The smallest througput part with developing is always changing.

In past it was the processing area, however that was very quick changed to
the area to save and get the data (I am express not writting about punch
card, hard disk etc). Then it became the network when we more and more were
sharing our work.

This is still going on all the time. I think that it is at the moment again
the data saving/retrieving area that is the bottleneck while for some kind
of solutions the screen. (By instance the by you called datareader, however
for sure not the internal part of that, but the way it is retrieving the
data from its source. But that is a part that a single person can not
influence anymore, there are so many possiblilities that can influence that,
that there should be a common way to let that going, not based on the most
optimized for one situation at one time by one user).

Let us therefore stop talking about how to optimize the processing of the
internal memory.

This not meant for the areas where .Net probably never will be the solution
(The processing of a watch by instance).

Just one of my idea's

Cor

Performance is one metric in development, not the only metric.

Let's take the DataReader. You end up with a couple of choices.

1. You keep the connection open until you use the reader (expensive)
2. You pack the reader into objects or something else

I would shy away from #1, as it will ultimately kill you. If you do #2,
you lose performance. The question is: Whose algorithm is faster, yours or
Microsoft's? Can you pack objects quicker than a DataSet? If so, you may
have more performant code, but you do so at the cost of a loss of
maintainability. Is it worth it? It depends on the application.

In general, I find maintainability costs companies far more than
performance. If I err, I will generally err on the side of
maintainability. Now, there are times you need to focus on performanc up
front, but I find it more common that you can find out if the application
is below the bar using testing tools. If so, you are fine and there is no
need to squeeze out a cycle or two. If not, you focus on the inner loop or
other bits that are called over and over again in "normal" application
use.

When you look at it, performance can be the enemy of scalability,
maintainability and even security. Are you willing to throw all of these
away for a few cycles? Possibly on some applications, but, in general, the
answer is no.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************

Thanks, but doesn't this kind of confirm some of my concerns? How many
times more efficient is a DataReader over a DataSet? Yes, if I want
well-constructed code, I'm better off using the less efficient method? Or
am I the only one concerned about performance these days?

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

If you want a static routine, as you have written, pass back out some
construct. The easiest, code wise, is to pass back a DataSet. Do not
pass out the reader, even if you keep the connection open, as there are
just too many issues there.

Here is a basic pattern you can use:

public static DataSet ExecuteProcedure(string procName)
{
//Consider strong typing here
DataSet ds = new DataSet();

using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter();

//As late as possible
cmd.Connection.Open();

da.Fill(ds);

}

return ds;
}

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box! |
*************************************************
I'm still trying to get a handle on ASP.NET and ADO.NET.

Since there is a bit of code involved in executing a procedure, I
thought I'd stick it in it's own routine.

So I tried the following. I was able to determine this does not work
because, apparently, the reader requires that the connection remains
open while the reader is being used.

public static SqlDataReader ExecuteProcedure(string procName)
{
string connStr =
ConfigurationManager.ConnectionStrings["ASPNETDB"].ToString();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(procName, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
return reader;
}
}
return null;
}

So then how would you avoid having to rewrite this code everytime you
want to execute a stored procedure?

I'm also troubled by the fact that ASP.NET does not have deterministic
finalization in the destructors. That means I can get into trouble if I
don't use using statements. That seems to make it all the harder to do
what I want to do in a clear and reliable way, and with less typing.

Thanks for any tips.

 

[Scott M.]

Scott,


How does the second part of that address the first part? If we can run out
of memory, we could get an error. Unless I'm missing something, the only
question that remains is whether or not we want to catch and respond to
such errors. I'm not getting how GC changes that.

But, my point is that we would NOT run out of memory like is possible in
C++. We have a Garbage Collector and its job is to ensure that we don't run
out of memory. That's why we don't have to worry about simply instantiating
an object causing us to run out of memory. Since running out of memory is
not going to happen as a result of instantiating an object, there's no need
to wrap instantiations that do not execute operations in any kind of
Try..Catch.

I thought you just got through agreeing with me that, in fact, it is
possible to get such errors.

No, I was making the point that because we work in an environment that has a
GC, this is not an issue that requires us to waste any brain power on it.

Nevermind. I was addressing a larger issue and then we went backwards.


I thought I just pointed out how this is not the case with C++ objects.

Maybe, but my point is that this isn't C++. It is a managed environment.
Because C/C++ developers have historically worked at a much lower level than
other languages, they are used to having to worry about managing machine
resources themselves manually. In .NET, these tasks become trivial to the
developer. We let the CLR take care of such things. It's ok to do so
because the managed environment of the CLR manages these duties very well.
I think this is really the issue you are facing here. You are having
trouble letting go of the chores of the C++ programmer and embrace that in
..NET we work a bit differently.

Oh well.


And there you have it. That fact that the Command object has a Dispose
method is not enough information. You really need to know even more about
how these objects work. This is exactly the point I've been trying to
make.

Well, you've said that this breaks encapsulation and I disagree with that.
Of course we have to learn about the classes we intend to use. We do not
need to know about the internal workings of the class, we just need to
examine the interfaces of the classes involved.

At any rate, I appreciate your input. But there's not much point in trying
to reach agreement on all these issues.

Good luck Jonathan!

 

[Jonathan Wood]

Scott,

But, my point is that we would NOT run out of memory like is possible in
C++. We have a Garbage Collector and its job is to ensure that we don't
run out of memory.

GC will optimize the heap, which prolongs running out of memory as long as
possible. I'm sorry, but you are mistaken if you think GC can prevent
running out of memory.

Well, you've said that this breaks encapsulation and I disagree with that.
Of course we have to learn about the classes we intend to use. We do not
need to know about the internal workings of the class, we just need to
examine the interfaces of the classes involved.

I just got through describing that the very example you gave shows that we
not only need to know something about whether or not the class maintains
resources that must be cleaned up, but now we also need to know about which
classes might clean up other classes. I don't know how you define
encapsulation but to me it's about only needing to understand a class's
interface to use it, and not anything about it's implementation. That's more
than one clear example of how the lack of DF is a step in the other
direction.

Again, I understand you may never agree to this but I've been doing this
long enough to understand the benefits of OOP and encapsulation. And, here,
learning this stuff is harder because I am not gaining all the traditional
benefits of OOP and encapsulation.

It's just my opinion. YMMV.