Articles filed in category 'Data'

Ken Getz's Finalize column for Jan/Feb 07

Using Oracle as your database in the .NET environment is relatively simple, except when returning JDBC ResultSets from Java stored procedures. The Microsoft and Oracle .NET data provider drivers allow for easy access to data from SQL queries and PL/SQL stored procedures. There is not, however, a simple interface to return ResultSet data from a Java stored procedure to a .NET client.

As a developer, it is no surprise to encounter unexpected behavior when working with a new technology.Microsoft added the Entity Framework (EF) to ADO.NET with the .NET 3.5 Service Pack 1 released in 2008 enabling developers to incorporate a data model directly in their application and interact with their data through the model rather than working directly against the database. For background on EF, see my previous article, “Introducing ADO.NET Entity Framework” in the N...ov/Dec 2007 issue of CODE Magazine.See More

Many developers have a dream: easy and efficient data binding.To be really quick and profitable, RAD (rapid application development) tools and techniques must be strong in data binding. They must provide a programming interface that is both easy to use and effective. Easy design-time composition of user interfaces; effective support of complex scenarios of interrelated data, dependencies, and filtering. In Windows Forms, the data binding machinery is highly sophisticated... and designed to meet common needs of former client/server applications, now migrating to the more modern .NET multi-tier design. This article reviews common Windows Forms data binding techniques and provides answers and explanations.See More

ADO.NET is a powerful toolbox but it's not a software magic wand.To get the most out of ADO.NET classes, developers must fully understand the model and study a few best practices. Based on years of real-world experience with ADO, ADO.NET provides a richer set of more powerful tools. But, ADO.NET is not designed to be an out-of-the-box tool that reduces any programming work to just point-and-click. In this article, you'll learn about common best practices for using three ...key element of any data access strategy: connections, security, and transactions.See More

ADO.NET has a strong and powerful disconnected model.It allows programmers to build a web of in-memory objects and relate contents coming from different tables and even from different data sources. When inter-related tables are involved with the process of query and update, code strategies are important to preserve scalability and maintain high performance. Sometimes compound queries can be more effectively accomplished splitting queries; sometimes not. Submitting change...s in batch mode, exploiting the DataSet and its disconnected model, often appears as the perfect solution. However, what if you need to move dozens of MB? The serialization mechanism of the DataSet would increase that by a factor. Tradeoffs is the magic word.See More

Separation of presentation and data has long been considered a best practice in the development of Web applications.Driven by the need for low friction deployment and a richer user experience, the types and architectures of Web applications are evolving dramatically. With the introduction and growth of AJAX-based applications and Rich Interactive Applications (RIA) using technologies such as Microsoft® Silverlight™, separation of presentation and data is no longer just a... best practice, it is required.See More

IT professionals agree that input is a big source of trouble. Input ultimately determines how applications work and wrong or malicious input may cause serious damage. It is extremely important that developers have this fact firmly in mind and consequently apply adequate countermeasures. Starting from the perspective that all input is evil is a good approach. Reasoning in terms of a whitelist instead of a blacklist is another excellent strategy. Working with strongly type...d data is the third pillar of secure applications. This article discusses the role of input data and related attacks in the context of ASP.NET applications.See More

Microsoft’s Entity Data Model allows you to define an application-oriented view of your data consistent with how you reason about that data.Part I of this article describes the Entity Data Model and how it enables you to represent real-world concepts in a way that makes relationships between related pieces of data more explicit and easier to query, navigate, and consume than through the traditional relational database model. Part II of the article discusses how Microsoft...’s ADO.NET Entity Framework provides a flexible mapping of an application-oriented conceptual schema in terms of the Entity Data Model to existing relational database schemas. Shyam Pather’s article, “Programming Against the ADO.NET Entity Framework” completes the picture by describing the actual programming model and API exposed by the framework.See More

The ADO.NET Entity Framework allows you to define an application-oriented view of your data consistent with how you reason about that data, and map that conceptual view to existing relational schemas.Part I of this article described the Entity Data Model and how it enables you to model real-world concepts in a more natural way. Part II of the article describes how that Entity Data Model is used within the ADO.NET Entity Framework to define an application-oriented concept...ual view of your data, and how that view can be flexibly mapped to existing relational schemas. Shyam Pather’s article, “Programming Against the ADO.NET Entity Framework” completes the picture by describing the actual programming model and API used by developers to work with data using the ADO.NET Entity Framework.See More

ASP.NET WebControls do more than just allow you to write reusable components. They can provide an entire approach to Web application development, allowing you to bring a new level of OOP to the UI and letting you program declaratively.Lately I've come to notice that no other programming term has more definitions than declarative programming. In this article, I will attempt to explain it in terms of how it applies to .NET development, specifically ASP.NET through the use ...of WebControls. I'll do this by illustrating some real-world examples that I have used in my own projects. In the end, I hope to leave you with an understanding of what declarative programming is, how you can use it when developing ASP.NET Web applications, and how, with the help of WebControls, to use it as an approach to ASP.NET development.See More

Both ASP.NET MVC and the ADO.NET Entity Framework are both very popular topics right now in the developer community.Having spoken at various user group meeting and code camps it is very obvious to me what topics a lot of developers are interested in. I see that sessions about ASP.NET MVC or the Entity Framework are always packed with developers eager for more information. The focus of this article is the Entity Framework, but in the context of an ASP.NET MVC application.... As such, I am assuming at least basic understanding of ASP.NET MVC but little-to-none with Entity Framework.See More

Mike looks at the technology that allows multiple teams to connect and scale their data despite disparate data storage methods.

If you want to be a great SQL Server developer, you’ll want to pay attention to these 13 key points. Kevin Goff covers both best practices and common pitfalls of Business Intelligence.

LINQ to XML, which makes query a first class construct in C# and Visual Basic, is the new XML API in the .NET Framework 3.5. With the introduction of Language Integrated Query (LINQ), Microsoft is introducing LINQ implementations that work over objects, data, and XML. LINQ to XML improves on System.Xml in the .NET Framework 2.0 by being both simpler to use and more efficient. Microsoft developed this new API because the W3C-based DOM API does not integrate well into the LINQ programming model.

Creating basic data access stored procedures is time consuming and boring work. Relieve the tedium by writing code that writes these stored procedures for you.

C# 3.0 includes a few syntactical additions to the language. For the most part, Microsoft added these language additions to support Language Integrated Query (LINQ). These features include (but are not limited to) lambda expressions, extensions methods, anonymous types, implicitly typed local variables, automatic properties, and object initializers.

With Sync Services for ADO.NET, developers can easily optimize their online experience by caching data locally within the easy-to-deploy SQL Server Compact embedded database engine.In this article I’ll cover how Sync Services for ADO.NET was designed to fit the growing developer needs for caching data locally in online-optimized, offline-enabled applications.

From the book Understanding .NET by David Chappell, published by Addison Wesley Microsoft's .NET is revolutionizing Windows-based software development. Since its initial release in 2002, .NET has changed significantly, becoming the foundation for a new generation of Windows applications. The .NET Framework and Visual Studio, the two core aspects of this initiative, provide a multilanguage environment in which developers can create Web services, graphical user interfac...es, and other kinds of applications. Taken as a whole, the .NET technologies have changed the way nearly every Windows application is built. Now fully updated for version 2.0 of the .NET Framework and Visual Basic 2005, Understanding .NET, Second Edition, is a concise guide to the landscape of Windows development. Margin notes, detailed diagrams, and lucid writing make this book easy to read and navigate, while analysis sections explore controversial issues and address common concerns. David Chappell's independent perspective and straightforward descriptions clarify both how the .NET technologies work and how they can be used. Coverage includes: An overview of .NET and its goals The Common Language Runtime (CLR) The .NET languages, including C#, Visual Basic, and C++ The .NET Framework class library Building Web Applications with ASP.NET Accessing Data with ADO.NET .NET framework integration with SQL Server 2005 The key to using a new technology is to understand the fundamentals. This book provides the robust foundation developers and technical managers need to make the right decisions and maximize the potential of this revolutionary framework. See More

SQL Server2005 Distilled Need to get your arms around Microsoft SQL Server 2005 fast, without getting buried in the details? Need to make fundamental decisions about deploying, using, or administering Microsoft’s latest enterprise database? Need to understand what’s new in SQL Server 2005, and how it fits with your existing IT and business infrastructure? SQL Server 2005 Distilled delivers the answers you need–quickly, clearly, and objectively. Former SQL Server ...team member Eric L. Brown offers realistic insight into every significant aspect of SQL Server 2005: its new features, architecture, administrative tools, security model, data management capabilities, development environment, and much more. Brown draws on his extensive experience consulting with enterprise users, outlining realistic usage scenarios that leverage SQL Server 2005’s strengths and minimize its limitations. See More

Managing application configuration and meta-data could not get any easier.Every application needs some way to configure itself so that it may interact with a given environment, whether it is customizing the title bar captions for a specific client or setting database connection strings for a development environment. For the simplest of applications, the app.config or machine.config probably meets your needs. For complex applications where security, scalability, and deplo...yment also have to be addressed, you may need something a bit more robust, secure, and scalable. Enter the Configuration Management Application Block.See More

Creating custom column types for the DataGridView control isn’t nearly as tricky as it once was.In this article, you’ll learn how to take advantage of inheritance to create your own bar graph column in a grid cell.

In early 2004, Microsoft released a new set of extensions to SQL Server 2000 called SQL Server 2000 Reporting Services.SQL Server Reporting Services provides a comprehensive platform for creating, managing, securing, scheduling, and outputting data from SQL Server, Oracle, OLE DB and ODBC data sources. When Microsoft created SQL Server Reporting Services they created it as an extendable platform. You can create your own data extensions and your own output extensions. In ...this article, I'll show you how to create your own custom data extensions for SQL Server Reporting Services.See More

The next version of SQL Server (code name Yukon) has extensive support of the Common Language Runtime (CLR).Previous versions of SQL Server (2000 and earlier) had a mechanism for creating custom data types. These data types were nothing more than aliases to system data types. In Yukon, you can create your own fully functional custom data types.

In all but the simplest applications, data is stored in a central location and accessed over a network.However, in many scenarios, distributed applications can not assume a certain kind of network connection, both in terms of performance and reliability. In scenarios where users access their applications on mobile PCs, network connections may not be available at all. This introduces relatively complex data access scenarios with which modern applications need to cope.

