March 2006 - Posts

Quickly Deleting Comments in .Text
28 March 06 12:27 PM | Scott Mitchell

While I'm certain that Community Server has the ability to easily delete comments en mass, this blog still uses Scott Watermasysk's close to originally-released version of .Text (gasp!). I've been meaning to upgrade, I promise, but with the assorted customizations I've done and the time needed to make the transition, it's hard to justify taking the time to fix something that's really not broken.

My one complaint, however, has been comment spam. This blog is blasted with dozens of comment spam messages on a daily basis. Most of these are filtered out proactively with some machine-level tests (essentially looking for keywords that indicate spamminess), which I've blogged about before. In fact, to date the technique I use to stop comment spam has quashed 13,046 messages from making their way to the ScottOnWriting.NET blog.

Regardless, comment spam still creeps in and deleting it through the .Text admin page is a pain. You see a list of the comments, each with a Delete button. Cicking Delete takes you to a screen that says, “Are you sure you want to delete this?” Hitting Confirm takes you back to the main page with the comment deleted. This is OK when there are one or two comment spams to delete, but a carpal tunnel-inducing nuissance when you are blasted with, say, 15 spam messages in a two minute period. Ideally I would be able to check a number of checkboxes and click a “Delete Selected” button.

To further put off the upgrade to Community Server and to get some good ol' ASP.NET 2.0 practice, I decided to create a simple ASP.NET 2.0 website locally that would provide such an interface. And I was able to create it in under 5 minutes with having to write less than 10 lines of code. 2.0 rocks.

Anyway, the code is available for download here. It's just a single page, Default.aspx, which lists the 50 most recent comments in a GridView. Each GridView row has a CheckBox. At the bottom there's a “Delete Selected Comments” Button that, when clicked, iterates through each GridView row and, if selected, programmatically invokes the SqlDataSource control's Delete() method (which is wired up to the blog_DeletePost sproc). (The exact sprocs/queries used here may differ for more modern versions of .Text or Community Server.)

For those three people still using .Text who haven't upgraded to Community Server, enjoy!

Working with SQL Server 2005 Express Databases
27 March 06 06:35 PM | Scott Mitchell

I teach an ASP.NET Programming II course at Univeristy of California - San Diego Extension that, for the final project, involves creating a forum site not unlike the ASP.NET Forums. To make the project easier to move from lab to home to work to me for grading, I encourage my students to use SQL Server 2005 Express Edition databases in the App_Data folder.

Earlier in the class the students created a MessageBoard database created with, at minimum, tables for storing Forums, Threads, and Posts. Later, after learning about Membership, they needed to add the membership and roles-related tables to their existing database. This is a lot easier said than done when using SQL Server 2005 Express Edition.

If you use the SQL Server 2005 Standard Edition you can simply use the aspnet_regsql.exe tool's little wizard, point it to your SQL Server 2005 database, provide the credentials, and you're done. Alternatively you can run this tool from the command-line, using something like (with the -E using a “trusted connection,“ although user ID/password credentials can also be supplied):

aspnet_regsql.exe -S server -d database -E -A all

While this concept still applies for SQL Server 2005 Express Edition, it can be a little harder to get the server and database names right. What database server is SQL Server Express installed on? And what's the database name for a .MDF file in the App_Data folder?

Assuming you are working on an ASP.NET application locally, the server name will be: localhost\SQLExpress

