Thursday, April 17, 2014

Accessing Databases with R (RStudio)

R has libraries for accessing several databases, but if you're on Windows, you'd probably prefer the RODBC package; with this, you can access almost any db through ODBC. You can create a DSN and use odbcConnect, or use odbcDriverconnect ; the problem is figuring out the connection string, especially since you need to specify the driver. For SQL Server, this works (assuming db is in localhost):

c=odbcDriverConnect('Driver=SQL Server;Server=localhost;Database=......;Trusted_Connection=True')

And then you can do odbcquery like:
res=sqlQuery(c,"SELECT * FROM ....")

And you get a data frame ! has odbc connection strings for many dbs

Wednesday, April 2, 2014

Super quick Linq to XML

I was trying to figure out how to get started with Linq to XML, and found this article. I figured I'd simplify it even more for myself :)
Imagine we have the following file, saved in c:\contacts.xml

Then we can access it as follows

  • on an XDocument, we can call descendants and pass it an element name, to get all the elements with that name (we can also call the no-args version, and get all descendants)
  • What we're getting is an XElement, on which we can call methods like Element or Attribute
  • We can directly cast an element into a string, and it gets us all the text inside that element
  • We can cast an attribute as a string, or an int (or even a float, double or many other types)

Thursday, March 6, 2014

C# is pretty expresive too

I just saw this post , about how to add methods to types in F# (and referencing another in rust), so you can make words like Months and Ago have meaning and allow you to say things like '3 years ago'. Since C# now has extension methods, I figured I'd try to see how it could be done in C#

So, what do you think ?

BTW, I tested this in LinqPad, may need something extra to become a full program

Friday, January 31, 2014

Personal mongodb Cheatsheet

I'm trying to learn more mongodb (taking one of their free courses), so I figured I'll keep here the things that I use often (and maybe it will be useful to others).


You can use the print method to print in the shell print('hello')

Iterating over a cursor

The value returned by find and findOne is not a collection but a 'cursor', so you can't just do 
for(var obj in
you need to do
while(cursor.hasNext()) {;

or define a function and use forEach()

Saturday, December 14, 2013

Azure for hosting HTML files

I'll be teaching a friend some HTML, and was looking for an easy, free host for playing sites; Azure has free websites, but I was afraid I'd need to teach them webmatrix of VS express (nothing against them, but it is one more thing to learn). I just discovered Azure can deploy sites from a git repo !! yipee !

Sunday, November 24, 2013

XML queries in MS SQL Server

Recently I needed to extract some data that was stored as XML in a SQL Server 2012 db; I found the documentation lacking, mainly because you need to know both MS proprietary SQL extensions and XPath, to query the xml. Here's what I figured; it may not be optimal, but it works :) (BTW, I think Server 2008 has the same XML methods, but I only tested with 2012).

The xml type

SQL server supports an XML datatype; we use singlle-quotes to denote its values, but it is not quite a varchar type. Below we define a table Person, with id, name and an XML field called SkillsXML; we insert two rows. The xml looks like <skills>  <skill level="High"> SQL</skill> ... </skills>.

Simple queries

With this, we can use the query method of the xml datatype to select only certain nodes; for example, the following query would select only the first skill for each person. Notice we're using an XPath expression for selecting here; simple XPath expressions look like folders; here we're selecting from the root of the document, the skills node, and from there the first skill node (notice XPath starts counting at 1, not 0). Notice the value returned is of type xml, not a varchar, which is fine to just look at it, but you may want to cast it as a varchar to use it in other places.

Getting the value of an attribute

We can also use the value method, which allows us to get the value of an XML attribute; this method takes 2 arguments, an XPath expression and a string with a SQL datatype; the value of the XPath expression will be cast (or converted) to that type. The following query gets the value of the level attribute of the first skill.

Nodes queries

The most interesting queries use the nodes xml operator; with this, and cross apply (or outer apply) we can get one row per xml element; for example, the following query gets us each person with their skills; since each of our person rows has 2 skills, we will get 4 rows total. We can make this much nicer by using a WITH expression, selecting both the skill name (which is text inside the element and its level attribute, and casting each of them as a varchar; the following example selects only those skills with Medium level.

To Do

I will post examples using XML namespaces, and will add links to other references soon.

Saturday, November 23, 2013

Web api and Dapper for super-simple REST APIs

Recently c# got the dynamic keyword, which allows for truly dynamic typing (like in Ruby, Python or Javascript), and allow you to define objects to which you can add fields at runtime. In fact, we now have ExpandoObject in the standard library.

An obvious place to take advantage of that is with communicating to databases; Most times, we're still using a relational database, and use SQL to communicate with it. SQL is dynamically typed (at least in the sense that your types are coming from a database, independently of your program), and translating it to our objects usually involves lots of gruntwork; it would be nice if the language helps with that gruntwork; with dynamic and ExpandoObject being available, there are now a few micro-frameworks for DB access; one of the simplest is Dapper ; it just adds a few extension methods to the SqlConnection class, and can either automatically put data into our classes, or into dynamic objects (which is what we'll do).

The new-ish web api in automatically generates REST APIs with very little code; with that, and Dapper, we can give access to our database in very little time; for example, if we have a database with one table, called People, with two fields, id, and name; we can generate a controller with code as simple as:

Of course, eventually you may want to use a more complete ORM, like EntityFramework, but for quick prototyping, it doesn't get any simpler than this.