Ryan Smith

Ok so I made a mistake when I kicked off this series...I picked Sql Server Express as our database for all the logical reasons...however I failed to remember that Sql Server Express doesn't do Sql Cache Dependency...so instead of retro fitting my series to use Sql Server 2005 I'll punt and just say (for now) that the Sql Data Source can cache the results of your queries to improve performance and can even take advantage of cache dependencies (provided you are using Sql Server 2005).

So I am going to leave caching out of the series for the moment.  I will come back to it at the end of the series and show it in action.

Please be gentle with me...I'm not a professional blogger. :-)

Ryan

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:

@customerid varchar(10)

RETURN

@CustomerID varchar(50),

@ContactName varchar(50),

@Address varchar(50),

@Region varchar(50),

@Country varchar(50),

@Fax varchar(50)

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

RETURN

RETURN

@CustomerID varchar(50),

@ContactName varchar(50),

@Address varchar(50),

@Region varchar(50),

@Country varchar(50),

@Fax varchar(50)

UPDATE [Customers] SET

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

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

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

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:

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

InsertCommand="InsertCustomer"

SelectCommand="SelectAllCustomers"

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

Welcome back!

If you are following along from the last post, you'll notice that two controls were automatically created on our page when we dropped the Customer table onto the page. The first control is called a GridView.  The GridView control is used to display data in much the same way that the SQL manager shows you results when you have run a query directly out of it (that is row by row).  The GridView is very powerful and flexible; we'll go into the GridView control a little deeper in later posts but for now let's focus on how we are getting data from the database to the page. 

The SqlDataSource serves as the plumbing that connects the web page to the database.  Let's take a look at how Visual Studio created our control and talk a little bit about the parameters it set up for us. 

Here's the code it created for us:

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString1 %>"
            DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @CustomerID"
            InsertCommand="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)"
            ProviderName="<%$ ConnectionStrings:NORTHWNDConnectionString1.ProviderName %>"
            SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region],

[PostalCode], [Country], [Phone], [Fax] FROM [Customers]"
            UpdateCommand="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">
            <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="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" />
                <asp:Parameter Name="CustomerID" Type="String" />
            </UpdateParameters>
        </asp:SqlDataSource>

There's a lot in there right?  Imagine if you had to type all that out!

So what you've got there is a control that will handle all of your CRUD (Create, Retrieve, Update and Delete) operations for the Customer table. Let's go through the properties and parameters a little. 

They're a little out of order but that's okay.

The first one is pretty self-explanatory, that's the ID.  It's just a unique name to identify the control.  We can use this ID in our code behind page if we need to make any programatical changes to our control.

The next one is really important: it's the connection string that resides in our Web.Config and was put there by Visual Studio when we dropped it on the page.  Notice that it's surrounded by the ASP markers, the reason for this is that the ConnectionStrings: object is a little bit of design time magic.  It has a reference to the ConnectionsStrings XML node in the Web.Config file.  There's a related one called ProviderName, this one dictates the type of database to be used, according to our Web.Config file.  Our sample will be using System.Data.SqlClient meaning it will be using Sql Server or Sql Server Express.   However you could conceivably use any database if there was an available provider.

Now we'll move on to the commands.  Visual Studio was nice enough to create T-Sql statements for each of the commands.  The commands created are pretty basic as you can see as they are just CRUD statements.

Take a look at the SelectCommand, it's very a standard Sql select statement, and Visual Studio was even so considerate to surround the fields with []'s in case your field's had spaces etc in them. Just because Visual Studio created T-Sql doesn't mean we're stuck with T-Sql, we can easily use Stored Procedures here, we'll show those off in the next post.

For each of the other Command statements, parameters have be created and integrated into the T-Sql. Let's take a look at them a little deeper.

Check out the DeleteCommand.  When you are deleting a record, you need a way to identify the record you want to delete.  By default Visual Studio uses the table's primary key, in this case it's CustomerID. This is almost enough to get the job done, however since these are Sql Parameters we need to know a little more about the parameter we are passing.  This is where the DeleteParameters section comes in.  In this section we define what type of data the parameter is.  In this case it's a string.

The rest of commands and parameters are more of the same, standard Sql statements and parameters, so I won't bore you by going through each of the others.

When a data control is bound to this SqlDataSource, these commands will be executed when requested by the data control.

If you take a few moments to examine the properties of the SqlDataSource control in Visual Studio, you'll find some interesting stuff.  We'll go into those in a future post and then finish with a look back to highlight the pros and cons of the SqlDataSource.  But now I'm off to get ready for the next post where we'll convert these T-sql statements into stored procedures.

Further Reading:

W3Schools Sql Tutorial

SqlDataSource Web Server Control Overview - Microsoft

Ok so you have Visual Studio or Web Developer Express installed, have installed SQL Server Express, and have downloaded and run the SQL Samples from the previous post.

Lets create our Web Project, select Create Project and select either a C# or a VB.Net ASP.NET Web Application, you can create this project anywhere you want, for reference I'm just putting mine locally and using the built in web server.

