Thursday, May 22, 2008

[dotnet] Visual Studio Database Projects in 2005 or 2008

Ah!  You caught me out; yes, we primarily do single-developer projects.

  • Still, I find the Database Project useful not only as a source repository of migration scripts, but also:
  • The list of Database References helps keep the 3 environments handy when wanting to apply a script to any particular environment.
  • And, when you right-click a database object in the Server Explorer and click Generate Create Script to Project = very handy.

From: dotnet@dot.net.nz [mailto:dotnet@dot.net.nz] On Behalf Of Andrew Shearer
Sent: Thursday, 22 May 2008 4:03 p.m.
To: dotnet@dot.net.nz
Subject: [dotnet] RE: visual studio database projects in 2005 or 2008 -->

Do you have a whole team working on this migration script, or just you?

From: dotnet@dot.net.nz [mailto:dotnet@dot.net.nz] On Behalf Of James Hippolite
Sent: Thursday, 22 May 2008 3:51 p.m.
To: dotnet@dot.net.nz
Subject: [dotnet] RE: visual studio database projects in 2005 or 2008

-->

I use Database Projects; here’s how:

  • Each week I create a new migration script e.g. Update20080523.sql.
  • All database changes made to DEV, are scripted and saved to this file.
  • I test and re-test it in DEV.  Obviously this requires some smarts around whether to attempt to recreate objects or update data (IF EXISTS…).
  • I apply it to TEST, usually only once.
  • Finally, during Friday’s change window, I apply it to PROD; definitely only once.
  • It helps to place a comment at the top of the file referencing the changed objects.

From: dotnet@dot.net.nz [mailto:dotnet@dot.net.nz] On Behalf Of Andrew Shearer
Sent: Thursday, 22 May 2008 3:31 p.m.
To: dotnet@dot.net.nz
Subject: [dotnet] visual studio database projects in 2005 or 2008 -->

Who here actually uses these and finds them practical?? Sure, the source control aspect would be great but all i see is this hugely outweighed by trying to manage schema and data changes in a sequential fashion and the major problems that arise if that isn’t done correctly. We don’t roll out the entire database with each product release, but simply the iterative changes that have been made. Is that where we’re going wrong in terms of how a database project works? If you release just the incremental schema and data changes like we do, how are you maintaining this sequence in your database project ‘change scripts’?

thanks

Tuesday, May 20, 2008

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

Saturday, May 10, 2008

Why Bother With SSRS?

One really interesting question I got during my presentation this week, to which I felt I didn't give a particularly well-rounded response, was "Why bother using SQL Server Reporting Services at all?"  The question was asked seriously in the context that I had just demoed how to create a really simple table report, which could more easily have been done on a page with a GridView.

Now that I've had time to think about it, there are 3 really good reasons:

  • If all you want is a simple table report, the questioner is absolutely correct: do it in a GridView.  However, if you want Excel-like charts, then SSRS is the easiest way forward.
  • Also, SSRS lets you easily create form-letters (with page breaks, headers and footers in the correct places).  You can't guarantee what a web page will output like.
  • And finally, SSRS lets you output to PDF.

Wednesday, May 7, 2008

Wellington .NET User Group Meeting

Presented on two topics last night:

  • Microsoft Certification in Visual Studio 2008: why and how?
  • SQL Server Reporting Services: How to create, deploy and consume a report.

Seemed to be quite well received.  Of the 30-odd there, only 4 of us had ever sat an exam before, so the audience had come to hear what was definitely a new topic for them.  Kirk Jackson was impressed to see so many new faces.

Darryl Burling asked at the end, how many people were interested in pursuing certification, now that they understood the mechanics.  The majority put their hands up.

Anyway, if you, dear reader, where one of the ones asking for my presentation notes, they are now available for download here:

http://cid-a93b6100e328706d.skydrive.live.com/browse.aspx/Presentations

Enjoy.