locked
Quick way to move class fields to datatable? RRS feed

  • Question

  • I have a class call FieldsToMonitor which house a bunch of data.  What is the quickest way to move all that data into a datatable?

            public class FieldsToMonitor 
            {
                public string ChangedBy { getset; }
                public int Estimate { getset; }
                public string History { getset; }
                public string WorkItem { getset; }
                public int Id { getset; }
                public DateTime changedDate { getset; }
                public string Title { getset; }
                public string Description { getset; }
            }

    These fields are populated through a foreach loop.  I don't know how to get it into a datatable. 

     

    thanks

    Nick

     

     

    Tuesday, November 8, 2011 3:39 PM

Answers

  • Just create the variable fm just before the start of the switch statement, set the values in the switch statement, and then add it to the list after the end of the switch statement.

     

    On a side note, a large number of the fields you have in your switch statement are set exactly the same way.  You should move those fields that are always set the same to be outside of the switch statement, and the only use the switch statement for those fields that are actually treated differently.

    • Marked as answer by nnnnnpatel Tuesday, November 8, 2011 6:51 PM
    Tuesday, November 8, 2011 6:06 PM
  • Also, in addition to what servy42 has added, I would only call the AddToList once rather then have redundant code.

    Declare the fm variable at the top of your method. Then at the bottom of the method do something like this.

    if (!String.IsNullOrEmpty(fm.ChangedBy){ AddToList(fm); }

    This will eliminate your duplication of code. Also, instantiate fm once, not twice at the top of the method.

    FieldsToMonitor fm = new FieldsToMonitor();
    // Check the history of the current work item
                        foreach (Revision item in wi.Revisions)
                        {
                            try
                            {
                                switch (wi.Type.Name)
                                {
     
                                    case "Issue":
                                    case "Bug":                                    
                                        fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                        fm.Estimate = 0;
                                        fm.History = (string)wi.Fields["History"].OriginalValue;
                                        fm.WorkItem = wi.Type.Name;
                                        fm.Id = wi.Id;
                                        fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                        fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                        fm.Description = (string)wi.Fields["Description"].OriginalValue;                                   
                                        break;
                                     case "Task": 
                                        fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                        fm.History = (string)wi.Fields["History"].OriginalValue;
                                        fm.WorkItem = wi.Type.Name;
                                        fm.Id = wi.Id;
                                        fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                        fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                        fm.Description = (string)wi.Fields["Description"].OriginalValue;   
                                        break
                                }
                                if (!String.IsNUllOrEmpry(fm.ChangedBy)){ AddToList(fm); }
                                          
                                        
    
                                      
    
    
    



    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Edited by JohnGrove Tuesday, November 8, 2011 6:17 PM
    • Marked as answer by nnnnnpatel Tuesday, November 8, 2011 6:51 PM
    Tuesday, November 8, 2011 6:13 PM
  • I think you'll need to have the fm object inside of the for loop, otherwise you'll be modifying the same single item every time.  Each iteration of the loop ought to be a new item, not updating one item.
    True, in this case.

    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Marked as answer by nnnnnpatel Wednesday, November 9, 2011 3:26 PM
    Tuesday, November 8, 2011 6:53 PM

All replies

  • public void InsertData(FieldsToMonitor fm)
    {
       String sql = "INSERT INTO MyTable (......) VALUES {....)";
       using (SqlCommand cmd = new SqlCommand(sql, conn))
       {
          //Add Parameters
          cmd.Parameters.AddWithValue("@ChangedBy", fm.ChangedBy);
          //etc....
         conn.Open();
         cmd.ExecuteNonQuery();
         conn.Close();
       }
    }
    



    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 4:40 PM
  • I would simply use a List<FieldsToMonitor> instead of a DataTable
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 4:41 PM
  •  

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            static List<FieldsToMonitor> myList = new List<FieldsToMonitor>();
    
            static void Main(string[] args)
            {
                FieldsToMonitor fm = new FieldsToMonitor();
                fm.ChangedBy = "John Grove";
                fm.Estimate = 1000;
                fm.History = "My History";
                fm.WorkItem = "My Workitem";
                fm.Id = 1;
                fm.ChangedDate = DateTime.Now;
                fm.Title = "My Title";
                fm.Description = "My Description";
    
                //add to list
                AddToList(fm);
    
            }
    
            static void AddToList(FieldsToMonitor fm)
            {
                myList.Add(fm);
            }
        }
    
        public class FieldsToMonitor
        {
            public string ChangedBy { get; set; }
            public int Estimate { get; set; }
            public string History { get; set; }
            public string WorkItem { get; set; }
            public int Id { get; set; }
            public DateTime ChangedDate { get; set; }
            public string Title { get; set; }
            public string Description { get; set; }
        }
    }
    

     

     


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Edited by JohnGrove Tuesday, November 8, 2011 4:48 PM
    Tuesday, November 8, 2011 4:44 PM
  • Sorry, I a newbie at this.  Here is how my code goes:

            public class FieldsToMonitor 
            {
                public string ChangedBy { getset; }
                public int Estimate { getset; }
                public string History { getset; }
                public string WorkItem { getset; }
                public int Id { getset; }
                public DateTime changedDate { getset; }
                public string Title { getset; }
                public string Description { getset; }
            }
           public void build()
            {
    
                foreach (WorkItem wi in workItems)
                {
                    if (wi.Type.Name == "Task" | wi.Type.Name == "User Story" | wi.Type.Name == "Bug" | wi.Type.Name == "Issue"//look for specific work items
                        {
                             var fieldsToMonitor = new List<FieldsToMonitor>();
                            // Check the history of the current work item
                            foreach (Revision item in wi.Revisions)
                            {
                                FieldsToMonitor fieldstoMonitor = null;
                                try
                                {
                                    switch (wi.Type.Name)
                                    {
     
                                        case "Issue":
                                        case "Bug":
                                            fieldstoMonitor = new FieldsToMonitor()
                                            {
                                                ChangedBy = wi.Fields["Changed By"].Value.ToString(),
                                                Estimate = 0,
                                                History = (string)wi.Fields["History"].OriginalValue,
                                                WorkItem = wi.Type.Name,
                                                Id = wi.Id,
                                                changedDate = (DateTime)wi.Fields["Changed Date"].Value,
                                                Title = (string)wi.Fields["Title"].OriginalValue,
                                                Description = (string)wi.Fields["Description"].OriginalValue
                                            };
                                            fieldsToMonitor.Add(fieldstoMonitor);
                                            break;
     
                                        case "Task":
                                            fieldstoMonitor = new FieldsToMonitor()
                                            {
                                                ChangedBy = wi.Fields["Changed By"].Value.ToString(),
                                                History = (string)wi.Fields["History"].OriginalValue,
                                                WorkItem = wi.Type.Name,
                                                Id = wi.Id,
                                                changedDate = (DateTime)wi.Fields["Changed Date"].Value,
                                                Title = (string)wi.Fields["Title"].OriginalValue,
                                                Description = (string)wi.Fields["Description"].OriginalValue
                                            };

    after each switch I need to write to list.  should I not have a class called FieldToMonitor?  I'm just trying to hookup my results to a grid.

    thanks

    Nick



    • Edited by nnnnnpatel Tuesday, November 8, 2011 5:03 PM
    Tuesday, November 8, 2011 5:01 PM
  • The fieldstoMonitor List should be declared outside the build method that way it can grow and expand. Else you will wipe out the data each time you call the build method rather then add to the list.
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 5:04 PM
  • BTW, you have a fieldstoMonitor List and then you have a fieldstoMonitor variable of the class. You need to make these names distinct

    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 5:06 PM
  • Also make sure that class FieldsToMonitor is outside the class you are working in. They way you have it, it looks like a nested class.
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 5:09 PM
  • Hi John,

    I tried to implement your sample (seems cleaner).  I have a question about when I get to my switches, it doesn't like me creating a new "fm" because it was already declared.  how do I create the new list?

     

                        // Check the history of the current work item
                        foreach (Revision item in wi.Revisions)
                        {
                            try
                            {
                                switch (wi.Type.Name)
                                {
     
                                    case "Issue":
                                    case "Bug":
                                        //fieldstoMonitor = new FieldsToMonitor()
                                        FieldsToMonitor fm = new FieldsToMonitor();
                                        {
                                            fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                            fm.Estimate = 0;
                                            fm.History = (string)wi.Fields["History"].OriginalValue;
                                            fm.WorkItem = wi.Type.Name;
                                            fm.Id = wi.Id;
                                            fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                            fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                            fm.Description = (string)wi.Fields["Description"].OriginalValue;
                                        };
                                        //add to list
                                        AddToList(fm);
     
                                        break;
     
                                    case "Task":
     
     >>>> problem here with fm            FieldsToMonitor fm = new FieldsToMonitor();
                                          {
                                            fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                             fm.History = (string)wi.Fields["History"].OriginalValue;
                                            fm.WorkItem = wi.Type.Name;
                                            fm.Id = wi.Id;
                                            fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                            fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                            fm.Description = (string)wi.Fields["Description"].OriginalValue;
                                        };
    
                                        //add to list
                                        AddToList(fm);
    Tuesday, November 8, 2011 5:53 PM
  • Just create the variable fm just before the start of the switch statement, set the values in the switch statement, and then add it to the list after the end of the switch statement.

     

    On a side note, a large number of the fields you have in your switch statement are set exactly the same way.  You should move those fields that are always set the same to be outside of the switch statement, and the only use the switch statement for those fields that are actually treated differently.

    • Marked as answer by nnnnnpatel Tuesday, November 8, 2011 6:51 PM
    Tuesday, November 8, 2011 6:06 PM
  • Also, in addition to what servy42 has added, I would only call the AddToList once rather then have redundant code.

    Declare the fm variable at the top of your method. Then at the bottom of the method do something like this.

    if (!String.IsNullOrEmpty(fm.ChangedBy){ AddToList(fm); }

    This will eliminate your duplication of code. Also, instantiate fm once, not twice at the top of the method.

    FieldsToMonitor fm = new FieldsToMonitor();
    // Check the history of the current work item
                        foreach (Revision item in wi.Revisions)
                        {
                            try
                            {
                                switch (wi.Type.Name)
                                {
     
                                    case "Issue":
                                    case "Bug":                                    
                                        fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                        fm.Estimate = 0;
                                        fm.History = (string)wi.Fields["History"].OriginalValue;
                                        fm.WorkItem = wi.Type.Name;
                                        fm.Id = wi.Id;
                                        fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                        fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                        fm.Description = (string)wi.Fields["Description"].OriginalValue;                                   
                                        break;
                                     case "Task": 
                                        fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                        fm.History = (string)wi.Fields["History"].OriginalValue;
                                        fm.WorkItem = wi.Type.Name;
                                        fm.Id = wi.Id;
                                        fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                        fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                        fm.Description = (string)wi.Fields["Description"].OriginalValue;   
                                        break
                                }
                                if (!String.IsNUllOrEmpry(fm.ChangedBy)){ AddToList(fm); }
                                          
                                        
    
                                      
    
    
    



    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Edited by JohnGrove Tuesday, November 8, 2011 6:17 PM
    • Marked as answer by nnnnnpatel Tuesday, November 8, 2011 6:51 PM
    Tuesday, November 8, 2011 6:13 PM
  • Thank you John and Servy42.  This site will make a programmer out of me yet.  Community, here is the code that John and Servy42 helped me with:

     

            public class FieldsToMonitor 
            {
                public string ChangedBy { getset; }
                public int Estimate { getset; }
                public string History { getset; }
                public string WorkItem { getset; }
                public int Id { getset; }
                public DateTime changedDate { getset; }
                public string Title { getset; }
                public string Description { getset; }
            }

     

            static List<FieldsToMonitor> myList = new List<FieldsToMonitor>();
    

     

               // var varfieldsToMonitor = new List<FieldsToMonitor>();
                FieldsToMonitor fm = new FieldsToMonitor();
     
                // Loop through all the work items in the system
                foreach (WorkItem wi in workItems)
                {
                    if (wi.Type.Name == "Task" | wi.Type.Name == "User Story" | wi.Type.Name == "Bug" | wi.Type.Name == "Issue"//look for specific work items
                    {
                       
                        // Check the history of the current work item
                        foreach (Revision item in wi.Revisions)
                        {
                            try
                            {
                                fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                fm.Estimate = 0;
                                fm.History = (string)wi.Fields["History"].OriginalValue;
                                fm.WorkItem = wi.Type.Name;
                                fm.Id = wi.Id;
                                fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                fm.Description = (string)wi.Fields["Description"].OriginalValue;
     
                                if (!String.IsNullOrEmpty(fm.ChangedBy))
                                { 
                                    AddToList(fm);
                                }
     
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.ToString());
                            }
                        }
                    }
                }
                MessageBox.Show("done");
            }
     
            static void AddToList(FieldsToMonitor fm)
            {
                myList.Add(fm);
            }
    
    Tuesday, November 8, 2011 6:50 PM
  • I think you'll need to have the fm object inside of the for loop, otherwise you'll be modifying the same single item every time.  Each iteration of the loop ought to be a new item, not updating one item.
    Tuesday, November 8, 2011 6:50 PM
  • I think you'll need to have the fm object inside of the for loop, otherwise you'll be modifying the same single item every time.  Each iteration of the loop ought to be a new item, not updating one item.
    True, in this case.

    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    • Marked as answer by nnnnnpatel Wednesday, November 9, 2011 3:26 PM
    Tuesday, November 8, 2011 6:53 PM
  •  // var varfieldsToMonitor = new List<FieldsToMonitor>();
                FieldsToMonitor fm;
     
                // Loop through all the work items in the system
                foreach (WorkItem wi in workItems)
                {
                    if (wi.Type.Name == "Task" | wi.Type.Name == "User Story" | wi.Type.Name == "Bug" | wi.Type.Name == "Issue") //look for specific work items
                    {
                       
                        // Check the history of the current work item
                        foreach (Revision item in wi.Revisions)
                        {
                            try
                            {
                                fm = new FieldsToMonitor();
                                fm.ChangedBy = wi.Fields["Changed By"].Value.ToString();
                                fm.Estimate = 0;
                                fm.History = (string)wi.Fields["History"].OriginalValue;
                                fm.WorkItem = wi.Type.Name;
                                fm.Id = wi.Id;
                                fm.changedDate = (DateTime)wi.Fields["Changed Date"].Value;
                                fm.Title = (string)wi.Fields["Title"].OriginalValue;
                                fm.Description = (string)wi.Fields["Description"].OriginalValue;
     
                                if (!String.IsNullOrEmpty(fm.ChangedBy))                            
                                    AddToList(fm);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine(ex.ToString());
                            }
                        }
                    }
                }
                MessageBox.Show("done");
            }
     
            static void AddToList(FieldsToMonitor fm)
            {
                myList.Add(fm);
            }
    


    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 6:54 PM
  • So,

    FieldsToMonitor fm;
    

    before the loop is just a declaration of what type the variable is being used?  And I assume that

    fm = new FieldsToMonitor();
    

    makes the fm null before we write the values right?

    thanks

    nick

     

    Tuesday, November 8, 2011 7:03 PM
  • Right
    John Grove, Senior Software Engineer http://www.digitizedschematic.com/
    Tuesday, November 8, 2011 7:53 PM
  • > I have a class call FieldsToMonitor which house a bunch of data. [...] I have a class call FieldsToMonitor which house a bunch of data.


    the simplest way is to use Code First with Entity Framework.
    an example below. it creates database automatically based on your class. saves data to database and loads data into the datagrid.
     

    using System;
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.SqlServerCe;
    using System.Linq;
    using System.Windows;
    
    namespace WpfApplication0
    {
        public partial class MainWindow : Window
        {
            public MainWindow()
            {
                InitializeComponent();
            }
    
            private void Create_Click(object sender, RoutedEventArgs e)
            {
                var data = new Data(new SqlCeConnection(@"Data Source=d:\temp\fields.sdf"));
                data.Database.CreateIfNotExists();
                for (int i = 0; i < 2; i++)
                {
                    data.Fields.Add(new FieldsToMonitor
                    {
                        ChangedBy = "c"+i,
                        Estimate = i,
                        History = "h" + i,
                        WorkItem = "w" + i,
                        changedDate = DateTime.Now,
                        Title = "t" + i,
                        Description = "d" + i
                    });
                }
                data.SaveChanges();
                this.DataContext = data.Fields.ToList();
            }
    
            private void Refresh_Click(object sender, RoutedEventArgs e)
            {
                var data = new Data(new SqlCeConnection(@"Data Source=d:\temp\fields.sdf"));
                if(data.Database.Exists())
                    this.DataContext = data.Fields.ToList();
            }
    
            private void Clear_Click(object sender, RoutedEventArgs e)
            {
                this.DataContext = null;
                var data = new Data(new SqlCeConnection(@"Data Source=d:\temp\fields.sdf"));
                if(data.Database.Exists())
                    data.Database.ExecuteSqlCommand("delete from fields");
            }
        }
    
        public class FieldsToMonitor
        {
            public string ChangedBy { get; set; }
            public int Estimate { get; set; }
            public string History { get; set; }
            public string WorkItem { get; set; }
            public int Id { get; set; }
            public DateTime changedDate { get; set; }
            public string Title { get; set; }
            public string Description { get; set; }
        }
    
        public class Data : DbContext
        {
            public DbSet<FieldsToMonitor> Fields { get; set; }
            public Data(DbConnection connection) : base(connection, true) { }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<FieldsToMonitor>().HasKey(e => e.Id).ToTable("fields");
            }
        }
    }
    
    
     
    <Window x:Class="WpfApplication0.MainWindow"
            xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
            xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
            FontSize="16"
            Title="MainWindow" Width="900" Height="600">
        <StackPanel>
            <WrapPanel>
                <Button Content="Add" Click="Create_Click" />
                <Button Content="Refresh" Click="Refresh_Click" />
                <Button Content="Clear" Click="Clear_Click" />
            </WrapPanel>
            <DataGrid AutoGenerateColumns="True" ItemsSource="{Binding}" />
        </StackPanel>
    </Window>
    
    


    Add References ...
    C:\Program Files\Microsoft ADO.NET Entity Framework 4.1\Binaries\EntityFramework.dll
    C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.dll
     
     
    for more infomation, refer following link Code-First Development (ScottGu's Blog)

     


    • Edited by Malobukv Tuesday, November 8, 2011 9:12 PM
    Tuesday, November 8, 2011 8:50 PM
  • Thank you for walking through this process.  you might make a programmer out of me yet.

     

    nick

    Wednesday, November 9, 2011 3:29 PM