Navision Database Access via C/ODBC WebService/ASP.NET Application

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, you will learn about a simple case of using a C/ODBC driver. This driver and technology allow you to work with a Native or C/SIDE Navision database. Navision also is available on Microsoft SQL Server; in this case, you use traditional Microsoft technologies, such as an OLEDB or MS SQL Server driver to open a ADO.NET connection. The goal is to help IT departments 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 a C/ODBC-based Linked Server—the mechanism available in MS SQL Server 2000—and transfer the results to an ASP.NET application. The goal will be ASPX page accessing Navision Customers.

To begin:

  1. In your case, you 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 the C/ODBC component from the Navision Attain CD.
  2. Now, create an ODBC DSN for Navision data access. Select Control Panel -> Administrative Tools -> Data Sources (ODBC). Then, select the System DSN tab and press the Add button. You'll use a C/ODBC 32-bit data access driver. Name it Data Source Name Navision; leave Connection Local. As to the database (Database button), select \Program Files\Navision Attain\Client\database.fdb (demo database). Then, click the Company button; use the CRONUS demo company. It is important for C/SIDE correct database access to set up the proper options for the C/ODBC connection. Press the Options button and look at the options available. You'll need the Identifiers parameter; it defines the identifiers types that will be transferred to the client application. To work correctly with MS SQL Server 2000 with C/ODBC source, you need to use these types: "a-z,A-Z,0-9,_". Now, DNS is done. Create Linked Server.
  3. Open MS SQL Server Enterprise Manager. Open a server tree for the server that you plan to use. For this server, open the Security folder and Lined Servers. With a right-click, select New Linked Server in the context menu. In the dialog box opened in Provider Name, select Microsoft OLE DB Provider for ODBC Drivers. Name your Linked Server NAVISION. In the Data Source string, enter the ODBC DSN name: NAVISION in your case. Linked Server is ready! Now, select the tables list and look at the data from Navision Attain database.
  4. Next, you need to create a small stored procedure for the 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 me clarify some points here. The TRACEON(8765) directive allows you to work with the data of variable length, returned by the C/ODBC driver. Without it, you cannot select Navision tables fields; you 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.
    
    The OPENQUERY command opens a linked server, gives it an execution request, and returns the selected record set. The ANSI_NULLS and ANSI_WARNINGS directives 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, you need to create an ASP.NET application. Use the free RAD environment ASP.NET WebMatrix. You can get information and download it at http://asp.net/webmatrix. You need .NET SDK 1.1 installed before WebMatrix installation.
  6. Launch WebMatrix and then select XML Web Service creation in the Wizard window. Leave all the parameters default; just change the file name to NavisionItems, the class name to NavisionItems, and Namespace as NavDemo. Midify the WebService code as below (change connection string to actual names):
    <%@ WebService language="VB" class="NavisionItems" %>
    
    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, create an ASP.NET application that will show the result set returned by the WebService. Create an empty ASP.NET page with the WebMatrix wizard. Name it TestNavisionItems.aspx. Place these controls on the page: DataGrid and Button. Then, switch to the Code mode and, from the Tools menu, launch WebService Proxy Generator. In the following dialog screen, specify http://localhost/NavisionItems.asmx as the WSDL URL (if you launch WebService on a different host or the Web server works through a different port, change the parameters accordingly).
    Note: At this time, if you are deploying WebService on the local machine, using the WebMatrix web server Cassini, it must be running already. Define Namespace as NavDemo and execute code generation. Next, define a 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 your page, press the button on the front, and you are getting Navision Items Navision Items!

About the Author

Boris Makushkin is the Lead Software Developer with Alba Spectrum Technologies (http://www.albaspectrum.com), a USA nationwide Navision Customization company. Boris can be reached at help@albaspectrum.com or 1-630-961-5918.



Comments

  • DBA

    Posted by John De Brabander on 04/17/2012 06:44am

    Despite this being a 7 year old article, I've seen it repeated at least half a dozen times when searching for this. I was pulling my hairs cause it wasn't working like the article described. That is until I tried different Identifiers and it worked, apparently my system language setting we're incorrect. This has been fixed. And now it works like the article describes.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: October 29, 2014 It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this webcast, Gene Kim discusses these survey findings and shares woeful tales of artifact management gone wrong! Gene also shares examples of how high-performing DevOps …

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

Most Popular Programming Stories

More for Developers

RSS Feeds