ADO.NET SQL Data access made simple and efficient.Microsoft has created a set of libraries known as Application Blocks. These libraries will help developers reduce the amount of code they must write while using the current best practices. One of the components, Data Access Application Block for .NET, addresses Microsoft SQL Server data access by wrapping up data access into a helper class.

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 so...me possible approaches on how to solve these concurrency issues.See More

The ASP.NET Provider Model drives many features within the ASP.NET architecture, yet most people only relate it to security or membership-related functionality. In this article, I’ll take you deep into the provider model, show you how it’s used from a security context, then take it up a notch and teach you how you can use it to turn any feature in your application into an extensible and swappable component. In fact, I’ll even show you why calling it the ‘ASP.NET’ Provide...r Model may be a misnomer.The ASP.NET Provider Model drives many features within the ASP.NET architecture, yet most people only relate it to security or membership-related functionality. In this article, I’ll take you deep into the provider model, show you how it’s used from a security context, then take it up a notch and teach you how you can use it to turn any feature in your application into an extensible and swappable component. In fact, I’ll even show you why calling it the ‘ASP.NET’ Provider Model may be a misnomer.See More

Visual Studio 2005 will ship some great new controls, but suppose you want your own control to play in the Data Sources Window?In this article, I'll explain how the Data Sources Window in Visual Studio 2005 will enable you to extend the list of controls supported for Drag Once Databinding. I'll show you how to create a PhoneBox control and an AddressBlock UserControl.

Using the new Data Sources Window in Visual Studio 2005, developers can now drag columns of their typed DataSets or properties of their own business objects directly to their form. Visual Studio 2005 will create, name, and label controls for each bound property. For those that prefer to lay out the forms with the toolbox, developers can use "Connect the Dots DataBinding" to drag and drop from the Data Sources Window onto their existing controls.

It’s becoming less and less common to show raw data directly to the user.At the very least, portions of your data may require reformatting to make it more readable for your users. But applying a simple format, say showing a numeric value as a percentage, only goes so far. WPF offers an impressive data transformation tool called Data Templates that can radically change the visualization applied to business data.

Integrating a web site with a structured database poses a particular challenge. How can a developer provide a flexible, easy to use, yet robust search interface for users to query the information stored in the database? In this article Beth provides a complete solution for this problem using Visual FoxPro, ADO and XML, by providing a dynamic searching mechanism in the middle tier that users can control from the front end.

Ken Getz Mar/April Finalize column.

Enterprise Application Integration (EAI) is a collection of architectural principles combined to integrate new and existing applications both within the enterprise and in business to business or partner integration scenarios. Building on the principles introduced in Part 1 (May/June issue of CoDe Magazine), I’ll provide a step-by-step example of how to implement a solution that addresses a business case and suggest some real-world patterns and techniques for fulfilling the business case.

When it comes to analysis and reporting, managers love Excel. Just give them the raw data and they have a field day. For enterprise-level reporting, however, you want everybody to have the same data and the same interpretation of that data. With some effort this can be achieved without having to say goodbye to Excel.

With this release, EF Core has really matured. Julie takes you on a tour and points out some of the highlights of this labor-simplifying tool.

All applications are dependent on data in some form and most developers find themselves writing reams of data access code.Microsoft has been building data binding frameworks for years. Each one promises to solve our data binding woes forever. We're still waiting for the perfect one. Is WPF data binding the one we've been waiting for?

All applications are dependent on data in some form and most developers find themselves writing reams of data access code.Microsoft has been building databinding frameworks for years. Each one promises to solve our databinding woes forever. We’re still waiting for the perfect one.In the Nov/Dec 2008 issue of CoDe Magazine you were exposed to programmatically controlling data binding mechanisms of WPF. Along with a programmatic interface, WPF also provides a declarative d...atabining interface. This second article will introduce you to using the declarative data binding mechanisms contained in WPF.See More

You might have heard some things about NoSQL; how Google and Facebook are using non-relational databases to handle their load. And in most cases, this is where it stopped. NoSQL came about because scaling relational databases is somewhere between extremely hard to impossible.

Basic error handling in SQL Server's programming language, Transact-SQL, is straightforward.But when you nest calls to stored procedures, and the procedures have SQL transactions, error handling becomes much more complex. In this article I'll show you some tried-and-true models for how to handle errors in nested stored procedures with transactions.

May/June 2008 .NET Rocks! column

Mar/April 2008 .NET Rocks by Carl Franklin

May/June .Net Rocks Column

Partial classes are a hot new feature of the next .NET compilers.Specifically designed to overcome the brittleness of tool-generated code, partial classes are a source-level, assembly-limited, non-object-oriented way to extend the behavior of a class. A number of advantages derive from intensive use of partial classes; for example, you can have multiple teams at work on the same component at the same time. In addition, you have a neat and elegant incremental way to add functionality to a class.

Unless your battery is really, really good, you’ll eventually want to store your Ink.In some situations, simple file storage or XML serialization is sufficient for your needs, but in the majority of business applications, you’ll want to move Ink into and out of a relational database. Here’s how.

SQL Server Reporting Services versions 2000 and 2005 (SSRS) has many powerful features. SSRS has a well-designed data access engine, a great set of layout tools, and an excellent expression system for creating complex formulas. While the expression system is quite powerful it is not suitable for all applications. This is where SSRS shines. SSRS gives developers the ability to add custom code to their report layouts. This article demonstrates adding custom code to SQL Ser...ver Reporting Services reports.See More

Nowadays archiving, searching and processing the explosion of data generated in applications means coming up with nontraditional ways of dealing with the data. NoSQL solutions offer intriguing and unique ways of handling the volumes of data available to us. Additionally, 10Gen offers an open source distributed document-oriented solution called MongoDB.

The challenge of bringing data from efficient storage engines such as SQL Server into object-oriented programming models is hardly a new one. Most developers address this challenge by writing complex data access code to move data between their applications and the database. This requires an understanding of the database so that you can access data either from the raw tables, from views, or from stored procedures.</b>

The Microsoft® Sync Framework is the new framework and runtime for adding synchronization, roaming, and offline capabilities to applications. It supports peer-to-peer scenarios, works with devices and services, and is agnostic of data types, stores, and protocols. In this article, I’ll cover the high-level vision for the platform as well as the enabled scenarios made possible by the framework for developers, ISVs, and OEMs.

If you’re curious about Data Science or Python—and if you’re interested in the Internet of Things, you should be—you’ll find this introduction both important and exciting. Wei-Meng makes it all clear for us by explaining the basics.

Learning R sets you up for creating machine learning projects. Wei-Meng takes a close look at the language, which can implement a wide variety of statistical techniques, tests, analysis, classification, clustering, and can help you produce publication-quality graphs.

Tom’s having a lot of fun playing with SQL Server 2005’s T-SQL enhancements.In this article he combines a bunch of them in concert to solve an interesting problem.

At PDC 2005, Microsoft introduced brand new technology known as LINQ, which stands for “Language Integrated Query.”The feature-set hiding behind this acronym is truly mind-boggling and worthy of a lot of attention. In short, LINQ introduces a query language similar to SQL Server’s T-SQL, in C# and VB.NET. Imagine that you could issue something like a “select * from customers” statement within C# or VB.NET. This sounds somewhat intriguing, but it doesn’t begin to communicate the power of LINQ.

In show #270 Richard and I talked to Erik Meijer from Microsoft about LINQ. In this excerpt we talk about LINQ to Entities.

With the combined launch of Visual Studio 2008, SQL Server 2008, and Windows Server 2008, Microsoft is introducing five implementations of .NET Language Integrated Query (LINQ).Of these five implementations, two specifically target access to relational databases: LINQ to SQL and LINQ to Entities.

At PDC 2005, Microsoft announced a new technology called Language Integrated Query (LINQ), which will be available with Visual Studio “Orcas” (the next version of Visual Studio). A lot of exciting new technologies are announced at every PDC, and as a result, LINQ got some attention, but not nearly as much as I think it deserves. LINQ represents the ability to run queries right inside of Visual Basic, C#, or any other .NET language.

Microsoft demonstrated a new technology at PDC called LINQ (Language Integrated Query). The following note from Alan Griver, a member of the LINQ team at Microsoft, offers some details related to the LINQ project. In future issues of CoDe Magazine we will have more details on LINQ.Microsoft demonstrated a new technology at PDC called LINQ (Language Integrated Query). The following note from Alan Griver, a member of the LINQ team at Microsoft, offers some details related ...to the LINQ project. In future issues of CoDe Magazine we will have more details on LINQ.See More

Moving a SQL Server database from one server to another is simple-initially. But there are various methods for moving a database, and some have more advantages than others. Investigating the types of moves ahead of time can make planning for a database migration easier.It takes essentially three steps to move a database from one server to another: take the database out of production, copy it to another server, and then bring the copy back into production. While you can c...hoose from many methods to accomplish these steps, it can be difficult to complete the move given all the database’s dependencies. In this article I’ll describe the methods for moving the database. In my next installment I’ll discuss the dependencies and additional related objects that you must also move along with the database data.See More

xIn the past two installments of this series I’ve discussed two key aspects of moving a SQL Server database from one server (or instance) to another. First I covered the advantages of designing databases to make them easier to move, and then I looked at the methods that you can use to move a database. Now it’s time to look specifically at the types of database dependencies that you must account for after you move a database.

Gaining the full potential of object binding requires more than just dragging and dropping your properties onto forms. In this article I’ll present a few tricks you need to know to get the most from your object binding.

Fifteen years after its launch, ODBC is a firmly entrenched cornerstone of the software industry. This article explains why and will explore the relationship between Microsoft SQL Server and ODBC and discuss where ODBC may go in the future.

In .NET Web applications you can find that in many places custom classes and collections are better choices than the DataSet or DataTable. The custom classes or the custom class collections, which are truly object oriented, allow developers to employ all object-oriented programming techniques.

Web Services is a powerful technology, even in its basic form.However, with .NET, you can easily couple Web Services with .NET's new data services to provide a powerful data delivery mechanism that works over the Web, making it possible to build distributed applications that work easily without a local data store. In this article, Rick describes various ways you can use Web Services and ADO.NET DataSets to pass data between client and server applications to build truly disconnected applications.

The ADO.NET Entity Framework raises the level of abstraction at which developers work with data.Rather than coding against rows and columns, the ADO.NET Entity Framework allows you to define a higher-level Entity Data Model over your relational data, and then program in terms of this model. You get to deal with your data in the shapes that make sense for your application and those shapes are expressed in a richer vocabulary that include concepts like inheritance, complex... types, and explicit relationships.The ADO.NET Entity Framework raises the level of abstraction at which developers work with data.Rather than coding against rows and columns, the ADO.NET Entity Framework allows you to define a higher-level Entity Data Model over your relational data, and then program in terms of this model. You get to deal with your data in the shapes that make sense for your application and those shapes are expressed in a richer vocabulary that include concepts like inheritance, complex types, and explicit relationships.See More

