Dependent objects in SQL Server

another of those ‘bloody hell, why did I not know this before’ moments, but one of the lads circulated this during the week as a means of checking dependencies on either stored procs or tables.

A simple

exec sp_depends [object name]

Will return a set of results that highlight which stored procs, views, tables, user-defined functions or triggers are dependent upon that object.  The full MSDN documentation is available here.

So handy when there are schema changes in legacy code/schema’s.

Orphaned SQL Server Users

Been blogged about all over the place, but I wanted a central place to remember it.

After restore of a database from another server, often the user account can become unassigned from an SQL server login.

The following sorts it:

sp_change_users_login 'update_one', 'orphaned_login', 'sql_username'

jobs a good un.

Now I never need hunt again – huzzah 🙂

jQuery, Validation, and asp.net

Well, new job, new challenges, and finally my brain can switch off at the end of a day!

This past week or so I’ve been playing with a new registration process for a website, and decided to wherever possible depart from the path of least resistance (classic asp.net, validation, telerik controls, asp.net ajax etc.) and try to focus on the user experience that can be gained from using jquery and any associated plugins.

I plumped for hand rolling my own accordion as I needed more flexibility than that available from the standard plugins.  The area I’ve been most enlightened with though is the jquery.validation plugin.  I love the flexibility in the tool, the customisation, and the improvements it can bring to a form.

a simple:

$(‘input:text:not(.skip_auto_validation),input:password,select:not(.skip_auto_validation)’).blur(

function() {
        validate_field(this);
});

has allowed me to validate fields on loss of focus, and the method targets a number of elements on fail and highlights them.

Before Validation

image

Blur on username – fail

image

which incorporates a $.ajax call to an .ashx handler, and after the call has occured, the user either gets a nice slide down message or a nice indicator that everything is well.

Blur on username – success

image

 

The function that handles the validation is:

/// 
/// field by field validation – we only want to validate fields that are
/// either already validated or have previously succeeded/failed validation
/// and now have a different value
/// 
function validate_field(field) {
    var prev_icon = $(field).prev(‘.icon_success,.icon_fail’);
    if ($(field).val().length > 0 || prev_icon.length > 0) {
        if (!$(field).valid()) {
            prev_icon.remove();
            $(field).addClass(‘field_error’).before(icon_fail).prev().prev().addClass(‘label_error’).parent().parent().addClass(’section_error’);
        }
        else {
            prev_icon.remove();
            $(field).removeClass(‘field_error’).before(icon_success).prev().prev().removeClass(‘label_error’).parent().parent().removeClass(’section_error’);
        }
    }
};

Obviously there are some specific .parent() and .before() .prev() etc. that’ll only work in this pages layout, but you get the idea.

Validating Server Side Fields

I had a mare initially with this until I realised that it was the UniqueID that I wanted of the control.  After that, jobs a good un.

$(‘#aspnetForm’).validate({
    errorElement: ‘div’,
    errorClass: "validate_error",
    // what rules do we have – remember this is page 1
    rules:    {
        "<%=txt_UserName.UniqueID %>": {
            required_6_20:                true,
            username_already_in_use:    true,
            minlength:                    6,
            maxlength:                    20
        },
        "<%=txt_Password1.UniqueID %>": {
            required_6_20:                true,
            minlength:                    6,
            maxlength:                    20
        }
        "<%=txt_Email1.UniqueID %>": {
            required:                    true,
            email_already_in_use:        true,
            email:                        true
        },
        "<%=txt_Email2.UniqueID %>": {
            email:                        true,
            equalTo:                    "#<%=txt_Email1.ClientID %>"
        }
    },
    messages: {
        "<%=txt_UserName.UniqueID %>": {
            required_6_20:        ‘Your username must be between 6 and 20 characters and can only contain letters, numbers and – ! _ . punctuation characters’,
            username_already_in_use:    ‘Your username is already in use – please select another’,
            minlength:            ‘Your u
sername is too short – please change it to be between 6 and 20 characters’,
            maxlength:            ‘Your username is too long – please change it to be between 6 and 20 characters’
        },
        "<%=txt_Password1.UniqueID %>": {
            required_6_20:        ‘Your password must be between 6 and 20 characters and can only contain letters, numbers and – ! _ . punctuation characters’,
            minlength:            ‘Your password is too short – please change it to be between 6 and 20 characters’,
            maxlength:            ‘Your password is too long – please change it to be between 6 and 20 characters’
        },

        "<%=txt_Email1.UniqueID %>": {
            required:            ‘You must enter an email address’,
            email:                ‘You must enter a valid email address’,
            email_already_in_use:        ‘Your email is already in use – please enter another or click the link shown’
        },
        "<%=txt_Email2.UniqueID %>": {
            email:                ‘You must enter a valid email address’,
            equalTo:            ‘\’Email\’ and \’Confirm email\’ must match – please double check them’
        }
    }
});

