Should I use SqlDataSource or SqlConnection (which is better)

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?
DanGettelAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MikeMCSDConnect With a Mentor Commented:
A) What is the difference between SqlDataSource or SqlConnection and which one is better to use for this sort of thing and why?
SqlDataSource is for people who like to use less code, although using more
code with SqlConn is not that difficult. SqlConn is better because you have
more options.

B) Which one is better to use:
-rowsAffected = GettelDataSource.Update()  
-or rowsAffected = comm.ExecuteScalar()
They are basically the same. Programmers preference.

C) Is it possible to use ExecuteScalar() with SqlDataSource that uses a stored procedure?  If yes, how?
Not sure.

I copied this code from another post:

CREATE PROCEDURE update_user
@FirstName varchar (50),
@LastName varchar (50),
@Email varchar (50)
AS
BEGIN
UPDATE TOP (1) myTable
SET
FirstName = @FirstName,
LastName = @LastName,
Email = @Email
WHERE FirstName IS NULL AND  Type = 33
SELECT @@ROWCOUNT
END

Make sure System.Data.SqlClient  is imported.

Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
 
        Dim rowsAffected As Integer
        rowsAffected = 0
        Dim conn As SqlConnection
        Dim comm As SqlCommand
        Dim connectionString As String = _
            ConfigurationManager.ConnectionStrings( _
            "MyConnectionString").ConnectionString
        conn = New SqlConnection(connectionString)
        comm = New SqlCommand("update_user", conn)
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.Parameters.AddWithValue("@FirstName", FirstNameTextBox.Text)
        comm.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)
        comm.Parameters.AddWithValue("@Email", EmailAddressTextBox.Text)
        Try
            conn.Open()
            rowsAffected = comm.ExecuteScalar()
        Catch
            Server.Transfer("update_problems.aspx")
        Finally
            conn.Close()
        End Try
 
        If rowsAffected <> 1 Then
            Server.Transfer("update_problems.aspx")
        Else
            Server.Transfer("update_confirm.aspx")
       End If
    End Sub

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_23473826.html?sfQueryTermInfo=1+executescalar+sqldatasourc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.