SQL Server Katmai, now officially announced as SQL Server 2008, introduces a significant amount of new and improved functionality, including enhanced data types and greater programming flexibility for database application developers.

Do you ever feel like you’re beating your head against a wall? I know I do; quite often, in fact. It seems like developers spend half of their time bending technology to their purposes when the technology doesn’t really quite fit. Well, I’m actually thinking of one problem in particular right now, namely that of validation. Can you think of a more boring topic? There are few, but I think you can agree that it is an extremely important one in business software.

XML is quickly becoming the preferred method of passing information, not only for the Internet, but also across applications and within applications.Until now, developers have been forced to create our own routines to convert data stored in a database system into XML. With the release of Microsoft SQL Server 2000, however, you can query data directly from SQL Server in XML format.

This article continues from the May/June 2009 issue of CODE Magazine (Quick ID 0906081) which covered why you want to use NHibernate, techniques for configuring NHibernate, how to map your objects to your data entities, and how to load basic objects.

Your application can detect a deadlock and resubmit its transaction, but a better approach is to resolve a deadlock by changing the conditions that lead to it in the first place. In this article, you'll learn how SQL Server deadlocks arise, what types of deadlocks there are, and how you can resolve them.

xCase (RESolution, Ltd.) is a desktop data-modeling software package that has developed into a very useful, yet cost-effective, tool for everyday database design and maintenance.The recently-released "Professional Version 6.01" has much to recommend if you are doing database development in Microsoft SQL Server, Oracle, Sybase, Visual FoxPro, Jet, or many other database platforms.

For years developers have been asking for query over data contained in a DataSet in a way that supports the expressiveness needed by today’s data-centric .NET applications. As part of the .NET framework 3.5, Microsoft® will introduce support for a technology called Language Integrated Query (LINQ), and with this introduction, an implementation of LINQ to DataSet.

Adding indexes to tables is the most effective way to optimize your SQL Server query performance. Tables that you query often may require several indexes in order to handle the various search arguments you send. There is one particular situation where indexes on large tables are a hindrance, though. When you're bulk-copying data into the table, it's best to have either no index at all, or just a clustered index. For those situations it is best to use Transact-SQL scripts... to remove and add those indexes. In this article you'll learn how to use Transact-SQL stored procedures to generate Transact-SQL indexing scripts that are more robust and less laborious than SQL Server's Enterprise Manager scripting tool. See More

SQL Server, like most complex databases, has potential security holes. This article discusses these security holes and how to close them.

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 sche...mes 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.See More

Business Connectivity Services (BCS) is a set of out-of-the-box features, services and tools that enhance SharePoint by streamlining the creation of solutions with deep integration of external data and services into SharePoint!SharePoint 2007 had a similar technology called BDC. But BDC was much more primitive compared to BCS.

In addition to the transaction-based SNAPSHOT isolation level, SQL Server 2005 Beta 2 also introduces a statement-level variation of the READ COMMITTED isolation level called READ_COMMITTED_SNAPSHOT.Part I of this article (see "Snapshot Isolation in SQL Server 2005," July/August, Volume 5, Issue 4) described the transaction-based SNAPSHOT isolation level. At the time Part I was written, just prior to the release of Beta 2, only the SNAPSHOT isolation level was fully docu...mented. With the release of SQL Server 2005 Beta 2, a fuller explanation of a second type of SNAPSHOT isolation behavior has come to light.See More

Jonathan Goodyear (the Angry Coder) January/Febuary 2005

Replication agents are the basic components of SQL Server 2000 replication.Before you settle on the type of replication you want in your application architecture, it is important to understand how each type of replication works and what agents they use. Since the different types of replication use different combinations of replication agents, understanding what those agents do helps clarify the resulting SQL Agent jobs when you install replication. For example, snapshot,... transactional, and merge replication all use the Snapshot Agent to initialize the subscriber's data. Then they employ other agents to implement the replication process. In this article, you'll learn about the roles of the replication agents and learn how to inspect the properties and profiles of the Snapshot and other agents.See More

Replication figures as one of the more prominent features of SQL Server 2000.Replication is a complex application that uses a combination of stored procedures and executables to distribute and copy data between SQL Server databases. If you take care not to get lost in the details and confused by occasionally misleading terms, replication can be an important component in the database architecture of a SQL Server application. In this article, you'll learn about the termino...logy surrounding replication, the types of replication available in SQL Server, and how to configure (that is, install) replication.See More

One of the classic problems with database applications is refreshing stale data. Imagine a typical e-commerce site with products and categories. A vendor’s product list most likely does not change very often and their category list changes even less frequently. However, those same lists must be queried from the database over and over again every time a user browses to that Web site. This is an annoyingly inefficient use of resources and developers and architects have bee...n stuck playing cat-and-mouse trying to reduce the waste.See More

SQL Server 2005 or "Yukon" is going to be a major SQL Server update containing updates to nearly every facet of the program, including T-SQL.In this article I am going to explore some of the new T-SQL features, commands, and capabilities in SQL Server 2005. Because covering everything new in T-SQL would require an entire chapter in a book, I am going to cover some of the more useful and mainstream enhancements.

