.NET news » Database

When crafting a query in SQL, there are often times when we may need to operate over a set of data that doesn't inherently exist within the system. For example, the database for an eCommerce web application would have the standard tables - Products, Customers, Orders, OrderDetails, and so on - but we may need to run reports on a particular subset of the data or against aggregate data across these tables. Or the reporting queries we need might need to group or filter by results returned by scalar subqueries. Typically, views are used to break down complex queries into digestible chunks or to provide scalar subquery results that can be grouped and filtered. Views, however, are sometimes overkill, as they are permanent objects at the system-level. If we only need to reference this complex query in a single stored procedure or UDF, another option is to use a derived table. Unfortunately, derived tables muddle the readability of the query and must be repeated for each use in a statement..

.NET 2.0 and SQL Server 2005 combine in Query Notifications to notify applications when critical data changes occur and eliminate the need to repeatedly ask the database, "Has it changed yet?"

A lot of articles have been written about database concurrency conflict detection and the various ways of handling them.Unfortunately most of these articles, and accompanying solutions, have one major flaw in that they focus on the technical issues and database implementation instead of real-world data and how people use the data. In this article, I will try to show the difference between focusing on the database implementation and on the real-world data. I will show some possible approaches on how to solve these concurrency issues.

Do more with less code is the slogan of Visual Studio 2005.When it comes to reducing the amount of written code, wizards are definitely a viable option. Visual Studio 2005 has a lot of wizardry in it, especially to generate data access code. Any code that gets silently injected in your project follows a strict logic and a well-known design pattern. A full understanding how Visual Studio 2005 does it puts you on the right track to modify and extend the code to build your made-to-measure data access layer. This article dissects the code behind table adapters and binding source components to unveil patterns and best practices.

In SQL Server 2005, XML becomes a first-class data type. Developers can make minor remote modifications to stored XML documents easily, taking advantage of new support for XML schema-based strong typing, and server-based XML data validation.

If you use DataAdapters and love their automatic connection-opening and closing capabilities—and ever use transactions, you should first understand what's really going on underneath the covers.

Is running .NET Framework code within SQL Server 2005 exciting or a threat? Which is it? This article explores the security issues of SQLCLR code so that both developers and DBAs can make informed decisions about its use.

For low-volume databases, those that are predominantly read and not written to, or database tables that are designed to not be updated or only updated under rare circumstances, a DBA or developer may be interested in being notified whenever the data in a particular table is modified. Or you may have certain records in a table that are assigned to a particular user and, upon that record being updated, that user should be notified of the change. While this sort of logic can be implemented at the code level, Microsoft SQL Server has all of the technologies needed to achieve this aim built directly into it. Triggers can be used to perform some action when data is inserted, updated, or deleted from a table, and Microsoft SQL Server's xp_sendmail extended stored procedure can be invoked to send an email to one or more recipients. Combining triggers with xp_sendmail provides a means for alerting specified users via email when the data in a particular table is modified...

SQL Server Reporting Services makes it easy to build reports from database data, but what if the data isn't in a database? This article shows you how to take advantage of Reporting Services' unique extensibility model to build reports from ADO.NET datasets.

Data is the blood in your system; it sits in its comfortable home of a database, and camps out in the tent of XML, but it deserves to be worked with in a reliable and consistent manner.But why should only data-related operations be reliable? Shouldn't you want to write reliable code for your other operations? The introduction of System.Transactions in .NET 2.0 brings a paradigm shift of how you will write reliable transactional code on the Windows platform. This article dives deep in the depths of how System.Transactions works, and how you can use it to your advantage. You will also see how you can leverage existing System.Transactions integration within ADO.NET, and why you need to really understand what is under the magic carpet.