Now we'll add the Northwinds Database to our project.  Right click on the App_Data and select Add->Existing Item and navigate to the C:\SQL Server 2000 Sample Databases folder or wherever you installed them and selecting Northwnd.mdf and select 'Add'.  It should now appear under your App_Data directory.

Now let's test out connection by doing one of the traditional 'Whizz Bang' marketing demos, where we'll drop a table from Northwinds right on to the page and kapow data displayed without writing a single line of code.

First, open up your default.aspx page and switch to design view.

Next make sure you have the Server Explorer window open, if it's not open you can open it by selecting View->Server Explorer.

Next in the Server Explorer window, expand the Northwind Data Connection, then expand the the Tables folder. 

Now for the fun part select the Customer table and drag and drop it on to the blank design surface.  Kapow!  Now let's view it in the browser by right clicking on the design surface and selecting View In Browser.  There you have it a bound data control with zero lines of code. Please see my note below for a little trouble I had with VS2008 and Vista.

So let's see what happened, first a SqlDataSource (In the next post I'll describe the SqlDataSource and some of it's features) was created with parameters that selected all the rows from the Customer table.  Then a GridView was placed on the page and bound to the SqlDataSource.  All this was done automagically for you.

Ok so now our environment is all set.  Feel free to play around with the GridView, try using the themes and fiddle with the properties.

In the next post I'll discuss the SqlDataSource in more detail and discuss the pros and cons of the control.  In some later posts I'll go into detail in how you can take control of the GridView and override some of it's default behaviors including nested data sources and additional GridViews.

Note: I'm running Vista Ultimate 32bit, and when I first tried to view the web page I got an error page.  This happened to any page I created even if it just had text.  Turns out it has something to do with IPv6 on Vista...after looking around I found Rob Bazinet's blog The Accidental Technologist in which he encountered the same problem.  Through discussions on the blog a work around was presented to edit the hosts file and comment out the line that contains ::1 Check the link to the post for details.  What was really weird is that I know I've created sites just fine before...so I don't know if it was a recent update that did it...but it works now...weird.

