Best way of read/receive and display data in label controls 1

[cesark]

Hi,

I am displaying a details page in which the details of an offer selected by the user in the previous page is showed. To retrieve the data from the DB and then assign the data to the label controls I use output parameters, but now I am considering using a DataSet or a DataReader instead.

Until now I use this system:

  Dim CmdOffer As New SqlCommand("offer_detail", strConnection)
  CmdOffer.CommandType = CommandType.StoredProcedure

  CmdOffer.Parameters.Add(New SqlParameter("@Offer_num", SqlDbType.bigint))
  CmdOffer.Parameters("@Offer_num").Value = Request.QueryString("Id")
  
  CmdOffer.Parameters.Add(New SqlParameter("@TheDate", SqlDbType.DateTime, 8))
  CmdOffer.Parameters("@TheDate").Direction = ParameterDirection.Output

  CmdOffer.Parameters.Add(New SqlParameter("@Offer_title", SqlDbType.varchar, 100))
  CmdOffer.Parameters("@Offer_title").Direction = ParameterDirection.Output 
 
  CmdOffer.Parameters.Add(New SqlParameter("@Reference", SqlDbType.varchar, 50))
  CmdOffer.Parameters("@Reference").Direction = ParameterDirection.Output 

  CmdOffer.Parameters.Add(New SqlParameter("@Company_name", SqlDbType.varchar, 100))
  CmdOffer.Parameters("@Company_name").Direction = ParameterDirection.Output 

  CmdOffer.Parameters.Add(New SqlParameter("@Web_page", SqlDbType.varchar, 100))
  CmdOffer.Parameters("@Web_page").Direction = ParameterDirection.Output 

  CmdOffer.Parameters.Add(New SqlParameter("@Company_State", SqlDbType.varchar, 50))
  CmdOffer.Parameters("@Company_State").Direction = ParameterDirection.Output 

  CmdOffer.Parameters.Add(New SqlParameter("@City_State", SqlDbType.varchar, 100))
  CmdOffer.Parameters("@City_State").Direction = ParameterDirection.Output 

  CmdOffer.Parameters.Add(New SqlParameter("@Activity_description", SqlDbType.varchar, 500))
  CmdOffer.Parameters("@Activity_description").Direction = ParameterDirection.Output 
  
  strConnection.Open()
   CmdOffer.ExecuteNonQuery
 
    [b]title.Text() = CmdOffer.Parameters("@Offer_title").Value 
    TheDate.Text() = CmdOffer.Parameters("@TheDate").Value[/b]
	
	If Not [b]CmdOffer.Parameters("@Reference").Value Is DbNull.Value[/b] Then 
	 [b]Reference.Text() = CmdOffer.Parameters("@Reference").Value[/b]
	Else
	 ref.Visible = False
	End If
	
	[b]Company_name.Text() = CmdOffer.Parameters("@Company_name").Value[/b]
	
	If Not [b]CmdOffer.Parameters("@Web_page").Value Is DbNull.Value[/b] Then
	 [b]url_company.Text() = "[URL unfurl="true"]http://www."[/URL] + CmdOffer.Parameters("@Web_page").Value[/b]
	 url_company.NavigateUrl = url_company.Text() 
	Else
	 more_inf1.Visible = False  
	End If
	
	[b]state_company.Text() = CmdOffer.Parameters("@Company_State").Value
            city_company.Text() = CmdOffer.Parameters("@City_State").Value
            activ.Text() = CmdOffer.Parameters("@Activity_description").Value[/b]

  strConnection.Close()