SQL Server’s Reporting Services is still the best workhorse for getting data to your users. Kevin tells you how to take advantage of some of its more subtle points.

An important principal of software design is that of “least privilege.”Basically, in any given layer of a program, you should only grant minimal access such that the code has rights to only exactly the resources it needs to get its job done-and nothing more. Most SQL Server developers understand this concept: one of the main reasons to use stored procedures is to encapsulate permission to data behind controlled and auditable interfaces, thereby not giving the caller direct access.

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.See More

Two-way data binding can save you a ton of coding, as long as you can get the bound controls to behave the way you want them to.Using the BindingSource and Binding objects in .NET 2.0, getting what you expect in minimal code becomes a whole lot easier. In this article, I will explore how to use the BindingSource and Binding objects to set up associations between complex data sources and bound controls. I'll show you how to keep multiple controls that are bound to the sam...e data source synchronized, and how to control the formatting and parsing of the data in those controls.See More

Testing SQL Server is an often-overlooked area of the software development process, because programmers primarily place testing focus on code residing in the middle tier or the client desktop, rather than the database. However, as SQL Server databases become a more important component of applications, they cannot be left out of the developer's testing process. An important place to start, to ensure your code works the way you want it to, is with SQL Server configuration....The focus of this column is to examine issues dealing with testing SQL Server 2000 databases using SQL Server's built in language, Transact-SQL, as the primary testing tool.See More

When I was a kid, I loved baseball. I lived it 24/7. In the summertime, happiness meant a pickup game during the day and a Phillies doubleheader at night. I’m still a kid at heart and I still love baseball - and I also love SQL Server. And right now, happiness means seeing all the cool new features in SQL Server 2012. There are so many of them that I can’t list them in a single article. So, I’m penning a two-part Baker’s Dozen. The first part of this “twin-bill” (yes, ex...pect a few baseball analogies!) will be 13 new T-SQL and database engine features in SQL Server 2012. The “night-cap” in the next issue will be 13 new features in SQL Server Integration Services and the new Business Intelligence Semantic Model.See More

The new release of SQL Server's Integration Services has many exciting new features

Regardless of your .NET language of choice, managing data is a vital skill for most applications. Developers frequently must work with data at different levels, with different tools, and in different forms. This article is the first in a two-part series on some of the more common data challenges that developers face. In Part 1 of this article, I’ll cover some capabilities in ADO.NET 2.0, ASP.NET 2.0, and T-SQL 2005. Part 2 will feature some additional T-SQL 2005 coverage..., as well as ways to use .NET 2.0 generics.See More

In the conclusion of this two-part series on managing data, I will focus on .NET generics, the ASP.NET 2.0 ObjectDataSource, and some more T-SQL 2005 capabilities. Regardless of whether you work in C# or VB.NET, or whether you spend more time on the front-end or back-end of an application, the ability to manage data will directly impact your contributions to a project. The new .NET generics classes provide you with near-quantum leap in efficiency by allowing you to write... simpler code that accomplishes more. ASP.NET 2.0’s ObjectDataSource class helps you to easily integrate middle-tier data-aware classes to data-bound controls on a Web page. Finally, as you saw in Part 1 of this series (and will see again here in Part 2), T-SQL 2005 provides new language capabilities to help database developers be more productive.See More

In part two of this series on optimizing SQL Server queries I’m going to continue with some T-SQL scenarios that pit one approach versus another. I’ll also look at what SQL developers can do to optimize certain data access scenarios. I’ll also compare approaches with temporary tables versus table variables, and stored procedures versus views.

Kevin takes a look at SQL Server “then and now,” and shows us just how important it is to understand the various tools involved in BI.

Are you moving a Windows desktop application to the browser, and sweating bullets, or perhaps just not quite sure about how all the new Web and data tools work together?With each passing year, Microsoft offers newer and more powerful tools for building rich database applications on the Web. So many and so frequently, in fact, that it can be hard to keep up with the new tools and still meet the requirements of your job! This article will show you how to get the most out o...f the new features in ASP.NET 3.5. The article will also show how you can use features in LINQ, even if you only use stored procedures for data access. And finally, since most applications use reporting, I’ll throw in a few nuggets on using SQL Server Reporting Services.See More

MDX is just like Transact-SQL, right? MDX is English-like and therefore easy to learn, right? Different software tools offer MDX generators and therefore I don’t need to learn MDX, right? MDX isn’t that popular, right?Well, just like the punch-line of the old Hertz car rental commercial, “Not exactly.” If your organization uses OLAP databases, you can add great value by knowing how to query OLAP databases using MDX queries. This article will cover common OLAP query requi...rements and MDX code solutions.See More

For over a decade, many Microsoft database developers have used SQL Server Analysis Services (SSAS) OLAP to create database cubes for advanced business analytics. SSAS OLAP offers many powerful features, although some developers view the learning curve of OLAP (and its supporting language, MDX) as very steep.

A skilled database developer might find it difficult to accept that other tools can increase productivity. When Microsoft released SQL Server 2005 Integration Services (SSIS 2005), I did not think that any tool could possibly make me more productive than the C# and T-SQL code I was writing by hand. After some reconsideration (and subtle persuasion from peers), I discovered that SSIS 2005 contained many features that indeed reduced my development time - WITHOUT sacrificin...g flexibility. Microsoft added new functionality in SSIS 2008 to make a strong product even better. In this article, I’ll present 13 different examples that demonstrate the power of Integration Services.See More

