Code Camp Auckland 2009 – Feedback from attendees

Over on Kirk Jackson’s Page of Words, he gives an overview of the feedback from attendees.  All very positive.

He also released individual speaker’s performances privately to each one of us speakers.  Here’re mine:

Becoming Certified. James Hippolite:

• Dissatisfied: 1 (3%)
• Satisfied: 11 (28%)
• Satisfied++: 19 (49%)
• Very satisfied: 8 (21%)

It was difficult to follow the structure in the Becoming Certified talk and the Secure Coding Practices talk.

I guess that’s because I had 1/2 an hour to cram 1 hour’s worth of presentation.

Kirk indicated that responses were on average about 50% of attendance, which implies I had 80 people in my talk.  Not bad, woo hoo!

Code Camp Auckland 2009 - Development | SQL | Security

I’m speaking at Code Camp, presenting on Microsoft Certifications during lunch. Here is the blurb from Kirk Jackson:

Code Camp is less than two weeks away!

If you want to catch some free sessions on the state-of-the-art in .NET development, SQL Server and developer security then sign up for Code Camp Auckland 2009 now.

Code Camps are non-profit, and organised by members of the local developer community. This year the Auckland Code Camp is the day before TechEd (Sunday 13 September), so we've managed to nab a few great speakers on their day off to present to us.

It's the biggest Code Camp ever - over 14 hours of sessions across 3 streams from 10am till 5pm:

Development...

• What's Happening in C# 4.0 and .NET Languages and Why Should You Care?
• Behaviour Driven Development
• Domain Specific Languages
• Silverlight with Prism
• .NET on the iPhone
• Becoming Certified
• Lightning Talks
• and more!

The latest and greatest in development topics, by the people that know!

SQL Server?

• SQL Server Virtualisation Best Practices and Recommendations
• SQL Server Analysis Services and Gemini
• Query Optimization and Query Tuning
• Understanding SQL Server Indexing
• SQL Server Maintenance

Training and guidance from the best SQL trainers in the industry!

Security!

• Secure Development Lifecycle and Threat Modelling workshop
• Secure Coding Practices

We are lucky to have Michael Howard, author of Writing Secure Code and 24 Deadly Sins of Software Security giving a free workshop for developers, architects and team leads on Threat Modelling and the Secure Development Lifecycle. This will be followed by a session on how to write secure .NET code.

Auckland has never seen such an awesome free event!

Presenters that are offering their time include Greg Low, Nicholas Dritsas and Auckland's Alex Henderson of Architecture Chat fame.

To cover the costs of the event, we have the help of our generous sponsors: Microsoft, Datacom, Intergen, INETA and Xero.

All that's left for you to do is to visit the website for more details, and sign up now!

See you there on Sunday 13 September,

What on Earth have I been up to?

I’ve been busy:

Date	Title	Venue
02-Apr-09	WebCast: Intro to ORM for DBAs	PASS USA
16-Apr-09	What’s New in Microsoft SQL Services	Wellington SQL Users
30-Apr-09	Microsoft Certifications	Auckland SQL Users
14-May-09	How To: Design and Develop Applications to Ensure Quality	VUW
26-May-09	How To: Design and Develop Applications to Ensure Quality	Ellerslie .NET Users
14-Jun-09	Microsoft Sync Framework	NZ .NET Code Camp, Porirua
18-Jun-09	Microsoft Sync Framework	Wellington SQL Users
15-Jul-09	How To: Design and Develop Applications to Ensure Quality	Wellington .NET Users
30-Jul-09	Microsoft Sync Framework	Auckland SQL Users & Ellerslie .NET Users
04-Aug-09	Creation: Why I Believe	Malakai’s Care Group, Wellington Elim
20-Aug-09	How To: Design and Develop Applications to Ensure Quality	Software Quality NZ
13-Sep-09	Microsoft Certifications	NZ .NET Code Camp, Auckland
29-Sep-09	Creation: Why I Believe	Jeannie’s Care Group, Wellington Elim

I’m available for bookings!

COALESCE T-SQL Function

I am constantly needing and forgetting the syntax for this function.  Finally, if I put it here, I'll have quick and easy access to it:

SELECT COALESCE(@column1 + ',', '') + column1
FROM Table1 

Web Application Stress Tool - 401 Unauthorized Errors

