SQL Row_Number OVER and PARTITION BY

Posted by & filed under SQL Server.

Below is something I’ve been using for a while now but is very useful code when you have a table containing multiple status records for a single object row (to use one example).

The code below shows that you can create a numbering system (partioned by objectID) for each status record. You can then use this numbering system to select, for example, the top row and therefore get back the latest status record (and any associated details). Nothing new, but handy 🙂

SELECT
ObjectID,
ObjectName,
Status
FROM
(
SELECT
o.ObjectID,
o.ObjectName,
,os.Status
, (ROW_NUMBER() OVER (PARTITION BY o.ObjectID ORDER BY os.CreationDate)) AS Row
FROM
Object o
JOIN ObjectStatus os ON o.ObjectID = os.ObjectID
)
itm
WHERE Row =1

Command Line Firewall Automation Script (and Service)

Posted by & filed under Uncategorized.

A handy command I’ve found, I’ve often used in scripts updates the windows advanced firewall…

netsh advfirewall firewall add rule name="Home PC" dir=in action=allow protocol=TCP remoteip=123.123.123.123

A problem I had was that I’d secured a hosted server by limiting Remote Desktop protocol down to my work IP Address Range. I also needed to get to this server from home, but as BT don’t offer static IP Addresses to home customers, I’d have a problem with constantly having to update the IP address within Windows Firewall.

Handly, a feature of the BT Home Hub (the router that BT supply) is that it can update a dynamic dns address if you give it your login details to services such as noIP, DynDNS etc. This gave me an idea and I created a windows service that would get the IP address from a given host and update the windows firewall with the new IP address.

Here’s the c# code I used, hope it’s useful.

FirewallService.zip (33 Kb)

Chris

Digital Distractions

Posted by & filed under Online Life.

I haven’t really been one for writing opinionated blog posts. As you can see from my feed, most are written purely as techie tips, coding examples or “how tos”. I found an article tonight however that got me thinking. It’s an article from the New York Times (full article) about the many distractions that young people face towards their studies – facebook, twitter, MSN being just a few. It seems that nowadays as a young person, if you’re not online then you’re not in touch with what’s going on.

Online Career Distractions

What got me thinking was how this transfers to adult life. I’ve always had a bit of an online presence. A bit geeky as a kid, I was one of the first in my school to have a 28.8kbps modem. I’d often spend lunchtimes on this new thing called “the internet” and a lot of the other kids couldn’t work out why. A little later along came tools such as MSN, MySpace and eventually the leviathan know as Facebook. Now many of the people who teased me for spending time online are sitting in front on the internet more than double the time I am.

There are many different ways to use these online tools. Obviously there’s the general chit-chat that is frowned upon by employers and many have installed strict firewalls and filtering software to stop this happening during work hours. There’s good reason for this – it can be hugely detrimental to a business if a large percentage of work hours is being spent simply chatting with mates. There’s also damage to a companies reputation if it becomes apparent that controversial opinions are aired or websites are viewed using company machines. Habits and addictions can also be developed or worsened such as online gambling.

Are digital distractions always a bad thing?

Many IT companies are a bit conflicted when it comes to social networking. A large UK based company that I previously worked for had very strict filtering rules until it was realised that many of the sites that they were talking about in meetings and using as case studies were actually blocked!

My current employers try to embrace online technology. I guess being a much smaller company, it’s much easier to manage and keep an eye on what everyone is up to. Any issues would be much easier to spot.

I believe in this case Social media, Blogs, News Feeds are a huge benefit. The core of my job is development and architecture but I get involved in a whole lot more. I enjoy this type of work, it gives me a great perspective on how products are used and integrated and it makes me a great technology “all rounder”. It also means that I don’t just get stuck behind a screen all the time, but I get to go out and meet clients, do installations and consultation work. With this type of role you HAVE to keep up with what’s going on in the technology world, what new methods of doing things are on the horizon and how people have coped with problems in the past. My google reader account has a few choice blogs which I read religiously to keep view on what’s going on and what the technology big players are up to. People such as Scott Guthrie, Ed Bott and Scott Hanselman are great resources for this.

I find that meeting up with clients, especially those with a technology interest (as most IT related people are) it’s great to have some general technology news to discuss. There’s lots of big technology news sites that are great for this – Lifehacker, Mashable (Social Media), TechCrunch, Gizmodo, Engadget to name a few.  Don’t get me wrong, I have a huge interest in technology and would be reading these sites regardless, but it’s great to have something to chat to clients or prospective clients about and find a common interest. Some of these sites post into Facebook meaning that even when I am checking on my friends I can see if an interesting post has made it’s way into my feed.

Lastly I find that tweeting on various subjects enables you to create a network of contacts with a common interest. It’s amazing how many times you’re able to tweet a quick question or problem and get a response from someone within a very short period of time. An invaluable resource.

Online Presence

Many recruitment agencies are now talking about “Building your personal online brand”. Many bosses will at least google a potential candidate’s name to get an idea of who that person is. Most people will have some kind of presence on the internet nowadays, so the question you need to be asking yourself is “does what’s visible about me on the internet give a good impression of who I am?”.

Linked-in can also be a great way of getting a professional profile online. Think of it as a digital CV, but a whole lot more including building a network of fellow professionals and giving and receiving recommendations on your work. Linked-in profiles are also heavily indexed by google meaning that you have a good chance of yours appearing in the results if a prospective employer googles your name.

The Future for Social Media and Blogging in the Workplace

The use of Social Media and Blogging in the workplace isn’t for everyone. It depends greatly on the type of workplace, the number of staff their ages and positions, industry sector etc. It can be a great resource but needs to be used correctly and with care.

nHibernate Replace Column Value

Posted by & filed under nHibernate, SQL Server.

As I’ve been learning nHibernate there have been certain queries that I’ve been struggling to replicate in nHibernate and google has not been my friend. One of those queries was trying to replicate the replace sql function so that I could strip out spaces from a postcode for comparison search, but after searching and tweaking I finally found the answer. Hope this helps someone…

// Create ISQLFunction object to define the replace function
ISQLFunction sqlreplace = new VarArgsSQLFunction("replace(", ",",")");

// Run the query containing the ISQLFunction instance
customerList = session.CreateCriteria(typeof(Customer))
.CreateCriteria("Address")
.Add(Expression.Eq(
Projections.SqlFunction(sqlreplace,
NHibernateUtil.String,
Projections.Property("Postcode"),
Projections.Constant(""),
Projections.Constant(" "))
, PostCodeTexBox.Text.Replace(" ", "")))
.List();