by Jeff Cogswell

Sometimes, when developing Web applications with ASP.NET, you encounter a problem like this. You have a small amount of data that rarely changes; you need that data again and again throughout the life of the application; and further, such data is shared among users accessing your application.

For example, you might have an online sales application that serves a limited region. Your application is aware of 50 different zip codes. For each zip code, you might associate the phone number of the sales office serving that zip code. This data might be used in various places in your application, including as a datasource for some Web server controls.

(more…)

1         Introduction

This documents intends to list a number of recommendations to be used when developing ASP pages. This is not a full standards documents but a number of recommendations that should be followed.

The following areas should be extended:

§         Define a naming convention for variables. In the case of ASP it is suggested to use one of the type: sString  / nNumber / objObject.

§         Define a common error handling/reporting framework.

2         Scope

The scope of this document is ASP development in the Software Development team. Specific teams may have additional requirements and standards.

3         Recommendations

1.       Use meaningful names for all your variables, functions, objects etc

2.       Use option explicit when possible.

3.       When modifying existing code or extending it may not be possible to use option explicit due to dependencies between the files. In those cases, try to make this possible and consult your team leader if the amount of work is significant or likely to impact your schedule of work.

4.       Always dim all variables.

5.       Use Local Variables in Subroutines and Functions.

Local variables are those declared within subroutines and functions. Within a function or subroutine, local variable access is faster than global variable access. Use of local variables also tends to make code cleaner and avoids data getting mixed.

6.       Avoid re-dimensioning arrays.

7.       Use Response Buffering. This minimizes the amount of writes to the browser and thus improves overall performance. Each write has significant overhead. If some part of the page may take a long time consider using Response.Flush before very expensive operations.

8.       Use client side validation when possible but validate server side as well.

9.       Copy individual values from collections into local variables, if you are going to reference the value more than once. This saves ASP from having to perform lookup processing in the collection for each and every reference.

10.    Don’t expect the ASP engine to clean up your objects. Always close then and set them to nothing as soon as possible.  E.g. set objObject = nothing

11.    If you are going to loop through a long recordset, consider loading it into an array using the Recordset.GetRows method and close your recordset before you process it.  If not using arrays consider using a field reference if you are looping through many results (e.g.  set objField = objRs(“fieldname”) ).  Then reference with objField.value. You can also use indexes instead of field names but define the numeric values in variables. E.g. nField1 and then recordset(field1)

12.    Minimize context switching. For readability and performance, try to minimize context switching between HTML and scripts. When possible, use a few large blocks of script on a page instead of several scattered fragments.

13.    Handle errors gracefully. Errors should be handled and appropriate user-friendly messages displayed. Don’t assume that things will execute correctly.

14.    Encapsulate your code. If you are going to have small include files with some functionality it makes more sense to encapsulate it and apply proper error handling to it.

15.    Think in terms of layers. Use VBScript classes to encapsulate business logic, database access and presentation tier when sensible.

16.    Limit the dependencies of objects and routines to their property and parameters. Avoid depending on global variables when possible or the QueryString and Form collections.

17.    Always validate input parameters before executing code. Make sure that if an input value is invalid it is handled appropriately.

18.    Before you code, think what you want to achieve and what is the best way of doing.

19.    Always comment you code. Always specify dependencies in the code.

20.    If you modify existing code, add comments of what you modify and when.

21.    Organise and name your files meaningfully.

22.    Don’t abuse on nested code include files; it makes documentation and debugging more difficult.

23.    Use standard headers in all script files and use VSS auto-replace features. For example:

‘ Channels: Ski

‘ Authors: Author Name 00/00/00

‘          A.N. 00/00/00: Change description

‘          A.N. 00/00/00: Change description

‘===============================================================

‘ VSS Details:

‘———————————————————–

‘ $Author:  $

‘ $Revision: $

‘ $Date:  $

‘ $Archive:  $

‘===============================================================

1.       Be consistent in your coding.

2.       Always properly indent your code; it makes it more readable and easier to understand and debug.

3.       Test, test and test what you do.

4.       Do performance testing.

1         Further reading:

25+ ASP Tips to Improve Performance and Style

http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnasp/html/ASPtips.asp

ASP Guidelines

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnserv/html/server122799.asp

Maximizing the Performance of Your Active Server Pages

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnasp/html/maxperf.asp

Seven Deadly Sins of < ?xml:namespace prefix ="" st1 ns ="" "urn:schemas-microsoft-com:office:smarttags" />ADO

http://sqlmag.com/Articles/Index.cfm?ArticleID=8423

ASP Conventions

http://msdn.microsoft.com/library/default.asp?URL=/library/en-us/dnasp/html/aspconv.asp

Why GetRows is best to fetch data

http://www.learnasp.com/advice/whygetrows.asp

Navision (former Navision Attain) together with Microsoft Great Plains, Axapta, Solomon, Microsoft CRM and Microsoft RMS are now supported by Microsoft Business Solutions. Navision has various customization options. Today we will describe simple case of using C/ODBC driver. This driver and technology allows you to work with Native or C/SIDE Navision database. Navision is also available on Microsoft SQL Server – in this case you use traditional Microsoft technologies, such as OLEDB or MS SQL Server driver to open ADO.NET connection. Our goal is to help IT departments to support and tune Navision with in-house expertise and skills.

