Anton Sergeenko Anton Sergeenko - 4 months ago 39
SQL Question

Complicated SQL query data binding to DataGrid

I have gone to this complicated SQL query while trying to make application for existing database, and it transfers to DataGrid.

private void buttonlaunch_Click(object sender, EventArgs e)
{
dataAkt.DataContext = GetComments("SELECT a1.*, inspectors.cFullName, organizations.cOrgName, d1.cString, d2.cString, d3.cString, towns.cTownName " +
"FROM akt as a1 "+
"left join inspectors on a1.nDocInspektor = inspectors.ROWID "+
"left join organizations on organizations.nDirectoryType = organizations.ROWID "+
"left join directories as d1 on a1.nOrgFormVlast = d1.ROWID "+
"left join directories as d2 on a1.nOrgVidPidpr = d2.ROWID "+
"left join directories as d3 on a1.nOrgVidEPDial = d3.ROWID "+
"left join towns on a1.nDocMisceSkladannya=towns.ROWID").DefaultView;
}


in XAML

<DataGrid x:Name="dataAkt" ItemsSource="{Binding}" AutoGenerateColumns="False" HorizontalAlignment="Left" VerticalAlignment="Top" SelectionChanged="dataAkt_SelectionChanged" Margin="10,10,0,0">
<DataGrid.Columns>
<DataGridTextColumn Binding="{Binding Path=cName}" Header="Название/комментарий" Width="100" IsReadOnly="True" Visibility="Collapsed" />
<DataGridTextColumn Binding="{Binding Path=ROWID}" Header="Номер" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cDocNomer}" Header="Номер документа" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=dDocDate, StringFormat=\{0:dd.MM.yyyy\}}" Header="Дата составления" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgName}" Header="Субъект хозяйствования" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cFullName}" Header="Персонал ГОСТРУД" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cDocUch1}" Header="При участии" Width="100" IsReadOnly="True"/>
<DataGridTextColumn Binding="{Binding Path=organizations.cOrgName}" Header="Тип организации" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d1.cString}" Header="Форма собственности" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d2.cString}" Header="Вид предпринимательства" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d3.cString}" Header="Вид экономической деятельности" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=towns.cTownName}" Header="Место составления" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgAdresa}" Header="Адрес организации" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgPhoneFax}" Header="Телефон/Факс" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cPosPIB}" Header="Фамилия, имя, отчество" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cPosPosada}" Header="Должность" Width="100" IsReadOnly="True"/>
<DataGridTextColumn Binding="{Binding Path=fPerZaborg}" Header="Задолженность, тыс. руб" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=fPerZaborgZv}" Header="Зад. уволенным, тыс. руб" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=MyBoolValue, Converter={StaticResource BooleanToStringConverter}}" Header="Проверка в ночное время" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=nPerPropoz}" Header="Внесено предложений" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgIndKod}" Header="Идентификационный номер" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgFormVlastKod}" Header="Код формы собственности" Width="100" IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=cOrgVidEPDialKod}" Header="Код вида экономической деятельности" Width="100" IsReadOnly="True" />
</DataGrid.Columns>


MySQL Workbench gives me resulting table with all columns that I need, BUT in
dataAkt
colunms
organizations.cOrgName
d1.cString
d2.cString
d3.cString
coluns are emply. Can't solve why.

Answer

You need to give meaningful aliases to your columns. You have:

organizations.cOrgName AS ocOrgName, d1.cString, d2.cString, d3.cString ...

Change this to:

organizations.cOrgName AS ocOrgName, d1.cString AS d1cString, d2.cString AS d2cString, d3.cString AS d3cString ...

Now you can bind correctly to the columns like this:

<DataGridTextColumn Binding="{Binding Path=ocOrgName}" Header="Тип организации"  Width="100"  IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d1cString}" Header="Форма собственности"  Width="100"  IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d2cString}" Header="Вид предпринимательства"  Width="100"  IsReadOnly="True" />
<DataGridTextColumn Binding="{Binding Path=d3cString}" Header="Вид экономической деятельности"  Width="100"  IsReadOnly="True" />

The problem is that a column selected as towns.cTownName will not get the name towns.cTownName in the result set, it will be named cTownName, without the table prefix. Since the tables, d1, d2 and d3 all have columns with the same name (cString) you need to give them aliases to be able to distinct which is which.