Database developers who learn Microsoft SQL Server Analysis Services (SSAS) know that they face a number of learning curves. In prior Baker’s Dozen articles, I’ve covered many of the steps for creating both SSAS OLAP and SSAS Tabular databases. In this article, I’m going to cover another topic: How to add or change data in analytic databases.

No, the title isn’t a misprint-this installment of the Baker’s Dozen will visit both sides of the planet. These days, many .NET user group meetings focus on database and business intelligence topics as well as hardcore .NET content. Over the last several months, I’ve spent roughly half my time modifying my own development framework for WCF. The result is some basic but functional factory classes I’d like to share. The other half of the time, I’ve been looking at differen...t capabilities in the SQL Server 2005 and 2008 Business Intelligence stack, and solving requirements that BI developers often face. So rather than pick one side and make the other side wait two months, I decided to combine the two.See More

To use a music analogy, many installments of “The Baker’s Dozen” have been like “concept albums,” where most or all of the tips work towards a big picture. Then there are times where I present a series of random tips that are largely standalone and don’t form a pattern. In this article, I’m going to present 13 random tips for SQL Server and T-SQL programming.

Whether you’re new to T-SQL or not, you’re sure to find something interesting among Kevin’s tips for using it, no matter which version of SQL Server you’re using.

This installment of “The Baker’s Dozen” presents a variety of tips and techniques to become productive with data handling techniques using ADO.NET 2.0 in Visual Studio 2005. ADO.NET 2.0 is faster than the first version of ADO.NET; in some instances, significantly faster. While many view ADO.NET 2.0 as more evolutionary than revolutionary, it provides many functions to give developers greater control over data access and data manipulation. It also leverages the new databa...se capabilities in SQL Server 2005. In addition, ADO.NET 2.0 simplifies the creation of multiple-database solutions.See More

rewarding experience of writing and speaking is taking a seemingly complex topic and making it more understandable and accessible. In this article, I’ll show how to create and use OLAP databases and cubes using SQL Server Analysis Services 2008 (SSAS 2008). The benefits of OLAP are significant, even monumental - but like most technologies, reaping the benefits means considerable research and effort into leveraging the tools. In the case of OLAP databases, developers nee...d to learn the differences between OLAP databases and relational databases, and how to use the tools that SSAS provides. In this article, I’ll walk through how to create an OLAP database and how to use the tools in Analysis Services to enhance the OLAP database. By the end, you’ll see why businesses and other organizations see the value of OLAP databases. I’ll also briefly cover some of the plans Microsoft has announced for the next generation of OLAP tools. See More

This article presents a follow-up to my January/February 2005 article, The Baker's Dozen: 13 Productivity Tips for Crystal Reports and .NET, where I presented productivity tips for developers who use Crystal Reports for .NET. In this sequel article, Crystal Reports Redux, I’ll offer some changes to the original article and will present some new material for Crystal Reports development. While I’ll focus on the version of Crystal Reports that ships with Visual Studio 2005 ...Professional, I’ll mention some of the capabilities found in the latest version, Crystal Reports XI.See More

Thinking of upgrading to SQL Server 2005? Or are you using SQL 2005 already and you want to learn more about the language features? Then this article may be for you!Microsoft implemented many new features in SQL Server 2005, including an impressive set of language enhancements. From new language statements for SQL-99 compatibility to new features in response to customer requests, Transact-SQL 2005 helps to increase developer productivity. In this article, I’ll cover most... of the new language features by posing a statement/scenario and then provide some code samples to show how you can use T-SQL 2005 to address the problem. At the end of the article, I’ll talk briefly about Visual Studio Team Edition for Database Professionals, a product that helps a development team to manage databases. Finally, I’ll give you a sneak preview of some features in the next scheduled version for SQL Server (SQL Server 2008, “Katmai”).See More

For several years, database developers have created analytic (OLAP) databases using tools such as Microsoft SQL Server Analysis Services. SSAS includes the MDX programming language for retrieving data and writing custom expressions. These tools remain very powerful for creating analytic applications. However, some view the tools in SSAS/MDX as difficult to learn. In SQL Server Analysis Services 2012, Microsoft has created a second model (known as the tabular model) for c...reating analytic databases. The new model brings the promise of simplified features and (in some cases) even better performance than traditional OLAP. In this article, I’ll provide an overview for this new model and will walk through a basic example of creating an SSAS 2012 tabular model database.See More

Imagine you’re working for a company that has accumulated a tremendous amount of transaction data. The business users want to perform all sorts of analysis, monitoring and analytics on the data. Some OLTP developers might reply with, “Just create views or stored procedures to query all the data the way the users want.” Many companies initially take that approach - however, just like certain technologies and system hardware configurations don’t scale well, certain methodo...logies don’t scale well either. Fortunately, this is where data warehousing and dimensional modeling can help. In this article, I’ll provide some basic information for developers on the basics of data warehousing and dimensional modeling - information that might help you if you want to provide even more value for your company.See More

In this first installment of his ETL articles, Kevin shows you what to do—and even what not to do—when designing your Data Warehouse.