so with .net controls you have to specify the field by using quotes, and <%= field.UniqueID %> to really get the rules to work.

Custom Rules

Creating custom validation rules is a doddle – just ensure they’re called before .validate()

jQuery.validator.addMethod(
    "valid_postcode",
    function(value, element) {
        // uk postcode regex – all straight forward apart from that last bit – apparently
        // uk postocdes don’t have the letters [CIKMOV] in the last 2
        var regex = /^[A-Z]{1,2}[0-9R][0-9A-Z]? ?[0-9][ABD-HJLNP-UW-Z]{2}$/i;    //after – no need for space
        return regex.test(value);
    },
    "This field is required"
);

This one validates against a UK postcode (regex actually published by the uk government – I couldn’t believe it!).

You then just say:

rules: {
“<%= txtPostcode.UniqueID %>”:
valid_postcode: true
}

It’s really nice to see microsoft are including this .validation library in the 2010 release of visual studio (I’m not sure if that means it’s going to replace the existing method of validation or not, but the fact that it’s gotten that level of support from Microsoft is ace.

Also, the CDN from Microsoft seems to include this as one of the libraries, so definitely an indicator of good things for the plugin.

Interesting times and justifying ones existence :)

Well, wasn’t yesterday an interesting day!  Had a conversation with a friend about what it is I actually do.  They didn’t feel that I was selling myself effectively enough via this blog, though thankfully this blog was never about that – it (I hope) shows that I’m keen to learn, keen to do more, and never content with knowing ‘enough’.  They asked in particular for me to clarify what work I had done whilst working on suite-e, and looking back over our work schedules, project documents, and just generally over the functionality in there, it turned out to be quite a list:

  • User Controls / Custom Server Controls
  • Using .net forms and role-based security with the membership and role providers
  • Use of Enterprise Library application blocks for Data Access, Exception Management and Logging
  • Extention methods
  • Significant Ajax use (both ASP.NET Ajax and Telerik Ajax wrappers)
  • Linq (minor)
  • Facade design pattern (5 tier solution, UI -> Business Facade -> Business -> Dal Facade -> Dal)
  • Significant use of inheritance throughout the data and UI layers
  • Interface use (minor, where necessary)
  • Custom/3rd Party Controls (Telerik)
  • Hand rolled URL routing for friendly URLs (sitting atop urlrewriting.net)
  • Use of Themes, Masterpages, including browsercaps useage to allow CSS targetting more effectively for cross browser
  • jQuery use (my input minor)
  • CSS
  • Xhtml
  • > 80 table relational data model, significantly more stored procs, cursors, temp tables
  • Web services to authenticate licensing of the product
  • Windows services to manage email send from the CRM module
  • Significant and ongoing refactoring, including fxcop use when readying the solution for microsoft testing
  • Upgrade from .net 1.1 through 2.0, and then incorporating 3.5 elements when applicable

Suite-e as a product has obviously had more than one developer work on it, though it felt good whilst writing this up to realise how far I’d come and what technologies I’d learned and put into practice during the implementation.  I essentially architected the vast majority of the product, both code and SQL schema from its early days through to the modular CMS, Product Catalogue, E-commerce, CRM and Events management solution that it is today, with over 170 files and 51k lines of code across 6 projects, over 80 tables, significantly more stored procedures… the list goes on 🙂

It’s incredibly understandable that a blog would give people a perception of who you are, indeed it’s a very personal blog, so it certainly should, though I hope this blog also gives folks a perception of the sort of developer I am, that will keep ploughing on and learning as much as I am able, because that is what is ‘fun’ to me.

NamingContainer, where have you been all my life!

Well, it’s been where it’s always been – in the .net framework, all the way back to version 1.0 apparently!  I’m sure I must have written some awful code in the past to get around the fact that I didn’t know about this, and I really must spend more time getting down to the details of the framework for reasons of framework nuggets like this.

Imagine you have a ListView and want to allow updates on each item, with perhaps a text field etc. in there, and you click a button inside that ListViewDataItem.  NamingContainer for the button will return that ListViewDataItem so that you can find just within that container… ace!

protected void btnWishList_Click(object sender, EventArgs e)
{
    Button _button = ((Button)sender);
    RadTextBox tb = (RadTextBox)_button.NamingContainer.FindControl("txtNotes");
    // do other processing
}

the above is formatting awfully at the moment, looking for a nice wordpress syntax plugin.

A wee step backward…

Well, maybe not.  I’ve been reviewing over the weekend my approaches to learning, and after reading considerably, I think Entity Framework isn’t the right path for me at the moment.  So many are reporting issues with it, and the Julie Lerman book highlights a number of hoops you need to jump through on complex datasets that simply shouldn’t be there.

