How to use .NET WebBrowser to take screenshots

Do you need to take snapshot of some urls? You just want a headless browser? You are using .NET? You don’t want to rely on node/phantom or on a .net library (awesomium, cefsharp, webkitdotnet)? You love IE? Yes to all? This post is for you!

Because we can’t do a screenshot in modern browsers

If you have control of your webapplication / website that needs to take the screenshots, you know that is no such thing in current browser, no standard whatsoever (but the WebRTC is coming !). Maybe you encountered the client library : html2canvas (and  cansvg if you are dealing with svg) but this is not enough. It’s good for svg and canvas, but as soon as some html5 controls are in or complex style, it won’t do a nice job. You need a true browser that renders the page nicely, is waiting for ajax, pictures and so on, then call a method on it to get a snapshot.

Let’s emulate IE (yes, we want!)

Hopefully for you, .NET WebBrowser control is there. It is not suitable in every situation (take a coffee and read this stackoverflow post), but if you just want a snapshot of a given url, it won’t be a problem.

STAThread

First of all, you need a thread in STA (Single Thread Apartment) mode. If you want to take a snapshot from WCF or a console app, or any non-STA thread program, you need to create this kind of thread like this :

        Dim ARE As New AutoResetEvent(False)
        Dim tWebBrowserSnapshot As Bitmap = Nothing
        Dim t = New Thread(Sub()
                               Try
                                   Using tWebBrowser = New WebBrowser()
                                   ...
                                   End Using
                               Finally
                                   ARE.Set()
                               End Try
                           End Sub)
        t.SetApartmentState(ApartmentState.STA)
        t.Start()
        ARE.WaitOne()
        Return tWebBrowserSnapshot

Snapshot me

The browser is created, yeepee! You can now add some code to give it some style/size.

tWebBrowser.ScrollBarsEnabled = False
tWebBrowser.ScriptErrorsSuppressed = True
tWebBrowser.Width = myWidth
tWebBrowser.Height = myHeight
tWebBrowser.Navigate(myUrl)

Then you can take a snapshot because it has a nice method: DrawToBitmap.

Dim tBitmap = New Bitmap(tWebBrowser.Width, tWebBrowser.Height)
tWebBrowser.DrawToBitmap(tBitmap, New Rectangle(0, 0, tWebBrowser.Width, tWebBrowser.Height))

Snapshot logic

The main logic is there, but if you try that, you can run into severals issues. (such as blank image)

First, don’t forget that it’s a ‘true’ browser that renders the page. So, it takes time for it to load the page, then if it has ajax or images, it will call them, process the response etc. There is no magic flag that is set when everything is loaded on the page (to wait to call DrawToBitmap). WebBrowser has some events (such as DocumentCompleted) but it’s like the jquery $(document).on(‘ready’, fn), nothing is done yet. If you don’t know the targetted url, you don’t have a lot of choices :
– wait a finite amount of time to be almost sure everything is loaded
– maybe you can try to catch every ajax (by injecting some script into the document) the website is doing then count how many came back (but still, add a max timeout otherwise you could wait an infinite time).
– if you control the website on the url, create a variable that will be set to some value (true), and just wait for that.

Windows Registry is there for you

If you did that, you can still have blank pages. Why ? Because WebBrowser can use a old IE engine to render the page, which is not compatible. Because your server is up to date, you want to use IE11. To do that, you need to add a key in the registry.

HKEY_LOCAL_MACHINE
   SOFTWARE
      Microsoft
         Internet Explorer
            Main
               FeatureControl
                  FEATURE_BROWSER_EMULATION

Add a REG_DWORD with the process name (if you are using IIS, the process is w3wp.exe; if you are using a console app, put the name of your exe) and as data: 0x2AF9 (check msdn to see the possible values, IE10 is 0x2711).

IIS will help too

Last thing, if it’s a WCF that does the screenshot, and if you are using IIS, you need to change who is running the pool to LocalService.

Have a nice screenshot !

How to have aggregated results in a flat table

You have some nice data to display into a pivot table, but you can’t rank or format them so you are stuck ? Don’t worry!

We are currently working on the pivot table to include theses features (among others!), until then, here is a workaround using the flat table. And even more, you will learn new awesome stuff using the DataTransform feature.

For instance, let’s take a simple example with a table which has 2 columns : [Car Name] and [Quantity]. You want to display the sum of [Quantity] over [Car Name]. Basically in pure SQL it’s : sum(quantity) .. group by [Car Name] but this reduces the row count from 1000 to 5 for instance (if you have 5 cars), thus, this is not useable in DataTransform where it can just add/remove columns, ‘group by’ is not available.

