One of my very favorite holidays is Halloween. I've always loved dressing up as something really gross or scary and freaking people out. Every year I throw a costume party the Saturday before Halloween. This year I was a demon and Alan was the devil. Sara even came down for the weekend and was Trinity from the Matrix with a side of clown because she was busting out animal balloons all night! My mom won a prize for most creative as she was Queen Elizabeth and VERY realistic! She stole a wig from my grandmother so I think that was what was creative about it! ;-)

I won for scarriest, which is good because that's what I was going for. If I had dressed up all sexy or cute and won scarriest I would be scared myself. Luckily the two hours of makeup I spent was well worth it. Here's a picture of Sara on the left and me on the right.

I also spent about two weeks building the haunted house downstairs (that's actually how I hurt my thumb). I still have it all together and will turn it all back on tonight to try and lure the trick-or-treaters into the maze. We'll see if they want the candy bad enough ;-). I'd stick a picture up here but I haven't gotten them all from my friend who was in charge of photos so stay tuned for that. I think a video would do it more justice so I may make one tonight.

Enjoooowaaaaaahhhaaahaaahhaaaaoooyyyy!

One really handy thing to do with relational data is to import and export it to Excel. This can be a total snap using LINQ to XML in Visual Basic 2008. For example, let's say I want to import data from an Excel spreadsheet into a local database file like SQL Compact Edition. I also want to be able to export that data back into an Excel spreadsheet, basically allowing me to edit the data from either my application or Excel.

