复杂的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，没有表前缀。由于表d1、d2和d3都具有相同名称（cString）的列，因此需要为它们提供别名，以便能够区分哪个是哪个。

相关文章：