Reflections on the .NET framework — II

Tech Forum - Dr. Nitin Paranjpe

Object Role Connection Manage connection with a data source. Connection can be used by other objects to retrieve and update data. Dataset The container that holds the data returned from a datasource. Dataset does NOT know where the data came from. Data Adapter A conduit between connection and dataset. Dataset is filled with data coming from a data source using a connection. Similarly, data changed in dataset is updated using the data adapter. Data Reader A one-way (forward only) cursor that allows returning rows/results to be read and acted upon. Data reader does not provide for update of the resulting rows. It is purely READ-ONLY.

In the last article, I put forward a different way of learning new technology. The aim is to make the learning more real-life-oriented, more expansive and more effective. As an example of this new method of learning, I had suggested that we choose one simple, yet commonly used object from .NET and explore it. We chose the SqlConnection object from the System.data.sqlclient namespace. This represents the connection object for ADO.NET. As you know ADO.NET offers SQLCLIENT and OLEDB as two different implementations. SQLCLIENT is to be used only with SQL Server. OLEDB is to be used with all other data providers that do not have their specific implementation like SQLCLIENT. Nowadays, Oracle, Sybase and many other RDBMS vendors have released their custom implementations of the SYSTEM.DATA related objects.

While exploring the SqlConnection object, we will need to consider many peripheral topics. These topics would usually be assumed to be known to the learner. But often, this is not the case. Most technical books conveniently assume that the reader knows the larger picture and other links and therefore, don’t even mention the need to brush up these topics.

What does SqlConnection do?

Here I am assuming some background knowledge of ADO.NET. The SqlConnection object is simply the good old CONNECTION object of ADO, with much more functionality. The syntax is:

Dim conn as New SqlConnection()

Key differences

Now in order to utilise ADO.NET effectively, we need to understand one thing very clearly. The earlier implementation of data access using ADO and the current implementation of ADO.NET are radically different. You cannot learn ADO.NET by just finding out equivalent commands from the ADO era.

Therefore you need to know exactly what has changed in ADO.NET. This information is available in MSDN and various other sources. Here are the most important differences:

What has not changed?

It is now important to know another aspect that is often left unsaid. Get this very clear: SQL Server 2000 has not changed after the launch of .NET. Only the way of accessing and manipulating data in SQL Server has been refined and made more object-oriented by providing a richer object model of ADO.NET. You might be wondering what’s so great about this. It is not so simple. Most of us tend to get carried away by new technology and forget the baseline stuff. For example, once you explicitly know that SQL Server is still the same, that can lead to many practical conclusions and best practices. For example:

Examples of possible misuse of ADO.Net

• Using events instead of triggers

  It is very tempting to use the RowChanged event to write code instead of writing it in the good old update trigger! Unfortunately, when to use or not use the RowChanged event is not mentioned anywhere. You have to think about it actively.

• Using dataset level code instead of stored procedures

  Datasets give row-based data handling. SQL syntax provides set-based data handling. Dataset-based handling of data is happening away from the SQL engine in an offline manner. Stored procedures run in the heart of the SQL engine. If you need to refer to other tables and perform updates across data items, it is best managed using stored procedures. Dataset-level data manipulation should be limited to the entities available in the runtime environment, away from data engine(s). Sounds like common sense but I have seen this mistake being repeated too often at too many clients while performing code audits.

Positioning of the SqlConnection object

With the above information, we can now understand the SqlConnection object. SqlConnection is the connection ONLY for SQL Server 7 or above (SQL 2000 is preferred).

The next logical question is: Can we use OledbConnection object for SQL Server data access? The answer is YES. But it is not recommended because this is guaranteed to be less efficient and slower than using SqlConnection. This is because SqlConnection is explicitly written to provide the best performance with SQL Server. It uses all possible techniques to enhance data access and also exposes all possible sophisticated features of SQL Server.

Intellisense: Method overrides

As soon as you try to type the define the object, you will see the tooltip and ‘intellisense’ which explains the syntax of the SqlConnection.

