Improving Performance with Connection Pooling

Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).

If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.

Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.

Connection pooling is enabled for both OleDb and SqlClient connections by default.

To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection.

To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.

Connection pooling options that you can add to the SQL Server connection string:

• Connection Lifetime— Destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed.
• Connection Reset— Indicates whether connections should be reset when they are returned to the pool. The default value is true.
• Enlist— Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.
• Max Pool Size— The maximum number of connections allowed in a single connection pool. The default value is 100.
• Min Pool Size— The minimum number of connections allowed in a single connection pool. The default value is 0.
• Pooling— Determines whether connection pooling is enabled or disabled. The default value is true.

SQL Injection Problem

SQL injection is a strategy often taken by hackers for attacking databases.

Example1:
An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'

It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.

Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --

There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere.

Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.

Running an exe or program as SQL Server Job

This can be achieved by SQL Server Agent which is a Windows service to run scheduled adminstrative tasks or commonly know as jobs. SQL Server agent uses SQL Server t ostore job information. A job consists of steps which can be set using steps wizard during job scheduling.

Job Scheduling process:

• Connect to the SQL Server where you want to schedule your job.
• Check if SQL Server Agent (you can find it at the bottom most entity) is running, if not run it and set it to run automatic.
• Right Click SQL Server Agent. Select New -> Job. This will open a wizard to schedule a new job.
• Select Steps from options given on the left side of wizard. This is the main place to set your exe\program as a running step. Give some name to this step, Select Operating System (cmd) for the Type and give the full path of exe in Text box for Command. Below is a sample step set up for running test.exe.
• Now Select the job schedules option from left to set the frequency. Give some name to schedule, selection other options as shown in the figure below.
• If you want you can set optional Alerts and notification (using SQL notification services) to send out email in case any things fails, succeeds or at any specific event.
• The targets option set the DB to look at or put data in. It should commonly be the localhost but you can set a remote SQL Server too here in case you have some DB interaction but then make sure that the account which you are using should have access to the remote server.

Your job is now scheduled to run everyday test.exe. just to make sure that everything run properly go to Agent -> Jobs -> NEW_JOB (the one you just created), right click NEW_JOB and select “start job at step”. This will run the job instantly for you and show the status if the job run successfully or failed.

So now you finally done.