Solved

Should I use SqlDataSource or SqlConnection (which is better)

Posted on 2008-06-13
1
1,224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 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