Solved

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

Posted on 2009-05-08
4
4,046 Views
Last Modified: 2013-11-12
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
Comment
Question by:perrytaylor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:ToddBeaulieu
ID: 24339588
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
 

Author Comment

by:perrytaylor
ID: 24339737
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
 
LVL 16

Accepted Solution

by:
ToddBeaulieu earned 500 total points
ID: 24341628
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
 

Author Closing Comment

by:perrytaylor
ID: 31579423
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question