CODEWITHSUNDEEP
c#wpfdatagrid
GettingStarted
GettingStarted

Reputation: 7625

How can I bind an SQL Query to a DataGrid?

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

This is my DataGrid

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;
        }
    }

My question

  1. Is there a way to load the SQL query and bind it directly with the DataGrid?
  2. The enabled column is a numeric field (0 = false and 1 = true), can we bind that to a DataGridCheckBoxColumn? Basically when the user unchecks a box, i want to update the table as quickly as possible.

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.

Upvotes: 1

Views: 3064

Answers (1)

15ee8f99-57ff-4f92-890c-b56153
15ee8f99-57ff-4f92-890c-b56153

Reputation: 37066

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 (partial):

        <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;
        }
    }
}

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.

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);

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.

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.

Upvotes: 2

Related Questions