Blogging over technologie.
Welcome to Technolog Sign in | Join | Help

Front Page News

  • Howto: Azure DocumentDB update/delete objects, without SelfReference

    Inherit Microsoft.Azure.Documents.Resource? No

    We can be short, just don’t. If you do, your library gets bloated and you need to add references to documentdb to ‘cross-reffed’ libraries as well, because of the ‘Resource’ dependency.

    Use a POC based ‘base object’? Yes

    If you implement DocumentDB for the first time, you’ll quickly find that documentation and old samples, suggest you, to use the Build in Microsoft.Azure.Documents.Resource base object properties, that DocumentDB supports.

    It’s quite simple, don’t use them. There is an improved syntax, like using mongodb, which enables you to define your own id and such.

    say, this one

    public class BaseObject
            public BaseObject()
                 public void UpdateTimeStamp()
                this.timestamp = DateTime.UtcNow;

            public virtual int? document_type { get; set; }

            [JsonProperty("id", Required = Required.DisallowNull)]
               public Guid id { get; set; }

            [JsonProperty("timestamp", Required = Required.DisallowNull)]
            public DateTime timestamp { get;  set; }
            /// <summary>
            /// warning, do not SET this is a calculated field
            /// </summary>
            [JsonProperty("name", Required = Required.DisallowNull)]
            public string name { get; set; }



    Now, your DocumentDB Context class (or whatever you named it) could have a method like this


    public async Task<bool> DeleteItemsAsync(BaseObject item)
                          var collection = MetaUtil.CollectionId(item.GetType());
                //calculate the URL ourselves
                // this differs from SelfLink but seems to work!
    var docuId = UriFactory.CreateDocumentUri(DatabaseId, collection,;
                    var response = await _client.DeleteDocumentAsync(docuId);

                       return response.StatusCode == HttpStatusCode.NoContent;
                catch (Exception ex)
                    Trace.TraceError("DeleteItem failed {0}", ex);
                    return false;



    As you can use, there is a UriFactory class, that contains a lot of static uri creators, for any object type, that DocumentDB supports.

    B.t.w. I like DocumentDB. After finding out about, I quickly could ‘unbloat’ the library :)

    04-03-2017, 20:36 by eprogrammer to Egbert Nierop technolog
  • How to parse and read web.Config or app.Config in C++/on Windows

    Just a tiny gem, which not often would be required but it can save you some time. It also demonstrates the power of the IXmlReader in unmanaged code. Because, as far I am aware of, the processing time a .config file measuring it with TickCount always is 0 ms (too small to measure). Microsoft has optimized the XML Reader implementation for fast forward reading, and it also does not allocate strings in memory, it just passes the pointer to the unicode strings (either key or value). In line with that, you might appreciate :) why I attach to the BSTR key to find as well.

    What this class does, it reads the <appSettings>  section and puts the key value pairs in a ‘named value collection’ item.

    Note 1: I am a big fan of CComBSTR when the final client still understands COM/automation. That is the reason I did not use CString in this class. In addition, the CComBSTR class has been boosted by me, to optimize reallocation of existing memory. But you can use the default MS implementation as well. So, you can change CSimpleMap to CSimpleMap<CString, CString> if you wish.

    Note 2: The .config file is cached but it is parsed again if the filewritetime of the .config file was changed.

    config.h header.

    #include <xmllite.h>
    #include <map>
    #pragma once

    using namespace ATL;

    class ConfigurationManager
        static const int DELAYTICKS = 1000;
        std::map<CComBSTR, CComBSTR> _map;
        time_t _ftLastCheck;
        CComPtr<IXmlReader> _xmlReader;
        CComPtr<IMalloc> _malloc;
        HRESULT CheckTimeOut();
        //ansi version!
        CComBSTR _szFilePath;
        void Init();

        ConfigurationManager(const BSTR configFile);
        std::wstring& AppSettings(const std::wstring key, PCWSTR defaultValue = NULL);
        BSTR AppSettings(const BSTR key, PCWSTR defaultValue = NULL);
        time_t GetFileTime();



    #include <ctime>
    #include <sys/stat.h>
    #include "config.h"
    #pragma comment(lib, "xmllite.lib")

    ConfigurationManager::ConfigurationManager(const BSTR configFile) throw()
        _szFilePath = configFile;
    ConfigurationManager::ConfigurationManager() throw()
        if (!_szFilePath.IsEmpty())
    void ConfigurationManager::Init() throw()
        if (!_szFilePath.IsEmpty())
            HRESULT hr = CoGetMalloc(1, &_malloc);
            hr = CreateXmlReader(IID_IXmlReader, (void**)&_xmlReader, _malloc);
            if (FAILED(hr))
    time_t ConfigurationManager::GetFileTime() throw()
        struct stat stResult;
        CComBSTR ansi(_szFilePath);
        ::stat((char*)ansi.m_str, &stResult);        // get the attributes of afile.txt
        return stResult.st_mtime;
    BSTR ConfigurationManager::AppSettings(const BSTR key, PCWSTR defaultValue) throw()
        HRESULT hr = CheckTimeOut();
        if (FAILED(hr))
            return NULL;
        CComBSTR find;
        auto found = _map.find(find);
           if (found != _map.end())
            return found->second.Copy();
        else if (defaultValue != NULL)
            return ::SysAllocString(defaultValue);
        return NULL;
    ConfigurationManager::~ConfigurationManager() throw()
    HRESULT ConfigurationManager::CheckTimeOut() throw()
        auto curT = GetFileTime();
        PCWSTR pwzValue;
        auto memResult = ::difftime(curT, _ftLastCheck);
        if (memResult != 0.0F)
            DWORD start = ::GetTickCount();
            HRESULT hr = S_OK;
            CComPtr<IStream> pStream;
            CComPtr<IXmlReaderInput> _readerInput;
            hr = ::SHCreateStreamOnFileEx(_szFilePath, STGM_READ | STGM_SHARE_DENY_NONE, FILE_ATTRIBUTE_NORMAL, FALSE,NULL, &pStream);

            if (SUCCEEDED(hr))
                hr = ::CreateXmlReaderInputWithEncodingCodePage(pStream, _malloc, CP_UTF8, TRUE, NULL, &_readerInput);           
                hr = _xmlReader->SetProperty(XmlReaderProperty_DtdProcessing, DtdProcessing_Prohibit);   
                hr = _xmlReader->SetInput(_readerInput);
                return hr;
            XmlNodeType nodeType = XmlNodeType::XmlNodeType_None;
            UINT lenValue;
            PCWSTR key;
            bool startCollecting  = false;
            while (S_OK == _xmlReader->Read(&nodeType) && hr == S_OK)
                switch(nodeType) {
                case XmlNodeType::XmlNodeType_EndElement:
                    //hr = pReader->GetDepth(&dept);
                    hr = _xmlReader->GetLocalName(&pwzValue, NULL);
                    if (startCollecting && lstrcmpW(pwzValue, L"appSettings") == 0)
                        //break loop
                        hr = S_FALSE;
                case XmlNodeType::XmlNodeType_Element:
                        // get element name such as option in <option value="11">
                        hr = _xmlReader->GetLocalName(&pwzValue, NULL);
                        if (FAILED(hr)) break;
                        if (startCollecting == false && lstrcmpW(pwzValue, L"appSettings") == 0)
                            startCollecting = true;

                            hr = _xmlReader->MoveToAttributeByName(L"configSource", NULL);
                            if (hr == S_OK)
                                hr = _xmlReader->GetValue(&pwzValue, NULL);
                                if (::PathIsRelativeW(pwzValue) == TRUE)
                                    //TODO: call back to do a Server.MapPath
                                    _szFilePath = pwzValue;
                                return CheckTimeOut(); //recursion                           
                            hr = S_OK;//reset otherwise loop stops
                        else if (startCollecting && lstrcmpW(pwzValue, L"add") == 0)
                            hr = _xmlReader->MoveToAttributeByName(L"key", NULL);
                            if (hr == S_OK)
                                hr = _xmlReader->GetValue(&pwzValue, &lenValue);
                                //key.Append( pwzValue, lenValue);
                                key = pwzValue;

                                //ATLTRACE(L"found key %s %d\r\n", pwzValue, lenValue);
                                hr = _xmlReader->MoveToAttributeByName(L"value", NULL);
                                if (hr == S_OK)
                                    _xmlReader->GetValue(&pwzValue, NULL);
                                    _map.insert(std::pair<CComBSTR, CComBSTR>(key, pwzValue));
            if (SUCCEEDED(hr)) _ftLastCheck = curT;
            if (_xmlReader != NULL)
            return S_FALSE;

        return S_OK;

    06-07-2015, 11:30 by eprogrammer to Egbert Nierop technolog
  • How to convert WCF REST services to Web API 2 (ASP.NET)


    Why convert WCF REST services anyway?
    First: WCF REST processes JSON using (by default) the DataContractJsonSerializer, while Web API 2 (by default) uses NewtonSoft JSON, which today is the best choice.
    Secondly: WCF more or less runs in ASP.NET context, using a HttpContext hack plus routing to a WCF service is much more complex than the easy Attributed like (RoutePrefixAttribute) Web API controller.
    Third: Arguably, WCF REST services, simply are from the previous generation .NET. Certainly, I still would use WCF for implementing a SOAP client/server, but not for REST.

    So, how can I convert my WCF Rest services to Web API 2 without telling customers to implement changes as well?

    Here is my experience, which might gain some time for you.

    Just this, my customer already used VB.NET in this project, so don’t blame me for using VB.NET instead of C# :)

    The existing Services (called ‘Controllers’ in Web API terms) looked like this…

    Service Body definition+attributes

    Partial Public Class MobileService
         Inherits ServiceBase

    ServiceBase is just a base class which has some methods for shortening getting the MemberShip User and Booleans like ‘IsAdmin’. For brevity, you don’t need to see it.

    Service actions

    They look like this:

    <WebInvoke(Method:="GET", UriTemplate:="Product/{productid}")>
    Public Function GetProduct(productid As String) As Product
        If MembershipUser Is Nothing Then Throw New UnauthorizedAccessException()
        Return New Product(CInt(productid))
    End Function

    There are several attributes, such as WebGet etc, which you quickly recognize having counterparts in WebAPI2

    WCF REST ?

    You know, REST = HTTP [ACTION VERB(S)] URL + [body].
    WCF had an Attribute AspNetCompatibilityRequirements which enabled you to even have a Session State and to run within the ASP.NET pipeline. However, REST should not have a ‘session state’.

    The response can depending on the Http Header Accept be either application/json, or application/xml

    JSON is the easiest stuff, because it does not deal with XML namespaces. However, if an endpoint client requests application/xml, the service might return a constructed rootname element, using the controller name as a basename. Such as  <ArrayOfHardwareService.Category xmlns:i="" xmlns=">

    As you see, because my WCF REST controller was named HardwareService, it is being used in the XML output. If you have existing customers, you cannot just modify it to be, say, ‘ArrayOfHardwareController’.

    Now, a real service implementor, would advice you to use CollectionDataContract attributes. Please do so, for new from scratch projects, However, again, I don’t want to redefine my existing object model, which can be a lot of work!

    TIP 1: Use this excelent hack, which really works like a charm.

    In VB.NET (I guess, your gasping to see it?) this little helper is like this:

    Public Class CustomHttpControllerTypeResolver
          Inherits DefaultHttpControllerTypeResolver
          Public Sub New()
              MyBase.New(Function(T As Type)
                             If T Is Nothing Then Throw New ArgumentNullException("t")
                             Return T.IsClass AndAlso T.IsVisible AndAlso Not T.IsAbstract AndAlso GetType(ApiController).IsAssignableFrom(T) AndAlso GetType(IHttpController).IsAssignableFrom(T)
                         End Function)
          End Sub
      End Class

    In Application_OnStart (or so) you add this

    Web.Http.GlobalConfiguration.Configuration.Services.Replace(GetType(System.Web.Http.Dispatcher.IHttpControllerTypeResolver), New Api.CustomHttpControllerTypeResolver())

    In WebApiConfig use this:

    'override the suffix 'Controller' requirement
    Dim suffix = GetType(DefaultHttpControllerSelector).GetField("ControllerSuffix", BindingFlags.Static Or BindingFlags.Public)
    If suffix IsNot Nothing Then suffix.SetValue(Nothing, String.Empty)
    I really like this hack above! Because we don’t need to mess with caching the WebAPI2 controllers ourselves. Which is indeed madness to implement ourselves (mostly).

    Now the next challenge. Most companies have JSON/XML services available as service for both end-to-end points, but also as data-source in websites, which consume it using javascript.
    In ASP.NET, you probably have some FormsAuthentication mechanism, which is cookie based and optimized for persisting an authenticated session.
    WebAPI 2 Controllers do support this, using the Authorize attribute, however, you’ll discover, it does NOT support Basic authentication, which is in combination with SSL, a good candidate for encryption over data for most B-2-B endpoints..

    So you need a ‘hack’ to elegantly support BOTH FormsAuthentication, and Basic Authentication. Note, the sample from the Web I used it from, ONLY supports BasicAuthentication, incorrectly calling it ‘Mixed’ support, which it was not. My code however, does support both FormsAuthentication as well as Basic authentication.

    Note 1: It does not support the FormsAuthentication challenge sequence, which I don’t need since one normally does not log on using a browser to a JSON Service URL/endpoint. So, MyBase.IsAuthorized(actionContext) does the trick. Thus you don’t have to validate the .aspxauth cookie (Part of FormsAuthentication) yourselves.

    Note 2: You must finish the TODO comment, otherwise, the attribute won’t work for you.

    TIP 2 Use the attribute below, as a replacement for the Authenticate attribute.

    ''' <summary> 
    ''' HTTP authentication filter for ASP.NET Web API
    ''' </summary>
    ''' <seealso cref=""/>
    Public MustInherit Class BasicHttpAuthorizeAttribute
        Inherits AuthorizeAttribute

        Private Const BasicAuthResponseHeader = "WWW-Authenticate"
        Private Const BasicAuthResponseHeaderValue = "Basic"

        Public Overrides Sub OnAuthorization(actionContext As HttpActionContext)

            If (actionContext Is Nothing) Then
                Throw New ArgumentNullException("actionContext")
            End If
            If (AuthorizationDisabled(actionContext) OrElse MyBase.IsAuthorized(actionContext) OrElse AuthorizeRequest(actionContext.ControllerContext.Request)) Then
            End If

        End Sub

        Protected Overrides Sub HandleUnauthorizedRequest(actionContext As HttpActionContext)

            If (actionContext Is Nothing) Then
                Throw New ArgumentNullException("actionContext")
            End If
            actionContext.Response = CreateUnauthorizedResponse(actionContext.ControllerContext.Request)
        End Sub

        Private Shared Function CreateUnauthorizedResponse(request As HttpRequestMessage) As HttpResponseMessage

            Dim result = New HttpResponseMessage() With
                            .StatusCode = HttpStatusCode.Unauthorized,
                            .RequestMessage = request

            'we need to include WWW-Authenticate header in our response,
            'so our client knows we are using HTTP authentication
            result.Headers.Add(BasicAuthResponseHeader, BasicAuthResponseHeaderValue)
            Return result
        End Function

        Private Shared Function AuthorizationDisabled(actionContext As HttpActionContext) As Boolean
            'support New AllowAnonymousAttribute
            If Not actionContext.ActionDescriptor.GetCustomAttributes(Of AllowAnonymousAttribute).Any() Then
                Return actionContext.ControllerContext.ControllerDescriptor().GetCustomAttributes(Of AllowAnonymousAttribute).Any()
                Return True
            End If
        End Function

        Private Function AuthorizeRequest(request As HttpRequestMessage) As Boolean

            Dim authValue = request.Headers.Authorization
            If (authValue Is Nothing OrElse String.IsNullOrWhiteSpace(authValue.Parameter) OrElse
                String.IsNullOrWhiteSpace(authValue.Scheme) OrElse
                authValue.Scheme <> BasicAuthResponseHeaderValue) Then

                Return False
            End If

            Dim parsedHeader = ParseAuthorizationHeader(authValue.Parameter)
            If parsedHeader Is Nothing Then
                Return False
            End If
            Dim principal As IPrincipal = Nothing
            If TryCreatePrincipal(parsedHeader(0), parsedHeader(1), principal) Then

                HttpContext.Current.User = principal
                Return CheckRoles(principal) AndAlso CheckUsers(principal)

                Return False
            End If
        End Function

        Private Function CheckUsers(principal As IPrincipal) As Boolean

            Dim usrs = UsersSplit
            If usrs.Length = 0 Then Return True
            'NOTE: This is a case sensitive comparison
            Return usrs.Any(Function(u) principal.Identity.Name = u)
        End Function

        Private Function CheckRoles(principal As IPrincipal) As Boolean

            Dim rls = RolesSplit
            If rls.Length = 0 Then Return True
            Return rls.Any(Function(r) principal.IsInRole(r))
        End Function

        Private Shared Function ParseAuthorizationHeader(authHeader As String) As String()

            Dim credentials = Encoding.ASCII.GetString(Convert.FromBase64String(authHeader)).Split(":"c)
            If (credentials.Length <> 2 OrElse String.IsNullOrEmpty(credentials(0)) OrElse
                String.IsNullOrEmpty(credentials(1))) Then
                Return Nothing
            End If
            Return credentials
        End Function

        Protected ReadOnly Property RolesSplit() As String()
                Return SplitStrings(Roles)
            End Get
        End Property

        Protected ReadOnly Property UsersSplit() As String()
                Return SplitStrings(Users)
            End Get
        End Property

        Protected Shared Function SplitStrings(input As String) As String()
            If String.IsNullOrWhiteSpace(input) Then Return New String() {}
            Dim result = input.Split(","c).Where(Function(s) Not String.IsNullOrWhiteSpace(s.Trim()))
            Return result.Select(Function(s) s.Trim()).ToArray()
        End Function

        ''' <summary>
        ''' Implement to include authentication logic and create IPrincipal
        ''' </summary>
        Protected MustOverride Function TryCreatePrincipal(user As String, password As String, ByRef principal As IPrincipal) As Boolean
    End Class
    Public Class MembershipHttpAuthorizeAttribute
        Inherits BasicHttpAuthorizeAttribute

        ''' <summary>
        ''' Implement to include authentication logic and create IPrincipal
        ''' </summary>
        Protected Overrides Function TryCreatePrincipal(user As String, password As String, ByRef principal As IPrincipal) As Boolean

            principal = Nothing
            If Not Membership.ValidateUser(user, password) Then
                Return False
            End If
            Dim rles = Web.Security.Roles.Provider.GetRolesForUser(user)

    'TODO: You must assign here your OWN principal       

            'principal = New GenericPrincipal(New GenericIdentity(user), roles)
            Return True
        End Function

    End Class


    Final Controller body

    <RoutePrefix("api/blah"), MembershipHttpAuthorize(Roles:=aspnet_Role.blahRole+","+ aspnet_Role.BlahRole2)>
    Partial Public Class MobileService
        Inherits Api.ApiBaseController

    As you can see, I don’t have the ‘ Controller’  suffix for my Web API2 controller, and I even can use the RoutePrefix attribute. Second, I did not use ‘ Authorize’  attribute, but the mixed MembershipHttpAuthorize attribute.

    Controller Actions

    ' <summary>
    ' Looks up some data by ID.
    ' </summary>
    <HttpGet, Route("Product/{productid}")>
    Public Function GetProduct(productid As Integer) As IHttpActionResult

        Return Ok(New Product(productid))
    End Function

    I don’t know if WCF could support non-string parameters, I don’t want to know, anyway, as above, you see, it’s quite simple.

    In this case, I like to have a function of type IHttpActionResult, because than I easily can return BadRequest or NotFound().



    - Sometimes, it seemed that JQuery simply did not behave nicely with a REST / JSON call (this also was the case in the WCF implementation of my client), that only returns HTTP 200 (OK) with no return body. So, I found out, that service reliability improved by returning A value such as Ok(True). So, basically, always define your actions with a specific type, not being ‘void’ or ‘sub’. OK?

    - Another issue occurring with HttpPost and HttpPut is when parameters are partly from a Uri and partly from body. WCF could figure this out, but strangely enough, you must help Web Api 2 using attributes FromUriAttribute and FromBodyAttribute. I did not have time to figure out when this was needed, or not but added the attribute.


    <HttpPost, Route("Network/{networkid}/GetCustomerConsumer/")>
    Public Function GetCustomerConsumer(networkid As Integer, <FromBody> req As GetCustomerConsumerRequest) As IHttpActionResult
            Return Ok(GetCustomer(networkid, req))
        Catch ex As Exception
            Return BadRequest(ex.Message)
        End Try
    End Function

    In the sample below, it certainly was necessary to define a ‘ dummy’  class, to pass simple types like status, which is an integer.

    <HttpPost, Route("status/{myid}")>
    Public Function SetStatus(myid As Integer, <FromBody> dm As Dummy) As IHttpActionResult

    Public Class Dummy
         Public remark As String
         Public status As Integer
    End Class

    - Ironically, the DataContractJsonSerializer was able to convert JSON ‘objects’ back to an interface, say, ICustomer, while NewtonSoft serializer complains about  not being able to cast from an object to ICustomer. It might happen with your project as well, as long as (not sure however) there is no ambiguity on which class it should instantiate for ICustomer. (The KnownTypeAttribute, normally should fix this). The NewtonSoft Serializer, allows you to utilize an attribute. This code below, also might save you some hours research on how to fix that. (Sorry, this time it is C#, yeah)

    Here we use the attribute:

    public ICustomer Customer { get; set; }

    And here you have the class. Just in case a concept you need it and how you use it!

    public class PersonConverter : JsonCreationConverter<Person>
            protected override Person Create(Type objectType, JObject jObject)
                if (FieldExists("Initials", jObject))
                    return new Person();
                if (FieldExists("Type", jObject))
                    return new Contact();
                return null;
            private static bool FieldExists(string fieldName, JObject jObject)
                return jObject[fieldName] != null;
        public abstract class JsonCreationConverter<T> : JsonConverter
            /// <summary>
            /// Create an instance of objectType, based properties in the JSON object
            /// </summary>
            /// <param name="objectType">type of object expected</param>
            /// <param name="jObject">
            /// contents of JSON object that will be deserialized
            /// </param>
            /// <returns></returns>
            protected abstract T Create(Type objectType, JObject jObject);

            public override bool CanConvert(Type objectType)
                return typeof(T).IsAssignableFrom(objectType);

            public override object ReadJson(JsonReader reader,
                                            Type objectType,
                                             object existingValue,
                                             JsonSerializer serializer)
                // Load JObject from stream
                var jObject = JObject.Load(reader);

                // Create target object based on JObject
                var target = Create(objectType, jObject);

                // Populate the object properties
                serializer.Populate(jObject.CreateReader(), target);

                return target;

            public override void WriteJson(JsonWriter writer,
                                           object value,
                                           JsonSerializer serializer)
                //default easy muke
                serializer.Serialize(writer, value);

    Annother quirk that might bite you, is the fact that a WCF REST service, defaults to return application/xml content while Web API defaults to application/json. If a client application did not specify the Accept Header or even specifies ‘text/html’. This line below fixes the default output again to application/xml.

    config.Formatters.XmlFormatter.SupportedMediaTypes.Add(New MediaTypeHeaderValue("text/html"))

    WebApi Config

    You need to adapt JSON serialization as well. Try to keep it using the NewtonSoft.Json serializer, instead of the  json.UseDataContractJsonSerializer=true!

    You need to set MicrosoftDateFormat to be compatible with the WCF REST JSON output (instead of ISO). Second, you need to output Null Values as well. There also is an issue, with TimeZone support in WCF, which is unspecified I believe, which leads to crazy bugs with DateTime output, anyway, without solving that WCF issue in this article, you need ‘ Unspecified’  here as well.

    Another nice feature while debugging/developing, is Indented JSON which allows you to easily read your JSON output using your favorite Browser Netwerk trace.

       Public Class WebApiConfig
            Public Shared Sub Register(ByVal config As HttpConfiguration)

                Dim json = config.Formatters.JsonFormatter
                json.SerializerSettings.DateFormatHandling = Newtonsoft.Json.DateFormatHandling.MicrosoftDateFormat          
                json.SerializerSettings.NullValueHandling = Newtonsoft.Json.NullValueHandling.Include
                json.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Unspecified
    #If DEBUG Then
                json.SerializerSettings.Formatting = Newtonsoft.Json.Formatting.Indented
    #End If
                'override the suffix 'Controller' requirement
                Dim suffix = GetType(DefaultHttpControllerSelector).GetField("ControllerSuffix", BindingFlags.Static Or BindingFlags.Public)
                If suffix IsNot Nothing Then suffix.SetValue(Nothing, String.Empty)  

            End Sub
        End Class

    12-19-2014, 20:04 by eprogrammer to Egbert Nierop technolog
  • The Ultimate T-SQL String Splitter (function)


    I just want to share this function, since there are a lot of version around, which are not resistant against zero positions advance, for instance, if you split ‘1,2,3’ into a table, it would be find, but what if one element is empty, such as ‘1,,3’? This function deals with it setting returning a null element.


    SELECT * FROM [udf_SplitVarchar2Table]('one,two,three', ',')


    ALTER FUNCTION [dbo].[udf_SplitVarchar2Table]
        @List varchar(max),
        @delimiter VARCHAR(10)

        @Values TABLE(col VARCHAR(512))

        IF @List IS NULL OR LEN(@List) = 0 RETURN;
      SET @List = replace(@List,CHAR(39)+CHAR(39),CHAR(39))
      DECLARE @Index INT=1; 
      DECLARE @ItemValue varchar(100);  
      DECLARE @pos INT = 1;
      DECLARE @l INT = LEN(@List);

      WHILE @Index > 0   
          SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
          IF @Index  > 0 
                IF (@index- @pos> 0)
                    SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
                    SET @ItemValue=NULL;
            IF (@l-@pos+1)>0
                SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
                SET @ItemValue = NULL;

          INSERT INTO @Values (col) VALUES (@ItemValue);    
          SET @pos = @index+1;

    08-28-2014, 12:31 by eprogrammer to Egbert Nierop technolog
  • How to read a HTML page from a remote site using VBA/.NET into a Htmlocument


    There are a lot of ways to read and parse HTML, the better tricks, don’t use IE itself, since this will deliver automation errors and waste memory.

    I’m for 99% of my time into .NET programming, but still, one of my hobbies use an Access 2013 database and thus, a VBA codebase, yummy! And to get powerfeatures, I compiled a tlb to have interfaces like IPersistStreamInit, IStream etc. (it’s called odl compiling and requires  MkTypLib.EXE, not midl.exe!)

    Now here is a neat way to fetch/get a plain HTML text and load it into a HTMLDocument without any dependency on IE automation. You’re a smart non-lazy programmer (right?) so you get the idea for C# as well since you need IPersistStreamInit there as well. It’s COM interop, dude!

    Public Function HttpGet(ByRef url As String) As mshtml.HTMLDocument
        Dim xmlHttp As MSXML2.ServerXMLHTTP60
        Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
        xmlHttp.Open "GET", url, False
       'set return value
        Set HttpGet = New HTMLDocument
        Dim stream As
        Set stream = CreateObject("ADODB.Stream")
        Dim istrea As IPersistStreamInit
       'get interface IPersistStreamInit from HTMLDocument
        Set istrea = HttpGet
       'write the muke using a binary array (bytes)
        stream.Type = adTypeBinary
        stream.write xmlHttp.responseBody
       'reset stream
        stream.position = 0
        'load the muke into the HTMLDocument
        istrea.Load stream

        Dim s As Single
        s = Timer

       'fake body onload ready
        Do Until Timer - s > 10 Or HttpGet.ReadyState = "complete"

    End Function

    07-11-2014, 10:43 by eprogrammer to Egbert Nierop technolog

Who is Online

There are 60 guest(s) online. Currently there are no online users.

Recent Additions

  • None
Powered by Community Server, by Telligent Systems