So it’s back to basics.  Linq, and then by definition Linq to Datasets, Linq to SQL (and eventually when it matures, Linq to Entities) is the path I’ve chosen.  Linq to Datasets may well be considered legacy code, though suite-e uses them in a few places to retrieve large sets of data (converting stored proc –> objects for 45,000 rows proved too time costly), and I think it’ll be handy to start at this grass roots level.

I’m convinced by ORMs, and ideally would like to proceed with Entity Framework, but at least the above will give me a solid grounding that I can then proceed through to something like EF with limited pain.  VS2010/C#4.0 apparently will have some updates, so I’ll keep monitoring.

On a separate note, I’m getting so heavily addicted to stackoverflow – I post where I can, but as a resource for learning it’s fantastic.  You still have a number of weaknesses (people not answering the question, but posting fast to try to get points, downvotes for stupid reasons, etc.) but on the whole, it’s a cracking resource.

This month will see me probably post very little, we have 4 large client sites up for launch and 2 minor client updates, so I suspect the hours will be long and arduous, but we soldier on 🙂

Where do we go from here…

Most people start their blogs with a ‘hello world, expect more from me’, so apologies for the war and peace tome that you’re about to suffer if you’re reading this!

Well, we find ourselves at a somewhat melancholic crossroads.  I was contacted by a recruitment agent from Nigel Wright earlier in the week to suggest that they had 2 jobs that they thought I’d be perfect for, and they based the decision on a CV that was nearly 4 years old.

Initially, excitement kicked in and I had no idea where it came from – what was wrong with my current job?  It got me thinking and reading a bit, I’ve been here for a little over 3 years now, and have been a very active part of the team, I’ve architected and built our in house CMS/E-Commerce/CRM solution that we can then use to build client websites (there’s another post in there somewhere as to why we didn’t go with an off shelf solution!), I’ve gotten the organisation Microsoft Partner status, something I’d recommend any organisation do if they’re developing for the MS platform btw, I’ve brought in a fair few clients – everything is peachy right?

Well, no.  What about me?  Selfish to say, but where is my career going in all of this, what am I doing, and how am I doing it?  I’ve been a web developer now for about 15 years in various guises, starting out with the good old perl/cgi/mysql combo, moving over to classic ASP, and then over the past 5-6 years focussing on c#/asp.net. I’ve done some very fulfilling work, and some that just ticks over, like any developer I guess. Since my computing degree I’ve always felt I’ve been at the softer side of development, never really delving, never getting too heavily into the software architecture simply for the sake of it.  I’ve hit a point where I can continue to do as I am, delivering quality code for sure, but really not being truly happy with it because I want that more thorough approach, or I can do something about it.

I’ve led development teams, I’ve implemented n-tier solutions using the facade pattern, I’ve done huge projects with tens of thousands of (more often than not necessary) lines of code, I’ve implemented rich SQL server schema, I’ve implemented interfaces, I’ve serialised, I’ve consumed, I’ve done an awful lot.

What’s Changed?

It was a chance tweet yesterday by Mike Taulty where he mentioned Rob the Geek, and you just know, anyone with a name like that, I have to check them out.  I got to Rob’s blog and in particular a post where he was lamenting some of the same issues that are facing me… I want to know more, I want to do more, I want to understand more – software development without learning just isn’t enough for me.

So what do I want?

I think first and foremost, I want to get deeper into the framework – there are any number of the simple things that I just want to understand more, reflection, generics (not just List<Object>), Lambda, goodness the list goes on – all fundamentals, and I know that if I sat for a short period on any of them I’d get them, and it’d stick – I’m certainly not an unintelligent guy (well…)

Key really for me is MCTS I think – I think the framework exam will lead me into a nice and thorough understanding of the framework so that I can then proceed to the ASP.NET 3.5 Application Development exam.

After that, my main thirst is to work with ORM, and in particular Entity Framework, I’ve read and understand the reasons that some people are waiting till 2.0 to roll out into production, but speaking as someone who’s written DAL/Object layers for 4 years, it’s getting very old very fast and I want to try something else that lets me focus on the good bits and worry less about the data.  After that (or probably in parallel), MVC looks pretty cool – again, just as potential starting points.

How do I go about all this then?

There are an awful lot of things I waste time on each week – online gaming, getting to work at 7:15am because I’m awake and I’ve got stuff to do, leaving at 7pm because I’m still awake, and I’ve got stuff to do… it all adds up, I suspect I can allocate at least 25hours a week to personal study (thankfully my wife is very supportive of me doing this, and the kids will I think reap the benefits of the happiness I get from it).

So that’s where we are.  And this blog, at least in the first instance, is intended to be a record of my path through all of the above – I’ll post about everything, even when I’m learning things that seem ridiculously simple – I think keeping this record will help motivate me, will help keep me focussed – I don’t particularly care if anyone ever reads it, I’ve found even just this seminal post to be incredibly cathartic.

Expect regular updates, as if I don’t it means I’m not doing what I said I would 🙂