The database name is (and here's it can get a bit tricky), is the path to the MDF file when it was created. So, say that you have an ASP.NET application created in the classroom lab at C:\Labs\Website\App_Data\MessageBoard.mdf. The name of the database is C:\Labs\Website\App_Data\MessageBoard.mdf, meaning you could install the membership services from the command-line using:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Labs\Website\App_Data\MessageBoard.mdf” -E -A all

Now, imagine that you zip up your files onto a USB keychain drive, go home, and copy your project files to C:\Home\Website. Now, if you wanted to create the services, you'd think you'd just type in:

aspnet_regsql.exe -S localhost\SQLExpress -d “C:\Home\Website\App_Data\MessageBoard.mdf” -E -A all

Ah, but the database name is C:\Labs\Website\App_Data\MessageBoard.mdf. Eep. So when you run the above command the database can't be found and cryptic error messages abound. Essentially, it can't find the database C:\Home\Website\App_Data\MessageBoard.mdf so it tries to create a database file in the default directory (%PROGRAM FILES%\Microsoft SQL Server\MSSQL.1\DATA) with the filename C:\Home\Website\App_Data\MessageBoard.mdf. This, of course, causes problems since that's not a valid filename. Ick.

So how do we fix this? There are a couple optios. The easiest is probably to download the (free) SQL Server 2005 Management Studio Express program and attach the database file. Then, from the Properties pane you can see the database name. You can then use this with aspnet_regsql.exe. (You could also rename the database at this point...)

If you want to be 3l33t you can use sqlcmd, attach the database (sp_attach_db) giving it a friendly name, which you can then use to run the aspnet_regsql.exe command line program against. Something like:

sqlcmd -S localhost\SQLExpress -Q “EXEC sp_attach_db 'Foobar', N'pathToDBfile'”

And then:

aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all

While the above may make help fulfill any geek quota you're trying to maintain, the easiest approach, by far, is to just download the SQL Server 2005 Management Studio Express product. With that you can work with SQL Server 2005 Express Edition databases much like you would with standard and up versions.

Hope this helps!

Filed under:
Efficiently Paging Through LARGE Resultsets in ASP.NET 2.0
20 March 06 04:23 PM | Scott Mitchell

My two latest articles on 4Guys examine how to efficiently page through very large resultsets in ASP.NET 2.0 using SQL Server 2005's new ROW_NUMBER() keyword. While default paging is as simple as point-and-click in ASP.NET 2.0, it's horribly inefficient and naive, bringing back all records from the underlying data source for each page of data displayed. Custom paging, on the other hand, allows you, the developer, to write code/stored procedures that will intelligently bring back the right subset of records.

The performance difference between these two techniques can be profound for sufficiently large resultsets. As I detail in Custom Paging in ASP.NET 2.0 with SQL Server 2005, paging through a 50,000 record table using default paging was nearly two orders of magnitude less efficient than custom paging. One challenge with custom paging, though, is that adding the boilerplate bi-directional sorting functionality is no longer a point-and-click proposition. Fortunately, it's not terribly difficult to implement, although there are a few subtle points that, if missed or overlooked, can impact performance. In Sorting Custom Paged Results I show how to efficiently include bi-directional sorting with custom paging - all it takes is a bit of dynamic SQL in your stored procedure and indexes on the columns that can be sorted.

Implementing custom paging in ASP.NET 2.0 is a good deal more involved than implementing default paging (as was the case in 1.x), but, in my opinion, custom paging in 2.0 is much easier to implement and get working right than in 1.x. (Ditto for default paging in 2.0 vs. default paging in 1.x.)

The whole custom paging vs. default paging brings up a good question: do you use custom paging all the time? That is, whenever creating a data interface using a GridView (or DataGrid), do you always use custom paging, or do you only implement custom paging if you know the resultset will contain hundreds or thousands of records? Personally, I do default paging if there are going to be less than 100 records in the resultset because of the ease of implementing default paging vs. custom paging. This can be a dangerous tactic, though, because even when you might expect that a resultset will remain relatively small, unless the resultset is something like the months of the year or the states in the US, there's no real guarantee that the size won't later swell into something less manageable. ... food for thought!

Filed under:
Upcoming Talk: San Diego ASP.NET SIG - ASP.NET 2.0's Site Navigation
15 March 06 03:45 PM | Scott Mitchell

I'll be speaking this coming Tuesday (March 21st) at the San Diego ASP.NET SIG on ASP.NET 2.0's new site navigation features. The talk is held at the Microsoft offices in UTC and there will be free pizza and soft drinks starting at 6:00!!1! If you can't make it, you can download the slides and demos I'll be presented here.

Also, if you're in the San Diego area and interested in courses on ASP.NET 2.0, I teach two for UCSD Extension. Specifically, I have an upcoming ASP.NET Programming II class starting in May at the La Jolla UCSD campus and both ASP.NET Programming I and II courses to be held sometime in the summer. (The schedule's not yet been finalized.) All courses are six four-hour meetings, spaces one a week.

Filed under:
April's Toolbox Column in MSDN
09 March 06 10:50 AM | Scott Mitchell

