Posted on by Gunnar Gestsson

Access your SQL server through dotnet interop

As the Icelandic localization for NAV 2013 is supposed to land here in April it is now time to start moving the solutions to the new platform.  One of the things that I have used are the automation variables for SQL server connection.  In NAV 2013 I plan to replace all automation variables with dotnet variables.  Here is a code that could help you replacing some of your automation variables.[code]OBJECT Codeunit 50000 SQL Mgt.
{
OBJECT-PROPERTIES
{
Date=07.03.13;
Time=14:58:29;
Modified=Yes;
Version List=Dynamics.is;
}
PROPERTIES
{
OnRun=VAR
BEGIN
END;

}
CODE
{
VAR
ClientFile@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
SQLConnection@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlConnection";
SigningSetup@1200050000 : Record 10017128;
HostSetup@1200050027 : Record 10001061;
FileMgt@1200050026 : Codeunit 419;
SigningTools@1200050006 : Codeunit 10017129;
NoSeriesMgt@1200050011 : Codeunit 396;
HasGotSigningSetup@1200050001 : Boolean;
Text001@1200050004 : TextConst ‘ENU=Table %1 does not exist;ISL=Tafla %1 er ekki fyrir hendi’;

PROCEDURE GetSigningSetup@1200050001();
BEGIN
IF NOT HasGotSigningSetup THEN BEGIN
SigningSetup.GET;
IF SigningSetup."Default Storage" = SigningSetup."Default Storage"::"Web Site" THEN BEGIN
SigningSetup.TESTFIELD("Database Server Name");
SigningSetup.TESTFIELD("Database Name");
SigningSetup.TESTFIELD("Database Table Name");
SigningSetup.TESTFIELD("Database Permission Table Name");
SigningSetup.TESTFIELD("Database Login Name");
SigningSetup.TESTFIELD("Database User Password");
SigningSetup.TESTFIELD("Link Reference Nos.");
END;
SigningTools.TryFindHostSetup(HostSetup);
HasGotSigningSetup := TRUE;
END;
END;

PROCEDURE OpenLinkDB@1200050000();
VAR
ConnectionString@1200050000 : Text[1024];
BEGIN
GetSigningSetup;

IF ISNULL(SQLConnection) THEN BEGIN
ConnectionString :=
STRSUBSTNO(‘Data Source=%1;Initial Catalog=%2;User Id=%3;Password=%4′,
SigningSetup."Database Server Name",
SigningSetup."Database Name",
SigningSetup."Database Login Name",
SigningSetup."Database User Password");

SQLConnection := SQLConnection.SqlConnection(ConnectionString);
SQLConnection.Open;
END;
END;

PROCEDURE CloseLinkDB@1200050004();
BEGIN
IF ISNULL(SQLConnection) THEN
EXIT;

IF SQLConnection.State = 1 THEN
SQLConnection.Close;
END;

PROCEDURE TableExists@1200050006(TableName@1200050000 : Text[50];ShowError@1200050001 : Boolean) : Boolean;
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLReader@1000000001 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Data.SqlClient.SqlDataReader" WITHEVENTS;
Query@1200050002 : Text[1024];
BEGIN
OpenLinkDB;

Query :=
STRSUBSTNO(‘SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ”%1” AND TABLE_TYPE = ”BASE TABLE”’,
TableName);

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLReader := SQLCommand.ExecuteReader;
IF SQLReader.HasRows THEN
EXIT(TRUE)
ELSE
IF ShowError THEN
ERROR(Text001,TableName)
ELSE
EXIT(FALSE);
END;

PROCEDURE CreateLinkTable@1200050020();
VAR
SQLCommand@1000000000 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
Guid@1200050000 : Text[40];
Query@1000000001 : Text[1024];
BEGIN
OpenLinkDB;

Guid := CREATEGUID;

Query :=
STRSUBSTNO(‘CREATE TABLE [%1](‘,SigningSetup."Database Table Name") +
‘[Link ID] [varchar](20) NOT NULL,’ +
‘[Table ID] [int] NOT NULL,’ +
‘[Period] [varchar](6) NOT NULL,’ +
‘[User ID] [varchar](20) NOT NULL,’ +
‘[Computer Name] [varchar](50) NOT NULL,’ +
‘[File Name] [varchar](1024) NOT NULL,’ +
‘[Content Type] [varchar](50) NOT NULL,’ +
‘[Object] [image] NULL,’ +
STRSUBSTNO(‘ CONSTRAINT [PK_%1_ID] PRIMARY KEY CLUSTERED’,Guid) +
‘([Link ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,’ +
‘IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]’ +
‘) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];’;

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinately for the command to finish.
SQLCommand.ExecuteNonQuery;
END;

LOCAL PROCEDURE UploadFileToSQL@1200050011(FileName@1200050000 : Text[1024];TableID@1200050007 : Integer) URL : Text[1024];
VAR
SQLCommand@1000000005 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlCommand";
SQLdbType@1000000003 : DotNet "’System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlDbType";
TempFileName@1100408001 : Text[1024];
Query@1000000006 : Text[1024];
ComputerName@1200050002 : Code[50];
LinkID@1200050006 : Code[20];
InsertCount@1000000007 : Integer;
BEGIN
TempFileName := FileMgt.UploadFileSilent(FileName);
OpenLinkDB;

SigningSetup.TESTFIELD("Link Web URL");
ComputerName := SigningTools.GetComputerName;

LinkID := NoSeriesMgt.GetNextNo(SigningSetup."Link Reference Nos.",TODAY,TRUE);
URL := SigningSetup."Link Web URL" + STRSUBSTNO(‘&ID=%1’,LinkID);

Query := STRSUBSTNO(
‘INSERT INTO [%1] ‘ +
‘ ([Link ID] ‘ +
‘ ,[Table ID] ‘ +
‘ ,[Period] ‘ +
‘ ,[User ID] ‘ +
‘ ,[Computer Name] ‘ +
‘ ,[File Name] ‘ +
‘ ,[Content Type] ‘ +
‘ ,[Object]) ‘ +
‘ VALUES ‘ +
‘ (@LinkID ‘ + // varchar(20)
‘ ,@TableID ‘ + // int
‘ ,@Period ‘ + // varchar(6)
‘ ,@UserID ‘ + // varchar(20)
‘ ,@ComputerName ‘ + // varchar(50)
‘ ,@FileName ‘ + // varchar(1024)
‘ ,@ContentType ‘ + // varchar(50)
‘ ,@Object) ‘ + // image
”,SigningSetup."Database Table Name");

SQLCommand := SQLCommand.SqlCommand(Query,SQLConnection);
SQLCommand.CommandTimeout(0); // wait indefinitely for the command to finish.
SQLCommand.Parameters.Add(‘@LinkID’,SQLdbType.VarChar,20).Value := LinkID;
SQLCommand.Parameters.Add(‘@TableID’,SQLdbType.Int).Value := TableID;
SQLCommand.Parameters.Add(‘@Period’,SQLdbType.VarChar,6).Value := FORMAT(TODAY,0,'<Year4><Month,2>’);
SQLCommand.Parameters.Add(‘@UserID’,SQLdbType.VarChar,20).Value := USERID;
SQLCommand.Parameters.Add(‘@ComputerName’,SQLdbType.VarChar,50).Value := ComputerName;
SQLCommand.Parameters.Add(‘@FileName’,SQLdbType.VarChar,1024).Value := FileMgt.GetFileName(FileName);
SQLCommand.Parameters.Add(‘@ContentType’,SQLdbType.VarChar,50).Value := SigningTools.ContentType(FileName);
SQLCommand.Parameters.Add(‘@Object’,SQLdbType.Image).Value := ServerFile.ReadAllBytes(TempFileName);
InsertCount := SQLCommand.ExecuteNonQuery;
ServerFile.Delete(TempFileName);
END;

LOCAL PROCEDURE DownloadFileFromWeb@1100408027(URL@1200050000 : Text[1024]) FileName : Text[1024];
VAR
HttpWebRequest@1100408002 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.HttpWebRequest";
HttpWebResponse@1100408001 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebResponse";
HttpWebHeaderCollection@1100408003 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.WebHeaderCollection";
Credential@1100408008 : DotNet "’System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Net.NetworkCredential";
MemoryStream@1100408004 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.MemoryStream";
ServerFileStream@1100408005 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.FileStream";
TempBlob@1100408009 : TEMPORARY Record 99008535;
Document@1100408011 : BigText;
OutStr@1100408007 : OutStream;
InStr@1100408010 : InStream;
TempFile@1100408006 : File;
FilePath@1200050001 : Text[1024];
TempPath@1100408000 : Text[1024];
BEGIN
GetSigningSetup;
Credential := Credential.NetworkCredential;
Credential.UserName := SigningSetup."Database Login Name";
Credential.Password := SigningSetup."Database User Password";
Credential.Domain := ‘.’;
HttpWebRequest := HttpWebRequest.Create(URL);
HttpWebRequest.Timeout := 30000;
HttpWebRequest.Credentials := Credential;
HttpWebRequest.Method := ‘GET’;
HttpWebResponse := HttpWebRequest.GetResponse;
HttpWebHeaderCollection := HttpWebResponse.Headers;

FileName := HttpWebHeaderCollection.Get(‘Content-Disposition’);
IF STRPOS(FileName,’filename=’) = 0 THEN
FileName := ”
ELSE BEGIN
FileName := COPYSTR(FileName,STRPOS(FileName,’filename=’) + 9);
TempPath :=
FileMgt.GetDirectoryName(FileMgt.ServerTempFileName(”)) +
FileName;
TempFile.CREATE(TempPath);
TempFile.CREATEOUTSTREAM(OutStr);
COPYSTREAM(OutStr,HttpWebResponse.GetResponseStream);
TempFile.CLOSE;
FileName := FileMgt.DownloadTempFile(TempPath);
END;
END;

PROCEDURE OpenLinksForRecRef@1200050005(VAR RecRef@1200050000 : RecordRef);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
OpenRecordLinks(RecordLinks);
END;
END;

PROCEDURE MailLinksForRecRef@1100408004(VAR RecRef@1200050000 : RecordRef;SuggestedEMailAddr@1100408000 : Text[1024];Body@1100408001 : Text[1024]);
VAR
RecordLinks@1200050001 : Record 2000000068;
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
SETRANGE(Type,Type::Link);
IF NOT ISEMPTY THEN
MailRecordLinks(RecordLinks,SuggestedEMailAddr,Body);
END;
END;

PROCEDURE OpenRecordLinks@1100408000(VAR RecordLinks@1100408000 : Record 2000000068);
VAR
URL@1200050002 : Text[1024];
FileName@1200050004 : Text[1024];
BEGIN
WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN BEGIN
HYPERLINK(FileName);
END ELSE
HYPERLINK(URL);
UNTIL NEXT = 0;
END;
END;

PROCEDURE MailRecordLinks@1100408003(VAR RecordLinks@1100408000 : Record 2000000068;SuggestedEMailAddr@1100408011 : Text[1024];Body@1100408012 : Text[1024]);
VAR
Mail@1000000000 : Codeunit 397;
URL@1100408002 : Text[1024];
FileName@1100408001 : Text[1024];
BEGIN
Mail.NewMessage(SuggestedEMailAddr,”,Text031,Body,”,TRUE);

WITH RecordLinks DO BEGIN
IF FINDSET THEN REPEAT
URL := URL1 + URL2 + URL3 + URL4;
IF COPYSTR(UPPERCASE(URL1),1,6) IN [‘HTTP:/’,’HTTPS:’,’FTP://’] THEN
FileName := DownloadFile(URL1 + URL2 + URL3 + URL4);
IF FileName <> ” THEN
Mail.AttachFile(FileName)
ELSE
Mail.AttachFile(URL);
UNTIL NEXT = 0;
END;

Mail.Send;
END;

PROCEDURE MailFile@1100408002(VAR FileName@1100408011 : Text[1024];VAR Description@1100408000 : Text[250]);
VAR
Mail@1000000000 : Codeunit 397;
BEGIN
Mail.NewMessage(”,”,Description,”,FileName,TRUE);
Mail.Send;
END;

EVENT SQLConnection@1000000000::InfoMessage@48(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.SqlClient.SqlInfoMessageEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::StateChange@49(sender@1000000001 : Variant;e@1000000000 : DotNet "’System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Data.StateChangeEventArgs");
BEGIN
END;

EVENT SQLConnection@1000000000::Disposed@50(sender@1000000001 : Variant;e@1000000000 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.EventArgs");
BEGIN
END;

BEGIN
{
You experience bad performance when you scroll any list in the classic client in Microsoft Dynamics NAV 2009

To resolve this problem, use one of the following methods:
You can create a required index directly in the SQL database for the Record Links table by using Microsoft SQL Server Management Stu
You can execute the following TSQL statement in Microsoft NAV 2009 database:

CREATE INDEX Sign_Index ON [Record Link] ([Record ID], [Type], Company)
}
END.
}
}

[/code]

11 Replies to “Access your SQL server through dotnet interop”

  1. Hi.

    Is it possible to post the missing objects? “Signing”
    – Table10017128
    – Table10001061
    – Codeunit10017129

    Reply

  2. Hi Gunner,

    Can i have these objects also.

    SigningSetup Record Table10017128
    HostSetup Record Table10001061
    SigningTools Codeunit Codeunit10017129

    Even the Objects are not in Public Number series, i can handle it. Thanks

    Reply

  3. Hi Gunner,

    What is the Codeunit actually doing, Inserting Data for which Table?

    What i am planing with the Code, change it to Copy Template Database Record (Master or Setup) if newly created or Modified. To Other MC’s Databases. I will call this Codeunit from On insert and On modify trigger in NAV. Let me know if you have any suggestion which will be very helpful for me. Thanks.

    Reply

      1. Thanks Gunner,
        The problem if i use web Service or XML is, i need to setup NAS for all the Other DB’s. I was hopping to Create a Store Procedure with Configration files and a Job Scheduler in SQL. Seeing the mail i though we can do this from NAV only.

        Reply

          1. So you are telling to create the Web service in all other company and Push the Data with the Web service?. Thanks for the guidance i will give it a try and keep you updated.

          2. Here is how:
            Create the following tables; Data Package, Data Package Destination, Data Package Status, Data Package Trigger Setup
            In Data Package Destination you put an entry for each company, the Soap URL and authentication. When a data is created or modified you check the Data Package Trigger Setup and if you need to act then create the XML and put it into an entry in the Data Package table. Also create an entry in the Data Package Status for each Data Package and Data Package Destination. On the remote end you need two functions. One to receive the package and another to import the package. A NAS job should be running to check the Data Package Status table for a package that needs to be delivered and/or imported. In the destination company you will store the received package in the same Data Package table and keep the same Data Package Status entry. With the code you can find on this web site you should be able to create this solution in two days or so.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.