复杂的SQL查询数据绑定到DataGrid

本文关键字:DataGrid 数据绑定 查询 SQL 复杂 | 更新日期: 2023-09-27 17:57:58

在尝试为现有数据库制作应用程序时,我使用了这个复杂的SQL查询,它将传输到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;
    }

在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为我提供了包含我需要的所有列的结果表,但在dataAkt colunms organizations.cOrgName d1.cString d2.cString d3.cString coluns是emply。无法解决原因。

复杂的SQL查询数据绑定到DataGrid

您需要为列提供有意义的别名。您有:

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

将其更改为:

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

现在您可以正确地绑定到如下列:

<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" />

问题是,选择为towns.cTownName的列在结果集中不会得到名称towns.cTownName,它将被命名为cTownName,没有表前缀。由于表d1d2d3都具有相同名称(cString)的列,因此需要为它们提供别名,以便能够区分哪个是哪个。