Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4078
  • Last Modified:

How to databind a combobox in wpf with Ado.net dataset

I have a combobox that I would like to fill with the column names of my dataset.  I tried three different methods and none worked correctly.  The first method I tried to bind the combobox and the program would not compile due to an error on making the window.  The second method fills my combo box with seemingly random numbers in place of the column name strings.  The third method was sort of a last ditch attempt and it didn't fill anything.  I'm sure all the methods are simple errors and I would prefer to have the first method of databinding fixed cause it allows a little more flexibility.

1st method 
 
            public Window1()
        {
            InitializeComponent();
 
            Binding drop_down = new Binding();
 
            drop_down.Source = sim1.Tables[0].Columns[0].ColumnName;
 
            //drop_down.Path = new PropertyPath("
 
            cmbXAxis.SetBinding(ComboBox.DisplayMemberPathProperty, drop_down); 
        }
 
2nd method
 
            int i = 0;
            cmbXAxis.Items.Clear();
            foreach (DataColumn column in myDataSet.Tables["data"].Columns)
            {
 
                string colname = column.ColumnName.ToString();
                cmbXAxis.Items.Insert(i, colname);
                i = ++i;
            }
 
3rd Method
            cmbXAxis.DataContext = myDataSet.Tables["data"].Columns;
            cmbXAxis.DisplayMemberPath = myDataSet.Tables["data"].Columns.ToString();
            cmbXAxis.SelectedValuePath = myDataSet.Tables["data"].Columns["Count"].ToString();

Open in new window

0
perrytaylor
Asked:
perrytaylor
  • 2
  • 2
1 Solution
 
ToddBeaulieuCommented:
Ok, two things. 1) I'd try to do as little binding through code as possible, but that's my opinion. I just don't like to see a lot of code-behind reaching into individual controls when 99/100 times you can do it in XAML at the control declaration.

I like your 3rd approach the best and it was close to being correct. You need to set the ItemsSource property, not the DC and the DisplayMember is being set to the string representation of the Columns collection itself.

I tried this code and it works.

I created a property to bind the ComboBox to. I set the DataContext of the page to itself, which I like to do. It lets me expose various properties and quickly bind to them.
XAML
----
        <ComboBox 
            Height="23" 
            HorizontalAlignment="Left" 
            Margin="10,10,0,0" 
            Name="comboBox1" 
            VerticalAlignment="Top" 
            Width="120" 
            ItemsSource="{Binding MyDataColumns}"
            DisplayMemberPath="ColumnName"
            />
 
CODE BEHIND
----
 
public partial class Window1 : Window
{
    public Window1()
    {
        InitializeComponent();
        LoadData();
        DataContext = this;
    }
 
    private DataTable _dataTable = null;
 
    private void LoadData()
    {
        SqlConnection cn = null;
        SqlCommand cmd = null;
        SqlDataAdapter adapter = null;
        DataSet dataSet = null;
 
        try
        {
            cn = new SqlConnection("Data Source=MyMachine;Initial Catalog=MyDb;Integrated Security=True");
            cmd = new SqlCommand("select top 1 * from MyTable", cn);
            adapter = new SqlDataAdapter(cmd);
            dataSet = new DataSet();
 
            adapter.Fill(dataSet);
 
            _dataTable = dataSet.Tables[0];
        }
        finally
        {
            if (cmd != null)
                cmd.Dispose();
 
            if (adapter != null)
                adapter.Dispose();
 
            if (dataSet != null)
                dataSet.Dispose();
 
            if (cn != null)
                cn.Dispose();
        }            
    }
 
    public IEnumerable MyDataColumns
    {
        get
        {
            return (IEnumerable)_dataTable.Columns;
        }
    }
}

Open in new window

0
 
