DataReaders, DataSets, Connection Pooling, Cache and SP’s
| · Connection Pooling in ADO.NET;
· Reading DB data using DataReaders;
· Using memory DataSets;
· Performance with Stored Procedures.
|
· C#, Visual Studio 2003, ADO.NET, ASP.NET.
|
In this article I will highlight some interesting tips to optimize ASP.NET applications. We’ll get acquainted with the powerful resources of data cache, practical use of stored procedures, connection pooling and other advanced techniques. You’ll learn how to use DataSets in memory to avoid unnecessary queries to the SQL server and, therefore, optimize data traffic. You’ll also be introduced to the interesting ADO.NET connection pooling resource.
To
build the examples, I’ll use Visual Studio .NET 2003 and the SQL Server
2000 as database. The applications will be made using C#, but they can
easily be written in VB.NET, in case you want to use that language.
Connection Pooling
In Visual Studio, click File>New>Project (or press Shift+Ctrl+N) and in the New Project window choose ASP.NET Web Application in the item Visual C# Projects. In the Location option, name the application and then click OK.
Starting from ToolBox, places a SqlConnection in the Web Form. Select the component and in the Properties window select New Connection in the property ConnectionString. In the editor that appears inform server IP address or server name in the first entry box. In User name and Password inform standard user and password for database access. And, finally, choose the Northwind database and click Ok. If you want, click the Test Connection button to
check if the parameters are correct. By doing this, we setup the SQL
Server connection using SQL provider, the first performance tip (never
use OleDB or ODBC in this case).
Notice that in User name and Password
we inform a standard database access user and password, but could have
used integrated authentication. However, here comes the second valuable
tip for optimization: supply a fixed user and password, in such a way
that all users that connect to the application use the same credentials.
If it’s necessary to restrain a certain user’s access, define that in Web.Config authorizations. Supplying a fixed user will make ADO.NET use the Connection Pooling resource effectively, without having performance loss.
Connection Pooling
is the mechanism that allows ADO.NET to reuse database connections.
Picture the following situation: a user access the application, we
connect to the database to extract information and show it in the form.
After that, we end the connection and feedback the result to the
browser. Since Web applications are state-less, if this exact
same user or another connects to the application, a new connection with
database will need to be reestablished. Connecting to the database at
each user’s request is literally a suicide in Web environment, where an
application can have hundreds and even thousands of simultaneous
connections.
The
ADO.NET solves this in a sufficiently elegant manner: after the page is
sent to the browser, the connection with database isn’t released, even
if you’ve explicitly called SqlConnection close method. The ADO.NET automatically saves the connection in pool
(picture this as a kind of connections cache). That is, the connection
stays open with the database and persists between requests. When another
user connects to the application, the ADO.NET checks if there exists an
available connection in pool and in case it finds one, uses it.
With this, all the time needed to locate the database server, establish a
connection, authenticate a user and check permissions will no longer be
taken at each request.
And
the best part of it all, you don’t need to do anything to use this
resource, since it’s already activated by default. Creating a Connection
Pooling mechanism manually through code is something extremely
complicated (unfortunately, I’ve had to got through that effort in a
given point in time). In ADO.NET, we’ve already got that ready in the
framework itself. Productivity is one of the strong points of .NET.
Note: Connection Pooling can only be used in a multi-thread environment (a Web application, for example), where we have several simultaneous threads processing client requests. It makes no sense, for example, use Connection Pooling in a traditional Windows Forms application (two layers). Internally, Connection Pooling uses an interesting resource of the Operational System to provide its functionality: semaphores
You can even control how ADO.NET works with Connection Pooling, making some adjustments in SqlConnection’s ConnectionString property. We can specify some parameters, see the main ones in Table 1.
| Maximum number of connections that can stay in pool. | ||
| Minimum number of connections that can stay in pool. | ||
| Indicates if Connection Pooling is registered. |
Table 1. Connection Pooling Parameters
Observe
an example of how we could use some of these parameters in our
application’s connection string (you can make this change straight in
the ConnectionString property, starting from the Properties window):
workstation id=ASUS;packet size=4096;user id=sa;data source=ASUS;persist security info=False;initial catalog=Northwind;Pooling=True;Min Pool Size=50;Connection Lifetime=120;
Attention: always use the same ConnectionString (with the same values for all parameters) in all connection objects, so that they share the same Connection Pooling.
Using a DataReader to display data in a DataGrid
Following up on our example, first we’ll see how to display data from a given SQL Server table in a DataGrid control, using our previously configured connection.
The fastest way to extract data from a database is using a DataReader (a SqlDataReader, in case of the SQL Server provider). A DataReader is responsible for the reading of the data returned for a SQL query, using a data cursor. It is fast for the following reasons:
· It is unidirectional: navigation through records is made in a sequential fashion (forward-only).
Basically, we read a record to do something with it (display data in a
control, for instance) and navigate to the next record;
· It is read-only: it isn’t possible to modify a DataReader;
· It doesn’t make cache: after a record is read, it is discarded from the memory.
The code in Listing 1 shows how to use a DataReader (place the code in the Web Form’s Page Load). Here we establish the connection to the database using SqlConnection’s Open method. After that, we use a SqlCommand to execute a Select command in the Products table. To run the query, we call upon the ExecuteReader method of SqlCommand (here called cmd), which return value is attributed to DataGrid’s DataSource property.
using System.Data.SqlClient;
...
private void Page_Load(object sender, System.EventArgs e)
{
sqlConnection1.Open();
try
{
SqlCommand cmd = new SqlCommand("select * from Products",sqlConnection1);
DataGrid1.DataSource = cmd.ExecuteReader();
DataGrid1.DataBind();
}
finally
{
sqlConnection1.Close();
}
}
Listing 1. Using the DataReader
But where it is the object SqlDataReader? A DataReader is never instanced directly. You will always get the reference to an object of this type through the calling upon a SqlCommand’s ExecuteReader method. You can then use the return object to sweep the obtained resultset, using its Read method. Here it wasn’t necessary to do this sweeping manually, since we used the DataBind resource of the ASP.NET for data connection with DataGrid, through the DataSource property.
Notice that all the execution code is wrapped in a try...finally block. The code inside the finally block will
always be executed, unconditionally, even if an exception occurs (for
example, if the command SQL typed wasn’t valid). With this we make sure
that SqlConnection’s Close is always called after the execution of the code inside try, returning the connection to the pool. Another tip is to always keep the minimum code possible between Open and Close, only what makes use of the open connection.
Using DataSets in Cache
Without a doubt the main component in ADO.NET is DataSet.
It can be used with any of ADO.NET providers, representing a data
structure in memory. Here the term “memory” relates to the fact that we
can create a query to a database table, extract information and use them
after the connection is closed. For this reason, applications that use
this component are known as “disconnected”.
While a DataReader demands that a connection is active so that a data reading is made, a DataSet can
get its data once and keep them in memory for posterior use. This
assures even more the scalability of ASP.NET applications. It’s up to
you to detect when it is better to use a DataReader or a DataSet.
To
be clearer, we’ll imagine a scenario: you work for a university and
need to build the registration page for that institution’s application
forms. In this page, there’s a form for candidate’s register, where
there are some TextBoxes for the filling in of fields such as Name, Address, Date of Birth, etc. The only dynamic information (that comes from a database) is in a DropDownList,
where the candidate can choose the course to which he wishes to
subscribe to. This information is obtained from a database table, that
contains all the courses with available vacant.
Now answer one thing: why connect to the database to fill in the DropDownList, every
time a user opens the page, if the courses never change? In a situation
such as this, the courses’ table would be modified in the database
probably once a year or semester. This is a typical example of where we
can use a DataSet instead of a DataReader.
Let’s see how to use the resource in the practice. Start a new ASP.NET application, following the same steps of the previous example. Place a SqlDataAdapter in
the form and, in the wizard that’ll open, chose the connection that
we’ve created previously. Use the following SQL instruction to get data
from the Categories table (that stores information of product’s categories):
SELECT
CategoryId, CategoryName
FROM
Categories
Place a DropDownList on the form and on the Page_Load of the Web Form type the code presented in List 2.
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
DropDownList1.DataSource = dsCatProd();
DropDownList1.DataMember = "Categories";
DropDownList1.DataTextField = "CategoryName";
DropDownList1.DataValueField = "CategoryId";
DropDownList1.DataBind();
}
}
Listing 2. Page_Load event code
Here, we are simply testing if the page is being loaded for the first time (IsPostBack) to then initialize the DropDownList. Observe that we’ve attributed the value of the DataSource property for a function called dsCatProd (seen in Listing 3), that returns a DataSet. The dsCatProd function as shown in Listing 3.
private DataSet dsCatProd()
{
if (Cache["dsCatProd"] == null)
{
DataSet ds = new DataSet();
sqlDataAdapter1.Fill(ds,"CATEGORIES");
Cache["dsCatProd"] = ds;
}
return (DataSet)Cache["dsCatProd"];
}
Listing 3. dsCatProd Function
In the previous code, we tested if a variable with the name we defined (“dsCatProd”) exists in the Cache. If it does exist, it’s because DataSet is already in memory (a user has probably already made the query previously). If not, we’ll remake the data cache, by calling the SqlDataAdapter’s Fill method to fill the DataSet’s data. And, finally, we place DataSet in memory (Cache object).
The Cache object is
used in ASP.NET to share data between all of the application’s users.
Even after the request is processed, the information remains in the
server memory and can be used later. You can place as many objects as
you wish in memory, needing only to supply a different name for each
one, but be careful not to exaggerate in the amount of information that
you store in the server.
Note: ASP.NET allows you to use a state server, allowing the cache stored information and session to reside in a process different other than the aspnet_wp.exe (the
process used by the ASP.NET to run Web applications). It is also
possible to specify a dedicated server (that has more memory, for
example) only to store cache and session objects. Another alternative is
to persist the sessions in the database itself, to save memory. State Servers are also used to share session data when multiple servers are used.
Figure 1 shows
the application running. Make a test: open a second browser and access
the same page however, stop the database server before. Observe that
data will be shown even if the SQL Server is disabled; indicating that
the information really hadn’t been obtained from the database, but from
the cache that was already stored in the server memory.
Figure 1. Using a cached DataSet
Using DataViews
Let’s
improve the example. We’ll allow the user to see the related products
when he chooses a certain category. Instead of creating a new query to
the database after the selection, we’ll filter a query that will already
be resident in memory. That is, we’ll keep all the products in cache and use a DataView for filtering.
Place a ListBox in the form and a second SqlDataAdapter, configuring the following SQL instruction, which returns all the records from the table of products (Products):
SELECT
CategoryId, ProductName
FROM
Products
Instead of using a second DataSet
to store the products, we’ll use the same created previously. This is
possible in ADO.NET; all that needs to be done is pass the same DataSet as a parameter for the Fill method of both the SqlDataAdapters. Include then the following code, just below the Fill of the SqlDataAdapter1, which is in dsCatProd method:
...
sqlDataAdapter2.Fill(ds,"PRODUCTS");
Now, when the page is opened for the first time, DataSet will
contain in memory data on all the products and categories of the
database. All we need to do is manipulate this information in the best
way, without the necessity of querying the database at every request.
For example, to display the products related to the selected category,
modify Page_Load as shown in Listing 4.
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
// DropDownList code
}
else
{
ListBox1.DataSource = dvCatProd(DropDownList1.SelectedValue);
ListBox1.DataTextField = "ProductName";
ListBox1.DataBind();
}
}
Listing 4. Updated Page_Load event code
The code is similar to the used one to fill in the DropDownList, except that now we are using a DataView as data source for the ListBox control. The dvCatProd function receives the code from a category as a parameter and returns the related products, through a DataView, obtained from the memory DataSet. Its code is shown in Listing 5
private DataView dvCatProd(object CategoryId)
{
DataView dv = new DataView();
dv.Table = ((DataSet)Cache["dsCatProd"]).Tables["PRODUCTS"];
dv.RowFilter = "CategoryId = " + CategoryId.ToString();
return dv;
}
Listing 5. dvCatProd Function
A DataView is ideal to be used with memory DataSets. It allows you to filter and organize resultsets,
supplying different visions of a same data set, without using any kind
of SQL instruction or extra communication with the database. It is worth
remembering that you can have several DataViews acting the same DataSet.
The last step is to setup AutoPostBack of DropDownList to True, so that the postback is made in the server when the user chooses an item from the control. Figure 2 shows the example in execution.
Figure 2. Using DataViews from a DataSet in memory
It is clear, if the data is modified in the database, they will not be reflected in the DataSet
of cache. It is your job to remake the cache whenever necessary.
ASP.NET possesses some resources to make this process easier. We can,
for example, specify a cache expiration criterion, or create a
dependence mechanism.
Stored Procedures
We could not speak of ASP.NET applications optimizations with ADO.NET without showing the use of Stored Procedures.
They drastically increase the speed of Web solutions, as much in the
obtaining of data from the SQL Server as in the update, insertion or
exclusion of records. This happens because SQL server can optimize
execution plans and pre-compile these instructions that reside in the
database, and no longer in the customer application, which only takes
charge of passing the appropriate parameters.
In this example, we will build a ASP.NET application that will make effective use of Stored Procedures,
in such a way as to demonstrate the increase in scalability. As is
custom, you’ll learn how Visual Studio can help us in this process,
automatically creating the procedures in SQL Server through wizards of SqlDataAdapter.
In a new ASP.NET application, we will put some TextBoxes for data entry in the Web Form. To make this easier, we will use only the table’s main fields (ProductName, CategoryID and UnitPrice) and we will work only with the addition operation. The names (ID property) of the TextBoxes must be as tbProductName, tbCategoryID and tbUnitPrice, respectively. Put some Labels and setup the Text to indicate what be must filled in. A Button with the Text “Insert Product” will be used to add the information in the database. Use Figure 3 as a reference to setup your form.
Figure 3. Main form of the application, with TextBoxes
The first step is to place a SqlDataAdapter from the Toolbox. An wizard will be showed, which will help in the setup of the various component options. Click in Next and in the screen that is displayed, click the New Connection button. Fill in the connection information, informing the SQL server name, user/password and database. Click in Test Connection to see if everything is working properly. Next, click OK and, back to the wizard, click Next.
The next screen is very important in the SqlDataAdapter
setup process. This is the moment when we have to inform VS.NET how the
component will gain access to the database: using standard SQL commands
(Select, Update, Delete and Insert) or through Stored Procedures. Since we haven’t yet created the procedures in the database, we’ll ask VS.NET to do that automatically for us, by selecting the last option. Next, click Next.
In the following screen, we have to setup the query to be used by SqlDataAdapter in order to obtain SQL Server data. To assist in the creation of the query, we can use the Query Builder option, which will open a visual editor. In the Add Table window, double click Products and, after that, click Close. In the Query Builder, select the fields ProductID, ProductName, CategoryID and UnitPrice (the others will not be used in this example). Observe that the Select command is being generated automatically by the IDE. Based on Select, the IDE will then generate the codes of the procedures. Click Ok to confirm and then Next.
In the next screen, we can inform the names that will be given to the new procedures
to be created in the SQL Server. Use the standard that you desire, here
I called them “Upd_Product”, “Ins_Product” and so forth. In this same
window, observe that we have the option “Yes, create them in the database for me”, indicating that the IDE can create the procedures in the database.
Clicking in Preview SQL Script, we can visualize the DDL code generated by the creation of the Stored Procedures. Observe that each procedure receives entry parameters, which are used to complement the SQL instructions that they use (Insert, Update, Delete and Select).
Click again in Next and check that in the last screen of the wizard a summary is displayed, indicating the actions to be made by the IDE. Click Finish to confirm the operations. At this moment, the IDE performed a series of operations for us, they are:
1 – Creation of four Stored Procedures in the database called, respectively, Sel_Products, Upd_Products, Ins_Products and Del_Products;
2 – Addition of two components to the designer of the application’s main Web Form, one SqlConnection and one SqlDataAdapter;
3 – Initializing of the SqlDataAdpater’s internal SqlCommands (DeleteCommand, InsertCommand, SelectCommand and UpdateCommand properties);
4 – For each internal SqlCommand described in the prior step, the IDE did the following:
· Reference to the SelConnection1connection through the Connection property;
· Setup CommandType for StoredProcedure (if there were traditional SQL Statements, this property would be setup as Text);
· Set the CommandText property to the respective Stored Procedure created in the SQL Server;
· Initialize the necessary parameters in the collection Parameteres.
The last step is to codify the Click event of the WebForm Button so that it executes the Stored Procedure and inserts the data from the form in the database. All we need to do is to capture the values of the TextBoxes and use them to fill the SqlDataAdapter InsertCommand parameters. The code of Listing 6 shows how to do this.
private void Button1_Click(object sender, System.EventArgs e)
{
SqlCommand cmd = sqlDataAdapter1.InsertCommand;
cmd.Parameters["@ProductName"].Value = tbProductName.Text;
cmd.Parameters["@CategoryId"].Value = tbCategoryID.Text;
cmd.Parameters["@UnitPrice"].Value = tbUnitPrice.Text;
try
{
cmd.Connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}
}
Listing 6. Configuring the insertion using Stored Procedures
Remember to add the namespace System.Data.SqlClient in using. Run the application to test the insertion from the ASP.NET form. Inform the values in the TextBoxes and click in the button Insert Product to insert the data in the SQL server using the Stored Procedure created. Select the table and observe that the register was included successfully.
Obtaining data using Stored Procedures
In this second example, we will see how to create and gain access to a Stored Procedure that returns a data set, showing data in DataGrids. Our Stored Procedure will be special: it will return two ResultSets, in such a way as to optimize the solution, containing data from the tables of Categories and Products.
Instead of using the IDE wizard to create the procedure, we will use the Enterprise Manager of SQL Server. In the Northwind database, locate the item Stored Procedures and right click on it, choosing the New Stored Procedure option (name it “Sel_ProductsAndCategories”). Click the Check Sintax button to verify your typing. Listing 7 shows the script.
CREATE PROCEDURE dbo.Sel_ProductsAndCategories
AS
SET NOCOUNT ON;
SELECT * FROM Categories;
SELECT * FROM Products;
GO
Listing 7. Stored Procedure Code Sel_ProductsCategories
In VS.NET, start a new ASP.NET application. Place a SqlConnection and setup the ConnectionString to access the Northwind database. Place also a SqlCommand and set your Connection to SqlConnection1. Set your CommandType to value Stored Procedure and in CommandText type Sel_ProductsAndCategories. And, finally, place two DataGrids.
In the Page_Load type the code in Listing 8. In the code, we call the ExecuteReader method of the SqlCommand to run the Stored Procedure, instead of the ExecuteNoQuery. This because the Stored Procedure returns a result set (actually, two), that it is returned in a SqlDataReader object (remember to add the System.Data.SqlClient in using).
Next, we make the DataBind of the DataReader for the first DataGrid, that will show data from the Categories table. In order for the SqlDataReader to advance to the second ResultSet, we call its NextResult method. We then make the DataBind for the second DataGrid, that will show data from the Products table.
private void Page_Load(object sender, System.EventArgs e)
{
sqlConnection1.Open();
try
{
SqlDataReader rd = sqlCommand1.ExecuteReader();
DataGrid1.DataSource = rd;
DataGrid1.DataBind();
rd.NextResult();
DataGrid2.DataSource = rd;
DataGrid2.DataBind();
}
finally
{
rd.Close();
sqlConnection1.Close();
}
}
Listing 8. Using a SqlDataReader to extract data from the database through Stored Procedures
With this, our solution is already sufficiently optimized, since we are making use of DataReaders (the fastest way to read data from the database) along with Stored Procedures. Observe, also, that bringing two ResultSets in this, makes the result a lot faster if compared to an application that brings data from two tables using two Selects.
Conclusions
Using
the techniques seen in this article, you will now be able to optimize
your ASP.NET applications so that they have a better performance and
scalability, leaving their final users much more satisfied with their
response time. The use of Stored Procedures, when done in an
appropriate manner, can assure the success of its ASP.NET solution with
ADO.NET, being unbeatable if compared to any another existing
technology. DataSets in memory and DataReaders also are excellent ingredients to turbo your Web Site with ASP.NET and ADO.NET.
