Advice on programming against SQL Server using C#

I am starting at a company in a few days where they have massive SQL Server databases, larger than I have ever worked with before.

The also use C# applications to work with massive DataSets. I have been told that the kind of programming that works okay against smaller tables does not work as well against larger tables.

I want to try and prepare a bit over the weekend. I need to acquaint myself with some of the common approaches to this kind of massive data processing. Some of it I will pick-up just by working there and talking with other programmers, I am sure, but in addition to that, I'd like to prepare by getting some advice here.

Until you are there, it is hard to know where to concentrate your study. It depends on the approach that they use in-house.

In big development shops, they often forget about the basic objects used in ADO.NET such as DataTables and DataSets, and they build their own classes and collections. DataTables and DataSets do a lot of work for you, but at the cost or an overhead that is often too heavy when working with a lot of data.

It is more work at the start, but it pays up in performance and maintenance cost in the long run. In such a company, your preparation would be to concentrate on object oriented programming and how to best use ADO.NET with your own classes. That usually means that you would try to learn well how to work with a DataReader, and forget about the DataAdapter.

This is the approach I personnally prefer, because as an experienced programmer, I know that it is usually the most cost effective way to do things when considering the whole life of the application instead of concentrating on the time it takes to put it in the hand of the users. At the same time, it is the most interesting and the most creative way to go for the programmers. I would not work in a shop that does not have that philosophy.

But this approach does not bring results fast, and a lot of employers or clients do not understand that the time the developers spend designing and coding aclass library or a framework, a time in which they see no screen to give the impression that the project is advancing, will be worth it in the years of maintenance that applications built around big databases usually requires.

In other shops, they go for a fast run. It is usually not as good for performance with huge databases, but since you start to see results faster in the development cycle, they have a feeling that they are more productive. Who cares about the long run nowadays. Then your aim might be exactly the reverse of what I said before. You would concentrate your preparation on how to use the DataAdapter, a tool that is easier to use to create DataTables and DataSets, and is supposed to do the routine work of updating the database for your. The difference there for big databases against small databases is usually that you would always try to get into memory only what you need, even if it means that you often have to to go the database, while with smaller database, you sometimes bring everything in memory and filter with a DataView without ever having to return to the database.

Some shops will not even deal with the basic coding required by ADO.NET. They will use all the tools provided in .NET, such as LINQ and Data Entities, as well as third party tools that make ADO.NET work for you. You might never see a line of pure ADO.NET code in those shops. This improves the development time, sometimes at the expense of control, but who cares. Wow, the application is ready in 2 months instead of 6. Who wants to wait nowadays. They do not care about the stability of the application, they are just so full of themselves because the application took so little time to be delivered.

If you are going for such a company, then read the maketing material put forth by Microsoft. This is the stuff they use to sell .NET. And as is the case with most salesmen, the flash they put in your eyes is usually not the best part of the product they sell (little editorial here, sorry).

So, as you can see, depending on the philosophy and experience of the people you will be working with, the things you need to learn can be very different. Personnally, my decision to go to work for a company or another would depend a lot on that factor.

Unless you know the philosophy of the company where you will be working with, there is no way to prepare unless you know how they work in there. The question is not one of big database vs small database, it is a question of how the company approach their work.

Featured Post

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…