Problem	I'm using the Microsoft Web Application Stress Tool.  It's meant to hit a web page repeatedly for testing purposes.  However, when I check the results, they were all "401 - Unauthorised".
Explanation	It turns out that if anonymous access is disabled on the web site you are accessing due to the way IIS evaluates authentication, this behavior is expected. In Operations Manager, the default evaluation for a Web Application is to generate a Critical Alert if an HTTP return code of 400 or anything above comes back. In IIS, even if anonymous authentication is disabled for the site, it is still evaluated and will always return a 401. The aggregated results (The outcome) will succeed but the 401 return code will cause the test to fail...
Solution	The simplest fix (for development and testing purposes) is to enable anonymous access to the site.

Unable to create SQL ENDPOINT

Problem	I'm doing a SQL Server CREATE ENDPOINT lab.  I have stopped the IIS Admin service, as recommended in the documentation.  I am connected to SQL Server as a Local Admin user (with full SA rights).  But still the CREATE ENDPOINT statement was failing: CREATE ENDPOINT GetEngineeringList STATE = STARTED AS HTTP ( SITE = 'localhost' ,   PATH = '/sql/employees' , AUTHENTICATION=(INTEGRATED), PORTS=(CLEAR) ) FOR SOAP ( WEBMETHOD 'GetEngineeringEmployees' (   name = 'AdventureWorks.dbo.GetEngineeringEmployees' ,   schema =STANDARD ), WSDL= DEFAULT ,   DATABASE = 'AdventureWorks' , BATCHES=ENABLED )
Explanation	Contrary to logic, the CREATE ENDPOINT statement does not run with the rights of the issuing user.  It runs with the rights of the SQL Server service account.  That account doesn't have rights to the PATH I specified.
Solution	Reserve the PATH using this command: EXECUTE sp_reserve_http_namespace N 'http://localhost:80/sql/employees'

Proxy Authentication Required

Problem	I'm coding up a Live Mesh sample.  I'm using Visual Studio to attempt to create a programmatic link to the Mesh.  I run it.  It was failing.
Explanation	(407) Proxy Authentication Required. So, my app was attempting to authenticate to the Windows Live Passport service, but the Telecom Web Proxy wasn't allowing my credentials through.  I needed a way of tunneling through the Telecom Web Proxy.
Solution	// Set the system proxy with valid server address or IP and port System.Net.WebProxy pry = new System.Net.WebProxy("akisa-ary1.telecom.tcnz.net", 8080); // The DefaultCredentials automatically get username and password pry.Credentials = CredentialCache.DefaultCredentials; // Deprecated: GlobalProxySelection.Select = pry; WebRequest.DefaultWebProxy = pry;

Profile Storage Space Issues

Both Martin and I are experiencing blow-outs of our profile space (currently around 120MB), mainly due to Visual Studio 2008.  We can handle it with the handy little Registry snippet which grants us unlimited space.  However, logging in and out on the network is taking longer each time.

I wonder if now is a good time to think about and/or investigate the option of running Virtual Machines and moving Visual Studio onto there.  I personally don’t have the time to look into this.  What are other people’s opinions?

• Norman replied:
  • My profile is sitting on 45Mb and I am running VS2008 (VS2005 and VS2003 as well).  I am wondering if it is SP1 for VS2008 that is storing a lot more in the d:\Documents and Settings...? 
  • I am a bit wary of the extra complexity of using VMs and think a better option maybe some further tweaks so that VS2008 stores its settings in a different location than d:\Documents and Settings\... .  I think most of it is set in the Registry as the options below only cover a small subset of where it stores its stuff
  • Totally agree we need to do something.  Recently I had to test Brochure Template on an EDS Machine in Auckland logging in using my T number.  As you can imagine very slow and could not log out (no rights to tweak the Registry).
• Martin replied:
  • Where is the official source to state the current size of your profile?  I was looking at System properties.. Advanced.. User Profiles.. and my roaming profile was 585MB!  Is this the same profile EDS tracks?  I've culled mine back to 100MB now..
  • I've cut that back dramatically by deleting the ReflectedSchemas and ReflectedTypeLibs that VS likes to generate all the time.
  • But it is only a temporary fix, though I plan to write a script to run when I want to log off or shut down that will automatically delete them every time.
  • Those project and template locations tend to not have much in them unless you are creating your own user templates.
  • The best solution would be to exclude certain folders from our profile.  The only thing stopping that from happening is getting EDS to set it up for us?
