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:

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!

Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies – USA nationwide Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, Texas, Georgia, Florida, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Microsoft CRM SDK, Navision, C#, VB.Net, SQL, Oracle, Unix developer.