Technolog

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

Front Page News

  • 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.

    Usage:

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

    returns:

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

    RETURNS
        @Values TABLE(col VARCHAR(512))
    AS

    BEGIN 
        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   
        BEGIN        
          SET @Index = CHARINDEX(@Delimiter,@List, @pos);  
       
          IF @Index  > 0 
                IF (@index- @pos> 0)
                    SET @ItemValue = SUBSTRING(@List,@pos, @index- @pos );
                ELSE
                    SET @ItemValue=NULL;
          ELSE
            IF (@l-@pos+1)>0
                SET @ItemValue =SUBSTRING( @List, @pos, @l-@pos+1) ;
            ELSE
                SET @ItemValue = NULL;

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

    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
        xmlHttp.send
       'set return value
        Set HttpGet = New HTMLDocument
        Dim stream As adodb.stream
        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.Open
        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"
            DoEvents        
        Loop

    End Function

    07-11-2014, 10:43 by eprogrammer to Egbert Nierop technolog
  • T-SQL Alternative to hexadecimal binary strings?

    I found an easy way to have binary parameters as base64 encoded string. You might wonder, why bother?

    Well, in a a well used environment, size and compactness of data over the wire, still matters! Because a binary value is sent as a hexadecimal over the wire; Hexadecimals are 4 times the size of one byte. Base64 encoded strings however, just need +/- 3 times the size of one byte. .

    example:

    EXEC proc_receiveMyBlob 0xA05FDAF  (etc) 

     The stored procedure itself would have this signature:

    CREATE PROC  @myBLob varbinary(max)    -- or image whatever

    BEGIN 

       INSERT INTO tblMyBlobs VALUES(@myBlob);

    END 

    The trick:

     CREATE PROC  @myBLob xml  -- <-- use the xml T-SQL data type

    BEGIN 

    -- remember, the binary field in SQL must not be changed to xml, keep it as binary! 

       INSERT INTO tblMyBlobs VALUES(@myBlob.value('xs:base64Binary(.)', 'varbinary(max)') );

    END  

     

    The call to the stored proc (obviously) looks something like this:

    EXEC proc_receiveMyBlob 'SGVsbG8gQmFzZTY0' 

    or if you like:

    EXEC proc_receiveMyBlob '<data>SGVsbG8gQmFzZTY0</data>' 

     

    02-12-2014, 8:28 by eprogrammer to Egbert Nierop technolog
    Filed under: , ,
  • SiteMaps made easy

     

    If you’re a site admin or asp.net developer for an internet site, you certainly need to look into sitemaps, if you want to perform SEO.

    It’s not necessary to  simply crawl your own site and then to give every page a priority, but consider this for a forum or other pages which are irregularly or often updated. If you don’t want to have crawlers do unneeded roundtrips, implement a sitemap.

    ‘robots.txt’ should contain a reference to your map eg. Sitemap: http://www.myfantasticsite.com/sitemap.xml

    Ideas of this class, written using C#, can be found anywhere on the net. However, as some might know me, I like it to be finished and neat and a self-supporting class ready for usage (e.g. it must not be written to a string to add or remove wished attributes that the serializer could not handle).

    The following things are solved. 
    Since  ‘changefreq’ and ‘lastmod’ and ‘priority’ are optional values, you don’t want the XmlSerializer to create empty tags!
    This is done by adding a DefaultValue attribute. It will cause XmlSerializer to check the current value against the default value. If they are equal, it is considered to be an empty non existing tag. Remember, that the defaultvalues need to be out of the range of possible values! Therefore, EnumChangeFreq contains an extra member ‘notused’
    Remember, the Xml.Serialization name space, offers the tools to get it done without converting your loading your XML in to some XmlDocument class.

    You can use the class as follows.
    UrlSet retVal = new UrlSet();

    retVal.AddUrl(new Url() { LastModifiedDateTime = DateTime.Now.ToUniversalTime(), Loc = “http://www.myfantasticsite.com/blah.aspx”) });

    Retrieve the XML sitemap string.
    string xml = null;
    using (var io = (MemoryStream)retVal.ToStream())
    {
                    xml =  new UTF8Encoding().GetString(io.ToArray());
    }

    or, to write it directly to an output stream

    using (var io = (MemoryStream)retVal.ToStream())
    {

    //todo: Deal with Response.Cache, etag and last modified to avoid unnecessary round trips.
    Response.ContentType = “text/xml”;
    Response.CharSet = “utf-8”;
    Response.BinaryWrite(retVal.ToArray());

    }

    using System;
    using System.Xml;
    using System.Xml.Serialization;
    using System.ComponentModel;
    using System.IO;

    namespace adccure
    {

        public enum EnumChangeFreq
        {
            notset,
            always,
            hourly,
            daily,
            weekly,
            monthly,
            yearly,
            never
        }

    [

    [XmlRoot(ElementName = "urlset", Namespace = SCHEMA_SITEMAP)]
    public sealed class UrlSet
    {
        [XmlNamespaceDeclarations]
        public XmlSerializerNamespaces xmlns;
        private const string XSI_NAMESPACE = "http://www.w3.org/2001/XMLSchema-instance";
        private const string SCHEMA_SITEMAP = "http://www.sitemaps.org/schemas/sitemap/0.9";

        private Url[] _url;

        public UrlSet()
        {
            _url = new Url[0];
            xmlns = new XmlSerializerNamespaces();
            xmlns.Add("xsi", XSI_NAMESPACE);
            SchemaLocation = SCHEMA_SITEMAP + " " + "http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd";

        }
        [XmlAttribute(AttributeName = "schemaLocation", Namespace = XSI_NAMESPACE)]
        public string SchemaLocation;

        public void AddUrl(Url url)
        {
            int l = _url.Length + 1;
            Array.Resize(ref _url, l);
            _url[l - 1] = url;
        }

        [XmlElement(ElementName = "url")]
        public Url[] url
        {
            get { return _url; }
            set { _url = value; } //bogus
        }
        /// <summary>
        /// serializes the UrlSet to a sitemap.xsd conform string ready for saving to disk.
        /// </summary>
        /// <returns>a Stream object</returns>
        public Stream ToStream()
        {
            XmlSerializer xmlser = new XmlSerializer(GetType());
            var io = new MemoryStream();
            xmlser.Serialize(new StreamWriter(io, Encoding.UTF8), this);
            io.Position = 0;
            return io;
        }
    }

        public sealed class Url
        {
            private string _loc;
            private DateTime _lastmod;
            private float _priority;
            private EnumChangeFreq _changefreq;

            public Url()
            {
                //setting defaults
                _changefreq = EnumChangeFreq.notset;
                _priority = 0.0F;
                _lastmod = DateTime.MinValue;
            }

            [XmlElement(ElementName = "loc")]
            public string Loc
            {
                get
                {
                    return _loc;
                }

                set
                {
                    if (string.IsNullOrEmpty(value))
                    {
                        throw new ArgumentNullException();
                    }
                    if (value.Length < 12 || value.Length > 2048)
                    {
                        throw new ArgumentException("loc must be between 12 and 2048 in length");
                    }
                    _loc = value;
                }
            }
            [XmlElement(ElementName = "lastmod"), DefaultValue(typeof(DateTime), "1-1-0001")]
            public DateTime LastModifiedDateTime
            {
                get
                {
                    return _lastmod;
                }

                set
                {
                    _lastmod = new DateTime(value.Year, value.Month, value.Day, value.Hour, value.Minute, value.Second, value.Kind);

                }
            }
            [XmlElement(ElementName = "changefreq")]
            [DefaultValue(EnumChangeFreq.notset)]
            public EnumChangeFreq ChangeFreq
            {
                get
                {
                    return _changefreq;
                }

                set
                {
                    _changefreq = value;
                }
            }
            [XmlElement(ElementName = "priority")]
            [DefaultValue(0.0F)]
            public float Priority
            {
                get
                {
                    return _priority;
                }

                set
                {
                    if (value < 0 || value > 1.0)
                    {
                        throw new ArgumentException("Must be between 0 and 1");
                    }
                    _priority = value;
                }
            }
        }
    }

    Tags van Technorati: ,,
    01-31-2010, 18:12 by eprogrammer to Egbert Nierop technolog
  • Howto: Create a custom numeric pager for the ASP.NET Gridview Control

    Figure 1: Our custom pager in action!

    I never have liked the concept of storing all the data in whatever form (DataTable/Lists of records/etc.) to the ASP.NET gridview control and having it automatically manage paging for me. This could however be improved using Visual Studio 2008 wizards. However, this requires writing stored procedures.

    I’ve got a concept for you, which works without a lot of extra work. The concept is:

    1. Inherit from asp:GridView and override the PageCount and the PageIndex properties
    2. Create in instance of my CustomPager class at DataBinding time.

    The result is as shown in figure 1: It adheres to PageButtonCount, to any styles that you have applied to the GridView and it features a ‘jump to’ page input box.
    The ‘native’ event handling in your ASPX, still can be maintained by this code since it emulates the PageIndexChanging event.

    Other solutions, implement an AJAX updatepanel per row. This really minimizes unnecessary refreshing of grid data.
    However, I don’t mind if say, 30 rows are being pulled from a DB and bound to a GridView. If we do so, we also get the best of two worlds, in one world, we get all data and have a fresh update of the real table rows, and in the other world, we have just the active row being fetched and we could end up having outdated data on our screen because of (for instance) co-writers/typers who updated rows at the database which are not reflected at our screen in grid (which is displayed using a gridview).

    So, in other words, I like this pager control since it is a balanced solution! It has been tested on 10,000 records. WHen I would page to for instance, page 900, it really is a matter of fractions of a second to get a response. (For paging solutions  on SQL server data tables which contain say millions of rows, we would need a more sophisticated approach).

    Here is the control source (it’s called gridhelper.cs)! (In theory, it should work for .NET 2.0 up to 3.5)

    // if you use this code, please leave the original author
    //author: Egbert Nierop
    using System;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    namespace adccure.tools
    {
        public sealed class GridView2 : GridView
        {
            public GridView2()
                : base()
            {
            }
            private int _pageCount;

            public override int PageIndex
            {
                get
                {
                    object o = ViewState["pgi"];
                    return o == null ? 0 : (int)o;
                }
                set
                {
                    ViewState["pgi"] = value;
                }
            }

            public override int PageCount
            {
                get
                {
                    return _pageCount;
                }

            }
            public void PageCountSet(int pageCount)
            {
                _pageCount = pageCount;
            }
        }

        public sealed class CustomPager : ITemplate
        {
            readonly Table tbl;
            readonly GridView2 _grid;

            //readonly int _totalPages;
            public CustomPager(GridView2 grid, int totalPages)
            {
                tbl = new Table();
                grid.PageCountSet(totalPages);
                tbl.Width = Unit.Percentage(100);
                _grid = grid;
            }
            void ITemplate.InstantiateIn(Control container)
            {
                container.Controls.Add(tbl);

                int pageSize = _grid.PageSize;
                int pageIndex = _grid.PageIndex;
                int pageButtonCount = _grid.PagerSettings.PageButtonCount;
                int pageCount = _grid.PageCount;
                ClientScriptManager cs = _grid.Page.ClientScript;
                _grid.PagerSettings.Visible = true;
                var trow = new TableRow();
                var trowpagePosition = new TableRow();
                tbl.Rows.Add(trow);
                tbl.Rows.Add(trowpagePosition);
                TextBox tb = new TextBox();
                tb.ID = "txtJumpPage";
                tb.MaxLength = 4;
                tb.Width = Unit.Pixel(40);
                tb.Text = (pageIndex + 1).ToString();
                //avoid bubble up by return false
                tb.Attributes["onkeydown"] = string.Format("if (event.keyCode==13) {__doPostBack('{0}', 'Page$' + this.value ); return false;}", _grid.UniqueID);

                LiteralControl lit = new LiteralControl(string.Format(" of {0}", (pageCount + 1).ToString()));
                TableCell posCaption = new TableCell();
                trowpagePosition.Cells.Add(posCaption);
                posCaption.Controls.Add(tb);
                posCaption.Controls.Add(lit);
                int cellspan = 0;
                if (pageIndex > 0)
                {
                    var cellText = new TableCell();
                    trow.Cells.Add(cellText);
                    cellspan++;
                    cellText.Controls.Add(new HyperLink()
                    {
                        NavigateUrl = cs.GetPostBackClientHyperlink(_grid,
                        string.Format("Page${0}", pageIndex - pageButtonCount >= 0 ? (pageIndex - pageButtonCount) + 1 : 1), false),
                        Text = "<"
                    });
                }
                for (int x = pageIndex; x < pageIndex + pageButtonCount && x <= pageCount; x++)
                {
                    var cellText = new TableCell();
                    cellspan++;
                    trow.Cells.Add(cellText);
                    cellText.Controls.Add(new HyperLink()
                    {
                        NavigateUrl = cs.GetPostBackClientHyperlink(_grid,
                            string.Format("Page${0}", x + 1), false),
                        Text = (x + 1).ToString(),
                    });

                }
                if (pageIndex + pageButtonCount < pageCount)
                {
                    var cellText = new TableCell();
                    cellspan++;
                    trow.Cells.Add(cellText);

                    cellText.Controls.Add(new HyperLink()
                    {
                        NavigateUrl = cs.GetPostBackClientHyperlink(_grid,
                        string.Format("Page${0}", (pageIndex + pageButtonCount) + 1), false),
                        Text = ">"
                    });
                }
                tbl.Visible = true;
                posCaption.HorizontalAlign = HorizontalAlign.Center;
                posCaption.ColumnSpan = cellspan;
            }
        }
    }

    Now, I don’t publish the code to read from sample data (such as northwind), it would be a yadda, yadda and you know the drill. (In my code, it is just a silly HttpReferrer table having all the columns which allow you research this specific statistical interest of your web site)
    But in my datalayer, I have things like shown below. It is a great solution for those tables, for say, less than 100,000 records. SQL server is able to deal with these types of queries pretty well and we still avoid pumping around lots of redundant data on the network and gridview control.

    public IList<HttpReferrer> getHttpReferrers(int pPage, int pPageSize, HttpReferrerSortOrder sortOrder,
                    SortDirection sortDirection,
                    out int totalRecords)
            {
                totalRecords = dcd.HttpReferrers.Count();
                IQueryable<HttpReferrer> retVal = null;
                if (sortDirection ==  SortDirection.Ascending)
                {
                    switch (sortOrder)
                    {
                        case HttpReferrerSortOrder.Referer:
                            retVal = dcd.HttpReferrers.OrderBy(t => t.Referrer);
                            break;
                        case HttpReferrerSortOrder.IP:
                            retVal = dcd.HttpReferrers.OrderBy(t => t.IP_Address);
                            break;
                        case HttpReferrerSortOrder.Page:
                            retVal = dcd.HttpReferrers.OrderBy(t => t.page);
                            break;
                        default:
                            retVal = dcd.HttpReferrers.OrderBy(t => t.ts);
                            break;
                    }
                }
                else
                {
                    switch (sortOrder)
                    {
    case HttpReferrerSortOrder.Referer:
                            retVal = dcd.HttpReferrers.OrderByDescending(t => t.Referrer);
                            break;
                        case HttpReferrerSortOrder.IP:
                            retVal = dcd.HttpReferrers.OrderByDescending(t => t.IP_Address);
                            break;
                        case HttpReferrerSortOrder.Page:
                            retVal = dcd.HttpReferrers.OrderByDescending(t => t.page);
                            break;
                        default:
                            retVal = dcd.HttpReferrers.OrderByDescending(t => t.ts);
                            break;
                    }
                }
                return retVal.Skip(pPage * pPageSize).Take(pPageSize).ToList();
            }

    So, our Grid, can sort and it can page.

    How do we deal with the paging event at the code behind the aspx?
    It’s so simple!

    void gridHttpReferrers_PageIndexChanging(object sender, GridViewPageEventArgs e) 

    gridHttpReferrers.PageIndex = e.NewPageIndex; 
    gridHttpReferrers.DataBind(); 
    }

    void gridHttpReferrers_DataBinding(object sender, EventArgs e)

    {   int totalRecords; 
       int pageSize = gridHttpReferrers.PageSize; 
       int pageIndex = gridHttpReferrers.PageIndex; 
      gridHttpReferrers.DataSource = datadal.getHttpReferrers(pageIndex, pageSize, SortOrder, SortDir, out totalRecords); 
       gridHttpReferrers.PagerTemplate = new CustomPager(gridHttpReferrers, totalRecords); 
       gridHttpReferrers.PageCountSet (totalRecords / pageSize);
    }

    What did I do to get the new grid behavior inside the ASPX?
    Just rename the tag from asp:GridView to ctrl:GridView2 and create the reference (or in web.config)

    <%@ Register TagPrefix="ctrl" Namespace="adccure.tools" %>

    <ctrl:GridView2 runat="server" emptydatatext="No data available."  ID="gridHttpReferrers" AllowPaging="true" AllowSorting="True"  Width ="100%" AutoGenerateColumns="false" PageSize="20" DataKeyNames="ID"><PagerSettings Position="Top" PageButtonCount="20" />
    <
    Columns> ETC.

    So, I hope this code was quite enlightning for you and you can play with it and have fun.

    10-28-2009, 21:51 by eprogrammer to Egbert Nierop technolog
    Filed under: , ,

Who is Online

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

Technolog is vernieuwd

Wil je ook een weblog? Meld je dan gratis aan.

Technorati Zoeken

Recent Additions

  • Sunset in Seattle
Powered by Community Server, by Telligent Systems