The topic of this article is Navision Attain database access through Webservice, connected to Navision via C/ODBC based Linked Server – the mechanism available in MS SQL Server 2000 and transfer the results to ASP.NET application. Our goal will be ASPX page accessing Navision Customers.

Let’s begin

1. In our case we will use Navision Attain 3.6 with Navision Database Server, Navision Application Server and Navision Client. These components are installed on Windows XP. You also need to install C/ODBC component form Navision Attain CD.

2. Let’s create ODBC DSN for Navision data access. Select Control Panel -> Administrative Tools -> Data Sources (ODBC). Then select System DSN tab and press Add button. We’ll use C/ODBC 32-bit data access driver. We’ll name Data Source Name Navision, Connection leave Local. As the database (Database button) select \Program Files\Navision Attain\Client\database.fdb (demo database). Then click Company button – we’ll use CRONUS demo company. It is important for C/SIDE correct database access to setup proper options for C/ODBC connection. Press Options button and look at the options available – we’ll need Identifiers parameter – it defines identifiers types, which will be transferred to the client application. In order to work correct with MS SQL Server 2000 with C/ODBC source we need to use these type: “a-z,A-Z,0-9,_”. Now DNS is done. Let’s create Linked Server.

3. Open MS SQL Server Enterprise Manager. Open server tree for the server, which you plan to use, for this server open Security folder and Lined Servers. With right click select New Linked Server in context menu. In the dialog box opened in the Provider Name select Microsoft OLE DB Provider for ODBC Drivers. Let’s name our Linked Server NAVISION. In Data Source string enter ODBC DSN name - NAVISION in our case. Linked Server is ready! Let’s select tables list and look at the data from Navision Attain database.

4. Next we need to create small stored procedure for sales data selection. Here is the text of the procedure:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

CREATE PROCEDURE NavisionCustomers AS

DBCC TRACEON(8765)
SELECT No_, Name, Address, City, Contact FROM OPENQUERY(NAVISION, 'SELECT * FROM Customer')

RETURN

Let’s clarify some points here. TRACEON(8765) directive allows us to work with the data of variable length, returned by C/ODBC driver. Without it we can not select Navision tables fields – we will have these errors:

OLE DB error trace [Non-interface error: Unexpected data length returned for the column: ProviderName=’MSDASQL’, TableName=’[MSDASQL]’, ColumnName=’Ship_to_Filter’, ExpectedLength=’250′, ReturnedLength=’1′].

Server: Msg 7347, Level 16, State 1, Line 1

OLE DB provider ‘MSDASQL’ returned an unexpected data length for the fixed-length column ‘[MSDASQL].Ship_to_Filter’. The expected data length is 250, while the returned data length is 1.

OPENQUERY command opens linked server and gives it execution request, and returns record set selected. Directives ANSI_NULLS and ANSI_WARNINGS are required – they provide the possibility of the execution for heterogeneous requests. To test the procedure you can give its name in MS SQL Query Analyzer – EXEC NavisionCustomers

5. Now we need to create ASP.NET application. Let’s use free RAD environment ASP.NET WebMatrix. You can get infor and download it at http://asp.net/webmatrix . You need .NET SDK 1.1 installed, before WebMatrix installation.

6. Launch WebMatrix, select XML Web Service creation in the Wizard window. Leave all the parameters default, just change the file name to NavisionItems, class name to NavisionItems, and Namespace as NavDemo. Midify the WebService code as below (change connection string to actual names):

Imports System
Imports System.Web.Services
Imports System.Xml.Serialization

Public Class NavisionItems

Function GetNavisionItems() As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Alba'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "EXEC NavisionItems"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand

Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function
End Class

7. Now let’s create ASP.NET application which will show the result set, returned by WebService. Create empty ASP.NET page with the wizard WebMatrix. Name it TestNavisionItems.aspx . Place these controls on the page: DataGrid and Button. Then switch to the Code mode and from the menu Tools launch WebService Proxy Generator. In the showing dialog screen specify http://localhost/NavisionItems.asmx as WSDL URL (if you launch webservice on the different host or web server works through different port – change the parameters accordingly). Note – in this time, if you are deploying webservice on the local machine, using WebMatrix web server Cassini, then it must be already running to this moment. Define Namespace as NavDemo and execute code generation. Next define Button handler:

Sub Button1_Click(sender As Object, e As EventArgs)
			 ' Insert page code here
			 '
			 Dim wsProxy As New NavDemo.NavisionItems()

			 DataGrid1.DataSource = wsProxy.GetNavisionItems()
			 DataGrid1.DataBind()
End Sub

8. Next launch our page, press the button on the for and we are getting Navision Items Navision Items!

Happy customizing, implementing and modifying! If you want us to do the job - give us a call 1-866-528-0577 or 1-630-961-5918! help@albaspectrum.com