.NET news » Database

SQL Server 2005 offers a number of new features over its predecessor, including many features aimed at making working with databases more like writing .NET application code. For example, in SQL Server 2005, stored procedures, triggers, UDFs, and so on can be written using any .NET Framework programming language (such as Visual Basic or C#). Another feature, and the focus of this article, is SQL Server 2005's support for TRY...CATCH blocks.

Prior to SQL Server 2005, detecting errors resulting from T-SQL statements could only be handled by checking a global error variable, @@ERROR. Because the @@ERROR variable value is reset after each SQL statement, this antiquated approach leads to rather bloated stored procedures, as the variable must be checked after each statement with code to handle any problems...

A dynamic web application needs an admin section for CRUD action of the Records/Tables in database, wouldn't it be nice to have a database admin, which can be plugged to any web application, Just give your sql connection string and it dynamically manages all table operation in just 5 pages

This month, John Papa answers questions on typed and untyped XML columns, TRY/CATCH exception handling in T-SQL, and triggers for table schema changes.

Data Application Block for Firebird SQL intended to speed development of applications.

Writing database-access code is a repetitious and time-consuming task, but now that it's available as a reusable Enterprise Data Access Application Block, you'll never have to write such code again.

A common pattern in web development is providing paged access to data. Rather than displaying the entire contents of a report or database table to an end user, developers often show only a subset of records per web page, with controls for moving from page to page. With ASP.NET 1.x, the DataGrid made paging incredibly simple - just set the AllowPaging property to True and add a few lines of code in the PageIndexChanged event handler and you were done! ASP.NET 2.0's GridView makes the process even simpler - just check the Enable Paging option from the GridView's smart tag - no code needed.

Of course nothing is free in life, and the tradeoff you make with the ease of checking a checkbox to enable paging (or, in the DataGrid's case, writing a couple lines of code) is performance. Out of the box, the DataGrid and GridView use default paging, which is a simple paging model that returns all of the records for each every page of data shown. When paging through small amounts of data (dozens to a hundred or so records), this inefficiency is likely outweighed by the ease of adding the feature. However, if you want to page through thousands, tens of thousands, or hundreds of thousands of records the default paging model is not viable.

The alternative to default paging is custom paging, in which you are tasked with writing code that intelligently grabs the correct subset of data. It requires a bit more work, but is essential when dealing with sufficiently-sized data...

Change collation order for all text columns in a database.

Few developers have paid much attention to the new classes and methods offered by ADO.NET 2.0, the enhanced set of database access classes that shipped along with Visual Studio 2005 and SQL Server 2005. That's a shame. There's a lot of new goodness hidden in ADO.NET 2.0; here are some of Alan Zeichick's favorites.

Windows Forms 2.0 increases support for data binding via the new BindingNavigator and BindingSource objects, which will save you a lot of effort. Find out how you can perform sorting and searching tasks using data binding and simplify the display of master-detail relationships in tables.

One of the major benefits of writing .NET code to run in the Common Language Runtime (CLR) hosted in any environment is code access security (CAS).CAS provides a code-based-rather than user-based-authorization scheme to prevent various kinds of luring and other code attacks. But how does that security scheme coexist with SQL Server 2005's own, newly enhanced security features? By default your .NET code is reasonably secure, but it's all too easy for the two security schemes to butt heads and cause you grief. In this article I'll look briefly at the concept behind CAS and a few new security features in SQL Server 2005, then explore how to make the two systems work for you instead of against you as you take advantage of these advanced programming features in SQL Server.