Peter Gfader 's Blog

Internet Explorer 8: Little things that matter

Try to guess which Tab page is the current one

Figure: IE 8

Figure: Chrome 1.0

Figure: Opera 9.5

Figure: Firefox 3

Troubles with XQuery in SQL server

My daily problems with XML, XQuery in SQL Server 2005.

We save XML in a VARCHAR column.

Why not VARCHAR , if you serialize and de-serialize only in your .net business layer.

But what if you want to create a report on this xml data?
Then you have to extract single elements from this xml string.

That's easy with XQuery in SQL Server, I thought.

That’s our table

Created with this

CREATE TABLE [dbo].[UnfinishedApplication](
 [ApplicationId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 [OrganisationId] [uniqueidentifier] NOT NULL,
 [ApplicationName] [varchar](100) NOT NULL,
 [State] [varchar](max) NOT NULL,
 [DateCreated] [datetime] NULL,
 [DateUpdated] [datetime] NULL,
 [EmpUpdated] [varchar](150) NULL,
 [EmpCreated] [varchar](150) NULL,
 [SSWTimestamp] [timestamp] NULL,
 [StepUrl] [varchar](350) NOT NULL,
 CONSTRAINT [PK_UnfinishedApplication] PRIMARY KEY CLUSTERED 
 (
 [ApplicationId] ASC
 )
)

We try this:

select [State].query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

And get

Msg 4121, Level 16, State 1, Line 2
Cannot find either column "State" or the user-defined function or aggregate "State.query", or the name is ambiguous.

Because we have a VARCHAR column and not an XML column. Let’s convert it to xml, that’s easy :-)

We try this:

select convert(xml,[State]) as tempXml
FROM [UnfinishedApplication]

And get

Msg 9402, Level 16, State 1, Line 2
XML parsing: line 1, character 39, unable to switch the encoding

Arrggghhh, because:

<?xml version="1.0" encoding="utf-16"?> 
   <UnProcessedApplication xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   --- SNIP ----

We serialize this object UnprocessedApplication in our .net business layer :-|
That’s the reason we have this nice character encoding in the xml declaration element :-)

Solution?

We just remove the declaration and convert that to XML. Then we can use finally XQuery :-)

SELECT (convert(xml, replace([State],'<?xml version="1.0" encoding="utf-16"?>', ''))).query('/UnProcessedApplication/Title/text()')
FROM [UnfinishedApplication]

We created a view for extracting this single fields. But I think a calculated column (COMPUTED COLUMN) would be better for this.
Is this possible todo in a computed column?
Hmm….

LINQ: Remember to use the let keyword

Sample code from http://www.codethinked.com/post/2008/04/The-Linq-quot3bletquot3b-keyword.aspx

How to filter for names that are 4 or 5 and startwith or endwith vowel?
My description is more difficult to read than the actual LINQ query.

Check it out!
namelist = List<string> { …with some names… };

var names = (from p in nameList 
                let vowels = new List<string> { "A", "E", "I", "O", "U" } 
                let startsWithVowel = vowels.Any(v => p.ToUpper().StartsWith(v)) 
                let endsWithVowel = vowels.Any(v => p.ToUpper().EndsWith(v)) 
                let fourCharactersLong = p.Length == 4 
                let fiveCharactersLong = p.Length == 5 
            where 
                (startsWithVowel || endsWithVowel) && 
                (fourCharactersLong || fiveCharactersLong) 
                select p).ToList();

Entity Framework: Winforms Combobox Databinding is just weird...

I wanted to use Databinding on a Combobox in Winforms, like I did with Objects or a Dataset on Master-Detail relations.
Databinding with the Designer is just so easy :-)

Figure: Designer properties on the combobox

I have 2 Bindingsources, the Master-Bindingsource and the the Detail-Bindingsource.
I choose the Combobox and set the different properties (DataSource, ValueMember, DisplayMember)

I set the ValueMember to the primary key of the Detail-Bindingsource and
I set the SelectedValue (Databindings) to the Master-Bindingsource.Doctor (= Entity class)

Figure: Combobox properties in Designer

Problem on runtime

I get the following error

Invalid cast from System.Int32 to ClientName.Business.Entities.Doctors

Reason: The Primary key (Int32) doesn’t fit into the Entity property of the Master-Table (entity)

The problem is: the Entity Framework hides the foreign-key from me (DoctorsId).
I cannot bind to it in the Designer!

How to solve that?

Solution
Don’t use Databinding.
There is no easy way of converting a primary key (Int32) to the actual Entity, with that Integer.
So, do it manually! (AAAAAARRRRRRRGGGGGHHHHHH)

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //HACK: AARRRGHHH: Why I have todo this manually??
            // With other Objects or Datasets we can do this in Designer
            Doctors preferredDoctor = comboBox1.SelectedValue as Doctors;
            if (preferredDoctor != null)
            {
                if (CurrentPatientFromBindingSource != null)
                {
                    CurrentPatientFromBindingSource.Doctor = preferredDoctor;
                }
            }
        }

private void LoadDoctors()
        {
            doctorsBindingSource.DataSource = patientsBL.GetDoctors();

            //HACK: Arrggghhh: Why do I have todo this manually?
            comboBox1.SelectedItem = CurrentPatientFromBindingSource.Doctors;
        }

Todo: Investigate if Linq to Entites provides a flag to configure: Show Foreign Key property for entity

Technorati Tags: linq to entities,entity framework,databinding,combobox,winforms

Entity Framework: Why not use Bindingsource.AddNew for creating new objects...

Assuming that you have a Detail Form with a Bindingsource, and the Bindingsource has as Datasource a IQueryable or something derived from that.

BAD

If you use the Bindingsource to create new objects like this:

      bindingSource.AddNew();

your record is detached, and your business rules fire on Save (probably to late for Winforms)

BETTER

Use

      bindingSource.DataSource = Business.AddNewObject();

Your record is attached, and business rules fire OnChange

Assuming you have a Business method like this.

      public Patients AddNewObject()
      {
          MyObject p = new MyObject ();
          // SetDefaultValues(p);
          DBConnection.AddToMyObject(p);
          return p;
      }