如何将SQL Query绑定到DataGrid？ [英] How can I bind an SQL Query to a DataGrid?

问题描述

    <DataGrid Name="getEmployees" ItemsSource="{Binding}">
        <DataGrid.Columns>
            <DataGridCheckBoxColumn />
            <DataGridTextColumn />
        </DataGrid.Columns>
    </DataGrid>

这是我的DataGrid

This is my DataGrid

我的CodeBehind

Here is my CodeBehind

    public ViewMyProxy()
    {
        InitializeComponent();            
        FillDataGrid();
    }

    private void FillDataGrid()
    {

        var connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
        string CmdString = string.Empty;
        using (OleDbConnection con = new OleDbConnection(connectionString))
        {
            CmdString = "SELECT proxyFor, enabled FROM accesscontrol WHERE currentlyLoggedOnUser = '" + Environment.UserName + "'";
            OleDbCommand cmd = new OleDbCommand(CmdString, con);
            OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
            DataTable dt = new DataTable("Employee");
            sda.Fill(dt);
            getEmployees.ItemsSource = dt.DefaultView;
        }
    }

我的问题

1. 有没有办法加载SQL查询并直接与DataGrid绑定？


2. 启用的列是一个数字字段（0 = false，1 = true），我们可以将其绑定到DataGridCheckBoxColumn吗？基本上当用户取消选中一个框时，我想尽快更新表。

使用代码隐藏，当我的WPF加载，我最终使用3列而不是2.我希望第一列成为复选框（从SQL查询启用），而proxyFor是第二列中我想要的字符串。

Using the Code-behind, when my WPF loads, I end up with 3 columns instead of 2. I want the 1st column to be the Checkbox (enabled from SQL query) and the proxyFor is a String that I want in the 2nd Column.

推荐答案

您需要将 DataGrid.AutoGenerateColumns 设置为 False ，以防止它自动生成列。然后，您需要显式绑定您自己定义的列。 DataGridCheckBoxColumn 和 DataGridTextColumn 都具有您可以设置的其他属性，以进一步自定义您在网格中看到的内容。

You need to set DataGrid.AutoGenerateColumns to False, to prevent it from automatically generating the columns. Then you need to explicitly bind the columns that you defined yourself. DataGridCheckBoxColumn and DataGridTextColumn both have other properties you can set as well, to further customize what you see in the grid.

<DataGrid 
    Name="getEmployees" 
    AutoGenerateColumns="False"
    >
    <DataGrid.Columns>
        <DataGridCheckBoxColumn 
            Binding="{Binding enabled}"
            Header="Enabled"
            />
        <DataGridTextColumn 
            Binding="{Binding proxyFor}"
            Header="Proxy For"
            />
    </DataGrid.Columns>
</DataGrid>

如果复选框列不符合您的要求，可以尝试编写值转换器在你的整数和一个布尔值。

If the checkbox column doesn't work the way you want, you could try writing a value converter to convert between your integer and a boolean.

XAML（部分）：

        <DataGridCheckBoxColumn 
            xmlns:myconv="clr-namespace:MyProject.Converters"
            Binding="{Binding enabled, Converter={myconv:IntToBool}}"
            />

C＃

namespace MyProject.Converters
{
    public class IntToBool : MarkupExtension, IValueConverter
    {
        public override object ProvideValue(IServiceProvider serviceProvider) => this;

        public virtual object Convert(object value, Type targetType, object parameter, CultureInfo culture)
        {
            return ((int)value) != 0;
        }

        public virtual object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
        {
            return ((bool)value) ? 1 : 0;
        }
    }
}

如果不起作用，您将需要使用 DataGridTemplateColumn ，这将为您提供更多的权力来控制列的外观和工作原理。

If that doesn't work, you'll need to use a DataGridTemplateColumn instead, which will give you a lot more power to control what the column looks like and how it works.

在回答您的第一个问题时，有没有办法加载SQL查询并直接与DataGrid绑定？我不知道这是什么意思是：您是否询问是否可以将SQL字符串分配给 DataGrid 的某些属性，并使其执行查询并填充本身？不，如果你真的想要，你可以写一个附属的财产。这可能很有趣

In answer to your first question, "Is there a way to load the SQL query and bind it directly with the DataGrid?", I'm not sure exactly what that means: Are you asking if you can assign a SQL string to some property of DataGrid and have it execute the query and populate itself? No. You could write an attached property to do that, if you really wanted to. That might be fun.

另一点，你没有问过，但应该提到：

Another point, which you didn't ask about but ought to be mentioned:

CmdString = "SELECT proxyFor, enabled FROM accesscontrol WHERE currentlyLoggedOnUser = '" 
    + Environment.UserName + "'";

如果您已经知道这是不好的做法，并且计划在代码正常工作后更换那么你不需要听到这个，但是如果不是这样，你应该绝对习惯使用参数：

If you already know that's bad practice and you're planning to replace it once you get the code working, then you don't need to hear this, but in case that's not so, you should really make an absolute habit of using parameters:

CmdString = "SELECT proxyFor, enabled FROM accesscontrol WHERE "
    + "currentlyLoggedOnUser = @userName";

OleDbCommand cmd = new OleDbCommand(CmdString, con);

cmd.Parameters.AddWithValue("userName", Environment.UserName);

如果有人在他的用户名中有单引号？可能会发生如果您在外部代码中使用SQL字符串的简单连接，则允许任何人在地球上在数据库中运行任意SQL。

What if somebody has a single quote in his user name? It can happen. And if you use simple concatenation of SQL strings in externally-facing code, you're allowing anybody on Earth to run arbitrary SQL in your database.

为什么在XAML中有 ItemsSource ={Binding}？这将把DataGrid的DataContext绑定到ItemsSource。如果你的视图有一个viewmodel，那么DataContext应该是viewmodel;否则，它可能为null。而你正在代码背后分配别的东西。

Why do you have ItemsSource="{Binding}" in the XAML? That will bind the DataGrid's DataContext to ItemsSource. If your view has a viewmodel, that DataContext should be the viewmodel; otherwise, it's probably null. And you're assigning something else in code behind anyhow.

这篇关于如何将SQL Query绑定到DataGrid？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！