Monday, April 14, 2008 7:27 PM ryansjedi

Data Access - The SqlDataSource Part 2

In our last post we showed what happened when you drag and drop a table onto the design surface, a fully functional (including sorting, paging, updating and deleting) SqlDataSource with T-SQL populating the command fields. While this scenario makes for a slick demo, it's not ideal.  The reason I say it's not ideal is that mixes data logic right into the aspx markup.  This makes making changes in the underlying data routines risky as the markup and data structures tightly coupled.  One way we can reduce these risks is by replacing the T-SQL statements that were auto generated for us, with stored procedures.  By replacing the T-SQL with stored procedures we can alter our database logic without having to touch the aspx markup.  The first task in this is to create the stored procedures.  Here's the SQL for the stored procedures:

CREATE PROCEDURE dbo.DeleteCustomer

@customerid varchar(10)

AS

delete from customers where customerid = @customerid

RETURN

CREATE PROCEDURE dbo.InsertCustomer

@CustomerID varchar(50),

@CompanyName varchar(50),

@ContactName varchar(50),

@ContactTitle varchar(50),

@Address varchar(50),

@City varchar(50),

@Region varchar(50),

@PostalCode varchar(50),

@Country varchar(50),

@Phone varchar(50),

@Fax varchar(50)

AS

INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax])

VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)

RETURN

CREATE PROCEDURE dbo.SelectAllCustomers

AS

Select * from customers

RETURN

CREATE PROCEDURE dbo.UpdateCustomer

@CustomerID varchar(50),

@CompanyName varchar(50),

@ContactName varchar(50),

@ContactTitle varchar(50),

@Address varchar(50),

@City varchar(50),

@Region varchar(50),

@PostalCode varchar(50),

@Country varchar(50),

@Phone varchar(50),

@Fax varchar(50)

AS

UPDATE [Customers] SET

[CompanyName] = @CompanyName, [ContactName] = @ContactName,

[ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City,

[Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country,

[Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @CustomerID

RETURN

Now that we have our stored procedures created, we can use the design surface to update our SqlDataSource control to use the procs.  So... go to the design view and click the smart tab thingy on the SqlDataSource control, and select configure data source. We'll keep our connection string intact so hit 'next'.  From the next page which should read Configure the Select Statement, select the radio button that reads "Specify a custom SQL statement or stored procedure and click 'next'. Now click the stored procedure radio button and use the pulldown to select the stored procedure named: SelectAllCustomers. Don't hit 'next' yet, use the tabs at the top to do the same for the Update, Delete and Insert tabs, using the appropriate stored procedure.  Now click 'next' and then finish.  Let's take a look at what the markup of our aspx page looks like now:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString1 %>"

DeleteCommand="DeleteCustomer"

InsertCommand="InsertCustomer"

ProviderName="<%$ ConnectionStrings:NORTHWNDConnectionString1.ProviderName %>"

SelectCommand="SelectAllCustomers"

UpdateCommand="UpdateCustomer" DeleteCommandType="StoredProcedure" InsertCommandType="StoredProcedure" SelectCommandType="StoredProcedure"

UpdateCommandType="StoredProcedure">

<DeleteParameters>

<asp:Parameter Name="CustomerID" Type="String" />

</DeleteParameters>

<InsertParameters>

<asp:Parameter Name="CustomerID" Type="String" />

<asp:Parameter Name="CompanyName" Type="String" />

<asp:Parameter Name="ContactName" Type="String" />

<asp:Parameter Name="ContactTitle" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="Region" Type="String" />

<asp:Parameter Name="PostalCode" Type="String" />

<asp:Parameter Name="Country" Type="String" />

<asp:Parameter Name="Phone" Type="String" />

<asp:Parameter Name="Fax" Type="String" />

</InsertParameters>

<UpdateParameters>

<asp:Parameter Name="CustomerID" Type="String" />

<asp:Parameter Name="CompanyName" Type="String" />

<asp:Parameter Name="ContactName" Type="String" />

<asp:Parameter Name="ContactTitle" Type="String" />

<asp:Parameter Name="Address" Type="String" />

<asp:Parameter Name="City" Type="String" />

<asp:Parameter Name="Region" Type="String" />

<asp:Parameter Name="PostalCode" Type="String" />

<asp:Parameter Name="Country" Type="String" />

<asp:Parameter Name="Phone" Type="String" />

<asp:Parameter Name="Fax" Type="String" />

</UpdateParameters>

</asp:SqlDataSource>

Again it's a little out of order, but notice how our SelectCommand, DeleteCommand, UpdateCommand and InsertCommands have been replaced with their respective stored procedure names.  Additionally a new CommandType parameter is paired with each Command, in our case it reads "StoredProcedure".

And now the SqlDataSource is completely up and running again, having replaced the T-SQL with stored procedures. 

There are a couple of advantages to using stored procedures with the SqlDataSource.  The first being that now you can change the logic for your CRUD statements in the stored procedures, rather than having to edit the aspx markup.  This is a big step forward in promoting the principal of loosly coupling our presentation layer (HTML and the aspx markup) and business and data layers.  However it's still coupled pretty tightly as we have the name of our stored procedure in our markup.  So, if we needed to change the name of the stored procedure in the database, we'll also have to modify it in the aspx markup.  Ideally we'd have another layer seperating the database and the aspx markup...which is where some other data source controls come in.  But for now we've at least made it easier to modify the logic.

In the next post I'll delve into the caching properties of the SqlDataSource and a summary of the pro's and con's of the SqlDataSource.

Further Reading:

Stored Procedures - Wikipedia

Filed under: , ,

Comments

# re: Data Access - The SqlDataSource Part 2

Sunday, April 20, 2008 10:18 AM by Roger Lipscombe

Thanks Ryan, this is good stuff.

The whole "which data access technique do I use?" question is one of the stumbling blocks I've found when trying to learn ASP.NET.

On one end of the spectrum, you've got the SqlDataSource stuff, which leaves you too tightly-coupled to the database. On the other end, you've got all that data-access-layer stuff, which always strikes me as way too much typing.

Maybe I just need an O/R mapper.

Are you going to demonstrate the pros and cons of using a full-blown separated presentation/business-logic/data-access layer?

What about other stuff like NHibernate?

# re: Data Access - The SqlDataSource Part 2

Monday, April 21, 2008 11:26 AM by ryansjedi

Hi Roger!

I hear what you are saying about too much typing...I'm with ya on that.  However in the long run it will provide a more maintainable application.  Additionally there are tools to help you out there, code generation tools are making huge strides each year it seems.  (I smell another series...)

With the rest of this series, it's my intent to at least initially talk about only about the different datasource controls, and when we get to the ObjectDataSource, I'll definately be talking about the pains of all that typing.  I'll be taking the minimalist approaches for clarity.  At the end of it I'll be discussing the pros and cons of all the methods.

I'll be wrapping up SqlDataSource over the next couple of days and then diving into the ObjectDataSource.

One topic I will definately want to cover in the near future are data access frameworks like NHibernate, Subsonic, LLBLGen and CSLA.

I've used all of those now except NHibernate so I need to do some tinkering.

O/R mappers are great and can bring a lot to the table but as with most technology, it's all about the right tool for the job.

Thanks for staying tuned!

Leave a Comment

(required) 
(required) 
(optional)
(required)