Get the content of a Sharepoint list as XML

Today we will talk about a feature built-in in Sharepoint, and it’s the ability to show the content of the lists in XML as if that would be a web service.

This can helping us in order to retrieve that information and do whatever we can image with it programatically.

What we need for this? the answer is very simple, just 2 small things.

Get the List Id
The first thing that you will need is the list id.
To get this information, open the Sharepoint list in a browser as usual, then click Settings -> List Settings.
Then in the url you will see the list id, you will getsomething like this.

http://sharepoint01/Direct/sites/WEB/SSPS/_layouts/listedit.aspx?List=%7B6E9CA676%2DEBC2%2D4F1C%2D8599%2D944FFE7FC0D9%7D

Let’s clean up this a little bit, replace the following
%7B” for “{”
%7D” for “}”
“%2D” for “-”

Now we have the list id. In our case is: {6E9CA676-EBC2-4F1C-8599-944FFE7FC0D9}

URL to get the XML.

Append to the list id to the following URL:

http://sharepoint01/Direct/sites/WEB/SSPS/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=true&List=%7B6E9CA676-EBC2-4F1C-8599-944FFE7FC0D9%7D

Once you have the URL ready just paste it in the browser and voila, you will the XML as a response.

If you see the URL is the same until _vti_bin/owssvr.dll, the previous portion of the URL is the same as your Sharepoint List is.

Advantages:

  • XML is an standard widely adopted and everybody can handle.
  • You can still using Sharepoint as your information center, now you are able to get that info in case you need to do something with it.
  • As the response is in XML and this is not dependent on any programming language; you can use it from any language (.Net, Powershell, Java, Python, Ruby, etc)
  • Is already built-in in your Sharepoint.

Hope this help you.

Enjot it!

Chris

Advertisements

Sending emails using Powershell

Here is a quick example how we can send an email using Powershell.
Currently at my work I need to perform some repetitive tasks and I was thinking how we can automate the process, so I decided to check on scripting language.

Advantages

  • It’s preinstalled in Windows
  • No set up
  • No compilation or build process, hence, no recompile for any change.
  • Creating scripts for easy modifications

Basically it’s a simple routine of sending emails, very similar to C#, so if you have any knowledge on this .Net MS technology it can be easier for you.

function SendEmail($recipient, $subject, $body)
{
    write-host "Sending email"
    $smtpServer = "exchange.myorg.com"

    # Creating a Mail Object
    $msg = New-Object Net.Mail.MailMessage

    # Creating SMTP server
    $smtp = New-Object Net.Mail.SmtpClient($smtpServer)

    # Email structure
    $msg.From = "powershell@myorg.com"
    $msg.To.Add($recipient)
    $msg.Subject = $subject
    $msg.body = $body
    $msg.IsBodyHTML = $true

    # Send
    $smtp.Send($msg)
}

So, the next thing that we need to do is just call the function and pass the parameters. Something like this:

SendEmail 'test@myorg.com' 'Email test' 'Hi,
 this is an automatic email please do not reply
 Thanks'

In case you want to do it in a schedule (like remainders) you can simply create a schedule task in Windows and save the above code in a powershell file (extension .ps1).
In the task you may need to especify how to run that file: Powershell D:/myAutomaticEmail.ps1

[Update] You can use the cmdlet ‘Send-MailMessage’ already built-in powershell.

Send-MailMessage -SmtpServer exchange.myorg.com -From "powershell@myorg.com" -To "myemail@myorg.com","anotheremail@myorg.com" -Subject "Email test" -Body "Hi,
 this is an automatic email please do not reply
 Thanks" -BodyAsHtml

See the complete definition here

Hope this helps. Enjoy it!

Chris

How to create/use Gmail aliases

This is a nice feature that Gmail has and no so much people is aware of. With Gmail you can create unlimited aliases with your email, and it is automatically supported you don’t need to do anything, so in that case you can have like multiple accounts and that feature can help you to manage in a better way your email.

Let’s see how to use it.

Suppose that you have a an account like myemail@gmail.com then you receives a ton of emails in it.
Now you can use different alias for different sources, so in case that you are subscribed to a group or newsletter you can sign in that group with an alias that will point to your email account.

In case for example that you are in a group for work, sport team or another you can set it up the email as follows:
Twitter : myemail+twitter@gmail.com
SportTeam : myemail+team@gmail.com
News : myemail+news@gmail.com