Today I'm starting a new series of posts regarding Data Access methods and Data Binding with focus on the new developer.  There are a multitude of ways to access data with ASP.NET and I will attempt to go through as many as I can, hopefully digging deep enough for you to get a solid understanding of the pros and cons of each method.  We'll be beginning with some of the traditional methods of straight ADO.NET (don't worry if you're lost already we'll get you caught up) and then look at ways we can leverage some best practices and techniques to enhance how we use them and make our lives easier, including the use of the Enterprise Library.   Then we will move on to some of the new technologies including LINQ and the Entity Framework.  For the binding aspect of the series I will mostly be sticking with the Gridview and the FormView, in the later posts I may branch out to third party controls but I will be keeping it simple to start.

Each post will include Visual Basic and C# code.

Through the tutorial I will be making the assumption that you are using VS2008 or Visual Web Developer 2008 Express Edition. We'll be using SQL Server 2005 Express Edition as our database.  So if you need either of those please visit their respective links and install them now and apply any necessary updates.

For the actual data itself we'll be using the AdventureWorksLT database which is a lighter version of the AdventureWorks Database, it should suffice for what we'll be showing.  Where it comes up short I'll address that (I'm specifically thinking about a section we'll be covering with a LARGE amount of rows).

UPDATE: After some suggestions I've decided to use Northwind instead.  This will make the demos and setup  a little simpler.

A lot of the material I will be presenting is probably available in different forms in other places on the web so I will have a "Further Reading" section in most posts so that you can do your own research where necessary.  What I hope to bring to the table is some 'real world' application of the techniques and a dialog on the good, the bad and the ugly.  I'll show you where the fancy "oohahh" presentations that Microsoft likes to show fall short and where they are appropriate.

In the next post (I'm shooting for Sunday April 13th) I'll walk you through setting up our development environment which will primarily just include the referencing of the AdventureWorksLT Northwind database and making sure we are ready to go. 

Here's the next in the series: Data Access Project Setup

Call me Mr. Observant....but I just noticed that for some reason my VS2008 start page is set to some Sql Server Rss that contains REALLY out of date information (see:http://msdn.microsoft.com/sql/rss.xml)....so my question to you is what do you have as your start page RSS feed?  On another box I had the DotNetKicks feed...but I always got an error when I clicked on a link in that window so I stopped using it after a bit.

So I had my first chance to work with the DLR and LOLCODE today while for a lark I rewrote a solution to one of Windows Scripting Games event as highlighted on Scott Hanselman's blog.  To install the runtime requirements check out this page on Scott's blog.  I was struck by the power of the DLR while I was doing this.  I was also a little stymied in spots as LOLCODE and specificaly this implementation of LOLCODE isn't exactly well documented so I'm sure there's a lot of room for improvement here.  Anyway the challenge was to create a round-robin schedule for 6 teams, ensuring that each team played each other once and that one team didn't play a bunch of games in a row.  Here's the code or (HERE DA CODEZ):

HAI
   CAN HAS System?

	I HAS A NUMBEROFTEAMS ITZ 6
	I HAS A NUMBEROFGAMES ITZ 15
	I HAS A HT ITZ NJU ArrayList ON Collections ON System WIT 15
	I HAS A RANDOBJ ITZ NJU Random ON System
	I HAS A COWNTER ITZ 0
	I HAS A INTA ITZ 0
	I HAS A INTB ITZ 0
	I HAS A THING
	I HAS A NOTHERTHING
	I HAS A HMMMM
	I HAS A GAME 

	IM IN YR
	  LOL HMMMM R COL get_Count ON HT
	     IZ HMMMM SMALR NUMBEROFGAMES?
		YARLY
		   LOL INTA R COL Next ON RANDOBJ WIT NUMBEROFTEAMS
		   LOL INTB R COL Next ON RANDOBJ WIT NUMBEROFTEAMS
		   IZ INTA BIGR INTB?
			YARLY
			   LOL GAME R COL Format ON String ON System WIT "Team {0} vs. Team {1}" AN INTA AN INTB
			   LOL THING R COL Contains ON HT WIT GAME
			   LOL NOTHERTHING R COL ToString ON THING
			   IZ NOTHERTHING LIEK "False"?
				YARLY
				   COL Add ON HT WIT GAME
				NOWAI
				   BTW NUFFIN HERE
			   KTHX
			NOWAI
		   KTHX		
		NOWAI
		   GTFO
		
	     KTHX

	KTHX

	I HAS A LUPESIZE ITZ COL get_Count ON HT
	LOL COWNTER R 0
	IM IN YR
		IZ COWNTER SMALR LUPESIZE?
			YARLY
				VISIBLE COL get_Item ON HT WIT COWNTER
			NOWAI
				GTFO
		KTHX
	UPZ COWNTER!!1

	KTHX
	


KTHXBYE

Before I begin I want to state that this post is intended for those who are unaware of the flexibility that overloading functions can provide, power developers can feel free to move along or provide feedback.

Assume you want to pass an optional parameter to a method, VB makes this really easy by the use of the OPTIONAL keyword:

Public Function MyFunction(ByVal parameter1 as string, Optional parameter as integer)

End Function

Very easy.

Unfortunately C# does not support the OPTIONAL keyword.  So we have overloaded methods to the rescue!  An overloaded method is simply a method with the exact same name as an existing method, however it has a different parameter list (aka signature).  Here's a brief example:

public void MyFunction(string FirstName)
{
Console.Write(FirstName);
}

public void MyFunction(string FirstName, string LastName)
{
Console.Write(FirstName);
Console.WriteLine(LastName);
}

Notice that the method name is duplicated, and the parameter signature is different.  Now when you are accessing that method Intellisense will have the little arrows to show you the different signatures denoting there is at least one overloaded method available.

This example however stinks.

There is duplicate logic there, and now it's more difficult to maintain.  Here's where chaining overloaded methods really gets cool.  Let's assume that LastName is optional.  Let's move all our code into the most well defined method and chain the methods above it.  Let me show you what I mean:

public void MyFunction(string FirstName)
{
MyFunction(FirstName, "");
}

public void MyFunction(string FirstName, string LastName)
{
Console.Write(FirstName);
Console.WriteLine(LastName);
}

Now if we call MyFunction and only pass it the first parameter, it will take that parameter and pass it and a second empty string to the version of the method that has two parameters.  This is a highly simplified example, but hopefully it will show you how you can extend some of your existing behavior without having to rip your application apart. In fact this post came about because I was lamenting that the application I was fixing is an ASP classic app and I was thinking how convenient it would be if I could use optional parameters.  Sure you could use the array method and all like that, which is fine if you started that way, but it adds a lot of work if you are retrofitting your function to use an array. 

Not a ground breaking post, but hopefully eyeopening to either newer developers or those making the switch from VB to C#.

So I recently started looking into using Commercial controls for my ASP.NET application (I won't name any names yet in case I'm way off base and crazy, I don't want to make them look bad, when it's really me that's at fault).  They look great and function well but my little input page of 25k (pre canned controls) is now 130k!  I've gone through and disabled viewstate where appropriate and have followed the vendors suggestions regarding making them more lightweight...but it's disconcerting to see that file size discrepancy.

The obvious take away from this is that these controls are providing a better user experience and the cost of size.  Now the system this is going to be deployed to is rather burly and has plenty of bandwidth, be even with that the page certainly cannot load as fast as it did before.  My question is to you Good Citizens...what is your take on this...does the added functionality warrant a bigger page?  Am I too concerned about the HTML size and will HttpCompression save the day and make the whole thing moot?

Looks like you can't be logged into the forums and weblogs at the same time.  I say this because I found out the hard way...when I tried to look something up in the forums while I was writing a post...lost the last part of my post.  No biggie but I thought I'd put it out there as a tip of sorts.