Solved

Should I use SqlDataSource or SqlConnection (which is better)

Posted on 2008-06-13
1
1,204 Views
Last Modified: 2013-11-07
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?
0
Comment
Question by:DanGettel
1 Comment
 
LVL 16

Accepted Solution

by:
MikeMCSD earned 500 total points
ID: 21783411
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

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question