The trick really is getting the format of the Excel spreadsheet as XML and using that sheet as a template to populate your data. First open Excel (for this example I'll use the Excel 2003 format so you can use Excel 2007 or 2003 to follow along). Next let's enter some column headers, for my example I have a list of customers I want to import/export so the fields I'll want are Abbrev, Name, Phone, Country. It's also helpful to enter one line of dummy data (you'll see why in a second). So here' what we've got:

Now what I want to do is get all the customers stored in my little SQL CE local database into this spreadsheet.

(As a side note, you can easily add a SQL CE local database file to your project by selecting the "Local Database" new item template in Visual Studio 2008. You can easily access the data in SQL CE sdf files by adding a new datasource (Data --> Add New Datasource), adding a new connection, and change the data source to "Microsoft Compact SQL Server 3.5" then locate or create the .sdf file. Then you can create typed Datasets like normal.)

So I have a form where I just dragged my CustomersDataSet that I created from my local database file onto it as a DataGridView. This also sets up the BindingSource and BindingNavigator toolstrip like we'd expect. I added a couple buttons to the toolstrip one called "Import" and one called "Export".

Now in order to get the Excel spreadsheet into an XML format you can simply "Save As.." and select "XML Spreadsheet 2003 (.xml)" as the file type. Now navigate to that file and open it with notepad, select and copy all the text into your clipboard. Open up your Visual Basic program, type "Dim sheet =" and then paste the contents of your clipboard directly into the editor. It should look something like this (I collapsed some of the sections we're going to ignore):

Next we need to import the namespaces in the spreadsheet. This is necessary so that when we create and query our XML, Visual Basic will know the correct namespaces we're working with. So at the top of code file for our form we'll add:

Imports <xmlns="urn:schemas-microsoft-com:office:spreadsheet">

Imports <xmlns:o="urn:schemas-microsoft-com:office:office">

Imports <xmlns:x="urn:schemas-microsoft-com:office:excel">

Imports <xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
Imports <xmlns:html="http://www.w3.org/TR/REC-html40">

Now we can write a query that will take all of the customers in our SQL CE database file (technically the typed DataSet that was created for us) and populate the spreadsheet. You should see your dummy data in the XML above. Cut that out (ctrl-X) because we're going to replace that with all our customers using embedded expressions like so:

I'm just selecting the data inside my typed Dataset which is populated in the load of the form, making sure not to grab any deleted rows or rows where the abbreviation is NULL. The customers variable above becomes a collection of XElements (IEnumerable(Of XElement)) and we simply pop that back into the sheet, replacing the dummy line we removed:

<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount=<%= customers.Count + 1 %> x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">

<Row ss:StyleID="s62">

               <Cell><Data ss:Type="String">Abbrev</Data></Cell>

               <Cell><Data ss:Type="String">Name</Data></Cell>

               <Cell><Data ss:Type="String">Phone</Data></Cell>

               <Cell><Data ss:Type="String">Country</Data></Cell>

       </Row>

       <%= customers %>

</Table>

Also notice that I had to adjust the ss:ExpandedRowCount attribute as well to include the count of our customers plus one for the header row. Now we can write the results out to disk and start Excel:

sheet.Save("C:\Customers.xml")

Process.Start("Excel.exe", "C:\Customers.xml")

Cool!

Okay so now we want to be able to import this data as well. If any rows were added or edited we want that data back in our database. I'm going to use the Abbrev field as the unique identifier of our data. The query we need to write will group join (outer join) the current (non-deleted) rows in our CustomerDataSet to the rows here in the spreadsheet. That way if the resulting items in the collection contain a group (of one CustomerDataRow in this case), then we know that the CustomerDataRow needs to be updated, otherwise we know it's a new customer that needs to be added. We also need to skip the first row because that's our header row in the spreadsheet, and I'm doing an extra check for empty cells.

Dim sheet = XDocument.Load("C:\Customers.xml")

'Skip the first row because that is the header row in the spreadsheet.

'Notice also that we're eliminating deleted rows from the group join.

'We'll use the existence of the group collection to determine if the

' row needs to be added to the database.

Dim customers = From xmlCustomer In sheet...<Row> _

                   Let Abbrev = xmlCustomer.<Cell>(0) _

                   Let Name = xmlCustomer.<Cell>(1) _

                   Let Phone = xmlCustomer.<Cell>(2) _

                   Let Country = xmlCustomer.<Cell>(3) _

                Where _

                   Abbrev IsNot Nothing AndAlso _

                   Name IsNot Nothing AndAlso _

                   Phone IsNot Nothing AndAlso _

                   Country IsNot Nothing _

                Group Join customer In _

                    (From row In Me.CustomersDataSet.Customers _

                       Where row.RowState <> DataRowState.Deleted _

                             AndAlso Not row.IsAbbrevNull _

                       Select row) _

                    On customer.Abbrev Equals Abbrev _

                    Into Group _

                Select _

                    Abbrev = Abbrev.Value, _

                    Name = Name.Value, _

                    Phone = Phone.Value, _

                    Country = Country.Value, _

                    Group _

                Skip 1

'Add or update the customer row depending on if the

' group collection exists.

For Each customer In customers

    Dim r As CustomersDataSet.CustomersRow

    If customer.Group.Count = 0 Then

        'Add new row ands set the customer abbreviation.

        ' This field is used to determine uniqueness of the data

        r = Me.CustomersDataSet.Customers.NewCustomersRow()

        r.Abbrev = customer.Abbrev

    Else

        r = customer.Group(0)

    End If

    r.Name = customer.Name

    r.Phone = customer.Phone

    r.Country = customer.Country

    If r.RowState = DataRowState.Detached Then

        Me.CustomersDataSet.Customers.AddCustomersRow(r)

    End If

Next

Now we can make changes to the spreadsheet, import the data into our local database, or make changes to our local database and export those changes to Excel. I've attached the complete example built with Visual Basic 2008. You'll also need Excel 2003 or higher to view the spreadsheet. It's worth reiterating that you don't actually need Excel to create the spreadsheet in Visual Basic via code as long as you have the XML format, you just need it if you want to open it in Excel later. Remember we're not using automation, we're just creating XML in a specific format.

Enjoy!

A couple quick tips here when using XML literals in your Visual Basic programs.

#1: I've started using XML literals everywhere I had been using multiline strings and text merging (as I explained in this post). For instance, I started using them in my ToString methods of my classes:

Public Overrides Function ToString() As String

    Return <string>

ID       : <%= Me.ID %>

Name     : <%= Me.LastName %>, <%= Me.FirstName %>

Address  : <%= Me.Address %>

         : <%= Me.City %>, <%= Me.State %> - <%= Me.Zip %>

           </string>.Value

End Function

#2: It's also sometimes handy to pass an XElement as a parameter if you need dynamic parameters, or were using your own custom parameter objects previously:

Dim id = 1

Dim name = "Beth"

DoIt(<param>

         <customer>

             <id><%= id %></id>

             <name><%= name %></name>

         </customer>

     </param>)

Private Sub DoIt(ByVal param As XElement)

    Dim customers = From customer In param...<customer> _

            Select New Customer With _

            {.ID = customer.<id>.Value, _

             .FirstName = customer.<name>.Value}

    For Each c In customers

        Console.WriteLine(c.ToString())

    Next

End Sub

Enjoy,
-B

I'll be speaking at QCon in SF on Friday morning, November 9th on LINQ to XML in Visual Basic 9. I'll be with my buddy Rob Windsor on the Bleeding Edge.NET track and it should be a lot of great content. Please drop by and say HI if you're in town and don't forget to check out all the other great sessions.

Oh and if you're interested in hearing me speak about LINQ for free, swing by the Silicon Valley Code Camp tomorrow morning!

Enjoy,
-B

No this isn't a Halloween "Boo" as in ghost, but rather a big boo boo or small-ish injury to my thumb. :-(

I ended up in the emergency room yesterday because I slammed my thumb in a folding ladder. I knew the instant it happened it was bad news. It felt crushed, smashed, okay I thought it was falling off. But I knew it wasn't bad enough to call 911 or anything even though I felt like I was going to pass out. Especially after I looked at it under the running water in the sink -- the red water was not a good sign. I was with my friend Robin so she played ambulance driver and nurse for me the rest of the day.

I haven’t been to the emergency room in a long time. I forgot how long they make you wait if you’re not dying. It hurt pretty bad (I told them a 6 on the pain/smiley-sad-face chart) but after X-Ray, no broken bones!!! YAY MILK. I’ll probably lose the nail though… but I figure I already bite mine so badly no one will notice. ;-). I have it on ice and the doc gave me some nice pain-killers so I’m doing just swell. Code camp should be interesting this weekend. :-)

But you know, typing with one hand is hard! Luckily it’s my left hand. I have an even greater respect now for people with disabilities using computers. For instance this post took 38 minutes for me to type with one hand. I'm a terrible typist anyway (I have to look at the keys) but this is ridiculous.

If you dare to look at it, click here. Don't tell me I didn't warn you.

Here are some thoughtful responses from my team when I told them about it:

"Thumbs Up!"

"If you doodle on it, is it a thumbnail sketch?"

"If you’re sailing is it a thumb-tack?"

"If you use that hand while operating your car, would that be called a thumb drive?"

"Rule Of Thumb: Be more careful"

Thanks guys! :-P

Recently I received a question from a customer asking how to get an Interop User control developed with the Interop Forms Toolkit to be able to read the application configuration settings (app.config) in order to call a WCF service. (If you're unfamiliar with how to develop Interop user controls you can read these posts and watch these videos.) Since Interop user controls are compiled into library (.dll) assemblies the configuration file that is generated is named after the assembly, not the VB 6 application, which means that the Interop User Control isn't able to read the settings from this file.

The trick to solve this is simple, just rename the .dll.config file generated in your bin folder to the name of your VB 6 EXE and place that file in the same folder as your VB 6 EXE. Once you do that, the WCF service client configuration settings will be read properly at runtime.

You can easily set this to happen automatically using Build Events. Just double-click on My Project in the Solution Explorer to open the project properties, select the Compile tab, click the "Build Events" button, then click "Edit Post-build...". In the window type the copy command you want to execute. I.e.

copy /Y "$(TargetDir)$(ProjectName).dll.config" "$(ProjectDir)..\..\VB6App\Project1.exe.config"

Now every time the app.config file is modified in your Interop user control project, the .config settings will get renamed and copied to the right place, your VB6 application folder.

But what about using My.Settings? My.Settings allow you to easily store application-scoped and user-scoped settings and access them easily in your .NET programs. They are also stored in the application config file. But just renaming the file in this case doesn't work alone. This is because My.Settings uses what's called a Settings Provider. In Visual Studio 2005 the default settings provider loads and saves settings using the configuration system, thus they appear in the .config file, however they are read differently than the WCF settings.

So in addition to renaming the config file, we need to also modify the section headers in the app.config file to also specify the name of our VB 6 application (the bolded sections were added): 

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <configSections>

        <sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

            <section name="AMyInteropUserControlLibrary1.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />

          <!--

          Using userSettings in a VB6 application:

          Change the name here to the name of your VB6 .EXE -->

            <section name="Project1.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />

        </sectionGroup>

        <sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >

            <section name="AMyInteropUserControlLibrary1.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

          <!--

          Using applicationSettings in a VB6 application:

          Change the name here to the name of your VB6 .EXE -->

          <section name="Project1.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

        </sectionGroup>

    </configSections>

These section groups will not be lost if you add new settings via the Settings tab on your project properties. Now you can freely change the setting in the .exe.config file after deployment and your Interop user control will be able to read the settings properly.  I've attached a complete example, including the WCF service, for you to play with in Visual Studio 2005. Please read the Readme.txt included for proper set-up.

Enjoy,
-B

This weekend is Silicon Valley Code Camp and I'll be speaking on LINQ. I've got two Saturday morning sessions, one a beginner intro to LINQ and then after that an intermediate talk on LINQ to XML in Visual Basic. I've got so many demos I don't know where to start! If you've been following along the posts I've been doing on the subject, you'll see some familiar code. But I just put together a set of pretty cool surprises so come on out and see for yourself what everyone is talking about in Visual Basic 9.

If you've never been to a code camp, they are FREE developer-focused events put on by the developers themselves. Anyone can sign up to go to or deliver a presentation. Silicon Valley code camp is not all about .NET either, you can learn a variety of technologies.

Currently there are over 70 sessions and over 700 people registered! So register today and come to my sessions :-)

Enjoy,
-B

One of the things I most dearly missed from FoxPro when I moved to VB.NET was the ability to easily dump a bunch of text (multi-line string literals) into the editor easily and embed code (text-merge). FoxPro has a keyword TEXT...ENDTEXT for this and I used to use it all the time. In VB it gets pretty darn ugly with any large amount of text because you have to concatenate string literals with your code and use underscores for readability.

  Dim oldWay = "this is a string" & vbCrLf & _

               "with formatting" & vbCrLf & _

               "and stuff" & vbCrLf & _

               "look ma, underscores" & vbCrLf & _

               "         tabs too"

  MsgBox(oldWay)

Not any more. With Visual Basic 9's built in XML literals support we can now easily write a bunch of text directly into the editor:

         Dim newWay = <string>

this is a string

with formatting

and stuff

look ma, no underscores!!!

            tabs too

                </string>

        MsgBox(newWay.Value)

The text formatting is preserved as well. All you have to do is get the .Value of the XElement, which is the string literal. As you can see this is much cleaner than what we're used to. And if you still like to see your string literals in the default reddish color, you can easily change the color settings for VB XML literals in Tools --> Options --> Environment --> Fonts and Colors, then select "VB XML Text" and set the custom color to RGB(163,21,21). Here's another example, some SQL query text (now with the reddish color): 

        Dim query = <query>

SELECT Customers.*, Orders.OrderDate

FROM Customers

    INNER JOIN Orders ON Orders.CustomerID = Customers.CustomerID

WHERE Customers.CustomerID = @CustomerID

                    </query>

        Dim cmd As New SqlCommand(query.Value)

        cmd.Parameters.AddWithValue("@CustomerID", id)

Now here's where it gets fun. You can also embed expressions into these literals with the <%= syntax. This means you can do any kind of text merging much cleaner than ugly concatenation of strings with code or use of String.Format especially as the size of the text increases. Here's some simple examples:

        Dim simple = <string>

This is a simple text merge example:

Hello, <%= Environment.UserName %>

                     </string>

        MsgBox(simple.Value)

        Dim controls = <string>

There are the following controls on this form:

<%= From item In Me.Controls Select item.ToString & vbCrLf %></string>

        MsgBox(controls.Value)

Calvin has some good examples here and here on how to generate scripts dynamically, but here's one that uses a simple code generation pattern. 

Private Sub CreateClass()

        Dim CustomerSchema As XDocument = XDocument.Load(CurDir() & "\customer.xsd")

        Dim fields = From field In CustomerSchema...<xs:element> _

                     Where field.@type IsNot Nothing _

                     Select Name = field.@name, Type = field.@type

        Dim customer = <customer>

Public Class Customer

    <%= From field In fields Select <f>    

        Private m_<%= field.Name %> As <%= GetVBPropType(field.Type) %></f>.Value %>

        <%= From field In fields Select <p>    

        Public Property <%= field.Name %> As <%= GetVBPropType(field.Type) %>

            Get

                Return m_<%= field.Name %> 

            End Get

            Set(ByVal value As <%= GetVBPropType(field.Type) %>)

                m_<%= field.Name %>  = value

            End Set

        End Property</p>.Value %>                       

End Class</customer>

        My.Computer.FileSystem.WriteAllText("Customer.vb", customer.Value, _

False, System.Text.Encoding.ASCII)

    End Sub

    Private Function GetVBPropType(ByVal xmlType As String) As String

        Select Case xmlType

            Case "xs:string"

                Return "String"

            Case "xs:int"

                Return "Integer"

            Case "xs:decimal"

                Return "Decimal"

            Case "xs:boolean"

                Return "Boolean"

            Case "xs:dateTime", "xs:date"

                Return "Date"

            Case Else

                Return "'TODO: Define Type"

        End Select

    End Function

I hope this gives you some good ideas on what you can do with XML Literals. You literally (pun intended ;-)) don't have to use them to produce XML, you can use them to produce any text-based output.

Enjoy,
-B

If you missed this intriguing interview on Channel 9 with Erik Meijer and Dave Thomas you should definately check it out. Dave Thomas is the creator of IBM's VisualAge SmallTalk/Java as well as one of the original founders of Eclipse. He has some very interesting comments on programming especially around 26 minutes into the interview where he mentions "I think the things that Erik and the VB Team have done are really kick a$$ for VB users" [direct quote] in relation to LINQ and the new language features in Visual Basic 9.

Enjoy,
-B

Carl Franklin, the master mind behind .NET Rocks and long time Visual Basic guru, is also an AMAZING musician and has put together this entire (re)production of Steely Dan's "Home at Last" in Silverlight. Carl recorded each track himself, including the vocals, and shot video of him playing each instrument. He then published it using Microsoft Expression Encoder to produce this HD Silverlight video. I'm very impressed with the streaming here, as my broadband modem barely blinked while I was watching this. But what's more impressive is the incredible musical talent that Carl has. I always love seeing him at DevTeach every year, where people gather in a corner of the hotel after the bars close and listen to him play his guitar and sing. What an amazing talent. Oh and he also does some of the coolest card tricks I've ever seen. Carl, you're a rare gem and a invaluable to the community.

Enjoy,
-B