• James replied:
  • I am reluctant to adopt Martin’s idea, as I believe those Reflected Schemas are what IntelliSense generate each time you invoke them, so I don’t want to constantly have to be waiting for IntelliSense every day. 
  • Does anybody in the community have any (useful) suggestions?
• Mike (manager) replied:
  • Move those files out of profile space onto hard drive;
  • Create shortcuts in the profile space to the actual files.
• Conclusion:
  • I did move those files out;
  • But shortcuts didn't work;
  • But in the ReflectedSchemas folder is a file "entries.xml" (same for ReflectedTypes folder);
  • if you edit this to point to the new location of the real files, then Visual Studio will happily use them;
  • New .xsd files will still be created in your profile as you develop, but a periodic sweep (manually) should fix that.

Opening BLOB from DB as Attachment

Background

· I'm storing attachments in a Binary Large Object (BLOB) field in a table in a database.

· I also store the MimeType.

· I want to open them.

· I list the attachments for the user to peruse, with a HyperLink under the name.

· The hyperlink opens a new blank window and redirects to a blank ASPX page.

· I also pass the Attachment ID to the new page.

· The ASPX page has a Page_Load event.

· The Page_Load event gets the record and Binary Writes the byte() to the Response.

· Also, I envisage that if it was a spreadsheet, I want to launch Excel.

Problem Description

· This works for JPG.

· But didn't work for a text file.

· I haven't tried PDF yet, for instance and the text file I chose was a .reg file so that might not have been a wise test!

· First I get a debug window

· Second the browser says 'The XML page cannot be displayed'

· 'Invalid at the top level of the document'

Problem Resolution

· Me.Response.AddHeader('Content-Disposition', 'inline;filename=' & fileName)

· Now the browser asks if I want to run or save the .reg file.

· I must specify the filename, even though I’m not loading the response from an actual file (but from a Memory Stream).

· This works for XLS, PDF, and JPG.

· I haven’t tried ZIP yet.

SubSonic Workaround

SubSonic is an Object Relational Mapper (ORM) tool to which I converted a few months back from Strongly-Typed Datasets in Visual Studio.  SubSonic is a free open source program.  It’s brilliant because you point it at your database (supplying the database connection string) and it generates the code that maps your tables into classes.  It automatically generates your Data Access Layer for you.  Anytime you need to alter a table structure, you merely re-run SubSonic, et voila, your classes instantly reflect the change.  You don’t need to muck around with editing 4 stored procedures like I used to do with Strongly Typed Datasets.

As long as the User ID you specified in your connection string has SELECT rights on your table(s) and/or EXEC rights on your Stored Procedures, then SubSonic will pick that up and make only those objects available.  If you want your application to do INSERTS, you also need to grant that right to your User, and SubSonic will auto-generate a method for Insert.

However, I’ve recently discovered a bug, and subsequently the workaround for it.

Problem Description

You can call a stored procedure with the following code:

Public Function GetOutstandingRequests() As DAL.RequestCollection

Dim sp1 As StoredProcedure
Dim ds1 As DataSet
Dim dt1 As DataTable
Dim retVal As New DAL.RequestCollection

sp1 = DAL.SPs.SelectRequestsByFilter()
ds1 = sp1.GetDataSet()
dt1 = ds1.Tables(0)
retVal.Load(dt1)

Return retVal
End Function

This will result in an error: Object does not exist on the line that attempts to GetDataSet().

Explanation

The bug is that SubSonic cannot instantiate a stored procedure that doesn’t have any parameters.

Workaround

The workaround is to alter your Stored Procedure to always accept at least one parameter and use them to instantiate the object, thus:

Public Function GetOutstandingRequests(ByVal createdBy As String, ByVal responseTypeID As Integer?, ByVal seekerID As Integer?) As DAL.RequestCollection
Dim sp1 As StoredProcedure
Dim ds1 As DataSet
Dim dt1 As DataTable
Dim retVal As New DAL.RequestCollection

sp1 = DAL.SPs.SelectRequestsByFilter(createdBy, responseTypeID, seekerID)
ds1 = sp1.GetDataSet()
dt1 = ds1.Tables(0)
retVal.Load(dt1)

