CODEWITHSUNDEEP
c#sqlwpf
Luke Litherland
Luke Litherland

Reputation: 227

C# - Converting my Data tables to ObservableCollection - WPF

I have been told my Data tables which store information that are populating Combo Boxes should be Observable Collections to make things easier. I am using WPF format.

I have tried looking at multiple answers, however where i am quite new to this and have written so much of the code already i am not sure how to convert everything i have written into an observable collection or if it is worth the amount of re-writing it will take.

My Code to get the data for the Data Tables:

public partial class MainWindow : Window
    {

        DataTable dtNotes = new DataTable();
        DataTable dtTemplateNotes = new DataTable();
        DataTable dtReplaceVariables = new DataTable();

        public MainWindow()
        {
            InitializeComponent();
            WindowStartupLocation = System.Windows.WindowStartupLocation.CenterScreen;
            DataContext = new TableList();

            //Setup connection to server
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "PRETEND IP";
            builder.InitialCatalog = "DiscoverThePlanet";
            builder.UserID = "PRETEND USER";
            builder.Password = "PRETEND PASS";

            string connectionString = builder.ConnectionString;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                SqlCommand cmdNotes = new SqlCommand("SELECT NoteID, NoteName, Note FROM Notes", conn);
                SqlCommand cmdTemplateNotes = new SqlCommand("SELECT TemplateNoteID, TemplateNoteName, TemplateNote FROM TemplateNotes", conn);
                SqlCommand cmdReplaceVariables = new SqlCommand("SELECT ReplaceVariableID, ReplaceVariableName, ReplaceVariableNote FROM ReplaceVariables", conn);

                SqlDataReader readerNotes = cmdNotes.ExecuteReader();

                dtNotes.Columns.Add("NoteID", typeof(string));
                dtNotes.Columns.Add("NoteName", typeof(string));
                dtNotes.Columns.Add("Note", typeof(string));
                dtNotes.Load(readerNotes);

                SqlDataReader readerTemplateNotes = cmdTemplateNotes.ExecuteReader();

                dtTemplateNotes.Columns.Add("TemplateNoteID", typeof(string));
                dtTemplateNotes.Columns.Add("TemplateNoteName", typeof(string));
                dtTemplateNotes.Columns.Add("TemplateNote", typeof(string));
                dtTemplateNotes.Load(readerTemplateNotes);

                SqlDataReader readerReplaceVariables = cmdReplaceVariables.ExecuteReader();

                dtReplaceVariables.Columns.Add("ReplaceVariableID", typeof(string));
                dtReplaceVariables.Columns.Add("ReplaceVariableName", typeof(string));
                dtReplaceVariables.Columns.Add("ReplaceVariableNote", typeof(string));
                dtReplaceVariables.Load(readerReplaceVariables);

                // Temporary loop to see if the DataTable (dt) has any data?!?
                //foreach (DataRow thisRow in dt.Rows)
                //{
                //    MessageBox.Show(thisRow["NoteName"].ToString());
                //}


                // Define the columns BEFORE setting the item source
                noteNamesList.SelectedValuePath = "NoteID";
                noteNamesList.DisplayMemberPath = "NoteName";

                templateNoteNamesList.SelectedValuePath = "TemplateNoteID";
                templateNoteNamesList.DisplayMemberPath = "TemplateNoteName";

                replaceVariableNoteList.SelectedValuePath = "ReplaceVariableID";
                replaceVariableNoteList.DisplayMemberPath = "ReplaceVariableName";


                // Set the ItemSource to my fully loaded data table!
                noteNamesList.ItemsSource = dtNotes.DefaultView;
                templateNoteNamesList.ItemsSource = dtTemplateNotes.DefaultView;
                replaceVariableNoteList.ItemsSource = dtReplaceVariables.DefaultView;

                //DEBUG START
                //MessageBox.Show("Hello");
                //DEBUG END

                conn.Close();
            }
        }

This topic came about when i mentioned that i needed to refresh these data tables after a command was ran (UPDATE a table) and then in order to refresh the combo boxes with the latest data from my SQL database i had to restart my app.

That's when someone suggested i change these data tables to ObservableCollections.

If you need more info please just ask, im new to this.

Upvotes: 1

Views: 2650

Answers (1)

Fabio
Fabio

Reputation: 32453

ObservableCollection is just collection of items of some type.
First you need create a class for item: 

public class MyNote
{
    public string Id { get; set; }
    public string Name { get; set; }
}

Then you can use it: 

var notes = new ObservableCollection();
using(DataReader reader = cmdNotes.ExecuteReader())
{
    var ordinals = new 
    { 
        Id = reader.GetOrdinal("NoteID"),
        Name = reader.GetOrdinal("NoteName")
    }

    while(reader.Read() == true)
    {
        var temp = new Note();
        temp.Id = reader.GetString(ordinals.Id);
        temp.Name = reader.GetString(ordinals.Name);

        notes.Add(temp);
    }
}


noteNamesList.SelectedValuePath = "Id";
noteNamesList.DisplayMemberPath = "Name";
noteNamesList.ItemsSource = notes;

But in your case, I think more faster will be to move code which getting data from database in separate method. And call that method where you want to update window with new data.

Upvotes: 2

Related Questions