Even with all the new features in the Microsoft SQL Server Business Intelligence (BI), sometimes the only way to accomplish a task is with good old fashioned T-SQL code. (Fortunately, “code” is the acronym for this great magazine!) In this latest installment of The Baker’s Dozen, I’ll present 13 T-SQL programming tips that could help you accomplish different database tasks.

How many software tasks DON’T involve reading through data? Answer: very few. Developers work all the time with database data, XML data, DataSets, collections, lists, and arrays-all with different syntax and functionality for each one. Developers who write T-SQL code often covet set-based language statements when they work against other types of data. Additionally, developers who have coded against strongly-typed custom collections wish they could write SQL database quer...ies with IntelliSense. Language Integrated Query (LINQ), a set of extensions to the .NET Framework for the next version of Visual Studio codename “Orcas”, brings the promise of integrated and uniform query capabilities to increase developer productivity when working with different types of data. While LINQ is a large topic worthy of books, this edition of The Baker’s Dozen will provide a crash course to learn what’s under the LINQ hood.See More

Many application developers face the challenges of working with SQL Server 2000.These challenges include retrieving and grouping large amounts of data, building result sets, and tracking changes to data. All require professional strategies that hold water against a seemingly endless number of possibilities. This installment of "The Baker's Dozen" presents a variety of real-world database situations and how you can use Transact-SQL and SQL-92 to tackle these issues.

New developers often struggle with the .NET DataGrid when trying to replicate grid functionality from other platforms.More experienced developers lament the deficiencies of the .NET DataGrid to address end user requirements. Consequently, many developers seek sophisticated third-party alternatives. In this article, I'll present a set of classes for the DataGrid to help address some of the more common struggles. Although third-party tools always offer more capabilities th...an a native control, this article also demonstrates how it's possible for you to implement some of the functions found in these third-party tools.See More

“Integrate the data tier developer in to the core development life cycle and process.”That is one of the main objectives of Visual Studio Team Edition for Database Professionals, also known under its project name “Data Dude”. Bringing the data tier developer into Visual Studio is the first step in enabling closer integration between the application and data tier developer. Having both environments leverage the same Team Foundation Build (Team Build) system enables daily ...and automatic integration of changes into the build process, enforcing closer integration and shorter feedback cycles between the two originally disjoint disciplines.See More

Visual FoxPro’s (VFP) Data Manipulation Language (DML) is one of VFP’s most compelling features. It is also the most obvious feature VFP developers miss in .NET languages such as C# and Visual Basic. However, Language Integrated Query (LINQ), a new query language for .NET developers is a new feature in the upcoming releases of C# 3.and Visual Basic 9.0 that addresses these shortcomings.

The Data Explorer introduced in VFP 9.0 allows developers to work with different types of data from diverse data sources independent of specific projects.The Sedna update extends this already powerful and productive tool.

In this article you will learn how to isolate yourself from change by taking advantage of the Provider Model.Designing your applications using the Provider Model will allow you to swap components out at runtime, thus allowing you to upgrade them easily.

Before any upgrade development begins, you need to know how users interact with your app. Jason explores one great tool, Mixpanel, and shows how to get the data you need.

The integration of the .NET Common Language Runtime (CLR) inside SQL Server 2005 (SQL CLR 1.0) enabled database programmers to write business logic in the form of functions, stored procedures, triggers, data types, and aggregates using modern .NET programming languages.This article presents the advances to the CLR integration introduced in SQL Server 2008, which significantly enhances the kinds of applications supported by SQL Server.

Before you start hard coding parts of your project to work with metadata in SQL Server, make sure that the functionality you want isn’t already part of the product.

All database developers should know about MySQL.MySQL is an open source database that you can use to develop robust applications without paying the steep licensing fees attached to other database systems.

A well-designed application that uses a relational database management system in the backend should make extensive use of stored procedures.A stored procedure is a named collection of SQL statements that you store in a database. To the client, a stored procedure acts similar to a function. You call the stored procedure by name, you can pass it parameter values, and it can return parameter values back to your client.

Visual Studio 2008 is all about making it easier for developers and development teams to create software for the latest and greatest platforms with technologies such as .NET Language Integrated Query (LINQ), ASP.NET AJAX ,and the Windows Presentation Foundation (WPF) designer, to name just a few.In this brief article I will highlight just a few of the code editing and designer improvements that are new to Visual Studio 2008.

If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.

If you have been working with the ADO.NET Entity Framework, you have probably been extremely eager to get your hands on the next version that is now part of Visual Studio 2010 and .NET 4.0. Long referred to as “EF Version 2,” this version is now called Entity Framework 4 or EF4, to align with the .NET Framework 4.0 version.

SQL Server 2008 is scheduled for release in 2008 and promises to deliver an array of new and exciting benefits to both developers and IT Pros alike.

You may be wondering, with all the hype over XML, what can XML do for me today?You see the power of XML but may be having a hard time figuring out where it belongs in your application development strategy. In this article browser based applications will be discussed from the perspective of using XML as the data transport mechanism.

Imagine the potential of accessing your database without needing to know any specifics about how that data is stored.Imagine the possibility of allowing your DBA the freedom to change the database structure without affecting your software code. Impossible? Not with XPath Queries and XDR Schemas. Using these two methods, practically any developer can write a program to access data in SQL Server 2000, just by knowing the structure of the XML documents returned by the server.