Luke Litherland Luke Litherland - 2 months ago 33
SQL Question

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.

Answer

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.

Comments