During the last year, when I was first learning about vb.net and trying to take my first baby steps writing data access code (without using the drag-and-drop tools) I first latched onto using SqlDataSource. Since then I have become aware of SqlConnection, but I don t really understand the pros and cons of each.
One example of this dilemma (maybe too strong of a word) are some different aspx webforms that I have created that will take the users input and uses it to update a SQL Server 2005 database via a stored procedure. One of the things these webforms do is keep track of the number of rows updated by using sqlDataSource.Update() (ex. rowsAffected = GettelDataSource.Update()), which is something I needed it to do.
I have since learned how to do the same thing with SqlConnection. All I needed to do was add SELECT @@ROWCOUNT at the end of my stored procedure and then access that value using ExecuteScalar() (ex. rowsAffected = comm.ExecuteScalar()) in my code behind.
Since I am able to successfully accomplish my task either way I am wondering if there is a compelling reason to do it one way or the other.
So here is my 3 part question:
A) What is the difference between SqlDataSource or SqlConnection and which one is better to use for this sort of thing and why?
B) Which one is better to use:
-rowsAffected = GettelDataSource.Update()
-or rowsAffected = comm.ExecuteScalar() (when getting the SELECT @@ROWCOUNT value)
C) Is it possible to use ExecuteScalar() with SqlDataSource that uses a stored procedure? If yes, how?