And now I want to use this system:

  Dim CmdOffer As New SqlCommand("offer_detail", strConnection)
  CmdOffer.CommandType = CommandType.StoredProcedure

  CmdOffer.Parameters.Add(New SqlParameter("@Offer_num", SqlDbType.bigint))
  CmdOffer.Parameters("@Offer_num").Value = Request.QueryString("Id")

  
  Dim add_data_of As New SqlDataAdapter()
  add_data_of.SelectCommand = CmdOffer
 
  Dim data_of As New DataSet
  add_data_of.Fill(data_of, "Offers")
 
    [b]title.Text() = data_of.Tables("Offers").rows(0).Item("Offer_title").ToString()  
    theDate.Text() = data_of.Tables("Offers").rows(0).Item("theDate").ToString()[/b]
	
	If Not [b]data_of.Tables("Offers").rows(0).Item("Reference")[/b] Is DbNull.Value Then 
	 [b]Reference.Text() = data_of.Tables("Offers").rows(0).Item("Reference").ToString()[/b]
	Else
	 ref.Visible = False
	End If
	
	[b]Company_name.Text() = data_of.Tables("Offers").rows(0).Item("Company_name").ToString()[/b]
	
	If Not [b]data_of.Tables("Offers").rows(0).Item("Web_page")[/b] Is DbNull.Value Then
	 [b]url_company.Text() = "[URL unfurl="true"]http://www."[/URL] + data_of.Tables("Offers").rows(0).Item("Web_page").ToString()[/b]
	 url_company.NavigateUrl = url_company.Text() 
	Else
	 more_inf1.Visible = False  
	End If
	
	[b]state_company.Text() = data_of.Tables("Offers").rows(0).Item("State_name").ToString()
	city_company.Text() = data_of.Tables("Offers").rows(0).Item("City_name").ToString()
    activ.Text() = data_of.Tables("Offers").rows(0).Item("Activity").ToString()[/b]

1. Which system is better? (first example or second one)
2. There is another more effective way to retrieve the data from the DataSet in the second example?
3. It would be better to use DataReader instead of DataSet to retrieve and bind the data to the controls? If so, how can I do it?

Thank you,
Cesar

 

[ca8msm]

To be honest you are the best person to answer this question. As we don't know the data (and can't try it for ourselves) it would be best if you did both examples and (by using page tracing) see how long each page takes to load and how much memory/file size is used in each version.

Also, read faq855-5662 if you would like to use a DataReader.

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

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[dragonwell]

To me, your first example makes the most sense as you are reading a single "record" form the database. BTW, You can close your connection immediately after executing the command and still have your parameter values filled with the output parameters.

The dataset solution would be a valid if you had multiple rows of data to display; to fill a dataset with one table containing one row seems like overkill.

MS says the most efficient way to retrive a single record is through output parameters.


Share your knowledge! -

http://www.howisitdone.com

 

[cesark]

Hi dragonwell

Ok. And what about using a DataReader or another alternative?

I ask this because when I write the Stored Procedures inside Sql Server to return the results, I have to define always output parameters, and I was thinking that perhaps it wouldn' t be necessary to define them and only write a simple 'Select' statement in the DB, then ASP.NET app could receive them faster with some class or similar.

But if the system I use normally to return single records (with output parameters) is the best way to do it, COOL! because I have many pages that uses this system.

Thank you

 

[dragonwell]

In terms of performance, the output parameter will be your best bet according to MS, with the DataReader a close second place.

However, there is always the performance/maintainability trade-off.

Decide which solution would require the most code changes (both in .NET and sql) if you needed to add or remove a column in your table.

If your database schema is set in stone and not likely to change frequently, you do not need to change the data-access code too much so maintainence is not a huge issue. But what if you are developing a system from scratch and want to evolve your database design gradually?

I use an ORMappper, and it only takes a few seconds to add or remove columns from my tables without worrying about breaking my code. I only need to change three things: My table, the business object (add a property), and the xml mapping file. I can do this and not worry about introducing bugs and having to change code all over the place. This lets the database evolve naturally and easily along with the program.

Ok, that's probably a little too much info... I guess I'm in a sort of philosphical mood today. (must be trying to avoid work )



Share your knowledge! -

http://www.howisitdone.com

 

[cesark]

All your extra information is wellcomed , and it' s a good thing trying to avoid work on Friday.

Until now output parameters works fine for single records, and if you say (and Microsoft) that it has the best performance, I will continue using them.

Thank you!
Have a nice weekend

 

[RTomes]

The first one I would stay away from bigtime.
You will run into maintinace nightmares with the first one.
You also have a hard time adding an a data access layer in the future with the first one.
The speed diference is negligable in a few milliseconds difference. Maintainablity is should exced a neglegable concern of speed unless you are having major issues with speed. You can check speed with now.ticks.tostring.

Keeping it Simple