Tuesday, July 27, 2021

SQL Snippet for transaction query

SQL snippets in SSMS is a very handy (thouth probably underused) feature to automate some repetative tasks.  A collection of some useful snippets can be found at https://github.com/asathkumara/SSMS17-Code-Snippets

I decided to add a snippet to SSMS for frequently used query template like this


<?xml version="1.0" encoding="utf-8" ?>

<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<_locDefinition xmlns="urn:locstudio">

    <_locDefault _loc="locNone" />

    <_locTag _loc="locData">Title</_locTag>

    <_locTag _loc="locData">Description</_locTag>

    <_locTag _loc="locData">Author</_locTag>

    <_locTag _loc="locData">ToolTip</_locTag>

   <_locTag _loc="locData">Default</_locTag>

</_locDefinition>

       <CodeSnippet Format="1.0.0">

              <Header>

                     <Title>Transaction Query Snippet</Title>

                        <Shortcut></Shortcut>

                     <Description>Code Snippet for Transaction Query</Description>

                     <Author>Pranav Kulkarni</Author>

                     <SnippetTypes>

                                <SnippetType>Expansion</SnippetType>

                     </SnippetTypes>

              </Header>

              <Snippet>

                     <Declarations>

                                                       <Literal>

                                  <ID>query_statement</ID>

                                  <ToolTip>Enter your Query statement(s) here.</ToolTip>

                                  <Default>[...]</Default>

                                </Literal>

                                                       <Literal>

                                  <ID>verify_statement</ID>

                                  <ToolTip>Enter your verify statement(s) here.</ToolTip>

                                  <Default>[...]</Default>

                                </Literal>

                                                      

 

                     </Declarations>

                     <Code Language="SQL"><![CDATA[

DECLARE @exec int = 1

DECLARE @commit int = 0

DECLARE @verify int = 0

 

BEGIN TRAN

IF @exec = 1 BEGIN

       -- Query Statements --

       $query_statement$

END

if @verify = 1 begin

       -- Verify Statements --

       $verify_statement$

END

IF @commit = 1 BEGIN

       COMMIT TRAN

END

ELSE BEGIN

       ROLLBACK TRAN

END

 

                     ]]>

                     </Code>

              </Snippet>

       </CodeSnippet>

</CodeSnippets>


Sunday, June 20, 2021

SharePoint JSOM retrieve app url

 To retrieve app url using JSOM in sharepoint we need to reconstruct the standard tokens. This can be done as follows:


function loadAppUrl(redirectUrl, appName) {

 

    // Here, I am fetching the URL to the root site. I want to connect to this app always

    // even when in a sub site collection

    var siteUrl = _spPageContextInfo.siteAbsoluteUrl.toLowerCase().replace("https://", "").split("/")[0];

    var documentPath = "https://" + siteUrl + "/_api/contextinfo";

    $.ajax({

        url: documentPath,

        type: "POST",

        contentType: "application/x-www-url-encoded",

        dataType: "json",

        headers: {

            "Accept": "application/json; odata=verbose"

        },

        success: function (data) {

            if (data.d) {

                var webUrl = data.d.GetContextWebInformation.WebFullUrl;

 

                // Need to create a new ClientContext to create cross site collecton call

                var clientContext = new SP.ClientContext(webUrl);

                var web = clientContext.get_web();

 

                // Fetch all app instances

                var appInstances = SP.AppCatalog.getAppInstances(clientContext, web);

 

                clientContext.load(web);

                clientContext.load(appInstances);

 

                clientContext.executeQueryAsync(

                    function () {

                        var url = "";

                        if (appInstances.get_count() > 0) {

                            for (var i = 0; i < appInstances.get_count(); i++) {

                                var v = appInstances.getItemAtIndex(i);

                                try {

                                    url = v.get_appWebFullUrl();

 

                                    // Compare the name of the app with the app key, or part of the

                                    // URL to match

                                    if (url.toLowerCase().indexOf(appName.toLowerCase())) {

                                        break;

                                    }

                                } catch (e) { }

                            }

                        }

 

                        var appurl = redirectUrl +

                            "?SPLanguage=en%2DUS" +

                            "&SPClientTag=13&SPProductNumber=" +

                            _spPageContextInfo.siteClientTag.split('$$')[1] +

                            "&SPHostUrl=" + encodeURIComponent("https://" + siteUrl) +

                            "&SPAppWebUrl=" +

                            encodeURIComponent(url);

 

                        //... call another method or update a top level

                        //... parameter here to store the appurl parameter

 

                    }, function (sender, args) {

                        // .. log error here using your preferred framework

                    });

            }

        },

        error: function (err) {

            // .. log error here using your preferred framework

        }

    });

}

 

 
Reference: https://blog.lekman.com/2017/11/retrieving-sharepoint-app-url-using-jsom.html

 

Another way which I have used in my projects is to use redirect.aspx as follows

 

function AddIRISSubmisisonsUrl(appName) {

    SP.SOD.executeFunc("sp.js", "SP.ClientContext", function () {

        SP.SOD.executeFunc("sp.runtime.js", "SP.ClientContext", function () {

            var currentContext = new SP.ClientContext.get_current();

            var currentWeb = currentContext.get_web();

 

            // Fetch all app instances

            var appInstances = SP.AppCatalog.getAppInstances(currentContext, currentWeb);

 

            currentContext.load(currentWeb);

            currentContext.load(appInstances);

 

            currentContext.executeQueryAsync(

                function () {

                    console.log('Current web url is ' + currentWeb.get_url());

 

                    var url = "";

                    if (appInstances.get_count() > 0) {

 

                        for (var i = 0; i < appInstances.get_count(); i++) {

                            var v = appInstances.getItemAtIndex(i);

                            try {

                                url = v.get_appWebFullUrl();

 

                                console.log('AppWebFullUrl is ' + url);

                                // Compare the name of the app with the app key, or part of the

                                // URL to match

                                if (v.get_title().toLowerCase() === appName.toLowerCase()) {

 

                                    var appRedirectUrl = currentWeb.get_url() + '/_layouts/15/appredirect.aspx?instance_id={' + v.get_id().toString().toUpperCase() + '}';

                                    var a = document.createElement('a');

                                    var linkText = document.createTextNode("Go to My App");

                                    a.appendChild(linkText);

                                    a.title = "Go to My App";

                                    a.href = appRedirectUrl;

 

                                    var contentDiv = document.getElementsByClassName("article-content")[0];

                                    var newDiv = document.createElement('div');

                                    newDiv.appendChild(a);

                                    newDiv.setAttribute('id', 'appLinkDiv');

                                    $(contentDiv).append(newDiv);

                                    break;

                                }

                            } catch (e) { }

                        }

                    }

                },

                function (sender, args) {

                    console.log(args.get_message());

                }

            );

        });

    });

}

This method can only work if the app has been added to current web

c# httpclient The remote certificate is invalid according to the validation procedure: RemoteCertificateNameMismatch

 If we get this error while trying to get http reponse using HttpClient object, it could mean that certificate validation fails for the remo...