Connection object

Can any one tell what is the disadvantage of Using MicrosoftApp开发者_运维问答licationsDataBlock.dll(SQLHelper class).

How can we sustain the maximum connection requests in a .net application

If we have lakhs of requests at a time then is it ok to use MicrosoftApplicationsDataBlock.dll(SQLHelper class).

More "modern" dataaccess libraries are generally preferable, they provide better performance, flexibility and usability. I would generally avoid the old SQLHelper class if possible. :) I worked on an old project where a dependency on the SQLHelper class kept us from upgrading from .NET 1.1 to .NET 4.

For awesome performance, you may want to take a look at Dapper, it's used here at Stackoverflow and is very fast and easy to use.

But if you're looking at 100k simultaneous requests (second, minute, day??) you probably want to avoid the database altogether. Look at caching, either ASP.NETs own built-in or maybe something like the Windows Server AppFabric Cache.

Disadvanatges of SQlHelper -> Dont think there is any . You get a lot off code for free to open and close connection , transaction handling etc... nothing that you cannot write yourself but number of connections that you can send from your app is not a factor of the SQLhelper or any other DbHelper you use. In any scenario you call system.data.sqlclient which is an API to connect and work with sqlserver... When you launch N connections they all go the SQL Server Scheduler services. If all the CPUs are busy working on available SPIDs(processes) the new ones go in queue. You can see then usign sp_who2 , or select * from sys.sysprocesses. The waiting SPIDs are offered CPU cycles at intervals (based on some kind of algo that I am not sure of) . This is called SOS Scheduler Yeild where one process yeilds the scheduler to other... Now this will fine till you dont reach maximum concurrent connections that server can hold. For different version of SQL Server (developer/enterprise etc) this is different. When you reach this MAX no of concurrent connections the SQL Server as no more threads left in its thread pool to allow your app to get new connections.. in these scenario you will get SQL.Exception of connection timed out...

Long story short you can open as many connection and keep them opened as long as you want using sqlhelper or traditional connection.open. In good practice you should open a connection , do an atomic transaction , close the connection and dont open too many connections coz your box (sql) will run out of connection handles to provide to your app.. SQL helper is just a helper , best practices of ADO.NET programming still applies no matter your use it or dont..