Tooltips were originally invented to explain what a toolbar button meant because there were too many buttons! Now they are a part of IDE intellisense also. The confusing part is the tooltips are so long that they have scrolling text. In case of methods, they scroll to show you different ways in which the method can be called—to show overridden method calls. We generally tend to ignore this. These tooltips capture the up-down arrow keys and scroll within them. This is irritating when you want to move to the next or previous row. But anyway, they are here to stay. The important part is the overrides. In case of SqlConnection there are only two of them (fortunately!). One without any parameters and one with the connection string parameter.

Most of us will find it very simple to understand that you can either pass or not pass the connection information. We will then move on to write the next line of code. That is how new technology is learnt. It is too intuitive to understand these two variants of SqlConnection initialiser and then move on. We know the connection string for this dev machine so we will put it in and go ahead. We have also convinced ourselves that we have understood the SqlConnection initialisation syntax. I call this the "Hello World" approach. But we have to apply a different approach. So here’s the twist:

‘Hello, REAL world’ approach

Don’t just move on and think you have understood the syntax. Why? Because we just lost an opportunity to apply this knowledge to a real-life production scenario.

Let us try and think of real life. From a production environment point of view, the following questions arise:

While thinking of all this, you will finally conclude—why not just use the DSN funda? It was the way we handled connection properties in past. Now you are in for a rude shock. DSNs are not supported in ADO.NET!

When you consider various options that the above questions lead to, you will finally reach the following conclusion: In most cases, it is best to pass the connection string as a constructor to a component in MTS. Now, believe me, this is not written in any reference material with so much clarity and justification. In fact, the constructor functionality has been there in MTS for years. But we have never used it effectively in all possible circumstances. From this, another piece of knowledge emerges:

Nothing has changed in MTS functionality. We need to make use of all the features like pooling, transaction management and so on using native MTS functionality that is exposed by Enterprise Services.

Have you used MTS with COM components in past projects (before .NET)? If the answer is yes, then ask yourself whether you have used MTS just as a setup and installation utility or have you used other features of MTS as well. Now, you know you are in a slippery territory. Believe me, in spite of COM and MTS being in the market for so many years, I am yet to see proper utilisation of all relevant features of MTS in an application.

The baseline features that should have been used are:

It is very rare for a custom-built application (whether in-house or packaged product or application service provider apps) to use all these features effectively. Why so? Is it too cumbersome to use these features? Or is it unnecessary to use all these features? Or you can get maximum benefits just by using the deployment features?

The answer is ‘none of the above’. It is indeed very rare that a team of architects, developers and application designers have actually changed their mindset of writing components to take advantage of MTS. If you ask any techie to describe (in English) how should you write components for MTS, you will only get silence as the answer.

MTS allows developers to write components as though these were being written for a single-user, idealistic scenario. All the complexity of transaction management, security, deployment, and performance is managed by MTS—outside the code.

If you have not understood this till now, please do so NOW. This can revolutionise your IT career! Now, extrapolating from the above knowledge, other great functionality becomes apparent:

If you have independent components which utilise all relevant features of MTS, you can assemble these components in any logical execution order—even at run-time—without worrying about transaction management, security, performance and appropriate invocation. This is the real beauty of the MTS functionality.

Now how is all this related to SqlConnection command? This thought process started when we wanted to decide whether to provide the connection string to the SqlConnection object while instantiating it or whether to pass it from somewhere outside. Now, considering that all business components are going to reside in MTS, it makes tremendous sense to finalise the following guideline.

For all components, the connectionstring should be stored in the constructor rather than keeping it in config files or passing it as an ad hoc parameter.

On the other hand, if the application is never going to use components as such, config file is the right approach.

Now we have done justice to the two variants of instantiating SqlConnection object! Do you see what I mean by a different method of learning? Do you perceive the broadening of your knowledge and understanding of the subject by thinking beyond syntax? I will continue exploring this SqlConnection object (and more importantly, highlighting this new method of learning) next week. In the meanwhile, do give me feedback about this learning experience. I will be glad to incorporate your feedback and refine my content for everyone’s benefit.

About the Author:Dr Nitin Paranjape is the Chairman and MD of Maestros (Mediline). He is a consultant with many organisations, covering appropriate technology utilisation, business application of relevant technology, application architecture and audit as well as knowledge transfer. He has authored more than 650 articles on various technology-related subjects. He can be contacted at nitin@mediline.co.in