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.

See inline....


Show quoteHide quote     //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.

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.

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

Show quoteHide quote

Scott,

Okay, I'll look at that.

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?

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

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.

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,

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.



Show quoteHide quote

Scott,

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

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)?

Show quoteHide quoteI 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.

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++.

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

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

Hi Jonathan,

See inline...


Show quoteHide quoteYes, 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.

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.

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

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?


Nope.

Show quoteHide quotePerhaps, 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 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!


Show quoteHide quote

Scott,

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.

Okay, that's good to know.

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

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

Show quoteHide quoteIt'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'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.

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;
}

Show quoteHide quote

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?

Show quoteHide quote

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

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.

Show quoteHide quote

vs2008 or not
Getting a subset of DataRows from a DataTable
Linq2Sql issues
DataTable foreign key
prompt user to select from recordset.