Best C# -> SQL calls.... opuinions?

Ok, so, I veto'd LINQ just because it's a larger app, (though, I will be using LINQ for objects, holy heck that's awesome).

Anyway, I'm usedto using SQLHelper for my database calls, is that even still around? Not much about it on the web. Is there a best practices for making calls to the DB from C# (syntax wise)?


Thanks.

That's a loaded question Sara.
Take for example you were specifically referring to ASP.NET 3.5 SQL methods from a Listview control.
It wouldn't matter from the control, you can do so much more with templates once the data is bound.
Did you mean something specific?
http://www.asp.net/learn/sql-videos/

btw, I found a ton of info related to SQLHelper:
http://www.google.com/search?q=SQLHelper%20&rls=com.microsoft:*:IE-ContextMenu

wisemx said:

That's a loaded question Sara.
Take for example you were specifically referring to ASP.NET 3.5 SQL methods from a Listview control.
It wouldn't matter from the control, you can do so much more with templates once the data is bound.
Did you mean something specific?
http://www.asp.net/learn/sql-videos/

btw, I found a ton of info related to SQLHelper:
http://www.google.com/search?q=SQLHelper%20&rls=com.microsoft:*:IE-ContextMenu

I suppose that is loaded. I'm talking about the best way to populate datasets using stored procedures in 3.5 for use in repeaters and
such.

I think that possibly there isn't a "best way" and most likely there are lots of different ways... but I wanted to make sure.

SaraJoRedux said:

wisemx said:

*snip*

I suppose that is loaded. I'm talking about the best way to populate datasets using stored procedures in 3.5 for use in repeaters and
such.

I think that possibly there isn't a "best way" and most likely there are lots of different ways... but I wanted to make sure.

Just a slight detour then from wisemx to http://www.asp.net/learn/data-access/

These are the best data access tutorials bar none and Scott does favour the sproc more than anything else. The only thing to be cognisant of in .NET 3.5, is the new table adapater manager component.This reduces quite a bit of tricky code, especially around transactions. Another essential .NET 3.5 feature is the ability to separate your table adapter logic into its own validation .dll, so it's very tidy. Those are the two key things to incorporate from .NET 2.0 (Scotts Tutorials - last time I checked).

I would recommend http://channel9.msdn.com/posts/funkyonex/Building-N-Tier-Applications-in-Visual-Studio-2008/ which was subsequently refined to http://msdn.microsoft.com/en-us/vbasic/cc138242.aspx

Even if you're a C# head, the principles are identical. This is the same for windows and web forms as well, you can just as easily add an object data source instead of a smart client binding source. I did a C# conversion at my old blog http://dudleigh.blogspot.com/2007/10/windows-communication-foundation.html
 

SaraJoRedux said:

wisemx said:

*snip*

I suppose that is loaded. I'm talking about the best way to populate datasets using stored procedures in 3.5 for use in repeaters and
such.

I think that possibly there isn't a "best way" and most likely there are lots of different ways... but I wanted to make sure.

It sounded like in your first post that you said you will be using objects.  If you are creating objects you might as well optimize your app and not use datasets.  If your using objects you can create a generic list of your object.  Generic lists can be bound to almost anything since it inherits from IEnumerable.  Consider the following way for retrieving data.  Create an object that represents a row in the result set you are pulling in.  Next declare a list of the object you created (example List<YourObject> collection = new List<YourObject>()).  Here is the slick and optimized part.  For the object you created, add a constructor to take in a SQLDataReader.  This way you can use the data reader to populate your instance directly in your class.  Now create your sql command linked to your stored proc.  Declare a SQLDataReader, and set the reader equal to sqlCommand.ExecuteReader().  Now you can start your while dr.Read() loop.  While looping you can use some code like this to populate your collection : collection.Add(new YourObject(dr));.  When your done just return the collection.  The function to retreive your data can even be a static function declared within your object to return a collection of that object by whatever parameters.  This way is the opposite of procedural programming, and and centralizes that data in one spot.  This way refactors are much easier.  You wil realize that you can build really sweet data layers similar to linq just by creating your own objects and using generics.  You can also use the linq extension methods on a generic list.  This way might not be the best for your situation, but my way of writing good C# to deal with data.  If you don't believe me look at all the functionality of the generic list class.  This is a different approach than you expected, but datareaders murder datatables when pulling in data. I am only telling you all this since your from NJ!

scott976 said:

SaraJoRedux said:

*snip*

It sounded like in your first post that you said you will be using objects.  If you are creating objects you might as well optimize your app and not use datasets.  If your using objects you can create a generic list of your object.  Generic lists can be bound to almost anything since it inherits from IEnumerable.  Consider the following way for retrieving data.  Create an object that represents a row in the result set you are pulling in.  Next declare a list of the object you created (example List<YourObject> collection = new List<YourObject>()).  Here is the slick and optimized part.  For the object you created, add a constructor to take in a SQLDataReader.  This way you can use the data reader to populate your instance directly in your class.  Now create your sql command linked to your stored proc.  Declare a SQLDataReader, and set the reader equal to sqlCommand.ExecuteReader().  Now you can start your while dr.Read() loop.  While looping you can use some code like this to populate your collection : collection.Add(new YourObject(dr));.  When your done just return the collection.  The function to retreive your data can even be a static function declared within your object to return a collection of that object by whatever parameters.  This way is the opposite of procedural programming, and and centralizes that data in one spot.  This way refactors are much easier.  You wil realize that you can build really sweet data layers similar to linq just by creating your own objects and using generics.  You can also use the linq extension methods on a generic list.  This way might not be the best for your situation, but my way of writing good C# to deal with data.  If you don't believe me look at all the functionality of the generic list class.  This is a different approach than you expected, but datareaders murder datatables when pulling in data. I am only telling you all this since your from NJ!

Thanks for your response.

please don't ostrasize me if this is a dumb questions but can I bind generic lists to repeaters? I use them for my collections, and iterating through but haven't tried them on objetcs.

SaraJoRedux said:

scott976 said:

*snip*

Thanks for your response.

please don't ostrasize me if this is a dumb questions but can I bind generic lists to repeaters? I use them for my collections, and iterating through but haven't tried them on objetcs.

Okay I won't OSTRASIZE you.  You sure can bind generic lists to repeaters.  The best part about binding to a repeater is that on the item created event you can access the dataitem.  The dataitem will be an instance of your object for the databound row!  Let me know if you have more questions.

scott976 said:

SaraJoRedux said:

*snip*

Okay I won't OSTRASIZE you.  You sure can bind generic lists to repeaters.  The best part about binding to a repeater is that on the item created event you can access the dataitem.  The dataitem will be an instance of your object for the databound row!  Let me know if you have more questions.

scott,

   Thanks for your help, I think this is going swimmingly.

   When I reference the list that is populating the repeater and bind it to a control in the repeater what is the syntax?

I am having trouble with: 

<%# DataBinder.Eval(Container.DataItem, "DateCreated") %>

SaraJoRedux said:

scott976 said:

*snip*

scott,

   Thanks for your help, I think this is going swimmingly.

   When I reference the list that is populating the repeater and bind it to a control in the repeater what is the syntax?

I am having trouble with: 

<%# DataBinder.Eval(Container.DataItem, "DateCreated") %>

Thats fine.. are you actually databinding it?

repeater.DataSource = blah;
repeater.DataBind();

Also, while eval is great and all that it is using reflection and doesn't really help you if you refactor properties at all.. the dataitem is the object that is getting bound to that repeateritem.. so you can actually just cast it..

<%# ((MyType)Container.DataItem).DateCreated %>

stevo_ said:

SaraJoRedux said:

*snip*

Thats fine.. are you actually databinding it?

repeater.DataSource = blah;
repeater.DataBind();

Also, while eval is great and all that it is using reflection and doesn't really help you if you refactor properties at all.. the dataitem is the object that is getting bound to that repeateritem.. so you can actually just cast it..

<%# ((MyType)Container.DataItem).DateCreated %>

yes, the error wasn't the way I was referencing it. Jumped the gun .

thanks!