perrytaylorAuthor Commented:
I think I need to tweak your answer a little bit because the dataset does not load when the window loads.  It loads after the user selects an excel sheet which gets imported as a dataset.  I'm not sure how to handle the window method in this case.  My excel code is included below so you can see what I am talking about and have now to load the dropdown, I def like the direction your solution is heading better than what I have.
        private void btnGetFile6_Click(object sender, RoutedEventArgs e)
        {
            string filename;
            get_excel_sheet(out sim6, out filename);
            tbFileName6.Text = filename;
        }
        
        public void get_excel_sheet(out DataSet myDataSet, out string filename)
        {
            myDataSet = new DataSet();
            filename = "";
 
            Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();
            dlg.FileName = ""; // Default file name
            dlg.DefaultExt = ".xls"; // Default file extension
            dlg.Filter = "Excel documents (.xls)|*.xls"; // Filter files by extension
 
            // Show save file dialog box
            Nullable<bool> result = dlg.ShowDialog();
 
            string file;
            if (result == true)
            {
                // Open document
                file = dlg.FileName;
                int filedel = file.LastIndexOf("\\");
                filename = file.Remove(0, filedel+1);
            }
            //else end function
            else
            {
                return;
            }
 
 
            string connectString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties= ";
        connectString = connectString+ @"""Excel 8.0;HDR=YES;""";
 
 
 
            OleDbConnection connection =
                    new OleDbConnection(connectString);
 
            string query = "SELECT * FROM [Lap Sim$A22:FG724] where (NOT ([Bank] IS NULL))";
 
            
            
            OleDbConnection myAccessConn = null;
            try
            {
                myAccessConn = new OleDbConnection(connectString);
            }
            catch(Exception ex)
            {
                  Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                  return;
            }
 
            try
            {
 
                OleDbCommand myAccessCommand = new OleDbCommand(query, myAccessConn);
                  OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
 
                  myAccessConn.Open();
                  myDataAdapter.Fill(myDataSet,"data");
 
            }
            catch (Exception ex)
            {
                  Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
                  return;
            }
            finally
            {
                myAccessConn.Close();
                connection.Close();
            }
 
  
            cmbXAxis.Items.Clear();
            foreach (DataColumn column in myDataSet.Tables["data"].Columns)
            {
                string colname = column.ColumnName.ToString();
                cmbXAxis.Items.Add(colname);
                cmbYAxis.Items.Add(colname);
            }
            
 
        }

Open in new window

0
 
ToddBeaulieuCommented:
Ok. The fact that I hooked into the window load was purely to simplify the example. In fact, I'd *never* be loading data like this at all in my window code. I'd be using the Model-View-ModelView paradigm, but that's an entirely different ball of wax.

You can still achieve your goal without manually building a data set and manually binding it to a control.

WPF uses change notification to know when to rebind UI elements to data sources.

If, when you load an excel file, you rebuild the dataset that the property exposes (and that the combobox is bound to), you should be able to simply send notification that that property changed and the UI will automatically rebind.

In the property setter, send notification. Typically, the getter/setter go together and the setter raises notification. We could change yours around to mirror that, but the way I proposed was to have the module variable hold the DataType and property expose the COlumns collection.

SO.... after updating the DataTable variable, you could send notice that the MyDataColumns property changed:

           _dataTable = dataSet.Tables[0];
           SendPropertyChanged("MyDataColumns");

This is not the best sample code, I admit, but it's late and I'm trying to get the basic ideas to you so you can research the concepts. Change notification is vitally important for you understand with WPF!


                        
                  }

        public event PropertyChangedEventHandler PropertyChanged;
 
        protected virtual void SendPropertyChanged(String propertyName)
        {
            if ((this.PropertyChanged != null))
            {
                this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

Open in new window

0
 
perrytaylorAuthor Commented:
Do you know of anywhere that spells out the "Model/View Model/View Paradigm" that you referenced.  I'm trying to get things that work quickly, but also trying to figure out how to be more elegant and use the tools properly for the long run.  I don't have access to a computer this weekend to test out your code, but it seems like it makes sense.  If you would continue to monitor this thread I would appreciate it.  
thanks for your help
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now