On Sample Simplicity


I was working through a Communications sample of connecting SilverLight to POX, then Web Services, and finally WCF and I came across the following instructions:

(Part 1: POX) The Generic handler will process an incoming request using the code declared in the ProcessRequest function. This function should create some new instances of CityData and add them to the myCities list. Here are some examples of cities with longitude and latitude { (London, 51.5, 0), (Stratford-upon-Avon, 52.3, -1.71), (Edinburgh, 55.95, -3.16) }. See if you can write the code to do this.

(And later in the lab...)

In this example your ASHX served up hard-coded data for 1 city. Can you build it so that it can accept parameters on the URI string (i.e. http://localhost:8001/Sample1Web/GetData.ashx?city=whatever)?

(And even further in the lab...)

Write a function that takes in a country and builds a List<CityData> of several cities for that country. Here are some cities and their latitudes and longitudes:

("Paris", 48.87, 2.33);
("Lourdes", 43.1, 0.05);
("Toulouse", 43.6, 1.38);
("London", 51.5, 0);
("Stratford-Upon-Avon", 52.3, -1.71);
("Edinburgh", 55.95, -3.16);
("Berlin", 52.52, 13.42);
("Munich", 48.13, 11.57);
("Hamburg", 53.58, 9.98);

Provided Solution

The sample provided "solution" code at the end of the instructions. The code is basic and unimaginative, but perhaps that is all the is needed of sample code (I would offer that samples are the perfect to push the boundaries -- lead by example).

// File: CityData.cs
public class CityData
{
    public string CityName { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }

    public CityData(string strCityName, double nLatitude, double nLongitude)
    {
        CityName = strCityName;
        Latitude = nLatitude;
        Longitude = nLongitude;
    }

    public CityData()
    {
    }
}

// File: GetData.ashx
public class GetData : IHttpHandler
{
    private List<CityData> getCities(string strCountry)
    {
        List<CityData> ret = new List<CityData>();

        switch (strCountry)
        {
        case "france":
            ret.Add(new CityData("Paris", 48.87, 2.33));
            ret.Add(new CityData("Lourdes", 43.1, 0.05));
            ret.Add(new CityData("Toulouse", 43.6, 1.38));
            break;

        case "uk":
            ret.Add(new CityData("London", 51.5, 0));
            ret.Add(new CityData("Stratford-Upon-Avon", 52.3, -1.71));
            ret.Add(new CityData("Edinburgh", 55.95, -3.16));
            break;

        case "germany":
            ret.Add(new CityData("Berlin", 52.52, 13.42));
            ret.Add(new CityData("Munich", 48.13, 11.57));
            ret.Add(new CityData("Hamburg", 53.58, 9.98));
            break;

        default:
            ret.Add(new CityData("London", 51.5, 0));
            ret.Add(new CityData("Stratford-Upon-Avon", 52.3, -1.71));
            ret.Add(new CityData("Edinburgh", 55.95, -3.16));
            break;
        }
        return ret;
    }
}

Alternative 1

I wanted to explore alternatives to the supplied code, so I decided to initialize the CityData list with the data instead of using "Add". In C++ this was more efficient (it set the initial list size instead of using the default capacity sizing algorithm among other things), but I'm not sure of performance in terms of C#. I would not expect any significant performance difference with this small sample though.

public List<CityData> getCities(string strCountry)
{
    List<CityData> ret = new List<CityData>();

    switch ( strCountry.ToLower() )
    {
        case "france":
            ret = new List<CityData>() {
                new CityData("Paris", strCountry, 48.87, 2.33),
                new CityData("Lourdes", strCountry, 43.1, 0.05),
                new CityData("Toulouse", strCountry, 43.6, 1.38)
            };
            break;

        case "germany":
            ret = new List<CityData>() {
                new CityData("Berlin", strCountry, 52.52, 13.42),
                new CityData("Munich", strCountry, 48.13, 11.57),
                new CityData("Hamburg", strCountry, 53.58, 9.98)
            };
            break;

        case "uk":
        default:
            strCountry = "UK";
            ret = new List<CityData>() {
                new CityData("London", strCountry, 51.5, 0),
                new CityData("Stratford-Upon-Avon", strCountry, 52.3, -1.71),
                new CityData("Edinburgh", strCountry, 55.95, -3.16)
            };
            break;
    }
    return ret;
}

Alternative 2

I haven't worked very much with LINQ, so I wanted to compare the same functionality using LINQ. I added a string "Country" to the CityData class and here is the result:

private static List<CityData> cityList = new List<CityData>()
{
    // UK
    new CityData("Paris", "UK", 48.87, 2.33),
    new CityData("Lourdes", "UK", 43.1, 0.05),
    new CityData("Toulouse", "UK", 43.6, 1.38),

    // France
    new CityData("Paris", "France", 48.87, 2.33),
    new CityData("Lourdes", "France", 43.1, 0.05),
    new CityData("Toulouse", "France", 43.6, 1.38),

    // Germany
    new CityData("Berlin", "Germany", 52.52, 13.42),
    new CityData("Munich", "Germany", 48.13, 11.57),
    new CityData("Hamburg", "Germany", 53.58, 9.98)
};

public List<CityData> getCities(string strCountry)
{
    IEnumerable<CityData> data = from city in cityList
                                 where city.Country == strCountry
                                 select city;
    return new List<CityData>(data);
}

This is still not the most efficient or optimal method, but I think it looks nice and is helps introduce LINQ in an understandable manner.

Conclusion

Samples and labs should be simple and yet it is a perfect time to include new technologies and push the boundaries so to speak. One thing I certainly don't want to see in samples are basic programming inefficiencies (things that make you cringe) due to lack of effort by the sample creator. That was not necessarily the case with this sample, but it would have been better to present alternatives such as the examples I listed.

Technorati tags: ,

author: Ryan Van Slooten | posted @ Friday, June 13, 2008 4:08 PM | Feedback (0)

Macro Guidelines for Excel VBA Beginners


I'm in the process of updating an Excel spreadsheet that is failing when it is running inside of Internet Explorer. The issue is related to the ActiveSheet and other global properties having a value of Nothing when the code is assuming they have valid references. As I am going through this spreadsheet, I am noting a wide variety of programming deficiencies and inefficiencies. Here is a list of some of the issues encountered:

If you run spreadsheets under Internet Explorer, use Application.ThisWorkbook and to ensure the browser evaluates the reference correctly.
NOTE: This is not an exhaustive list, nor do I claim that the "Better" examples are the best code example, but rather they are (hopefully) significant improvements over the "Bad" examples. In many cases, I have kept the design of the bad example so that you can see the differences however you should strive to completely refactor the code if possible.
  1. Do not abuse the "With" statement in VBA.

    Some people like "With" and some people don't and I don't particularly care for it. In particular, do not use nested "With" statements.

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    With ActiveSheet
      With TestForm
        ' ...
      End With ' TestForm
    End With ' ActiveSheet
    ' ... BAD EXAMPLE: DO NOT USE ...
    
    
  2. Avoid using the "Goto" statement in VBA.

    "Goto" is almost always a bad idea and a sign of spaghetti code. There are very few cases were it is appropriate to use it -- very few cases!

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    For i = iHere To lRealLastRow Step 2
      BuildStr = "A" & i + 1
      Range(BuildStr).Select
      If Range(BuildStr).Value = "---" Then GoTo DoneWithNames
      SelectItemDlg.Items_List.AddItem Range(BuildStr).Text
    Next
    
    DoneWithNames:
    ' ... BAD EXAMPLE: DO NOT USE ...
    

    Example of Better Code (I still would have designed it differently)

    ' ...
    Dim MyCell As Range
    For i = iHere To lRealLastRow Step 2
      Set MyCell = Cells(i + 1, 1)
      If MyCell.Value = "---" Then
        Exit For
      End If
      SelectItemDlg.Items_List.AddItem MyCell.Text
    Next
    ' ...
    
  3. Avoid using the name of a Form inside the Form code

    The current form is implied in the code. It is not necessary to use the form name to reference controls on the form. If you want to differeniate form controls, you can use the 'Me' keyword such as: Me.Hide

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    ' This example is doubly bad because it uses the form name (ChartConfig)
    ' both explicitly and in a With statement
    If ChartConfig.Variable1.Text = ChartConfig.Variable2.Text Then
        MsgBox "Variable1 cannot be same as Variable2. Please choose another variable type.", vbOKCancel
    
        'Turn off the Change effect on Variable2.
        changeCasevar = False
        With ChartConfig
            .Variable2.Text = ""
            .Variable2Desc.Text = ""
            .Variable2Uom.Text = ""
        End With
        'Turn back on the Change effect on Variable2.
        changeCasevar = True
        Exit Sub
        '
    End If
    
    ' ... BAD EXAMPLE: DO NOT USE ...
    

    Example of Better Code (I still would have designed it differently)

    ' ...
    If Variable1.Text = Variable2.Text Then
        MsgBox "Variable1 cannot be same as Variable2. Please choose another variable type.", vbOKCancel
    
        'Turn off the Change effect on Variable2.
        changeCasevar = False
    
        Variable2.Text = ""
        Variable2Desc.Text = ""
        Variable2Uom.Text = ""
    
        'Turn back on the Change effect on Variable2.
        changeCasevar = True
        Exit Sub
    End If
    
    
  4. Use Error Handling and Resume to restore Application.ScreenUpdating

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    ' There are quite a few global variables and a pass-by-reference
    ' boolean that should just be the function return value.
    Application.ScreenUpdating = False
    
    iOption = "Single"
    x = ChartPicker.Input_Val_Text
    Call CheckUserRangeInputsAndOutputs(iOption, OkToChart)
    iOption = ""
    
    If OkToChart = True Then
        With ChartConfig
            CreateChartingArrays
        End With
    End If
    
    Application.ScreenUpdating = True
    ' ... BAD EXAMPLE: DO NOT USE ...
      

    Example of Better Code (I still would have designed it differently)

    ' ...
      On Error GoTo ErrorHandler
    
      Application.ScreenUpdating = False
    
      If CheckUserRangeInputsAndOutputs("Single") Then
        ChartConfig.CreateChartingArrays
      End If
    
    Exit_Handler:
      Application.ScreenUpdating = True
      Exit Sub
    
    ErrorHandler:
      MsgBox "Error " & Err.Number & ": " & Err.Description
      Resume Exit_Handler
    
      
  5. Use If, Else If, and Else correctly.

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    If TextBoxMinimum.Text = "" Then
      MsgBox "Must enter Minimum Value before proceeding", vbOK
      Result = False
      Exit Function
    End If
    
    If TextBoxMaximum.Text = "" Then
      MsgBox "Must enter Maximum Value before proceeding", vbOK
      Result = False
      Exit Function
    End If
    ' ... BAD EXAMPLE: DO NOT USE ...
    

    Example of Better Code

    ' ...
    ' For a explicit method, you can directly refer to the controls
    Result = False
    If Not IsNumeric(TextBoxMinimum.Text) Then
      MsgBox "Must enter Minimum Value before proceeding", vbOK
      TextBoxMinimum.SetFocus
    ElseIf Not IsNumeric(TextBoxMaximum.Text) Then
      MsgBox "Must enter Maximum Value before proceeding", vbOK
      TextBoxMaximum.SetFocus
    Else
      Result = True
    End If
    
    ' If you can use a generic method, you can iterate through all of the controls
    ' There are obviously better ways of doing this, especially with .NET
    Dim ValidateControl
    Dim ControlList = Array(TextBox1, TextBox2, TextBox3)
    For Each ValidateControl In ControlList
      If Input_Val_Text = Null Or Input_Val_Text = "" Then
        MsgBox "Must enter Value in " & ValidateControl.Name & " before proceeding", vbOK
        Result = False
        Exit Function
      End If
    Next
    
  6. Avoid using the ActiveSheet, Range, Cells, and other global variables.

    Rather than assuming a position, it is much better to set a variable to the desired Workbook, Worksheet, or Range.
    In particular, if there is any chance the spreadsheet might run under a browser, use Application.ThisWorkbook to ensure the browser evaluates the macro correctly.

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    'NOTE: Arrays start with 1st position as "0", not "1"
    'Thus ColLtrs(0) is not used but placed there as a spacer for letter to column alignment:
    ColLtrs = Array("", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
    
    With ActiveSheet
      Range(ColLtrs(iOutCol) & iOutRow).Select
        
      If UnitSystem = "Metric" Then
        iRow = ActiveCell.Row
        For i = iRow To iLastRow - 1
          currCell = ColLtrs(ActiveCell.Column) & i + 1
    
          If Range(currCell).Value <> "" Then
            'capture the value from original calculatated data
            Range(currCell).Select
    
            strOrigValueCell = ColLtrs(iResultsCol) & ActiveCell.Row
            Result = ConvertUom(Range(strOrigValueCell))
          End If
        Next i
      End If
    End With
    ' ... BAD EXAMPLE: DO NOT USE ...
    

    Example of Better Code

    ' ...
    Dim mySheet As Worksheet
    Dim Value As Variant
    
    Set mySheet = ActiveSheet
    
    For i = iRow To iLastRow - 1
      Value = mySheet.Cells(i, iOutCol).Value
      If Value <> "" Then
        Result = ConvertUom(UnitSystem, Value)
      End If
    Next i
    
    ' To get a column letter/code, do not reinvent the wheel!
    ' Various functions from:
    ' http://www.dicks-blog.com/archives/2004/05/21/column-numbers-to-letters/
    
    Function ColLetter(ColNumber As Long) As String
        On Error Resume Next
        ColLetter = Application.Substitute(Application.ConvertFormula("R1C" & ColNumber, xlR1C1, xlA1, 4), "1", "")
    End Function
    
    Function ColumnLetter(ByVal c As Long) As String
      Dim p As Long
      While c
        p = 1 + (c - 1) Mod 26
        c = (c - p) \ 26
        ColumnLetter = Chr$(64 + p) & ColumnLetter
      Wend
    End Function
    
  7. Avoid using .Select and moving selection in macros unless absolutely.

    Do not change the current sheet or selection unless that is the intention of the macro. In general, don't mess with the user, flip sheets, or other heinous acts and try to leave things the way you found them.

    Example of Bad Code

    ' ... BAD EXAMPLE: DO NOT USE ...
    For i = 1 To 10
      Range("A" & i).Select
      ActiveCell.Value = "Row " & i
    Next i
    ' ... BAD EXAMPLE: DO NOT USE ...
    

    Example of Better Code

    Dim iColumn As Long
    Dim mySheet As Worksheet
    Dim myRange As Range
    
    iColumn = 1
    Set mySheet = ActiveSheet
    
    For iRow = 1 To 10
      Set rng = mySheet.Cells(iRow, iColumn)
      rng.Value = "Row " & iRow
    Next iRow
    

author: Ryan Van Slooten | posted @ Tuesday, May 27, 2008 6:28 PM | Feedback (0)

When aspnet_regsql.exe won't connect


I was building a quick test web site and I was using the aspnet_regsql tool to add membership to a SQLEXPRESS database. At first, I tried:

aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\code\Test\APP_DATA\aspnetdb.mdf"

For some reason, the SqlConnection insisted that it try to create the database and disregarded the full path to the database (note the path in the exception).

SQL Exception:
System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\C:\code\Asp.net\ServerControlTest\App_Data\Database.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Creating the C:\code\Asp.net\ServerControlTest\App_Data\Database database...
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Next I tried the simple commands (credit Scott Mitchell) that worked great:

sqlcmd -S localhost\SQLExpress -Q "EXEC sp_attach_db 'Foobar', N'pathToDBfile'"
aspnet_regsql.exe -S localhost\SQLExpress -d Foobar -E -A all

References

  1. Working with SQL Server 2005 Express Database
  2. Using ASPNET_RegSQL.exe with SQL Express databases in APP_DATA

author: Ryan Van Slooten | posted @ Thursday, April 24, 2008 3:50 PM | Feedback (0)

Unable to log into SQL Server after creating self-signed SSL certificate


In my SharePoint experiments with form-based authentication (FBA), I have been installing self-signed SSL certificates since I am developing in a virtual machine without a certificate authority. Last night, I shut down my virtual machine instead of the usual Suspend operation. This morning, I started the virtual machine but SharePoint wasn't working and said: Cannot connect to the configuration database.

I then tried to open SQL Server Management Console and tried to connect, only to receive the following message:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

I didn't freak out, but I did check the SQL Server Service which was running, and then the SQL Server Log files which looked normal. There was one line which I didn't recognize:

The certificate was successfully loaded for encryption.

This is actually a normal line in the log file, but it was then that I realized that the self-signed certificates I created were interfering with the login process. I searched around for Internet resources, but finally found what I was looking for in the Certificate MMC snap-in configuration. Since I had created multiple SSL certificates, I deleted the unused certificates and checked the encryption and certificate settings in the SQL Server Configuration Manager. After a quick reboot, I was back in business.

References:

  1. Configuring Certificate for User by SSL
  2. How to enable SSL encryption for an instance of SQL Server
  3. SQL Protocols: Troubleshoot Connectivity Issues in SQL Server 2005, Part III

author: Ryan Van Slooten | posted @ Thursday, April 24, 2008 11:29 AM | Feedback (0)

SharePoint FBA Limitations and Options


I recently set up an isolated, single-server SharePoint site with forms-based authentication (FBA) with the hopes that it would eliminate the need to create unrelated user accounts for all of the SharePoint users. The FBA setup and installation went fine and I used the SharePoint FBA tool on codeplex to administer the accounts and users which is great.

Unfortunately I didn't realize that disabling "Client Integration" in the SharePoint Cental Admin / Application Management / Authentication Providers would have such a dramatic effect on usability. Some of the challenges are using SharePoint Designer, the MySites functionality is impacted, inability to export list data to Excel, as well as inability to integrate with Outlook. The composite screen shot below shows the difference between enabled and disabled client integration.

SharePoint Client Integration

After spending quite a bit of time configuring and setting up FBA, I don't want to abandon it immediately. I am looking into two options to use the FBA user store (ASPNETDB) with IIS, basic authentication, and SSL. The two likely candidates are MADAM (Mixed Authentication Disposition ASP.NET Module) and Custom Basic Authentication.

I will post updates as available.

author: Ryan Van Slooten | posted @ Wednesday, April 23, 2008 10:07 AM | Feedback (0)

Just the facts, ma'am


I was looking at Douglas Crockford's 360 blog today and came across a hilarious quote:

Do you write regularly for any publications in this field?

Just the blogs.

In researching the book, did you come across any surprising facts, figures, or statistics that the press might be interested in?

Surprisingly, facts have very little to do with web development.

Douglas Crockford is simply amazing and is the original author of expat (one of the first XML parsing libraries), as well as JSLint, JSMin, and JSON (one of the primary components of AJAX).

P.S. Bonus points for any one identifying the blog post title source.

author: Ryan Van Slooten | posted @ Monday, April 21, 2008 10:15 AM | Feedback (0)

SharePoint FBA Configuration and machine.config


I recently implemented Forms-Based Authentication (FBA) in MOSS 2007 and had it working great. Unfortunately we had to reinstall the system and I actually had more issues getting FBA to work correctly on the reinstall than I did the first time. It turned out that the issue was mixing different implementation methods that counteracted each other. The simplest way to configure FBA is to edit the machine.config file on the server (%WINDOWS%\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config) and change the LocalSqlServer connection string to the ASPNETDB user store or other database with the user information. This takes advantage of the pre-configured AspNetSqlMembershipProvider and AspNetSqlRoleProvider in the <system.web><membership> and <roleManager> sections. The AspNetSqlMembershipProvider and AspNetSqlRoleProvider sections normally include something similar to:

<system.web>
  <membership>
    <providers>
      <add
        name="AspNetSqlMembershipProvider"
        type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        connectionStringName="LocalSqlServer"
        enablePasswordRetrieval="false"
        enablePasswordReset="true"
        requiresQuestionAndAnswer="true"
        applicationName="/"
        requiresUniqueEmail="false"
        passwordFormat="Hashed"
        maxInvalidPasswordAttempts="5"
        minRequiredPasswordLength="7"
        minRequiredNonalphanumericCharacters="1"
        passwordAttemptWindow="10"
        passwordStrengthRegularExpression=""
      />
    </providers>
  </membership>
  <roleManager>
    <providers>
      <add
        name="AspNetSqlRoleProvider"
        connectionStringName="LocalSqlServer"
        applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
      />
      <add
        name="AspNetWindowsTokenRoleProvider"
        applicationName="/"
        type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
      />
    </providers>
  </roleManager>
</system.web>

The important thing to note is the connectionStringName attributes that are configured to use LocalSqlServer. If you change the settings for LocalSqlServer then the membership and roleManager will automatically use the updated setting.

Many SharePoint FBA guides recommend changing the web.config for the web application. This is still a good recommendation however if you choose this implementation method, be sure not to use both methods and edit both the machine.config and web.config. For example, I added the following to my web.config:

<system.web>
  <!-- This caused the SharePoint authentication to fail.
       I already knew these providers were configured in the machine.config,
       so I simply referenced them. -->
  <membership defaultProvider="AspNetSqlMembershipProvider" />
  <roleManager defaultProvider="AspNetSqlRoleProvider" enabled="true" />

What happened is that I tried to login and I would see an "Access Denied" screen. I double-checked the Site collection administrators in the SharePoint Central Administration and it was set properly but I was unable to access any content using FBA. Eventually it dawned on me to remove the configuration from the web.config and simply try the settings in Central Administration under Application Management \ Authentication providers. Once I removed the redundant information in web.config, FBA worked again.

Technorati tags: , ,

author: Ryan Van Slooten | posted @ Monday, April 14, 2008 3:23 PM | Feedback (0)

Overcoming SQL GROUP BY challenges with ROW_NUMBER


In SQL, aggregate functions will return the group by values or the aggregate function results, but it is difficult (or at least harder than it should be) to return the primary key or ROWID. In contrast, most programming languages will return the instance (or a pointer/reference to the instance) when searching for items.

Background

A project manager allocated me several weeks ago on emergency basis to help out with another project that was having difficulties with a SQL Historian system. I ended up developing a nice set of SQL tables, functions, and stored procedures to transfer data from a remote linked server into the Historian. It was actually rather fun to work on as it had several challenging aspects. Of course, after several late nights of development, I don't know how much fun like that I could take!

The key to the data transfer was obtaining the latest data from the remote database and inserting it into the Historian. The remote database was an Oracle database and the Historian was a SQL Server database. My first reaction was to do a simple query with a TOP specification but Oracle doesn't have a TOP function so I started to search for ways to achieve the same result. I came across the ROW_NUMBER() function and it did the trick although it required a sub-query such as:

SELECT  *
FROM    (
    SELECT
        SAMPLE_DAY
    ,   ASSET
    ,   TANK_LEVEL_MM
    ,   TANK_PRES_PSIG
    ,   TANK_VOL_M3
    ,   FEED_FLOW_M3HR
    ,   ROW_NUMBER() OVER (PARTITION BY ASSET ORDER BY SAMPLE_DAY DESC) RN
    FROM    REMOTE_DATA_VIEW_1
    ORDER BY ASSET, SAMPLE_DAY DESC
)
WHERE   RN = 1

It turns out that this query is more powerful than the TOP query as it essentially performs a GROUP BY aggregate (ASSET and order by SAMPLE_DAY descending) without some of the limitations of SQL aggregate functions.

Exploring Aggregate Queries

In a traditional SQL aggregate query, the columns returned by the query have to be in the GROUP BY clause or in an aggregate function. This means that it is difficult to return the primary key for a table because you usually are grouping by a name, location, or other non-unique field. Here is an example:

-- DDL: This table stores the tag names, source, and values. This
-- table is just for demonstrative purposes and does not represent
-- a normalized structure.
CREATE TABLE TAG_VALUE
(
    ValueID             int             IDENTITY    PRIMARY KEY
,   DateTime            datetime        NOT NULL
,   TagName             varchar(50)     NOT NULL
,   Value               numeric             NULL
,   Ignored             bit             NOT NULL    DEFAULT 0
,   Processed           bit             NOT NULL    DEFAULT 0
)
GO

-- Get the most recent entry grouped by tag name
SELECT  TagName
,       MAX(DateTime) AS DateTime
FROM    TAG_VALUE
GROUP BY TagName

If you try to put the primary key as a return column, you will get an error. Unfortunately this means you end up having an ugly subquery and potentially expensive (hopefully you have indices on the matching columns as well), such as:

SELECT  ValueID
FROM    TAG_VALUE   AS V1
    JOIN (

        SELECT  TagName
        ,       MAX(DateTime) AS DateTime
        FROM    TAG_VALUE
        GROUP BY TagName

    )               AS V2
    ON (    (V1.TagName  = V2.TagName)
        AND (V1.DateTime = V2.DateTime))

Using ROW_NUMBER

The ROW_NUMBER function has the powerful feature of specifying the PARTITION BY which provides some of the same functionality that GROUP BY would perform but with less hassle. For example, the following query analyzes the TAG_VALUE table and looks for duplicate TagName rows. It then marks the "old" values as "Ignored" and leaves the newest entry alone.

UPDATE  TAG_VALUE
SET     Ignored = 1
FROM    TAG_VALUE AS V1
    JOIN (
        SELECT  ValueID
        ,       ROW_NUMBER() OVER (PARTITION BY TagName ORDER BY DateTime DESC) AS RN
        FROM    TAG_VALUE
        WHERE   TagName IN (
            SELECT DISTINCT TagName
            FROM        TAG_VALUE
            GROUP BY    TagName
            HAVING      COUNT(TagName) > 1
            )
    ) AS V2
    ON (V1.ValueID = V2.ValueID)
WHERE   RN > 1

Conclusion

So, in conclusion, ROW_NUMBER provides an easier method of returning the actual row so you can perform updates, deletes, or return the primary key.


Sample SQL Code

/* ====
NOTE: The table structure resembles a database design that I have seen used.
I did not design the table structure and it is intentionally flat. Originally
this example used a remote query to allow SQL Server to query ORACLE, but for
the sample it is all in SQL Server. With very minor changes it works in Oracle
as well.
==== */

-- DDL: This table simulates a remote linked server
CREATE TABLE REMOTE_DATA_VIEW_1
(
    SAMPLE_DAY          datetime
,   ASSET               varchar(50)
,   TANK_LEVEL_MM       numeric
,   TANK_PRES_PSIG      numeric
,   TANK_VOL_M3         numeric
,   FEED_FLOW_M3HR      numeric
)
GO

-- Sample data
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-02-04', 'ASSET_01', 1200.0, 18.5, 100.0, 150.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-02-01', 'ASSET_01', 1100.0, 17.5,  90.0, 145.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-01-25', 'ASSET_01', 1000.0, 16.5,  80.0, 155.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-02-03', 'ASSET_02',  800.0, 19.5, 110.0, 160.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-02-02', 'ASSET_02',  750.0, 17.0,  95.0, 165.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-02-04', 'ASSET_03', 1500.0, 18.0, 100.0, 170.0)
INSERT REMOTE_DATA_VIEW_1 VALUES ('2008-01-31', 'ASSET_04', 1350.0, 19.0,  90.0, 135.0)
GO


-- Return the raw data
SELECT
    SAMPLE_DAY
,   ASSET
,   TANK_LEVEL_MM
,   TANK_PRES_PSIG
,   TANK_VOL_M3
,   FEED_FLOW_M3HR
,   ROW_NUMBER() OVER (PARTITION BY ASSET ORDER BY SAMPLE_DAY DESC) RN
FROM    REMOTE_DATA_VIEW_1
ORDER BY ASSET, SAMPLE_DAY DESC

/* ====
SAMPLE_DAY              ASSET    TANK_LEVEL_MM TANK_PRES_PSIG TANK_VOL_M3 FEED_FLOW_M3HR RN
----------------------- -------- ------------- -------------- ----------- -------------- --
2008-02-04 00:00:00.000 ASSET_01 1200          19             100         150            1
2008-02-01 00:00:00.000 ASSET_01 1100          18             90          145            2
2008-01-25 00:00:00.000 ASSET_01 1000          17             80          155            3
2008-02-03 00:00:00.000 ASSET_02 800           20             110         160            1
2008-02-02 00:00:00.000 ASSET_02 750           17             95          165            2
2008-02-04 00:00:00.000 ASSET_03 1500          18             100         170            1
2008-01-31 00:00:00.000 ASSET_04 1350          19             90          135            1
==== */


-- Use a simple embedded query
SELECT  *
FROM    (
    SELECT
        SAMPLE_DAY
    ,   ASSET
    ,   TANK_LEVEL_MM
    ,   TANK_PRES_PSIG
    ,   TANK_VOL_M3
    ,   FEED_FLOW_M3HR
    ,   ROW_NUMBER() OVER (PARTITION BY ASSET ORDER BY SAMPLE_DAY DESC) AS RN
    FROM
        REMOTE_DATA_VIEW_1
    )   DATA
WHERE   RN = 1

/* ====
SAMPLE_DAY              ASSET    TANK_LEVEL_MM TANK_PRES_PSIG TANK_VOL_M3 FEED_FLOW_M3HR RN
----------------------- -------- ------------- -------------- ----------- -------------- --
2008-02-04 00:00:00.000 ASSET_01 1200          19             100         150            1
2008-02-03 00:00:00.000 ASSET_02 800           20             110         160            1
2008-02-04 00:00:00.000 ASSET_03 1500          18             100         170            1
2008-01-31 00:00:00.000 ASSET_04 1350          19             90          135            1
==== */


-- This is an uglier version of the query above but provided for comparison.
-- Return only the most recent entry using SAMPLE_DAY and ASSET as keys. On a production
-- table, there should be a real primary key or ROWID that you would use instead.
SELECT
    DATA.SAMPLE_DAY
,   DATA.ASSET
,   DATA.TANK_LEVEL_MM
,   DATA.TANK_PRES_PSIG
,   DATA.TANK_VOL_M3
,   DATA.FEED_FLOW_M3HR
FROM    (

        SELECT
            SAMPLE_DAY
        ,   ASSET
        ,   ROW_NUMBER() OVER (PARTITION BY ASSET ORDER BY SAMPLE_DAY DESC) AS RN
        FROM
            REMOTE_DATA_VIEW_1

    )   AS  REMOTE_QUERY
    JOIN    REMOTE_DATA_VIEW_1  AS DATA
        ON (    (REMOTE_QUERY.SAMPLE_DAY  = DATA.SAMPLE_DAY)
            AND (REMOTE_QUERY.ASSET       = DATA.ASSET) )

WHERE   REMOTE_QUERY.RN = 1
ORDER BY DATA.ASSET, DATA.SAMPLE_DAY DESC

/* ====
SAMPLE_DAY              ASSET    TANK_LEVEL_MM TANK_PRES_PSIG TANK_VOL_M3 FEED_FLOW_M3HR
----------------------- -------- ------------- -------------- ----------- --------------
2008-02-04 00:00:00.000 ASSET_01 1200          19             100         150
2008-02-03 00:00:00.000 ASSET_02 800           20             110         160
2008-02-04 00:00:00.000 ASSET_03 1500          18             100         170
2008-01-31 00:00:00.000 ASSET_04 1350          19             90          135
==== */
GO

Technorati tags: , ,

author: Ryan Van Slooten | posted @ Monday, April 14, 2008 2:35 PM | Feedback (0)

Automatically setting the Specified property in WSDL Generated files


The wsdl.exe tool with Visual Studio can be used to manually generate a code file to use with a project. If you use the nillable or minOccurs attributes in the XSD though, the wsdl.exe tool generates a boolean 'Specified' field that must be set to true in order to transmit the data through the web service. For example:

WSDL File (myfile.wsdl)

<xs:element name="duedate" type="xs:dateTime" nillable="1" minOccurs="0" maxOccurs="1" />

Generated CS File (wsdl.exe myfile.wsdl)

// ...

private System.Nullable<System.DateTime> duedateField;

private bool duedateFieldSpecified;

// ...

/// <remarks/>
[System.Xml.Serialization.XmlElementAttribute(IsNullable=true)]
public System.Nullable<System.DateTime> duedate {
    get {
        return this.duedateField;
    }
    set {
        this.duedateField = value;
    }
}

/// <remarks/>
[System.Xml.Serialization.XmlIgnoreAttribute()]
public bool duedateSpecified {
    get {
        return this.duedateFieldSpecified;
    }
    set {
        this.duedateFieldSpecified = value;
    }
}

"Breaking Change"

What I wanted was simply add the line to the 'duedate' setter to check if the newly assigned value was null or not (the assumption is that a non-null value would mean the field is 'specified'):

    set {
        this.duedateField = value;
        this.duedateFieldSpecified = (value != null);
    }

I looked around for a solution, but all I found a very good suggestion to add this functionality to the wsdl.exe tool. Unfortunately the status was "Closed (Won't Fix)" and the comment was:

Thank you for the suggestion.
This would be nice enhancement, but unfortunately this also is a breaking change: in the previous versions of the .net framework setting value of 'xxx' field did not toggle the xxxSepcified value, so if we change this runtime behavior of some existing applications may change and will require user code change to run the same way as before.

On the contrary, I contend it would not be difficult or breaking to simply add the functionality through optional parameters, such as:

wsdl.exe /autospecified:1 myfile.wsdl

The optional parameters would not affect default operation nor break existing code, but instead we have to deal with a short-sighted decision.

Quick and Dirty Solution

I decided to write a quick script to try and automatically generate the extra line of code for each setter. I decided to use awk (there are a number of sources for a Windows awk/gawk - cygwin, unxutils, and more) as it is very quick to implement and script. The following script is the result:

#!/usr/bin/awk -f

# This is a quick and dirty script to add automatically set the
# 'myFieldSpecified' variables generated from the Visual Studio
# wsdl.exe tool. The script searches for System.Nullable types 
# (myField) and verifies a specified variable exists 
# (myFieldSpecified). It then searches for the setter and adds
# the line: this.myFieldSpecified = (value != null);
#
# Copyright (C) 2008 Ryan Van Slooten
#
# Version:
#   1.0     2008-04-01      Initial release
#

BEGIN { FS = " "; }

# Match the nullable types in the file: int, DateTime, etc.
/[ \t]*private System.Nullable/ {
    print $0;

    # Add name of field to nullable array
    name = substr($3, 1, length($3)-1);
    nullable[name] = "";
    next; 
}

# Match the xxxFieldSpecified variables
/[ \t]*private .*FieldSpecified;/ {
    print $0;

    # Increment value of 'specified' null field to nullable array
    name = substr($3, 1, length($3)-10);
    specified = substr($3, 1, length($3)-1);
    nullable[name] = specified;
    next; 
}

# Match the field setter and check if it is nullable and specified
/[ \t]*this.* = value;/ {
    print $0;

    # Check if field is nullable and has a specified property
    name = substr($1, 6);
    if (name in nullable) {
        if (length(nullable[name]) > 0) {
            # Fix lost indentation level
            indent = substr($0, 1, index($0, $1)-1);

            # Adjust the xxxFieldSpecified value
            $1 = $1 "Specified";
            $3 = "(" substr($3, 1, length($3)-1) " != null);";
            print indent $0;

            # Remove element from array
            delete nullable[name];
        }
    }
    next;
}

# default line handler
{ print $0; }

kick it on DotNetKicks.com

Technorati tags: , , ,

author: Ryan Van Slooten | posted @ Tuesday, April 01, 2008 3:51 PM | Feedback (0)

Have I seen this Luggage before?


I was reading Coding Horror today about "Just a Little Bit of Software History Repeating" about the failures of the Denver International Airport and London Heathrow Airport automated luggage systems. Denver's failed baggage system was designed from the inception of the airport and London Heathrow's T5 was installed with the construction of the new T5 terminal. The situations are quite similar to the "complete rewrite" scenario that developer's are frequently tempted by.

It is difficult enough when other people (proposals, marketing, managers, etc.) underestimate the time required for a task or project, but unfortunately developers often vastly underestimate the time required as well. This is well documented in Steve McConnell's classic "Code Complete" and other books as well.

bttf_radsuit

George McFly: Lorraine, my density has bought me to you.
Lorraine Baines: What?
George McFly: Oh, what I meant to say was...
Lorraine Baines: Wait a minute, don't I know you from somewhere?
George McFly: Yes. Yes. I'm George, George McFly. I'm your density. I mean... your destiny.

Learn from the past! Don't be someone's density!

author: Ryan Van Slooten | posted @ Monday, March 31, 2008 12:51 PM | Feedback (0)

Float Back In Time


FloatDebugUpdate01Well it might not be as old as "Ye Olde English" but every once in a while you have to brush the cobwebs off of binary numbers and hexadecimal formats. A coworker had a problem converting numerical constants from a PLC editor and for some reason the programmers decided to print floating-point constants in the editor as integer numbers instead of sensible real numbers. For example, the software would list 1092616192 instead of 10.0! My co-worker had found a web site tool to convert the integer numbers into floating-point but then hit a stumbling block when he encountered large negative integers, such as -1088841318 (which is -0.6). My first thought was to take the two's complement of the number to convert it back to a positive integer so he could continue using the web site tool he had found.

Fortunately, the old floating-point debugger tool I wrote a long time ago (1999) came to the rescue. It was quicker to enter the numbers into the application rather than the web site, but the only problem was that the entry area to enter the numbers only accepted floating-point and hexadecimal. So I created an integer entry text box and also placed a 1's and 2's complement button on the form for ease of use. He was able to use this tool to finish the conversion.

After discussing the solution with another co-worker, we delved into the difference of 1's versus 2's complement. My initial contention since the difference was either +0 or +1, it only affected the final bit and would have no real impact to the result. My co-worker then reminded me of the special cases where adding 1 to the result would cause the 1 to be carried up in the number and in some cases even into the exponent. In order to debug these cases, I took the core code from the VB application and created an Excel spreadsheet that could duplicate the functionality and serve as a test bed for the fringe cases.

The test cases that I generated showed that the carry was indeed negligible as I originally postulated. Here are some examples:

Decimal Sample Hex 1's comp 2's comp 1's float 2's float %Diff
-1098907648 0xBE800000 0x417FFFFF 0x41800000 15.99999905 16 0.000006%
-1132462080 0xBC800000 0x437FFFFF 0x43800000 255.9999847 256 0.000006%
-1140850688 0xBC000000 0x43FFFFFF 0x44000000 511.9999695 512 0.000006%

FloatConvert The bottom line is that the nature of the IEEE floating-point number minimizes the effect of the 2's complement carry. In other words, adding one to the value does not cause a big change in the number specifically because the mantissa is really the sum of the fractional powers of 2 (2^-2 + 2^-3 + 2^-4 + ... + 2^-23). In the case where the mantissa is 0x3FFFFF (2^-3 + ... + 2^-23) adding 1 to the mantissa results in 0x400000 (2^-2). The actual difference in 0x3FFFFF and 0x40000 when summing the inverse powers of 2 is (4194302 / 8388608) compared to (4194304 / 8388608) which is 0.4999998 versus 0.5!

Technorati tags:

author: Ryan Van Slooten | posted @ Wednesday, March 05, 2008 12:14 AM | Feedback (0)

Resizing Virtual Machines Guide with VMware


Lately I've been setting up a lot of virtual machines. It is time-consuming and frustrating but the end results are extremely useful. I discovered that using Windows 2003 R2 consumes a lot of hard disk space. I started with an 8 Gb virtual hard drive and before I knew it I ran out of space. Of course, depending on the VM I needed Microsoft Office, or Visual Studio, or SharePoint, or SQL Server, or BizTalk, or all of the above. I developed my base virtual machine first and then sysprep-ped it and then started installing all of the requisite software.

Once I realized there was no way the VM would survive with limited disk space, I set out to increase the virtual disk capacity. Unfortunately you can't resize the partition through Windows but you need to download a partition editor to do this.

Step 1: Resize the virtual hard disk with "vmware-vdiskmanager"

Open a command prompt (I like Console2) and change the directory to your VM image location. Use the "vmware-vdiskmanager -x" command to increase the hard disk size. I preferred to add the VMware directory to the path so I didn't have excessively long commands. For example, the command I used was:

SET PATH=%PATH%;C:\Program Files\VMware\VMware Server

vmware-vdiskmanager -x 16GB win2k3-sql.vmdk

vmware-vdiskmanager-grow

When the disk manager is finished resizing the disk, it looks like the following:

vmware-vdiskmanager-done

For reference, the help text from "vmware-vdiskmanager --help" is at the end of this post.

Step 2: Download GNU Partition Editor (GPartEd)

There were several recommendations on the VMware web site, but the GNU Partition Editor seemed to be the smallest and easiest way to edit the partition table.

Step 3: Mount the gparted ISO in VMware

Select the VM\Settings menu item

vmware-settings

vmware-console

Step 4: Reboot the VM and select the Boot Menu

You have to act fast when you reboot the VM. Click on the main window area to set the window focus and then press 'Esc' key to open the boot menu and then from the CD-ROM Drive. You need to click on the window to set focus because at this point in the boot process, the vmtools isn't loaded and can't provide the normal integration features.

gparted-boot

Step 5: Resize the partition

Select the primary partition and right-click on the partition and select "Resize".

gparted-main

Expand the partition

gparted-resize

When it is finished, it displays the results

gparted-results

Step 6: Reboot the VM

The VM will most likely check the disk since the dimensions have changed.

win2k3-chkdsk

Once it has finished, you should be done. Congratulations!


Reference

Here is the "vmware-vdiskmanager --help" output:

VMware Virtual Disk Manager - build 56528.
Usage: vmware-vdiskmanager.exe OPTIONS diskName | drive-letter:
Offline disk manipulation utility
  Options:
     -c                   : create disk; need to specify other create options
     -d                   : defragment the specified virtual disk
     -k                   : shrink the specified virtual disk
     -n <source-disk>     : rename the specified virtual disk; need to
                            specify destination disk-name
     -p                   : prepare the mounted virtual disk specified by
                            the drive-letter for shrinking
     -q                   : do not log messages
     -r <source-disk>     : convert the specified disk; need to specify
                            destination disk-type
     -x <new-capacity>    : expand the disk to the specified capacity

     Additional options for create and convert:
        -a <adapter>      : (for use with -c only) adapter type (ide, buslogic or lsilogic)
        -s <size>         : capacity of the virtual disk
        -t <disk-type>    : disk type id

     Disk types:
        0                 : single growable virtual disk
        1                 : growable virtual disk split in 2Gb files
        2                 : preallocated virtual disk
        3                 : preallocated virtual disk split in 2Gb files

     The capacity can be specified in sectors, Kb, Mb or Gb.
     The acceptable ranges:
                           ide adapter : [100.0Mb, 950.0Gb]
                           scsi adapter: [100.0Mb, 950.0Gb]
        ex 1: vmware-vdiskmanager.exe -c -s 850Mb -a ide -t 0 myIdeDisk.vmdk
        ex 2: vmware-vdiskmanager.exe -d myDisk.vmdk
        ex 3: vmware-vdiskmanager.exe -r sourceDisk.vmdk -t 0 destinationDisk.vmdk
        ex 4: vmware-vdiskmanager.exe -x 36Gb myDisk.vmdk
        ex 5: vmware-vdiskmanager.exe -n sourceName.vmdk destinationName.vmdk
        ex 6: vmware-vdiskmanager.exe -k myDisk.vmdk
        ex 7: vmware-vdiskmanager.exe -p m:
              (A virtual disk first needs to be mounted at m:
               using the VMware Diskmount Utility.)
kick it on DotNetKicks.com

author: Ryan Van Slooten | posted @ Tuesday, February 19, 2008 12:05 PM | Feedback (0)

Installing WPF in Visual Studio 2005 with .NET 3.0 sp1


In an effort to stay up to date with the changing technology and languages, I tried to install the WPF extensions for Visual Studio 2005 on my computer only to be rebuffed by Windows Installer. Here is the error message:

Setup has detected that a prerequisite is missing. To use Visual Studio 2005 extensions for .NET Framework 3.0 (WCF &amp; WPF), November 2006 CTP you must have the .NET Framework 3.0 runtime installed. Please install the .NET Framework 3.0 runtime and restart setup

PhotoDemo I saw several recommendations to uninstall .NET 3.0 sp1 and then install the extensions and finally reinstall the service pack, back I wasn't interested in that. Fortunately, someone posted the solution in the MSDN forums. The key is to tell the installer to override the prerequisites.

msiexec /i vsextwfx.msi WRC_INSTALLED_OVERRIDE=1

author: Ryan Van Slooten | posted @ Monday, January 28, 2008 9:44 PM | Feedback (0)

SOAP Acronym Doesn't Mean Anything


Love_symbol_2 Well, you learn something new every day. I didn't know that the acronym formerly-known as SOAP, aka Simple Object Access Protocol, is no longer an acronym and is just a protocol and means nothing.

Note:
In previous versions of this specification the SOAP name was an acronym. This is no longer the case.

Source: SOAP Version 1.2 Part 1

author: Ryan Van Slooten | posted @ Monday, January 21, 2008 2:14 PM | Feedback (0)

SVG Building Map (Part 2)


Note: Eventually all of the Building Map samples will be converted to Silverlight for comparison.

In the last installment, I showed a simple example of a building map with static personnel information that worked with both Internet Explorer and FireFox. In this post I will add dynamic building information by generating JavaScript from SQL Server data.

Step 1: Define a SQL table

I defined a simple SQL table to store the information for the building occupants. The schema will become more sophisticated as the samples progress, so I chose to keep it simple for the time being.

CREATE TABLE [dbo].[BUILDING_ENTITY] (
    [EntityID]          [uniqueidentifier]  PRIMARY KEY
        CONSTRAINT [DF_BUILDING_ENTITY_EntityID]  DEFAULT (newid())
,   [Name]              [nvarchar](50)      NOT NULL
,   [Location]          [nvarchar](50)          NULL
,   [Phone]             [nvarchar](50)          NULL
,   [Email]             [nvarchar](50)          NULL
)

Step 2: Generate the SQL data

I modified the simple Python script from the last sample to generate the SQL CREATE and INSERT statements.

def makeemail(s):
    return s.lower().replace(' ','.') + '@looneytunes.null'
    
def makedict(name, num):
    return { 'name':name, 'room':str(num), 'email':makeemail(name) }
    
def makedata(names, num, f):
    for name in names:
        print f % makedict(name, num)
        num = num + 1

n = ["Bugs Bunny",
    "Marvin the Martian",
    "Tweety",
    "Taz",
    "Daffy Duck",
    "Foghorn Leghorn",
    "Miss Prissy",
    "Chickenhawk",
    "Dawg",
    "Wile E Coyote",
    "Road Runner",
    "Speedy Gonzalez",
    "Pepe Le Pew",
    "Penelope",
    "Porky Pig",
    "Elmer Fudd",
]

s = """
INSERT BUILDING_ENTITY (Name, Location, Network, Phone, Email)
VALUES ('%(name)s', '%(room)s', '1-800-555-0%(room)s', '%(email)s' )
"""

tabledef = """
if exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[BUILDING_ENTITY]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[BUILDING_ENTITY]
GO

CREATE TABLE [dbo].[BUILDING_ENTITY] (
	[EntityID]          [uniqueidentifier]  PRIMARY KEY
        CONSTRAINT [DF_BUILDING_ENTITY_EntityID]  DEFAULT (newid())
,	[Name]              [nvarchar](50)      NOT NULL
,	[Location]          [nvarchar](50)          NULL
,	[Phone]             [nvarchar](50)          NULL
,	[Email]             [nvarchar](50)          NULL
)
GO
"""

# Main
print tabledef
makedata(n, 100, s)

Step 3: Generate the data in an ASP.NET page

Since we are dynamically generating the data, we need to open a connection to the database and then iterate through all of the data. Disclaimer: For the sample and in the interest of simplicity, I used inline code in the ASPX page and I also used a straight SQL query in the code.

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        string floorDataScript = @"
<script language='javascript' type='text/javascript'>
var g_rmRawData = [
${floorData}
];

var g_rmData = [];

function loadRoomData()
{
    for (var i = 0; i < g_rmRawData.length; ++i)
        g_rmData['Rm_' + g_rmRawData[i].room] = g_rmRawData[i];
}

loadRoomData();
</script>
";
        string personData = string.Empty;

        using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(
            System.Configuration.ConfigurationManager.ConnectionStrings["buildingConnectionString"].ConnectionString))
        using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(
            "SELECT Location, Name, Phone, Email FROM BUILDING_ENTITY",
            connection))
        {
            connection.Open();
            System.Data.IDataReader buildingInfo = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            while (buildingInfo.Read())
            {
                if (personData.Length > 0)
                    personData += System.Environment.NewLine + ",";
                
                personData += string.Format("{{ room : '{0}', name : '{1}', phone : '{2}', email : '{3}' }}", 
                    buildingInfo.GetString(0),
                    buildingInfo.GetString(1),
                    buildingInfo.GetString(2),
                    buildingInfo.GetString(3)
                );
            }
        }

        floorDataScript = floorDataScript.Replace("${floorData}", personData);
        ClientScript.RegisterClientScriptBlock(this.GetType(), "floorData", floorDataScript);
    }
}

Conclusion

The move to dynamic data is important as we do not have to manually edit the JavaScript data file and we can now maintain the occupant information from a web page. Future samples will add support for computer resources, specifically printers as I am always wondering where all of the printers in my building are located.

kick it

Technorati tags: , ,

author: Ryan Van Slooten | posted @ Friday, January 11, 2008 9:18 PM | Feedback (0)