Those who managed to get acquainted with FastSQLDataSource – our first ASP.NET application data-display-acceleration product, will be surprised reading this article I think. The new product of the NitrsBase family NBDataSource gives much more acceleration thanks to the superb NitrosBase caching technology.
Here we create a simplest ASP.NET application using NBDataSource. Let’s start from the task definition.
TASK DEFINITION AND DATA PREPARATION
We need to display on a web page a goods catalog. The catalog is stored in MS SQL Server database in GOODS table of the following structure:
CREATE TABLE GOODS(
ID int,
Item varchar(50),
Cost float
)
The table is filled in with 1 million items. For simplicity the data generated as follows:
You can use the stored procedure similar to the one we described in our previous article (http://nitrosbase.blogspot.com/2010/03/how-to-create-simple-aspnet-application.html) for filling in the table.
APPLICATION ARCHITECTURE
Architecture of an application based on NBDataSource, is substantially different than the one based on FastSQLDataSource. You can get the idea from the following diagram:
Main Database – the database storing the catalog and supposedly many other data like orders, customers, etc.
NitrosBase – In-Memory database used as a cache for data to be displayed (the GOODS table in our case).
NBDataSource is supplied with NitrosBaseNet.dll – specialized .NET version of NitrosBase. All the NitrosBaseNet.dll API functions are called via NBDataSource.
Display Element – the control displaying data (ex, GridView ListView, etc.), bound to NBDataSource.
And NBDataSource controls all these objects.
Now let’s look at all that in motion, walking along arrows.
- When application stars Application_Start method of Global class is called (we show the code below in this article). It performs the following:
- Creates GOODS table in NitrosBase identical to GOODS table in main database;
- Duplicates the GOODS table contents from main database into NitrosBase.
- Each time you address to the display element, NBDataSource loads a portion of data into display element (GridView in our case).
- When the data changed on GridView, the changes are immediately handled by NBDataSource. Namely (see arrows 4 and 5):
- The changes are immediately fixed in NitrosBase.
- ModifyData event is generated.
- If ModifyData event handler is empty then the changes made in GridView aren’t transferred into main database. The changes will be lost when the application is closed. If you want to save changes in main database then ModifyData event give you all necessary means for that.
Note: NitrosBase table is not necessarily the copy of main database table. It may be part of table records, part of table columns, it may be join of the tables, etc.
Note: you don’t necessarily need to use ModifyData event for that though. You can save the changes once immediately before finishing the application. Application_End of Global class is suitable for that.
Application Development
We’ll need the file, containing SQL statements for creation all NitrosBase tables (in our case it would be one table – GOODS) and necessary indexes:
CREATE TABLE GOODS(ID int, Item varchar(50), Cost float); CREATE INDEX ID_IND ON GOODS (ID) with hash; CREATE INDEX ITEM_IND ON GOODS (Item) with hash; CREATE INDEX COST_IND ON GOODS (Cost) with hash;
Let’s call that file crtab.sql and put it into the application’s root folder.
Our application will be simplistic. There will be 1 main form and 2 controls: GridView и NBDataSource.
Of course, you should already have NBDataSource installed. Otherwise download the product from the vendor’s site (http://www.nitrosbase.net) and install it.
Start Microsoft Visual Studio, Create new ASP.NET project.
On Default.aspx page go to Design section.
Drag NBDataSource from the toolbar and drop it to the form.
The same way put GridView onto the form.
Attach NBDataSource to NitrosBase. ConnectionString property is used for that. The value assigned to ConnectionString is the relative path to crtab.sql file, counting from the application rood folder. The same path to crtab.sql we’ll use later when filling in Global class.
ConnectionString = “crtab.sql”
Set ExactCount property to True. We do that only to make NBDataSource behavior “standard”. The thing is that in many cases NBDataSource gives additional performance increase, if you don’t need to know exact number of records returned by query, and agree to get them gradually. Here for simplicity we give up this big advantage.
ExactCount = True
SelectCommand property is responsible for interaction with NitrosBase. As soon as we want to render all the columns and all the records, the setting would be
SelectCommand = “select * from Goods”.
Set GridView properties:
- DataSourceID = NBDataSource1 (select from the list).
- AllowPaging = True
- AllowSorting = True
Now we need to add some code for loading data from main database to NitrosBase. Application_Start method of Global class is appropriate. Add global.asax file to the project.
Open Global.asax.cs file and write the following code into Application_Start method:
protected void Application_Start(object sender, EventArgs e)
{
/* Create NitrosBase tables. crtab.sql is the same file that
used as a value for ConnectionString property
of NBDataSource control */
NBFastQuery query = new NBFastQuery(@"crtab.sql");
if (!query.IsDbLoad()) // Check: If NitrosBase wasn’t loaded
{
/* Create and open connection to main database */
SqlConnection conn1 = new SqlConnection();
conn1.ConnectionString =
@"Data Source=(local);Initial Catalog=NBSample;Integrated Security=True";
conn1.Open();
/* SQL statement reading data from main database */
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn1;
cmd.CommandText = "select * from Goods";
/* SQL statement adding data to NitrosBase */
SqlDataReader reader = cmd.ExecuteReader();
string insert_sql = "INSERT INTO Goods (ID, Item, Cost) VALUES ({0}, '{1}', {2})";
string formed_sql = "";
/* Read from SQL Server and record into NitrosBase */
try
{
while (reader.Read())
{
formed_sql = string.Format(insert_sql,
reader["ID"].ToString(),
reader["Item"].ToString(),
reader["Cost"].ToString());
query.RunQuery(formed_sql);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
reader.Close();
query.SetDbLoad();
}
}
Now we can start the application. If everything’s done well we’ll see main database data displayed in GridView.
Immediately after loading…
…sorting…
…paging…
… and everything pretty quick.
CONCLUSION
The application we developed here was aimed just preliminary outline of NBDataSource features. It doesn’t exhaust even small part of this control’s advantages. Particularly, fixing the GridView changes in the main database is totally omitted. However, we made the first step. And if you like NBDataSource’s the performance, compactness, application design transparency, paging and sorting effectiveness, you can proceed yourself reading the product documentation. The more so, as on the vendor’s site (www.nitrosbase.net) you can always get an evaluation copy.
An application based on NitrosBase technology, can be concurrently used by hundreds of users. Performance degradation would be extremely slow. Using NBDataSource in your applications would lead you out of the typical situations when the application “just flying” on the local developer’s computer, suddenly becomes “just dying” when installed on the provider’s hosting which usually has not much memory, many parallel processes and a lot of concurrent users.
Tags: ADO.NET, ASP.NET, Binding, bound controls, data displaying, Database, DBMS, FastSQLDataSource, gridview, MS SQL Server, NBDataSource, NitrosBase, paging, performance, sorting, SQL Server Accelerator