What if we create a new column which is the sum of Quantity for each [Car Name] first ? Do you now the SQL “over” function ? This allows you to use aggregations that are computed for each row.

sum([Quantity]) over(partition by [Car Name])

Image

The computed column looks like :

Image

We can see that each value is repeated for each [Car Name]. (13, 6, and 8).

This is not yet useful in a flat table because of those repeated values. Let’s find a way to take only the first one. Because you can’t just remove rows in datatransform, we need to find a way to remove them using a filter on the dashboard instead.

Let’s create a new column that will help us to identify the first one for each group. To do that, we use generate a simple auto-incremented value (1, 2, .. ) and we will be able to just take the sum(Quantity) where this column equals 1 (each group will have its own sequence from 1 to n).

row_number() over(partition by [Car Name] order by [Car Name])

Image

The preview looks like :

Image

We can distinguish 5 groups : Alfa Romeo, AMC Hornet, Aston Martin, Bentley, BMW, and for each rownumber = 1, the sum of quantity : 13, 6, 8, 11, 8 (the sum is computed on the preview values only, not on the overall. It will be when you will process the transform). Thus, we can create our flat table from that and use ranking, filtering, format and so on.

Image

Image

Et voilà ! Happy Dashboarding !

How to vertical-align an element in css using flexbox

Forget about :

  • display: table
  • display: table-cell
  • height: 100px / line-height: 100px / line-height: 1
  • top: 50% / margin-top: -25px
  • or why not some JS ? parent.style.marginTop = “-” + (parent.clientHeight/2) +”px”;

You can use the css flexbox layout to do that without any trick, and being more configurable.
You just have to set some flexbox properties on the container that contains what you wish to center.

If you haven’t heard of flexbox, go to read W3C Spec or more friendly Using CSS flexible boxes for instance.


<div class="container">
    <span>I wish I could be centered</span>
</div>
<hr />
<div class="container">
  <span>I wish I could be centered</span>
  <span>I wish I could be centered</span>
</div>

.container {
   display: flex;
   flex-direction: row; /* default is row */
   justify-content:center; /* default is stretch */
   align-items: center; /* default is flex-start, center is like a text-align: center in our case (because we are in row mode) */
   height: 200px;
   width: 500px;
   background: #aac;
}
.container > * { padding: 5px; border: 1px solid black; background: white; }

The result is http://jsfiddle.net/derste/jhuH5/1/ :

flex hcenter
Using flex-direction: column, the result would be :

flex vcenter

Chrome support

Just add the well known “webkit-” in front of flex properties.

.container {
    display: -webkit-flex;
    -webkit-justify-content: center;
    -webkit-align-items: center;
    height: 200px;
    width: 500px;
    background: #aac;
}

Firefox support

Using firefox, you need to enable the flexbox (disabled by default) (yes, ask your users to do it, right) :
firefoxflex

Cross-browser

Well, for now, only Chrome (>=21) has a good support using -webkit prefix, Firefox is using the W3C notation (>=22) with the trick in about:config, Opera (>=12.10) works, IE10 and Safari are using the old draft of flexbox, so not compatible.

Have fun !

Gradient backgrounds in CSS

Gradient background

We are used to css3 gradient backgrounds, such as this one :

background: #258dc8; /* Old browsers */
background: -moz-linear-gradient(top, hsla(202,69%,47%,1) 0%, hsla(202,69%,47%,1) 100%); /* FF3.6+ */
background: -webkit-gradient(linear, left top, left bottom, color-stop(0%,hsla(202,69%,47%,1)), color-stop(100%,hsla(202,69%,47%,1))); /* Chrome,Safari4+ */
background: -webkit-linear-gradient(top, hsla(202,69%,47%,1) 0%,hsla(202,69%,47%,1) 100%); /* Chrome10+,Safari5.1+ */
background: -o-linear-gradient(top, hsla(202,69%,47%,1) 0%,hsla(202,69%,47%,1) 100%); /* Opera 11.10+ */
background: -ms-linear-gradient(top, hsla(202,69%,47%,1) 0%,hsla(202,69%,47%,1) 100%); /* IE10+ */
background: linear-gradient(to bottom, hsla(202,69%,47%,1) 0%,hsla(202,69%,47%,1) 100%); /* W3C */
filter: progid:DXImageTransform.Microsoft.gradient( startColorstr='#258dc8', endColorstr='#258dc8',GradientType=0 ); /* IE6-9 */