Return retVal
End Function

Status

If anyone is aware of whether this issue will be fixed in future versions of SubSonic, I’d be grateful to be kept informed (I’m using version 2.1 [Final] at present).  Please leave a comment or send me a tweet.

How to validate length of input into a multi-line textbox

You can validate the length of input of an ordinary TextBox using the MaxLength attribute.  However, this doesn't work if the TextBox has a TextMode of Multi-line.  In those instances, use this piece of code.

<script type="text/javascript" language="javascript">

function MaxLength()

    {

var txt = $get("<%=ResponseNote_TextBox.clientid %>");

var msg = $get("<%=Message_Label.clientid %>");

if (txt.value.length > 500)

        {

            SelectText();

            msg.className = "Failure";

            msg.innerHTML = "* Response has more than 500 characters";

        }

else

        {

if (msg.innerHTML == "* Response has more than 500 characters")

            {

                msg.innerHTML = "";

            }

        }

    }

function SelectText()

    {

var txt = $get("<%=ResponseNote_TextBox.clientid %>");

var rng = txt.createTextRange();

            rng.moveStart("character", 501);

            rng.moveEnd("character", 0);

            rng.select();

    }

function RemoveCRLFs()

    {

// debugger;

var txt = $get("<%=ResponseNote_TextBox.clientid %>");

var str = window.clipboardData.getData("Text");

    str = str.replace(/(\s*(\r?\n|\r))+$/, '');

    txt.value = str ;

    }   

</script>

Page_load:

ResponseNote_TextBox.Attributes.Add("onblur", "javascript:MaxLength()")

ResponseNote_TextBox.Attributes.Add("onpaste", "javascript:RemoveCRLFs()")

RegisterForEventValidation can only be called during Render();

This error comes when you are exporting the GridView control from Visual Studio. To solve this error you have to turn the eventValidation off.  You can do this in the web.config file but if you do, the eventValidation will be turned off for all the pages.

<pages enableEventValidation ="false" ></pages>

Alternatively, you can do this in the Page directive which will turn off the validation for a single page.

<%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true"

CodeFile="ExportGridView.aspx.cs" Inherits="ExportGridView" %>

How to Drop a Column

• Discover whether there is a constraint;

DECLARE @ConstraintName NVARCHAR(50);
SELECT @ConstraintName = [name]

FROM sys.objects o

WHERE o.[parent_object_id]=OBJECT_ID('Schema.TableName')

AND o.type='D'

AND o.[name] LIKE '%First5LettersOfColumnName%'

• If there is, remove it;