My fourth Toolbox column in the April 2006 issue of MSDN Magazine is now available online. The April issue examines three products:

  • IEWatch, an IE plug-in for dissecting HTTP traffic and diving into the markup sent to the browser
  • SQL Examiner, a tool for comparing and synchronizing the schemas of different databases, and
  • VBDocman .NET, an XML comments tool for VB.NET; also includes a nifty XML comment WYSIWYG tool for creating XML comments in both VS.NET and VS2005.

Also reviewed is APress's SQL Server 2005 T-SQL Recipes. From the article:

Most developers rely on IntelliSense and statement completion to relieve the burden of memorizing the vast array of classes, methods, properties, and events that make up the .NET Framework and any third-party components or controls used. Unfortunately, for SQL we must still use the old fashioned techniques—memorization, documentation, online resources, and books.

While there are plenty of books that provide a thorough discussion of database theory, or the intricacies of a particular database platform, often I'm in need of just a nugget of information, such as the syntax for iterating through a cursor, the syntax for RAISERROR, or how to retrieve database metadata through T-SQL syntax. These tidbits of information are just a glance away with SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach (Apress, 2005) by Joseph Sack. Like a cookbook, SQL Server 2005 T-SQL Recipes contains a bounty of short and to the point sections, each one detailing a feature of the SQL Server 2005 T-SQL syntax.

You can keep abreast of the latest Toolbox articles through the column's RSS feed or the Toolbox column category here on my blog.

Also! I'm planning on reviewing a couple of open-source / freeware tools for the column, and am looking for any recommendations on products to consider. Ideally, the products won't be ones that have already been covered extensively in MSDN Magazine (such as NUnit), and are not Microsoft created (such as Reflector). If you have any suggestions, email 'em on in to toolsmm@microsoft.com!

Filed under:
Review: Programming Visual Basic 2005
03 March 06 01:33 PM | Scott Mitchell

In general, there are two kinds of computer trade books: those that focus on a very specific topic, such as building custom WinForm controls; and those that cover a more general topic, such as learning C#, or mastering ASP.NET. Those more general books tend to be quite lengthy, understandably, as they must cover a wide swath of material. Programming Visual Basic 2005 (O'Reilly Publishing, $39.95, 548 pages), by Jesse Liberty, is an exception to this rule. Programming Visual Basic 2005 tackles Windows and Web application development, as well as introducing new Visual Basic 2005 features, in a relatively scant 548 pages.

The book's low page count is due in large part to the author's insistence in leaving out all but the most important information, which he makes abundantly clear in the introduction: "I'm not going to waste your time. You won't find a long treatise on why .NET is great. … I'm not going to [fill] pages with material that is otherwise freely available." There is even a "What I Threw Away" section highlighting those chapters that the author considered, but pitched.

Programming Visual Basic 2005 is geared toward the intermediate developer who has past experience with Visual Basic, but needs to quickly learn how to create Windows and Web applications with Visual Basic 2005. The first third of the book examines programming Windows applications and includes chapters on designing forms, accessing data, using GDI+, and working with legacy COM controls. The next third of the book focuses on building Web applications and explores ASP.NET basics, validation controls, master pages, working with data, personalization, and web services. The book concludes with a look at Visual Studio 2005, Visual Basic fundamentals, generics, and a discussion on object-oriented programming.

While the book does walk the reader through building numerous examples, due to its brevity it doesn't explore the full features available or discuss subtle side-effects. Nor does it include discussions on common pitfalls and how to correct such problems. In other words, the book assumes that if you hit a snag in one of the examples, you will have the patience and know-how needed to be able to research and find a workaround on your own. In short, Programming Visual Basic 2005 offers only an overview of building Windows and Web applications with Visual Basic, trusting the reader to be able to locate and assimilate any additional information needed on their own.

Readers who are looking for a quick introduction to new features in Windows and Web development with Visual Basic without the usual bloat will find Jesse Liberty's book a breath of fresh air. Those who seek a more detailed examination will need to keep looking.

More Posts

Archives

My Books

  • Teach Yourself ASP.NET 4 in 24 Hours
  • Teach Yourself ASP.NET 3.5 in 24 Hours
  • Teach Yourself ASP.NET 2.0 in 24 Hours
  • ASP.NET Data Web Controls Kick Start
  • ASP.NET: Tips, Tutorials, and Code
  • Designing Active Server Pages
  • Teach Yourself Active Server Pages 3.0 in 21 Days

I am a Microsoft MVP for ASP.NET.

I am an ASPInsider.