Solved

Should I use SqlDataSource or SqlConnection (which is better)

Posted on 2008-06-13
1
1,197 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 to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now