gradient0

This is pasted from http://www.colorzilla.com/gradient-editor/ (preset Blue Flat #1)
This is the best online editor to make gradients and contains a lot of presets.


Stops

A gradient has a list of gradient-stop : at least 2, for the beginning and for the end, and it can have others between to add variations.
For Blue Flat, if we look only the W3C css :

background: linear-gradient(to bottom,
hsla(202,69%,47%,1) 0%, /* beginning */
hsla(202,69%,47%,1) 100% /* end */
);

For instance, let’s add 2 between :

background: linear-gradient(to bottom,
hsla(212,67%,36%,1) 0%, /* beginning */
hsla(207,69%,51%,1) 50%, /* one at 50 */
hsla(208,73%,46%,1) 51%, /* one at 51 */
hsla(206,70%,70%,1) 100%); /* end */

gradient1

This kind of stops (one just next to the other) permits to create a direct break in the gradient : to change the color from 50% to 51%, it’s only 1% of distance of the container, so, almost invisible, you just see a break.


Direction

The other parameter is the direction of the gradient. In thoses examples, it’s “to bottom”(vertical), but you can set it horizontally “to right”,  or specify an angle 135deg, or else, make a radial gradient that starts from the center : ellipse at center or radial-gradient(at 50% 50%) or wherever you want using %.

gradient1

gradient2

gradient3

gradient4


Cross-browser issues