IF NOT (@ConstraintName IS NULL)

   EXECUTE ('ALTER TABLE Schema.TableName 
   DROP CONSTRAINT ' + @ConstraintName)
GO

• Remove the column

IF EXISTS(SELECT * FROM sys.columns WHERE [name]='ColumnName 
AND [object_id]=OBJECT_ID('Schema.TableName'))

      ALTER TABLE Schema.TableName

      DROP COLUMN ColumnName;

GO

Emailing from a Web Page

Imports System.Net.Mail

'Create an instance of the MailMessage class
Dim objMM As New MailMessage()
Dim Requestor_DropDownList As DropDownList
Dim IssueID_Label As Label

With Issue_FormView   Requestor_DropDownList = .FindControl("RequestedBy_DropDownList")
   IssueID_Label = .FindControl("JobID_Label")
End With

'Set the properties
objMM.To.Add(Requestor_DropDownList.SelectedValue + "@telecom.co.nz")
objMM.From = New MailAddress("est@telecom.co.nz")

'Send the email in html format
objMM.IsBodyHtml = True

'Set the priority - options are High, Low, and Normal
objMM.Priority = MailPriority.Normal

'Set the subject
objMM.Subject = "Feedback"

'Set the body
Dim sb As New StringBuildersb.AppendLine("Kiaora " + Requestor_DropDownList.SelectedItem.Text)
sb.AppendLine()
sb.AppendLine("Your EST Issue is about to be closed.")
sb.AppendLine("Please take 5 minutes to rate the service you have received the the Enterprise Service Team.")
sb.AppendLine("Click into this link: http://wstProdIIS/EST/register/Issue.aspx?ID=" + IssueID_Label.Text)
objMM.Body = sb.ToString

'Now, to send the message, use the Send method of the SmtpClient class
Dim objSC = New SmtpClient
objSC.Host = "WNEXBE01.telecom.tcnz.net"
objSC.Send(objMM)

Some extra Reporting Service Parameters

<rsweb:ReportViewer ID="rptLetter" runat="server" ExportContentDisposition="AlwaysAttachment" 
       Height="475px" ProcessingMode="Remote" PromptAreaCollapsed="true"
       ShowPromptAreaButton="false" SizeToReportContent="True" Width="100%" ShowFindControls="false"
       BackColor="#ededed">
        <ServerReport ReportPath="/Reports/Letter" ReportServerUrl="https://reportserver" />
        </rsweb:ReportViewer>

  Dim MyDate As New ReportParameter("date", PrintDate)
  Dim p() As ReportParameter = {MyDate}

  rptLetter.ServerReport.ReportPath = "/Reports/" & ReportName
  rptLetter.ServerReport.ReportServerCredentials = New ReportViewerCredentials(
       "username", "password", "domain")
  rptLetter.ServerReport.SetParameters(p)
  rptLetter.ServerReport.Refresh()
  rptLetter.Visible = True

Eliminate Double-Lines from Table Borders

<table border=1 bordercolor="black" style="border-collapse:collapse">
   <tr>
      <td>
         Something clever goes in here
      </td>
   </tr>
   <tr>
      <td>
         Something smart goes in here
      </td>
   </tr>
</table>

Something clever goes in here

Something smart goes in here

When 2 grids are linked

When you have:

• a web page
• 2 DataGridViews (parent/child relationship)
• user wants to insert a new record into parent grid
• parent grid has datasource set to Nothing so that EmptyDataTemplate shows
• EmptyDataTemplate contains a DetailView
• user fills in fields and clicks Insert

Then you get:

• the parent record saves correctly; and
• the page refreshes; but 
• an error occurs because the child grid is linked to an empty Parent grid.
• No amount of fiddling with the SelectedItem property of the Parent grid seems to work.

Resolution:

• Hide the second grid until the user manually makes a selection on the Parent grid.

Implementing Manual Sorting on GridView

Problem:

• I have GridViews, powered by ObjectDataSources.
• I wanted to implement sorting
• Error: DataSource doesn't implement IEnumerable.
• Went looking it up: too hard

Solution:

• GetData() method accepts two input parameters: SortName and SortOrder
• Page.Load method (snippet 1)
• GridView.Sorting method (snippet 2)
• ObjectDataSource.Selecting method (snippet 3)

Snippet 1:

  If IsPostBack Then
    Exit Sub
  End If

  ViewState("SortName") = "ColumnName"
  ViewState("SortOrder") = WebControls.SortDirection.Ascending

Snippet 2:
  e.Cancel = True 'Cancel the automatic sort, do it manually

  If ViewState("SortName") = e.SortExpression Then
     ViewState("SortOrder") = Not ViewState("SortOrder")
  Else
     ViewState("SortName") = e.SortExpression     
     ViewState("SortOrder") = e.SortDirection 
  End If

  GridView1.DataBind()
Snippet 3:

Dim iod As IOrderedDictionary = e.InputParameters 
iod("SortName") = ViewState("SortName")
iod("SortOrder") = ViewState("SortOrder")

Displaying Access Denied for Authenticated User

Learned a new trick:

• User is authenticated, but not authorised to perform a certain function
• Can protect the page that performs that function in web.config (snippet 1)
• However, by default, when unauthorised user attempts to get to that page, they are shown the "Yellow Screen of Death"
• Can alter this default behaviour in Login.aspx.vb, put this in Page_Load. (snippet 2)

<location path="Admin">
  <system.web>
    <authorization>
     <allow roles="Administrator"/>
      <deny users="*"/>
    </authorization>
  </system.web>

</location >

If User.Identity.IsAuthenticated Then

   'You were redirected here becasue you're not authorised

   Session("sMessage") = "You are not authorised to perform that operation."

   Response.Redirect("Errors/AccessDenied.aspx")

End If

DropDownList won't change value

Before changing the SelectedValue of a DropDownList, you must first issue the DataBind statement to populate it!

D'oh!