Reflections on .NET - III

Tech Forum - Dr. Nitin Paranjpe

In the last article, I have 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. In this example I continue to explore and learn the SqlConnection object.

Connection string and Connection pooling

The ADO.NET data provider for SQL Server automatically manages connection pooling. If multiple requests are sent for the same connection properties, existing connections are used rather than opening a new one.

A new item is added to the pool for each unique connection string. When a connection is closed, it is returned to the pool. When a new request arrives, the pool is checked for available free connections with same properties. If one is found, it is allocated.

It is very important to close a connection using the Close or Dispose method. Otherwise the connection is still considered as busy and will not be returned to the pool. Therefore, if you expect to have some time lag between two data access calls, it is better to open the connections on demand. Without pooling, this type of behavior would have resulted into an overhead.

Connection pooling works automatically. But there are many parameters that are passed through ConnectionString which allow you to control the pooling.

Pooling
Values are ‘true’ or ‘false’. Using this you can disable connection pooling. Why would you want to disable such a good feature? Think…

Connection Lifetime
This is the total time for which connection will be kept alive. When a connection is added to the pool, this time is checked. If it exceeds the lifetime, it is removed from the pool. As mentioned in the books online, this is useful for clustering environments. How? Well, you will now have to spawn your reading and learning for clustering. I won’t do that for this article. But you are now getting the hang of how many things can relate to each other and impact the adequacy of your knowledge.

Max and Min Poolsize
These parameters set the minimum and maximum connections in the pool. Defaults are 0 to 100.

There are more connection string parameters. Read about them.

How do you know pooling is working? How do you know it is working effectively?

There are 5 performance counters for ADO.NET SQLCLIENT pooling itself.

Now this brings us to another revelation:

All the connection pooling details mentioned above will work if the SQLCLIENT data provider of ADO.NET is used.

If you have other applications talking to the same database using OLEDB, ODBC and any other non-ADO.NET functionality, there will be NO connection pooling.

If you really want connection pooling and efficient management across providers, it has to be done at a place that is as close to the database engine as possible.

Food for thought

Have you ever thought of this paradox? The entire .NET syntax ADO syntax and is very nicely object oriented. It consists of a set of well thought out hierarchies, encapsulating generic functionality at higher level and providing specific features at lower levels.

Now with this level of sophistication, why do we still require a cumbersome semicolon delimited list of parameter–value pairs with arcane and long names called Connection String?

It would have been much simpler to have a connection object with all these parameters as properties which can be set at runtime and then we call the Open method. Think about it… Let me know if you find a clear logic for this.

Properties, methods and events
The main difference in earlier versions of development tools and .NET is the strong object orientation. Every facility required by the programmer, whether it relates to core Windows API, storage, Web, data access or security, is available as a well thought out and structured object model.

SqlConnection object itself has the hierarchy :

This way the thinking and learning process also becomes more structured and coherent. Each object can have more objects as child objects. In addition these have properties, collections, methods and events. The great Intellisense provides you with a quick list of available items.

Using Intellisense Intelligently

How do we use this intellisense? You get an alphabetical list the momemt you type <objectname> followed by a dot.

Usually we just navigate to the item we want and proceed further. If it is a method call, multiple overridden calls, if any, are listed.

First we have to choose the right override and then proceed to specify parameters – mandatory as well as optional.

Nothing great here. We do this all the time.

But here is a great tip for learning quickly and learning online:

Why is this such a great tip? Because, it provides a quick snapshot of the functionality available to you at that object level. Of course you have help files and MSDN and quick help and all that. But just think how often do we press F1 while programming just for learning? Pressing F1 when you have a problem is a different issue. Because we don’t press F1 often – proactively – for explicit learning, it is a good idea to use the opportunity of utilising the intellisense popup instead. It requires no extra effort. Just a small pause to scroll through each item and look at the tooltip. Then another small additional effort to think about each item and see if you can understand its significance and usage scenario.

Now let us use this method with SqlConnect object.

SqlConnect members

Here is the intellisense list for the SqlConnect object. (See table below) Here I have only mentioned properties that are specific to SqlConnection object. I have not listed properties that are inherited from generic objects like lifetime service, component class and so on.

One problem with the intellisense list is that it is in alphabetical order. While learning from this list, we need to arrange it in logical order first. Let us do that. The parameter info is also mentioned.

Open method
This is a simple enough method. It just opens the connection. If possible it gets the connection from the pool. Otherwise it will create a new one.

Also remember that the best way to eliminate all password related complications is to use Windows Integrated Security.

Close Method
This method closes a connection and releases it to the pool. Most commands that actually require an open connection have a nice method of minimising the time for which the connection was kept open.

Datareader, DataAdapter and SqlCommand objects first check if the connection is open. If it is not, they open it, do the work and close it. If the connection was open beforehand, these objects do not close the connection.

Moral of the story?

BeginTran
Begin Transaction is the good old command associated with any transaction based DBMS. The Begin Tran command is mentioned here as BeginTran method. Nothing new so far. But then what would be the next expected command? Obviously ‘Commit’. Right? Now you are in for a surprise. There is no equivalent of Commit at all. This is problem 1.

Where has Commit gone?

The answer is that the way the connection object used to work earlier v/s now has changed. The BeginTran method actually returns a Transaction object. This object has a method called Commit. This provides better control over transaction management, if you want to control it manually.

This brings us to another issue. Do you want to control transactions explicitly throught the SqlConnection object? Or you want to use the COM+ mechanism for managing transactions originating from components?

Give it a little further thought. How do we convert this apparent confusion into a very sturdy knowledge foundation for data access? Simple. Let us just enlist all available methods as of now (within .NET or outside .NET) for managing transactions.

Remember what was stressed earlier – The underlying database has NOT changed. What has changed in .NET is only a more sophisticated, efficient and structured way of handling data from the database.

Therefore, non-.NET methods also need to be considered while learning data access in .Net.

Here are different ways of transaction management.

There may be more ways. But the point here is that we need to consider all these to learn appropriate usage of transactions.

Learning from raw SQL commands

With so many different transaction management methodologies, it is possible that we lose track of actually what happens under the hood. And this can often result in inefficient data handling, which is not even noticed.

Remember, whatever may be the transaction management and frontend methodology, SQL Server only understands its native commands. Due to this object orientation and COM+ type of encapsulation, lot of commands are autogenerated and sent to SQL. There is a lot to learn from viewing these raw commands. Moreover, this can also lead to many optimisation opportunities.

Ideally, while learning as well as regular coding for data access, SQL Profiler should be kept oopen.

ChangeDatabase
The current database is available in the Database property. ChangeDatabase changes it to a different one. Simple enough to understand. Here are some additional points you should consider before proceeding further.

Now, if you really need to change across databases within a given application, what does that indicate? Nothing technical actually. It indicates that you have ended up with heterogeneous applications! It may even be an opportunity for server consolidation to minimise maintenance costs and improve centralised administration.

Packetsize
This specifies the size of packets (in bytes) which will be used to communicate with the database server. Default is 8k. Why? Because pagesize of SQL Server is 8k. (This is a guess, by the way.)

When to change it?

Want to know the reality? Nobody ever thinks it needs to be changed. This is like thousands of other default values which remain default because nobody is interested in knowing when to change them!

The concept

First you have to know what type of data transfer does your application generate. For example, a document management solution which stores scanned pictures in Image columns will generate a huge data flow. On the other hand, a shopping type of application will generate a much smaller sized data flow.

Now, depending upon the type of data transfer activity likely to be generated by your application, you should decide whether you need to change the packetsize.

In case of very performance sensitive and heavily used applications, you may even have to experiment to find out if changing the packet size improves
performance.

State Property
Currently only Closed and Open are possible values. If you read the help, there are some interesting values like Boken, Connecting, Executing and Fetching. These are only to be introduced in future. Why mention them now in the current help? No idea!

Summary

Now our SqlConnection object learning comes to a logical conclusion because all the base members have been covered. The learning of efficient data handling, optimal coding and creating customer value continues…

Happy Learning!

Intellisense list for the SqlConnect object

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