Function to Return Datareader? 2

[DSect]

Hello - I'm at an interim point in my learning, here.. I'm starting to understand "objects" and OOP and their roles. In fact, I'm finding out more and more that what I need is some kind of Data Manager or Data Access Class.

So - I tried something like this in it's simplest form. I wanted to make a "Function" that returns a Datareader.

My purpose was to make a reuseable function to fill forms on my site - this one is for account maintenance..

I will post the code and you guys can see what I was doing:

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ' Get Customer_Id from Session / DB / etc.
        ' Fake it for testing:
        Dim Customer_Id As Integer = 4
        ' Check for Querystring - See if it's valid stuff
        ' Then do an action based on "action" parameter
        ' Actions correspond to forms - edit contact info, edit addresses, etc..
        If Len(Request.QueryString("action")) < 1 Then
            ' No Action Specified - Do Nothing
        Else
            Select Case Request.QueryString("action")
                Case "editcontact"
                    ' Edit Contact Information
                    ' Populate form from database
                    Do While FillForm(Customer_Id, Request.QueryString("action")).Read
                        ' ^^^^ This is where I know I messed up!!! ^^^^
                        ' Because I can't call Read without the ARGS
                        ' And if I Dim dr as SQLDatareader and do:
                        ' dr = FillForm(Customer_Id, Request.QueryString("action"))
                        ' It still will not read..
                    Loop
            End Select
        End If
    End Sub

    Function FillForm(ByVal cust_id As Integer, ByVal action As String) As SqlDataReader
        Dim con As New SqlConnection(ConfigurationSettings.AppSettings("connStr"))
        Dim cmd As New SqlCommand("sp_Management", con)
        cmd.CommandType = CommandType.StoredProcedure
        With cmd
            .Parameters.Add("@Customer_Id", SqlDbType.Int).Value = cust_id
            .Parameters.Add("@Action", SqlDbType.VarChar, 50).Value = action
        End With
        ' Start Trap for errors here
        con.Open()
        ' We're trying to simply SELECT from DB to fill a form.
        ' Update will be handled somewhere else using parameters.
        FillForm = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        ' ******************
        ' * Object Cleanup *
        ' ******************
        If con.State.Open Then
            con.Close()
        End If
        If Not IsNothing(con) Then
            con.Dispose()
            con = Nothing
        End If
        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
    End Function

I don't even know where to start w/ questions.

I guess my MAIN one at this time is:

Why does it say my FormFill DataReader is closed when I try it's READ method?

Anyways - You guys probably see where I'm going with this - I want to learn how to make a reuseable data "class" to use within a value object I made. I was just messing around and tried to pass a DR via function to see what happens and I failed miserably..

 

[Veep]

Here's a simple example in C# (not much difference). Create a class called DataHelp (or whatever you want). In that class create a method(function) that returns a DataReader something like this:

public SqlDataReader myreader(int cust_id,string action)
		{
			string strConn = ConfigurationSettings.AppSettings["connStr"];
			SqlConnection conn = new SqlConnection(strConn);
			conn.Open();
//SqlCommand and Parameter stuff here.			
			return cmd.ExecuteReader(CommandBehavior.CloseConnection);
		}

Then on your page when you want to access it:

DataHelp dh = new DataHelp();
SqlDataReader reader = dh.myreader(cust_id,"yourAction");
if(reader.HasRows)
			{
				while(reader.Read())
				{
				  //retrieve values here
	             //txtCustName.text = reader["CustomerName"].ToString(); 
				}

			}
			else
			{
				//do something else
			}

There are a variety of ways to do this stuff. This is only one very simple example.

 

[Custom24]

Basically, it looks to me like you were almost there.

The CommandBehavior.CloseConnection is very important using this approach - it means that the connection to the db will be closed when the close method is called on the data reader.

So you must ensure that in the code which uses the datareader function, that you close it when you are finished. For this reason, you need to dim the datareader and initialise it separately, as you tried in your commented out code.

However, the reason that your data reader will not read is because in your function, you are closing the connection anyway.

Remove the following code.

        If con.State.Open Then
            con.Close()
        End If
        If Not IsNothing(con) Then
            con.Dispose()
            con = Nothing
        End If

HTH

Mark

 

[DSect]

Thanks Veep -

I actually found exactly what I was looking for:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

Microsoft Patterns and Practices: Data Access

This is what I have been looking for but didn't really look for "patterns". That MSDN section has a TON of patterns that are useful!

Anyways - Thanks!

I'm not even going to mee with my function - I'm going to go back to using my Customer Class and create the Data Access class based on the MS stuff..

I didn't want to put the data code in the Customer Value Object - and now I don't have to..

It's tough figuring this out, but I know it didn't feel "abstract" enough after loading up my Customer class w/ DB code!

Anyways! Thanks again!

 

[DSect]

Custom24:

Thanks for you info - Yes - I was able to get my thing working after doing what you said.

Dim dr As SqlDataReader = FillForm(Customer_Id,Request.QueryString("action"))
  Do While dr.Read
    Response.Write(dr.Item("Title"))
  Loop

That worked to get me the stuff I need..

Here's my question - I commented out my object cleanup block to make this work, as per your instructions.

I'm kinda newbie so I don't know WHY that made it work?>?>

Here's what I don't understand:
I'm doing this: FillForm = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Doesn't that "close" the active connection anyway? If I close and dispose of it right after I do that (the code I commented out), how is it different?
Do I really need an open connection to use the DataReader once it has been filled or am I getting this confused w/ another data object?

The REAL and final question is: What do I do for Garbage Cleanup? I'm killing my COMMAND object in the function - is it safe to say that if I DISPOSE and DESTROY the DataReader after use in the Page Load Sub, then I am doing the best garbage cleanup I can do?

Thanks for the info - I'm kind new at "fancy" stuff and I'm learning this all on my own so I want to do it right from the beginning--- Then I stumbled upon true OOP programming concepts and I'm starting to implement them.. This is a very basic step to understand how an object can pass an entire object to something.. I'm really excited at this point as you can imagine the paradigm shift I'm experiencing!!

 

[DSect]

PS - Custom, I just Reread your post more clearly -

The connection MUST be open for the datareader to work and killing the datareader will KILL any connection object used to open it. I totally understand now.

I think there's another Data Something that can work with a closed connection and that's where I got confused.

Thanks again- Sorry for the misread as I am kinda getting really antsy when things that I try work 99% of thw way and then I really freak when they actually work 100%.

I think I have answered my question above about "Am I doing garbage cleanup the best I can"/.. Looks like I am!@!

Thanks again everyone! I'm moving ahead into some cool stuff and I can't wait until I digest some of these advanced patterns!!!!!!!! AHHHH YEAH!