Friday, April 29, 2016

The data flow is a critical part of any project that handles data as part of its main functionality. Even when this is not true, the correct flow of data is an important goal that always takes, at least, some effort.

With that in mind, I'll review now a tool to do just that: export data to various formats. The tool is Spire.DataExport for .NET. Some of the features of this tool are:

This article will be both a walk through and a review. For that, I'll create a simple example that shows the real usage of the tool. Since XML is the format I worked with in many projects, I'll show the feature to export to XML.

Of course, you can use different data sources. These requirements are specific for this example.Steps taken:

Create a new project

1. I created a simple Windows Form Application. I named it "SpireDataExportTest":

2. Add a DataGridView component and a button. It should look like this:
AS you can see, I modified the text of the button. You can do other UI modifications as well if you think you need to.

3. Add the Spire.DataExport reference.
3.1 Right click References > Add Reference...
3.2 Click Browse and go to the path where you installed Spire.DataExport. The default path is C:\Program Files (x86)\e-iceblue\Spire.DataExport\Bin\NET4.0, in the case you have .NET Framework 4 (you might have to check your .NET Framework version. You can consult this if you have doubts).
Now, click OK.

4. Connect the grid to the database
There are several steps you need to take for this. Although it's not too complicated, I won't list all the steps here because the goal of the article is to do a review of the Spire.DataExport component. You might need a more detailed explanation, so I highly recommend to read this article.

However, this is the code you should have in Form1.cs up to this point:

And this is the database I used:
I called "testdb". Take into account that the Connection String in the code depends on this database and its configuration. Let's move on.Code the action of exporting to XML

1. Double click the "Export to XML" button
2. Inside the method "button1_Click", enter the the code to export to xml. It should look like this:

As you can see, the method names are very straightforward. The only detail that you have to take care of is that xmlExport.DataTable only receives DataTable. So, what I did was declaring DataSet ds = new DataSet(); at the class level and then I converted the first table of the data set to DataTable.

The last thing left is to test the code. So, click Start and then click "Export to XML" once the program is running. You should see the data in XML:

Conclusions

The most important feature of this particular Spire product is that it's very simple to understand, learn and use. In fact, when writing this article, it was far harder to connect the database to the data grid than adding the functionality to export to XML. Another point is that the methods have clear names. This is very important, and many developers tend to forget that detail.

I really enjoy using these products, and I look forward to try the next one. More information about this tool and other similar products here.

Saturday, April 16, 2016

The Common Table Expressions are a type of SQL query that allows us to write herarquical queries in various relational database management system that include SQL Server 2014. CTEs are very useful and, above all, are easy to understand, which is one of the main problems when learning SQL and reading the huge queries commonly used in software projects.

As usual, it's easier to understand everything with an example. What we will need here is:

1. Open SSMS (SQL Server Server Management Studio)
2. Right click Databases > Restore Database...
3. Select Device
4. Click the "..." button to browse for the .bak file corresponding to the Adventure Works database. Then click Add
5. Browse to and select the Adventure Works database.
6. Click OK 3 times. Wait a little and you should see the success message. Click OK.Create CTE:

Now that you have the Adventure Works 2014 in SSMS, Let's construct a query to satisfy the following instruction:

Write a query that returns all the BusinessEntityIDs of the employees whose job is "Production Technician - WC50" and are single.

Before someone mentions it, I know very well that this instruction is very simple and can be completed with a simple query. But for example purposes, we are going to do it with a CTE.

A CTE is a piece of code that "creates" a "virtual table" from where we can query data. IN this example, I'll create a query that returns all the employees with a marital status of "S" (single). This is easy enough:

Let's see this part by part:WITH CTE_married_employees(businessID, job)

Here, we declare the CTE and give names to the result columns of the query. Afterwards, if you want to query this CTE, you'll have to do it using the these column names. Remember, these don't need to be the same as the original columns, so you can name them at you're convenience. Another detail is that you can use any name, you don't need to add the "CTE".

Here, you just put whatever expression you want. Now, all you have to do is to consult the CTE with a regular query. The complete script is as follows:WITH CTE_married_employees(businessID, job)AS ( SELECT BusinessEntityID, JobTitle FROM HumanResources.Employee WHERE MaritalStatus = 'S')SELECT businessID from CTE_married_employeeswhere job = 'Production Technician - WC50'

You have to execute the whole script to get it work. The result should be:

The use of CTEs makes it very easy to create complex queries without sacrificing the legibility of the script. Also, if you just test something that requires you to read the databases, it's very likely that you'll see CTEs, and you'll be able to understand the simple but not always obvious syntax.

Wednesday, April 13, 2016

Indubitably, xpath is one of the most used ways to create locators when automating (often used over Selenium). It's relatively easy to understand and it has enough flexibility for almost every scenario to test.

Of course, there are some times when you have to learn everything when the project is already in progress, so you might have a couple of holes in your knowledge about this. So, for those of you who didn't know about this, I'm gonna explain what are the xpath axes about.

Basically, the axes are keywords that select nodes relative to other nodes. This will be more understandable with an example. So, this is the scenario:

We need to select my name (Jorge Maguiña) from the panel below "Contributors":
But we need to do it in function of the "Contributors" title. This situation can come up when there are repeated ids, or the same element is displayed in several parts of the screen. Selecting an element relatively to another also gives more accuracy to the element. In some cases, it can prevent the locator from becoming obsolete when there's a minor change in the page. So, how do we do it? Keep reading and find out.

1. We'll work with a section of this page, so you can open a new tab or work directly here.
2. Right click in any blank space > Inspect in FirePath
3. Click the "Select" (not the actual name) tool at the upper right part of the FirePath panel
4. Click the "Contributors" title. You should see the HTML code for that element:
5. Expand all the nodes at the same level than the selected title:
Typically, we would go down to the nested nodes to reach my name, but in this case we have two ways of getting to the desired element:
a) Going backwards with "../". As in a command line, xpath allows us to go back in the HTML tree with this. For this particular case:

a1. In the "Xpath" text area, enter this: //h2[@class='title']
That means that we are selecting all the h2 nodes that has the class "title".

a2. Now, we add the syntax to go back and select my name: //h2[@class='title']/../div/a/span[contains(text(), 'Jorge Maguiña')]
That means: Select all the h2 nodes that has the class "title", go back one level, select a div, a, span, which contains the text "Jorge Maguiña"

The "following-sibling::" part means: select all the following nodes at the same level. The following sibling nodes for "Contributors" are marked in the image below:
So, when you use the xpath, you'll be able to select the name relative to the "Contributors" title:
This is meant to be a basic example, but actually the use of the other axes is very similar. You can see the complete list of axes and their descriptions here.