Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / Connecting to DB and u...
Microsoft Dynamics NAV (Archived)

Connecting to DB and using SQL via NAV

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi I would like to connect to a database and both receive and insert data into tables via SQL. How can you do that in nav 2013? I dont mean the database on which NAV is running

In C# it would look something like this:

string DB = "nav104_final";
string Table = "[Kjærulff Fodpleje A_S$Item]";
string Kolonne = "[Item Disc_ Group]";
string NO = line.No;
ProjectConnection connect = new ProjectConnection();
connect.GetDWIConnection(dwiServer, DB);
SqlDataReader MyOrderReader = null;
string Query = "SELECT " + Kolonne + " FROM " + Table + " WHERE " + "[No_] = " + "'" + NO + "'";
SqlCommand MySQLCommand = new SqlCommand(Query, ProjectConnection.dwiConnection);
MyOrderReader = MySQLCommand.ExecuteReader();
MyOrderReader.Read();
string result = string.Format("{0}",MyOrderReader[0]);
MyOrderReader.Close();

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Connecting to DB and using SQL via NAV

    Hi,

    please check the below link.

    NAV 2013 ADOConnection

    http://www.mibuso.com/forum/viewtopic.php?f=32&t=57266&start=0&sid=84ff6390251ff9dab0305310fcb9a8b4

    Using ADO on RTC in NAV

    community.dynamics.com/.../using-ado-on-rtc-in-nav

    Make a connection to the database, using a "connectionstring"

    www.connectionstrings.com is a very helpful website to help you compose your connectionstring. Usually, I create a seperate setup table with the parameters needed to create a connectionstring.

    In C/SIDE, you can create a connection like this:

    IF ISCLEAR(lADOConnection) THEN CREATE(lADOConnection);

    lADOConnection.ConnectionString:=GetConnectionString(precDBServer."Server Name", precDBServer."Database Name", precDBServer.Login, precDBServer.Password);

    lADOConnection.Open;

    LADOConnection is an automation variable of the type "'Microsoft ActiveX Data Objects 2.8 Library'.Connection". You also see that I compose my connectionstring in a seperate function:

    GetConnectionString(...) : Text[1024]

    ltxtConnectionString:='Driver={SQL Server};'

       + 'Server='+pcodServerName+';'

       + 'Database='+pcodDatabaseName+';'

       + 'Uid='+pcodUserID+';'

       + 'Pwd='+pcodPassword+';';

    EXIT(ltxtConnectionString);

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: Connecting to DB and using SQL via NAV

    Please check the below link, you can use .NET interop to execute the commands

    www.erpsoftwareblog.com/.../ado-net-integration-in-dynamics-nav-2013-how-well-does-nav-2013-handle-large-datasets-part-2

  • Verified answer
    Rajasekhar@MS Profile Picture
    Rajasekhar@MS 5,567 on at
    RE: Connecting to DB and using SQL via NAV

    Hi

    With ADO you can achieve the above,refer the below links

    mibuso.com/.../using-ado-on-rtc-in-nav

    www.mibuso.com/.../viewtopic.php

    techblog.byllemos.com/.../ado-in-navision

    Regards

    Rajasekhar.Y

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans