Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Should I use SqlDataSource or SqlConnection (which is better)

Posted on 2008-06-13
1
Medium Priority
?
1,231 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 1500 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

Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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