This is beautiful but we all know that the browsers handles css differently. For instance, to make a radial, you can find the notation : (center, ellipse cover,.., for a vertical gradient, you can find : “to bottom” for “top”. Moreover, the way to specify the gradient stops is also different : in Chrome it’s color-stop(0%,hsla(212,67%,36%,1)). And we don’t talk about <IE10 with the filter: progid:DXImageTransform.Microsoft.gradient( startColorstr=’#1e5799′, endColorstr=’#7db9e8′,GradientType=0 );

It’s for this you should use a generator that won’t do mistakes.

The good news, is that if you support only recent browsers (such as IE10, FF20, Chrome 27), know that the W3C notation is working on those one (I don’t know since which version exactly), no need for all the variations.


background-size

You can combine the gradient with the property background-size. I’m not talking about “cover” and “contain” value, but the px and % notation (mostly the px for that).
For instance :

/* background-size: <width>px <height>px */

div {
    background: linear-gradient(to bottom, hsla(192,69%,47%,1) 0%,hsla(202,69%,47%,1) 100%);
    background-size: 100px 10px;
  /* background-repeat: repeat; this is by default but it is needed for the effect! */
}

This will render a nice tiled background of my gradient (a repeated tile of 100px 2px) (http://jsfiddle.net/xQxbe/)
gradient5

You can create things like an interlaced overlay, or old school effect with a radial gradient (http://jsfiddle.net/zhp3H/)
gradient6


Test it!

Give a try on this jsfiddle to see all that in action !

gradient7

http://jsfiddle.net/DVqmG/1/


Multiple background

Know that the gradients are compatible with the css3 multiple background. For browser support, check http://caniuse.com/#feat=multibackgrounds (it’s been a while it’s around). For instance, let’s make a tiled radial of 20px*20px a bit faded on its left (http://jsfiddle.net/fP2tR/):

    background:
        linear-gradient(to right, rgb(255, 255, 255) 0%, rgba(255, 255, 255, 0) 100%),
        radial-gradient(ellipse at center, hsla(212,67%,36%,1) 0%,hsla(206,70%,70%,1) 100%);
    background-size:
        100% 100%,
        20px 20px;

gradient8

You can combine that with traditional background url(…), background-repeat, background-position properties to make crazy stuff. (http://jsfiddle.net/2TpFT/)

body {
    background:
        radial-gradient(at 74px 32px, rgb(255, 255, 255) 0%, rgba(255, 255, 255, 0) 30%),
        url(http://clicdata.com/Images/clicdata2.png),
        linear-gradient(to right, rgb(255, 200, 200) 0%, rgba(255, 155, 155, 0) 50%, rgb(255, 200, 200) 100%),
        url(http://clicdata.com/Images/pricing/pricing.jpg),
        linear-gradient(to right, rgb(255, 255, 255) 0%, rgba(255, 255, 255, 0) 100%),
        radial-gradient(ellipse at center, hsla(212,67%,36%,1) 0%,hsla(206,70%,70%,1) 100%);
    background-size:
        100px 100px,
        200px 30px,
        60px 200px,
        200px 200px,
        100% 100%,
        20px 20px;
    background-position: 150px 20px, 50px 50px, 50px 200px, 50px 200px, top left, top left;
    background-repeat: no-repeat, no-repeat, repeat-x, repeat-x, repeat, repeat;
}

gradient9

Have fun !

$.ajax errors handling

Why jqXHR.responseText is not enough

You have a HTML/JS application, and some RESTful JSON webservices behind. When an error occurs in your webservices, you throw an WebFaultException exception 404, 500 or whatever you want, with a nice message or a proper error structure such as :

        [WebGet(UriTemplate = "Crash")]
        public List<string> CrashMe()
        {
            throw new WebFaultException<CustomError>(new CustomError() { Code = 42, Message = "I just crash" }, System.Net.HttpStatusCode.Forbidden);
            // or you can just send a string
            // throw new WebFaultException<string>("I just crash as string", System.Net.HttpStatusCode.Forbidden);
        }

Then JS side :

 var tGeneralWS = $.get('Crash')
    .done(...)
    .fail(function (e) { ... });

So, my webservice crash and here what I got :

standard error js

That’s nice, but I have to parse this string if I want to do something with that.

parsejson

And because I don’t want to do that in all my error handler for multiple reasons : I could have different error type to handle, I could do special thing according to the error, and the more important, I want to write my logic only once in the application in case that changes, so let’s fix that.

Let’s try to create a property .ErrorMessage in the jqXHR we could access in our error handler if needed.


$.ajaxError

You could think to use the global handler $.ajaxError this way :

$(document).ajaxError(function (event, jqXHR, settings, exception) {
  // parse the responseText to .ErrorMessage
  try { jqXHR.ErrorMessage = JSON.parse(jqXHR.responseText); }
  catch (e) { }
});

But it will be too late if you need to .ErrorMessage in your own handler .error() or .fail; they will be called before the global error handler so .ErrorMessage won’t be set.

Just to remind you the order : (source: http://api.jquery.com/Ajax_Events/)

  • ajaxStart (Global Event)
    • beforeSend (Local Event)
    • ajaxSend (Global Event)
    • success (Local Event)
    • ajaxSuccess (Global Event)
    • error (Local Event)
    • ajaxError (Global Event)
    • complete (Local Event)
    • ajaxComplete (Global Event)
  • ajaxStop (Global Event)

Override the local error handler

Because the global ajaxError is too late, we need to use the error handler. But because it’s almost sure that there is already an error handler attached to the jqXHR, we will just call the existing one in our own.

$.ajaxPrefilter(function (options, originalOptions, jqXHR) {
  // if you are not using error: function(e) { ... } in $.ajax, this is not needed
  var error = options.error;

  // set the .error handler to add custom logic (and calls existing .error at the end if there is already an .error handler)
  options.error = function (jqXHR, textStatus, errorThrown) {
    // parse the responseText to .ErrorMessage
    try { jqXHR.ErrorMessage = JSON.parse(jqXHR.responseText); }
    catch (e) { }
    // override error handling if exists
    if ($.isFunction(error)) {
      return $.proxy(error, this)(jqXHR, textStatus, errorThrown);
    }
  };
});

Basically :
– we save the old error handler
– we set the new error handler that will
* parse the responseText into ErrorMessage
* call the old error handler if exists

So, in your custom error handler, you have your custom variable, and whatever you want to add to the jqXHR at this time according to the error.


New variable available in jquery recent versions

All that to said that this trick is now useless in modern version of jquery (>= 1.10.1), because you have now access to a new variable in your error handler : jqXHR.responseJSON and its xml equivalent jqXHR.responseXML that will contain the parsed version of the responseText.

recent jquery json error

Have fun !

Working with XML columns in SQL Server, a first approach

Need to work with xml column in SQL Server, and you don’t have a clue what’s XQuery is ?

Well, that was my situation, here’s some basic things you could use if you need to.

XQuery is a query and functional programming language that is designed to query and transform collections of structured and unstructured data, usually in the form of XML, text and with vendor specific extensions other data formats (JSON, binaries, …). (http://en.wikipedia.org/wiki/XQuery)

That said, let’s see what we can do with that in SQL Server. I’ll just present you the different functions available to work with.


query() : works against an xml column, returns xml

SELECT Properties.query('/Properties/Data')
FROM [Table] where ID = 2584

If you have a same node in different hierarchies (for instance Data in /Properties and in /Metadata), you can leave a blank between 2 slashes :

SELECT Properties.query('//Data')
FROM [Table]

query() : http://msdn.microsoft.com/fr-fr/library/ms191474.aspx


exist() : similar to the WHERE statement, but in a xml

Retrieve node /Properties/Data for all records :

... WHERE Properties.exist('/Properties/Data') = 1
... WHERE Properties.exist('/Properties[Data]') = 1 -- equivalent

To get only the nodes whose value is a given one (3214) :

... WHERE Properties.exist('/Properties/Data/Column[(ID=3214)]') = 1

To test the value of an attribute, for instance, here, the “xsi:type” :

&lt;Data xsi:type=&quot;Pie&quot;&gt;
&lt;Data xsi:type=&quot;XY&quot;&gt;

Use the prefix “@” in front of the attribute name :

... WHERE Properties.exist('/Properties/Data[@xsi:type=&quot;Pie&quot;]') = 1

exist(): http://msdn.microsoft.com/fr-fr/library/ms189869.aspx


value() : select a value in your xml and returns a value cast into the datatype you want.

This function takes 2 params :

– xquery : an xquery that returns only one value, for that, use the syntax “(/path/value)[1]” otherwise you will end up with the error “‘value()’ requires a singleton (or empty sequence), found operand of type ‘xdt:untypedAtomic *'”

– the type you want to return, between quotes (internally, it uses CONVERT)

SELECT Properties.value('(/Properties)[1]', 'nvarchar(20)') ...
SELECT Properties.value('(/Properties/Column/ID)[1]', 'int') ...

value(): http://msdn.microsoft.com/fr-fr/library/ms178030.aspx


modify() : update an xml column.

It takes a string in param, that is not xquery, but XML DML (XML Data Modification Language).

This language is an extension of xquery adding some keywords such as ‘insert’ ‘delete’ ‘replace value of’, used to manipulate the xml.


modify : delete

To remove all <Useless>…<Useless> from its parent node :

update [Table]
set Properties.modify('delete /Properties/Useless')

If you want to remove the second occurence, use :

update [Table]
set Properties.modify('delete (/Properties/Useless)[2]')

Even if there is no match, SQL Server returns : (1337 row(s) affected)

To delete an attribute :

update [Table]
set Properties.modify('delete (/Properties/Useless/@myattr)[1]')
-- to delete all attributes :
set Properties.modify('delete (/Properties/Useless/@*)')

To delete all inner nodes :

update [Table]
set Properties.modify('delete (/Properties/Useless/*)')

To delete severals nodes in the same query :

update [Table]
set Properties.modify('delete ( /Properties/Useless/, /Properties/YouToo, /Properties/AndYou )')

modify : insert

To add it back (just one) :

update [Table]
set Properties.modify('insert &lt;Useless&gt;&lt;/Useless&gt; into (/Properties)[1]')

‘into’ could be prefixed by ‘as first’ or ‘as last’ according to where you want to add it into the child. (‘as last into’)
But you can insert your node, not inside the target, but after or before it : use ‘after or ‘before’ instead of ‘into’.
In short : {as first | as last} into | after | before

Again, you have to specify something that is a single node using (..)[1] otherwise you’ll get :
The target of ‘insert’ must be a single node, found ‘element(Properties,xdt:untyped) *’

If you want to insert a value into a node, you have to use the text{} function :

-- &lt;Score /&gt;
update [Table]
set Properties.modify('insert text{&quot;42&quot;} into (/Properties/Score)[1]')
-- &lt;Score&gt;42&lt;/Score&gt;

To add an attribute to a node, use ‘insert attribute {}’ :

-- &lt;Score /&gt;
update [Table]
set Properties.modify('insert attribute gameover {&quot;true&quot;} into (/Properties/Score)[1]')
-- &lt;Score gameover=&quot;true&quot; /&gt;

To change its value :

-- &lt;Score /&gt;
update [Table]
set Properties.modify('replace value of (/Properties/Time/@test)[1] with &quot;&quot;')
-- &lt;Score /&gt;

modify : replace value of … with …

This function takes 2 params, the first is the path where to replace, the second is the value, it makes sense.

The first param needs a single node (“(/…)[n]”) otherwise : The target of ‘replace’ must be at most one node, found ‘element(Useless,xdt:untyped) *’

The second param should be just a string. If you try some metadata like with <score></score>, you’ll get an error : The target of ‘replace value of’ must be a non-metadata attribute or an element with simple typed content, found ‘element(,xdt:untyped) ?’

-- &lt;Score&gt;573&lt;/Score&gt;
update [Table]
set Properties.modify('replace value of (/Properties/Score/text())[1] with &quot;42.0&quot;') 
-- &lt;Score&gt;42&lt;/Score&gt;

This will work if your node already has a value, otherwise, it won’t do a thing (for instance if you have <Score />).

For this case, use the insert text{} :

-- &lt;Score /&gt;
update [Table]
set Properties.modify('insert text{&quot;42&quot;} into (/Properties/Score)[1]')
-- &lt;Score&gt;42&lt;/Score&gt;

If you want to clear a node value, use “with “”)

-- &lt;Score&gt;42&lt;/Score&gt;
update [Table]
set Properties.modify('replace value of (/Properties/Score/text())[1] with &quot;&quot;') 
-- &lt;Score /&gt;

Now, let’s introduce more complex cases that uses functions or references others parts of the same xml.


Reference other part of the XML

Insert nodes which values are from another part of the xml :

-- &lt;Target /&gt;
set Properties.modify('insert /Properties/Source into (/Properties/Target)[1]') 
-- &lt;Target&gt;&lt;Source&gt;google&lt;/Source&gt;&lt;Target&gt;

You can see that the whole node had been put into the target. If you want the value only :

-- &lt;Target /&gt;
... set Properties.modify('insert /Properties/Source/text() into (/Properties/Target)[1]') 
-- &lt;Target&gt;google&lt;Target&gt;

You can count how many nodes match a xquery :

-- &lt;Target /&gt;
... set Properties.modify('insert text{ count(/Properties/Data/*) } into (/Properties/Target)[1]') 
-- &lt;Target&gt;7&lt;Target&gt;

Reference the record columns

You have a “Width” column in your table, you want to put it into the xml :

-- &lt;Target /&gt;
... set Properties.modify('insert ( &lt;Width&gt;{ sql:column(&quot;Width&quot;) } &lt;/Width&gt; ) into (/Properties/Target)[1]') 
-- &lt;Target&gt;&lt;Width&gt;573&lt;/Width&gt;&lt;Target&gt;

Moreover, there are functions available to modify your strings, for instance the well known ‘substring’. Let’s combine that with the text{} function to add a value (not a node) to our target.

-- &lt;Target /&gt;
... set Properties.modify('insert text{substring(sql:column(&quot;Name&quot;),1,3)} into (/Properties/Target)[1]') 
-- &lt;Target&gt;Joh&lt;Target&gt;

If statement

Finally, know that you can do small function directly inside your xml dml such as :

-- &lt;Target /&gt;
... set Properties.modify('insert(
if (count(/Properties/Data/*) &gt; 3)
then
	attribute complex {&quot;true&quot;}
else
	attribute complex {&quot;false&quot;}, 
	attribute type {upper-case(sql:column(&quot;Name&quot;))},
	text {/Properties/Title/text()}
) into (/Properties/Target)[1]') 
-- &lt;Target complex=&quot;false&quot; type=&quot;JOHN&quot;&gt;my title&lt;Target&gt;

FLWOR

What is that ? Well, it stands for : for, let, where, order by, and return.

For instance, if you want to query your xml and returns xml from it :

SELECT Properties.query('
	for $data in /Accounts/*
	return
		&lt;User&gt;
			&lt;ID&gt;{ data($data/Guid) }&lt;/ID&gt;
			&lt;Fullname&gt;{ data($data/Firstname), data($data/Lastname) }&lt;/Fullname&gt;
		&lt;/User&gt;
')
FROM [Table]

Severals things in this code :
– creates … for every nodes in /Accounts
– creates 3434 for each , based on the Guid node text : data(node) is equivalent to node/text()
– creates John Doe from the concatenation of the firstname ans lastname nodes text.

There is a ‘where’ clause, and an ‘order by’ too.

FLWOR Statement and Iteration : http://msdn.microsoft.com/en-us/library/ms190945.aspx


Now that you know the basics, read the documentation, there is no better place to know more. 😉

modify: http://msdn.microsoft.com/fr-fr/library/ms187093.aspx
XML Data Modification Language (XML DML) : http://msdn.microsoft.com/fr-fr/library/ms177454.aspx
insert: http://msdn.microsoft.com/fr-fr/library/ms175466.aspx
delete: http://msdn.microsoft.com/fr-fr/library/ms190254.aspx
replace value of: http://msdn.microsoft.com/fr-fr/library/ms190675.aspx


XQuery basics : http://msdn.microsoft.com/fr-fr/library/ms190262.aspx

Available functions to work against xml column : http://msdn.microsoft.com/fr-fr/library/ms189254.aspx
Functions on String Values (length, concat, convert, contains, substring..): http://msdn.microsoft.com/fr-fr/library/ms178064.aspx

Put every resource values from a resx in your aspx

If you are using javascript (in a dedicated file ofc) to create some DOM that needs translated strings, maybe you are usin some code like this in your .aspx like :

var labelAdmin = '<%=GetLocalResourceObject("Administration")%>';

and thus, use this var in your javascript :

item.text(labelAdmin);

And each time a developer adds a key, he first needs to add its ‘var’ in the aspx. And if he removes one key from the resx, needs to clean up the javascript.
Well, time is money, so, let’s remove this trick and let the aspx generate all those vars. Sadly, the function GetLocalResourceObject needs a param (the key), there is no signature without any (that could retrieve all of them!).

To do this, make your pages where you have resx (of the same name) implements a custom Page class that you will override to automatically inject some javascript into your page.

Public Class MyPage
    Inherits PageWithTranslations
    ...

Public Class PageWithTranslations
  Inherits Page

  Protected Overrides Sub OnPreRender(e As System.EventArgs)
    MyBase.OnPreRender(e)
    ' ... code that will get the resources and inject them in JS into the aspx

For the resources getting part, we are going to use the class ResXResourceReader (don’t forget to add a reference to the assembly : System.Windows.Forms (in System.Windows.Forms.dll)). Don’t try to use the class ResourceManager it is used for the .resources files.

This will be permits us to have an iterator on all the key/value pair and then insert into the page a javascript containing all those key/value pairs.
But to create a ResXResourceReader (the easiest way), is to give it the full path of your resx. It’s pretty simple :

Dim tResXPath = Path.Combine(Page.MapPath(Me.AppRelativeTemplateSourceDirectory), "App_LocalResources", Path.GetFileName(Request.Path) & ".resx")

For the page “/myfolder/help.aspx” :

Me.AppRelativeTemplateSourceDirectory = "~/myfolder/"
Path.GetFileName(Request.Path) = "help.aspx"
tResXPath = "C:mypathtothecodemyfolderApp_LocalResourceshelp.aspx.resx"

From this path, you create a ResXResourceReader, and just iterate through the key/value pairs to make what you want and you’re all set.


Public Class PageWithTranslations
  Inherits Page

  Protected Overrides Sub OnPreRender(e As System.EventArgs)
    MyBase.OnPreRender(e)

    Dim tResXPath = Path.Combine(Page.MapPath(Me.AppRelativeTemplateSourceDirectory), "App_LocalResources", Path.GetFileName(Request.Path) & ".resx")

    ' we ensure that the resx file exists
    If File.Exists(tResXPath) Then

      ' create the reader that will iterate through the key/value pair
      Dim tResXReader As New ResXResourceReader(tResXPath)

      ' to avoid to have the serializer to write '{ "Key: "toto", Value: "val" }', we put all in a dictionary to be serialized as '{ "toto": "val" }'
      Dim tEntries As New Dictionary(Of String, String)
      For Each tEntry As DictionaryEntry In tResXReader
        tEntries(tEntry.Key) = tEntry.Value ' edit: for multilanguages: = GetLocalResourceObject(tEntry.Value)
      Next

      ' use the js serializer to format it as a javascript array
      Dim tJavaScriptSerializer As New Script.Serialization.JavaScriptSerializer
      Dim s = tJavaScriptSerializer.Serialize(tEntries)

      ' insert the translations into a var _LanguageStrings js side
      Dim tScript = "var _LanguageStrings = " & s & ";"
      Dim tLiteral as New LiteralControl With { .Text = "<script type=""text/javascript"">" & tScript & "</script>"

      ' add it to the header of the page
      Me.Page.Header.Controls.Add(tLiteral)
    End If
  End Sub

End Class

To easily use the generated array in your code, use something like :

function _(k){return _LanguageStrings[k]?_LanguageStrings[k]:k;}

This way, if your ask for

item.text(_("mykey")); // returns "Welcome!" if it exists
item.text(_("mykeythatdoesntexist")) // returns "mykeythatdoesntexist", you won't have any errors, it will just return the name of the key.

Some limitations :
I didn’t test with other cultures than the neutral one. If I was french, I would need to open “help.aspx.fr.resx”.
By using CultureInfo.CurrentCulture I guess we could fix that. 😉
Edit: Actually, just do : tEntries(tEntry.Key) = GetLocalResourceObject(tEntry.Value). This way, it will automatically get the good value using the resource manager of the page.

Have fun !

SD

Copy table content between SQL Servers

To copy the content of a table using INSERT sql statements from a server to another, in SQL Server Management Studio, you can do this using just some clicks. (_don’t_ copy/paste using ctrl+c/v from the select * view, you could lose data in your fields)

First, you right click on the database your original table belongs to (well, they could have done a shortcut directly on the table !) :

generatescripts

Then you select the table you want to copy the content (tables, stored procedures, data types..) from :

fromtables

From here, you can save the script into a file, in several parts, clipboard or query window.. as you want. The important thing to do is to go to the advanced options :

advanced

Here, you can change if you want to only generate a script containing the schema of the table and/or its content. By defaut, only the schema sql will be generated.

advanced2

A few Next and here it is, you have your script.

By default, it enables the identity insertion because the INSERT contains the identity column.

SET IDENTITY_INSERT [dbo].[Sample] ON
GO

and disables it back at the end of the INSERTs

SET IDENTITY_INSERT [dbo].[Sample] OFF
GO

Have fun with it !

Mock up with Powerpoint Storyboard

To complete a previous post, there is now another solution to mock up application screen, and it’s a popular one : Microsoft Powerpoint Storyboarding. You have this application if you have installed Visual Studio 2012 and Powerpoint 2007+ (here, i’ll use 2013).

Here is the toolbox you have access to :
Toolbox

 

You can set the global style of your powerpoint workspace using one of this background, for instance let’s do a window app and drop some controls into it :
test

It’s very easy to make a mock-up because you can use the full power of the Powerpoint interface for the move, drag, rotate, edit properties, align, group, z-index etc. (same behavior than the classic Powerpoint). Here is (almost) all the components you have access to :
items

 

As you can see, you can even mock up windows phone app :
phone

Have fun !

Url encoder/shortener

Nowadays, you can find a lot of url shortener on the web to shorten urls mainly. I needed something like that to create some kind of sharing link to a given resource that anonymous could access. But I had some constraints :

  • i give the tool an numeric id, it gives me an unique short string
  • i can set up the alphabet it is using in the result string
  • hide what could identify the resource in the system (name, id..)
  • prevent any user guesses (such as try to “increment” the shorten url to access other resources)
  • 1-1 relation between a resource and a link

For instance, the input would be “8745” (an id), the generated string would be : “u2sx9Am” and decode(encode(8745)) = 8745.

I’ve found a nice recipe in python written by Michael Fogleman, so I translated it in VB.NET (in C# first eheh). It’s not exactly the same than the original but most of the code is identical.

Basically, it works in 2 steps :

  • it encodes the ID by manipulating its bits
  • it changes the base of this result to a specified base using specified alphabet (for instance, using “mn6j2c4rv8bpygw95z7hsdaetxuk3fq” (the length is 31 so is the encoded base) (our numerical number uses alphabet “0123456789” base 10 :-))

The ‘enbasing’ is a pretty basic operation :

    Private Function _Enbase(ByVal x As Long) As String
        Dim n As Integer = Me.Alphabet.Length
        If x < n Then Return Me.Alphabet(x).ToString
        Return Me._Enbase(x  n) + Me.Alphabet(x Mod n)
    End Function

The interesting part is the bits manipulation. We could skip this step and just ‘enbase’ the resource id, but if we do that, it’s easy to guess all the sharing links by incrementing them (if the resource id are just something that increment one by one).

The program permits use to set how many bits are going to be manipulated. This manipulation should be rollback-able to find again the ID from the sharing link. Currently, it just reverse the sequence of the N last bits. So you unique string result is the combination of :

  • the [length-N] bits unchanged
  • OR (the bitwise operation)
  • the [N] last bits reversed

You could implement something else like hard code bits position changes (bit position 4 goes to position 6, position 2 goes to position 1, etc.) and do the inverse to find the original id. I guess there are tons of options to think about.

Hope this will be helpful.