Home

Columns Maintenance

 

Introduction

Column maintenance consists of adding one or more columns to a table, identifying an existing column in a table, looking for a column in a table, deleting one column or deleting all columns of a table. All these operations are easily supported by the various classes of the System.Data namespace.

The Parent Table of a Column

You are probably now familiar with the relationships among the data set, the table and the columns. Just in case:

  1. A table belongs to a data set and not the contrary. Also, you can create and use a data set without creating a table
  2. A column must belong to a table. A table without at least one column is no table at all. It is useless

When using the information stored in a table we will learn when studying records, sometimes you will need to identify the table that owns a particular column you are accessing. This information can be provided by the Table property of the DataColumn class.

Identifying a Column

Once again, remember that the group of columns of a table is an object of type DataColumnCollection. The DataColumnCollection class implements the GetEnumerator() method of the IEnumerable interface. This allows you to enumerate the columns of a table using foreach.

 

Practical Learning Practical Learning: Listing the Columns of a table

  1. To be able to list the columns of a table, change the videos.cs file as follows:
     
    using System;
    using System.Data;
    
    namespace VideoCollection2
    {
        public class Video
        {
            private DataSet dsVideoCollection;
    
            private DataColumn colActorName;
            private DataColumn colDateOfBirth;
            private DataTable  tblActors;
    
            private DataColumn colTitle;
            private DataColumn colDirector;
            private DataColumn colLength;
            private DataColumn colFormat;
            private DataColumn colRating;
            private DataTable  tblVideos;
    
            public Video()
            {
                dsVideoCollection = new DataSet("VideoCollection");
    
                tblActors = new DataTable("Actors");
    
                colActorName = new DataColumn();
                colActorName.ColumnName = "Actor Name";
                colActorName.DataType   = Type.GetType("System.String");
                tblActors.Columns.Add(colActorName);
    
                colDateOfBirth = new DataColumn("Date of Birth",
                                                Type.GetType("System.DateTime"));
                tblActors.Columns.Add(colDateOfBirth);
    
                dsVideoCollection.Tables.Add(tblActors);
    
                tblVideos = new DataTable("Videos");
    
                colTitle = new DataColumn("Title", Type.GetType("System.String"));
                tblVideos.Columns.Add(colTitle);
    
                colDirector = new DataColumn("Director",
                                             Type.GetType("System.String"));
                tblVideos.Columns.Add(colDirector);
    
                colLength = new DataColumn("Length",
                                           Type.GetType("System.String"));
                tblVideos.Columns.Add(colLength);
    
                colFormat = new DataColumn("Format",
                                           Type.GetType("System.Int16"));
                tblVideos.Columns.Add(colFormat);
    
                colRating = new DataColumn("Rating",
                                           Type.GetType("System.Byte"));
                tblVideos.Columns.Add(colRating);
    
                dsVideoCollection.Tables.Add(tblVideos);
            }
    
            public void ShowTables()
            {
                int i = 1;
    
                Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
                Console.WriteLine("Video Collection - Tables");
                Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
    
                foreach (DataTable tbl in dsVideoCollection.Tables)
                    Console.WriteLine("{0}. {1}", i++, tbl.TableName);
                Console.WriteLine("----------------------------");
            }
    
            public void ShowColumns(string table)
            {
                int i = 1;
                DataTable tbl = dsVideoCollection.Tables[table];
    
                Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
                Console.WriteLine("Video Collection - {0} Columns", table);
                Console.WriteLine("=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
    
                foreach (DataColumn col in tbl.Columns)
                    Console.WriteLine("{0}. {1}", i++, col.ColumnName);
                Console.WriteLine("----------------------------");
            }
        }
    }
  2. give the user the ability to choose, access the Program.cs file and change it as follows:
     
    using System;
    
    namespace VideoCollection2
    {
        public class Program
        {
            static int Main(string[] args)
            {
                char answer = 'q';
                char choiceTable = '0';
                Video vdo = new Video();
    
                do
                {
                    try
                    {
                        Console.WriteLine("\nWhat do you want to do?");
                        Console.WriteLine("1. Show the tables");
                        Console.WriteLine("2. Show the columns of a table");
                        Console.WriteLine("0. Quit");
                        Console.Write("Your Choice: ");
                        answer = char.Parse(Console.ReadLine());
                        Console.WriteLine("=========================\n");
                    }
                    catch (FormatException)
                    {
                        Console.WriteLine("Invalid Choice!");
                    }
    
                    switch (answer)
                    {
                        case '1':
                            vdo.ShowTables();
                            break;
                        case '2':
                            Console.WriteLine("Here are the available tables");
                            vdo.ShowTables();
    
                            try
                            {
                                Console.Write("Enter the table whose columns you want to review: ");
                                choiceTable = char.Parse(Console.ReadLine());
                            }
                            catch (FormatException)
                            {
                                Console.WriteLine("Invalid answer!");
                            }
                            if (choiceTable == '1')
                                vdo.ShowColumns("Actors");
                            else if (choiceTable == '2')
                                vdo.ShowColumns("Videos");
                            break;
    
                        default:
                            break;
                    }
                } while((answer == '1') ||
                        (answer == '2'));
    
                return 0;
            }
        }
    }
  3. Execute the application and test it:
     
    What do you want to do?
    1. Show the tables
    2. Show the columns of a table
    0. Quit
    Your Choice: 1
    =========================
    
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Video Collection - Tables
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    1. Actors
    2. Videos
    ----------------------------
    
    What do you want to do?
    1. Show the tables
    2. Show the columns of a table
    0. Quit
    Your Choice: 2
    =========================
    
    Here are the available tables
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Video Collection - Tables
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    1. Actors
    2. Videos
    ----------------------------
    Enter the table whose columns you want to review: 2
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Video Collection - Videos Columns
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    1. Title
    2. Director
    3. Length
    4. Format
    5. Rating
    ----------------------------
    
    What do you want to do?
    1. Show the tables
    2. Show the columns of a table
    0. Quit
    Your Choice: 0
    =========================
    
    Press any key to continue . . .
  4. Close the DOS window
 

Previous Copyright © 2006-2016, FunctionX, Inc. Next