Rules

  • It should have the same username.
  • It should ends with @gmail.com
  • You should use “+” character to append the alias that you want.

After that, you will receive in the same inbox myemail@gmail.com all the email but you can create the appropriate rules to manage/move/delete the emails, another interesting thing is whether you see a lot of spam in a particular alias you can immediately know which account causes that ūüėČ

Enjoy it and use it!

Chris

Querying XML columns in SQL

For some reasons you may need store information in your database as XML format, SQL offers this data-type and even when this is a native data-type and offers advanced data handling, it has some differences and constraints at the time that you want to know/see/query your information. With this kinf of columns you cannot perform the same “normal” queries for the other relation data.

So let’s examine which options we have to see what’s in our database.

Here is an example of our table that contains XML column:

CREATE TABLE ProductReviews (
ProductId INT,
Reviews XML
)

Now that we have the table we can add some information to it. Run the following queries

INSERT INTO ProductReviews
VALUES (1,
'<Product>
<ProductId>1</ProductId>
<Reviews>
<Review>
<CustomerId>1</CustomerId>
<Desc>This is awesome</Desc>
</Review>
</Reviews>
</Product>')

INSERT INTO ProductReviews
VALUES (2,
'<Product>
<ProductId>2</ProductId>
<Reviews>
<Review>
<CustomerId>2</CustomerId>
<Desc>Dont purchase this product, is waste of money</Desc>
</Review>
</Reviews>
</Product>')

INSERT INTO ProductReviews
VALUES (3,
'<Product>
<ProductId>3</ProductId>
<Reviews>
<Review>
<CustomerId>1</CustomerId>
<Desc>This product is ok, 4 out of 5</Desc>
</Review>
<Review>
<CustomerId>2</CustomerId>
<Desc>Best product ever</Desc>
</Review>
<Review>
<CustomerId>3</CustomerId>
<Desc>The quality of this product is amazing but can offer more</Desc>
</Review>
</Reviews>
</Product>')

AS you can see we have three rows, if you get all the content for that table, you can get the next results:

SELECT * FROM ProductReviews

Let’s start with XQuery.

One of the most simple approach to know what is stored in that field is the value function in which only we need specify the hierarchic path to get the desired value. This function receives two parameters, one is the path and the other is the data-type.

SELECT
PR.ProductId
,PR.Reviews.value('(/Product/Reviews/Review/CustomerId)[1]','int') AS CustomerId
,PR.Reviews.value('(/Product/Reviews/Review/Desc)[1]','varchar(255)') AS Review
FROM ProductReviews PR

Here is the result:

use of value function

Another function is query where you can see in XML result what is inside of the current XML or in a specified path. This receives one parameter that is the xml hierarchic¬†path, if you want to see the subsequent nodes from the root node you can write (‘/./’)

SELECT
PR.ProductId
,PR.Reviews.query('(/Product/Reviews/Review/CustomerId)') AS CustomerId
,PR.Reviews.query('/Product') AS Review
FROM ProductReviews PR

Here is the result:

use of XML query function in SQL

Use of exist function. It will evaluate a path (similar to query() and it will return true in case it exist. The format is the following:

SELECT
PR.ProductId
,PR.Reviews.query('(/Product/Reviews/Review/CustomerId)') AS CustomerId
,PR.Reviews.query('/Product') AS Review
FROM ProductReviews PR
WHERE PR.Reviews.exist('(/Product/Reviews/Review/CustomerId)[2]') = 1

Here is the result:

use of XML Exist function in SQL Server

What to do with multiple child nodes?

This is a good question, so far we have seen  how to get unique values or one value per row, one of the reasons to store information as XML is because you can store multiple values related to one item as is the case of the row number three in this example where it contains for one product two reviews.

Using “CROSS APPLY operator and nodes function you can resolve this mess, this will allow you to create a “JOIN with every child node and get the information as an¬†independent¬†row. Let’s see one example.

Suppose you want to display all the reviews, but the reviews should be in a different rows even or not are related to the same product, the query should be something like this:

SELECT
PR.ProductId
,Review.value('CustomerId[1]','int') AS CustomerId
,Review.value('Desc[1]','varchar(255)') AS Review
FROM ProductReviews PR
CROSS APPLY Reviews.nodes('(/Product/Reviews/Review)') AS R(Review)

Here you go the result:

use of Cross Apply with nodes to iterate to all child nodes in XML SQL Server

Now you have learned how to take advantage of the XML columns and write some queries with this kind of column, based on that I hope that from now work with XML columns in SQL Server will be more fun than annoying.

Enjoy it!

Chris

Data Driven Test in MSTest

Here we will see how to run our test based in data and that is stored somewhere, also an interesting thing is that we can run a test in iterations, based on a set of data.

Problem.

You need to test your data that is stored in some place.
You need to run the same test across different test values, iterations.

Solution

Data Driven testing, it will allow us specify any (or almost any) data source (e.g. database, excel, xml, whatever supported by MS) and run our test cases based on dynamic/stored information.

Also we can specify a set of test, let’s image that we have a test method called Sum() that basically will have one number and sum another one, so we can define a source of data that contain multiples combinations and then just run the test and it will run one test for every row.

How it works?

DataSource: it specifies where and how to pull the information to work with.

You can see in the example below that it will read an XML file (called numbers.xml) that is included as a part of the test (we declare “DeploymentItem” to make sure that this resource will be available in the test output.

[TestMethod]
[DeploymentItem("numbers.xml")]
[DataSource("Microsoft.VisualStudio.TestTools.DataSource.XML", "|DataDirectory|\\numbers.xml", "StyleInfo", DataAccessMethod.Sequential)]
public void Sum()
{
    double num1 = (double)TestContext.DataRow["NumberOne"];
    double num2 = (double)TestContext.DataRow["NumberTwo"];
    double result = num1 + num2;
    Assert.IsNotNull(result);
}

Suppose you have the following XML:

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfNumbers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Numbers>
    <NumberOne>1</NumberOne>
    <NumberTwo>2</NumberTwo>
  </Numbers>
  <Numbers>
    <NumberOne>2.1</NumberOne>
    <NumberTwo>3.9</NumberTwo>
  </Numbers>
  <Numbers>
    <NumberOne>-29</NumberOne>
    <NumberTwo>21</NumberTwo>
  </Numbers>
</ArrayOfNumbers>

Now if you run the test and everything goes fine, you will see that the test was complete sucessfully.

Execute the test and see the test results.

And going forward you can double click on that result and it will show you the detail per DataRow (every single row in you test will be treated as an independent unit test).

Detail of the test results

Enjoy it!

Chris

WatiN and how to create automated smoke testing

Hi,

I just found this amazing test framework that I want to write about it.

How many times are in the position that you wrote your code and the unit test for it and when you are ready to deploy you need to be prepared to do some smoke testing (basic or advanced that depends on the coverage that you want to do) in your application.

You can find that even when your unit tests can prove that nothing is wrong with some pieces of code, for the final user that kind of testing does not make a lot of sense, basically you will need to do some smoke test called functional test to corroborate that everything is fine.

Because most of the time you may find this task repetitive and boring, e.g. go to home page of your site, navigate to the site click on some links, for example sign in, type your credentials, click enter, then change something in your account page (like change address or name or email). And can be every time that you deploy something.

Even when you are not touching anything else you may need to do a smoke test though.

Here is where WatiN is come to the rescue. From code (thanks for that) you can define a set of testing using the real application in a web browser and not some code that resides in the server.

Advantages

  • Easy to integrate with you favorite unit test framework
  • Supports AJAX
  • Works with IE and Firefox
  • Cross language in .Net
  • Open Source

Step by step

Ok, in this example I will create a test project from scratch and we will have fun of a commercial website, let’s say bestbuy.com.

First of all, is pretty easy just go to Visual Studio 2010 and then create a new test project in this case we are selecting MS Test as our testing framework.

Create a unit test project in visual studio

Once the project is create go to class and go to TestMethod1. Change the name of the method for something else more representative like Shopping.

Let’s start with WatiN, yay!

Using NuGet, add a reference to WatiN.

Do a right click in the solution explorer and the manage NuGet packages, search for WatiN and the install it.

Add WatiN from NuGet package

Once completed you can see the new reference added to WatiN.Core

Important: Change the “Embeded Interop Type” to False in the properties of the Interop.SHDocVw reference just added, otherwise it won’t work.

Go to the bestbuy site and use the developer tools or firebug or another preferred tool to get the name, selectors and id of the HTML objects that you want to interact.

In the image below you can see that the name and id of the text for search in the website is “st“, and for the button the id is “sb

Developer Tools

Once you get all the elements that you need you can write your unit test using WatiN. Insert the code below:

[TestMethod]
public void Shopping()
{
    using (IE browser = new IE("http://www.bestbuy.com/"))
    {
        browser.TextField(Find.ById("st")).TypeText("laptops");
        browser.Button(Find.ById("sb")).Click();

        browser.Link(Find.ByName("&amp;lid=newmacbook")).Click();
        browser.Link(Find.ByName("&amp;lid=PM_65123")).Click();
        browser.Link(Find.ByName("&amp;lid=65123_apple_shop")).Click();
        browser.Image(Find.ByName("addToCart")).Click();
        browser.WaitUntilContainsText("ADDED TO CART");

        Assert.IsTrue(browser.ContainsText("You have added this item to your cart"));
    }
}

As you can see in the code above you are creating a new IE browser, then you are writing each action to do in the page specified as if you were interacting with the page.

At the end just do an Assert and validate that anything you desire is there in the page (like values, images, objects, elements), any kind of validation you can perform on it.

See the magic running the test, you will see your browser poping out and do all the test for you ūüôā

IE atomated testing

References

WatiN official website

Enjoy it!

Chris

How to iterate in all the tables of a SQL database.

Hi, I want to share with you one stored procedure non document by MS that give us the giant possibility to iterate in all the tables contained in one of our databases with one word!

Usually when you need to check or print all the tables in your database you need to create a cursor and iterate within all the elements contained in information_schema.tables or in sys.objects system tables.

Now you can use the sp_MSforeachtable to do so.

That stored procedure allows you iterate through all the tables in the current database. That stored procedure receives one parameter that is an executable query, like this:

EXEC sp_MSforeachtable "SELECT count(*) from ?"

Another approach is use this stored procedure to get the statistics for every table that you have, to do that you need to run the following query:

EXEC sp_MSforeachtable "sp_spaceused ?"

Also you can update the statistics for all the tables in one query.

EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

 

I hope you can find this helpful and you save some time creating and typing queries.

Bonus: You may interested in the stored procedure sp_MSForeachdb with a similar approach.

Enjoy it!

Chris

Windows 8 comes just in two versions.

Microsoft finally has been announced the versions that will be available to the customers about their new OS insignia of the company, Windows 8.

The two version available announced will be:

  • Windows 8 (preinstalled in all OEM computers).
  • Windows 8 Pro.

This makes the things more easy when you are looking for a new computer, no need to compare all the versions to see all the features and the different prices as well, that was a pure nightmare which everybody prefers to skip. So, no more Starter, Home Basic, Home Premium, Enterprise, Professional and Ultimate :).

With two versions available there is no much decision about which version do you need in your computer, if the “basic” version meets with your requirements you don’t need to worry about all the other features that maybe you won’t use , and just let the advanced version for professionals that need those features, sometimes most of the advanced features are not used even for the advanced users. Take into account that if you want to try the Pro version, you will spend some extra bucks, but if you want it go ahead and try it yourself.

This remember me to the times of XP (for so many people is the best OS that the company has created) with their Home and Professional versions, those days were very easy to know and decide  decide what version was the right for me.

Hopefully MS is learning from all their mistakes in the past (or at least a bunch of them) and focus in offer/deliver a competitive and reliable good products.

Chris

[# TFS11] TFS Express, now for free! and integrated with Visual Studio Express!

Sharing an interesting post from elBruno, now we can use for TFS for FREE, even for testing purposes or for small development teams.

[# TFS11] TFS Express, now for free! and integrated with Visual Studio Express!.

Enjoy it!

Chris

Detect CapsLock with Javascript (client side)

Here we will see how to know when CapsLock is pressed in our web page with JavaScript.

Suppose that we have a input area in our web page and you need to inform (warning) to the user if the CapsLock is On or Off.

Step by Step:

  • Creating our Web Page:

Here we have a simple HTML page:

<html>
<head>

   <title>Checking for CAPS LOCK</title>

</head>
<body>

   <div align="center">
   <table width="100%" border=0 cellspacing=0 cellpadding=0>
      <tr>
         <td width="100%" align="center" valign="top">
            <strong>Password:</strong>
            <input id="item" type="Password" name="Password" size=16 maxlength=16 />
         </td>
      </tr>
   </table>

</body>
</html>
  • Add our message that will be displayed in case that Caps Lock is on.

<div id="divWarningCapsLock" style="visibility:hidden">Caps Lock is on.</div>

So, to show the message to the user when Caps Lock is on, we need that the user enter/press any letter or key in their keyboard, otherwise we cannot know whether the Caps Lock is on or not. In order to do that we need to add the onKeyPress event to the input area and check the key pressed by the user.

  • Add the onKeyPress event:

<input id="item" type="Password" name="Password" size=16 maxlength=16 onKeyPress="checkCapsLock( event )">

As you can notice we are calling the JavaScript function named checkCapsLock() that receive one parameter. Next thing to do is, obviously, add our script to the page.

Add the JavaScript into our page between the head section:

<script language="JavaScript">

   //Check the keyboard in order to know wich key has been pressed by the user
   function checkCapsLock( e ) {
      var capsLockON;
      keyCode = e.keyCode?e.keyCode:e.which;
      shiftKey = e.shiftKey?e.shiftKey:((keyCode == 16)?true:false);
      if(((keyCode >= 65 && keyCode <= 90) && !shiftKey)||((keyCode >= 97 && keyCode <= 122) && shiftKey)) {
          capsLockON = true;
      } else {
         capsLockON = false;
      }
      if (capsLockON)
         document.getElementById('divWarningCapsLock').style.visibility = 'visible';
      else
         document.getElementById('divWarningCapsLock').style.visibility = 'hidden';

   }
</script>
  • Test our web page

Is time to test and see our pretty basic web page, save the entire above code as HTML page and open it with your browser.

If you introduce something and then play with CapsLock and then retype you will see how the warning message appears/disappears upon Caps Lock key is turned On/Off.

Caps Lock On (then type something).

Caps Lock Off (then type something).

We can say that we finished our job ūüôā WOW!, but if you have a client very picky (as 98.99% of the clients) you maybe are in front of the next situation, the client wants that if the Caps Lock warning appears, itself disappear when Caps Locks is turned Off, not when you type the next letter.

Ok, challenge accepted!

Creating a hack for Caps Lock.

Basically the onKeyPress event cannot tell you when Caps Lock is pressed, simply you are not triggering this event when you press the Caps Lock key. However we can use another events like our best friend onKeyDown ūüôā this are the things in this scope.

  1. Store the state of Caps Lock in a variable.
  2. Call the onKeyDown event.
  3. Check if the Caps Lock key was pressed.
  4. Change the value of the Caps Lock variable.
  • Add the onKeyDown event

To the body change the following line as follows:

<body onKeyDown="changeCapsLock( event )">

Change the current JavaScript for the following:

<script language="JavaScript">
   <strong>var capsLockON;</strong>

   //Check the keyboard in order to know wich key has been pressed by the user
   function checkCapsLock( e ) {

      keyCode = e.keyCode?e.keyCode:e.which;
      shiftKey = e.shiftKey?e.shiftKey:((keyCode == 16)?true:false);
      if(((keyCode >= 65 && keyCode <= 90) && !shiftKey)||((keyCode >= 97 && keyCode <= 122) && shiftKey)) {
          capsLockON = true;
      } else {
         capsLockON = false;
      }
      <strong>displayMsg();</strong>

   }

   //HACK: In case that the users activate/deactivate CapsLock change the status
   //and display the correct status ONLY if the user already entered any value
   function changeCapsLock( e ) {
      //e.keyCode == 20 Caps Lock key
      if (typeof capsLockON != 'undefined' && e.keyCode == 20) {
         capsLockON = !capsLockON;
         displayMsg();
      }

   }

   //Display the message if CapsLocks is ON, otherwise conceal the message
   function displayMsg() {
      if (capsLockON)
         document.getElementById('divWarningCapsLock').style.visibility = 'visible';
      else
         document.getElementById('divWarningCapsLock').style.visibility = 'hidden';
   }
</script>

Now is time to see our final web page. Save all the changes in our page and open it in your favorite browser.

Remember that the user needs to enter first any key to know the initial state after that you are able to change the message accordingly!

Caps Lock off and type something:

Change the Caps Lock and automatically you will see how the message change

Enjoy it!

Chris

%d bloggers like this: