The Rows of a Table

Introduction to Records

In our description of tables, we saw that a table was made of one or many columns that represented some categories of data. Here is an exampleof a table with a few columns:

After creating such a table and its columns, you (actually the user) can enter values in the table to make it a valuable list. Filling up a table with values is referred to as data entry.

Practical Learning: Introducing Delegates

1. Start Microsoft Visual Studio
2. On the main menu of Microsoft Visual Studio, click File -> New -> Project...
3. In the middle frame of the New Object dialog box, make sure ASP.NET Web Application (.NET Framework) is selected.
   Change the project Name to GasDistribution1
4. Click OK
5. In the New ASP.NET Web Application dialog box, in the templates list, click the MVC icon and click OK
6. In the Solution Explorer, right-click Content -> Style Sheet
7. Type GasUtilityCompany as the name of the file, and press Enter
8. Add the following styles to the file:

   body {
       background-color: #EEEEEE;
   }
   
   .bold              { font-weight: 600;   }
   .left-column       { width:       120px; }
   .utility-container { margin:      auto;
                        width:       400px; }
   .common-font       { font-family: Garamond, Georgia, 'Times New Roman', serif; }

9. In the Solution Explorer, under Views, expand App_Start and double-click BundleConfig.cs to open it
10. Change the document as follows:

    using System.Web.Optimization;
    
    namespace GasDistribution1
    {
        public class BundleConfig
        {
            // For more information on bundling, visit https://go.microsoft.com/fwlink/?LinkId=301862
            public static void RegisterBundles(BundleCollection bundles)
            {
                bundles.Add(new ScriptBundle("~/bundles/jquery").Include(
                            "~/Scripts/jquery-{version}.js"));
    
                bundles.Add(new ScriptBundle("~/bundles/jqueryval").Include(
                            "~/Scripts/jquery.validate*"));
    
                // Use the development version of Modernizr to develop with and learn from. Then, when you're
                // ready for production, use the build tool at https://modernizr.com to pick only the tests you need.
                bundles.Add(new ScriptBundle("~/bundles/modernizr").Include(
                            "~/Scripts/modernizr-*"));
    
                bundles.Add(new ScriptBundle("~/bundles/bootstrap").Include(
                          "~/Scripts/bootstrap.js"));
    
                bundles.Add(new StyleBundle("~/Content/css").Include(
                          "~/Content/bootstrap.css",
                          "~/Content/site.css",
                          "~/Content/GasUtilityCompany.css"));
            }
        }
    }

11. In the Solution Explorer, right-click Controllers -> Add -> Controller...
12. In the Add Scaffold dialog box, click MVC 5 Controller With Read/Write Actions
13. Click Add
14. Type GasMeters to get GasMetersController
15. Press Enter
16. Change the document as follows:

    using System.Data;
    using System.Web.Mvc;
    
    namespace GasDistribution1.Controllers
    {
        public class GasMetersController : Controller
        {
            private readonly DataColumn dcMake;
            private readonly DataColumn dcModel;
            private readonly DataColumn dcMeterNumber;
            private readonly DataColumn dcCounterValue;
    
            public DataTable dtGasMeters;
            public DataSet dsGasMeters;
    
            public GasMetersController()
            {
                dcMake = new DataColumn("make");
                dcModel = new DataColumn("model");
                dcMeterNumber = new DataColumn("meter-number");
                dcCounterValue = new DataColumn("counter-value");
    
                dtGasMeters = new DataTable("gas-meter");
    
                dtGasMeters.Columns.Add(dcMeterNumber);
                dtGasMeters.Columns.Add(dcMake);
                dtGasMeters.Columns.Add(dcModel);
                dtGasMeters.Columns.Add(dcCounterValue);
    
                dsGasMeters = new DataSet("gas-meters");
                dsGasMeters.Tables.Add(dtGasMeters);
            }
    
            // GET: GasMeters
            public ActionResult Index()
            {
                return View();
            }
    
            . . . No Change
        }
    }

17. In the document, right-click one of the Create() methods and click Add View...
18. Make sure the text box displays Create and click Add
19. Create a form as follows:

    @{
        ViewBag.Title = "New Gas Meter";
    }
    
    <h2 class="text-center common-font bold">New Gas Meter</h2>
    
    <hr />
    
    @using (Html.BeginForm())
    {
        <div class="utility-container">
            <table class="common-font">
                <tr>
                    <td class="left-column bold">Meter #:</td>
                    <td>@Html.TextBox("MeterNumber")</td>
                </tr>
                <tr>
                    <td class="bold">Make:</td>
                    <td>@Html.TextBox("Make")</td>
                </tr>
                <tr>
                    <td class="bold">Model</td>
                    <td>@Html.TextBox("Model")</td>
                </tr>
                <tr>
                    <td class="bold">Counter Value:</td>
                    <td>@Html.TextBox("CounterValue")</td>
                </tr>
            </table>
            <hr />
            <p class="text-center"><input type="submit" name="btnCreateGasMeter"
                                          value="Create Gas Meter" class="btn btn-primary" /></p>
        </div>
    }

20. Click the GasMetersController.cs tab to activate it

The Collection of Rows of a Table

A record on a table is represented as a row (horizontal) of data. A row, or record, is an object of a class named DataRow. It starts as follows:

public class DataRow

As you can see, this class has no ancestry except for Object, which is the ultimate ancester to all .NET classes. To support the various records that belong to a table, the DataTable class is equipped with a property named Rows. The DataTable.Rows property is an object of a class named DataRowCollection. The DataRowCollection class provides the necessary properties and methods you can use to create and manage the records of a table.

A record on a table is an object of type DataRow.

Creating a New Row

When performing data entry and while doing it on a record, the record has a status that can be identified by the DataRow.RowState property which is a value based on the DataRowState enumeration.

Before adding a new record to a table, you must let the table know. To let you do this, the DataTable class is equipped with a method named NewRow. Its syntax is:

public DataRow NewRow();

The DataTable.NewRow() method returns a DataRow object. Here is an example of calling it:

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("First Name");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("Last Name");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
}

Converting the Value of a Column

IAs we saw different syntaxes of the indexed property of a DataRow object, in a data, the value of a column is of type object. For this reason, before involving such a value in an expression, you may have to first convert it. Normally, the easiest way to do this is to convert the value to a string. This is possible because, as you should know it, the object type is equipped with a ToString() method.

Data Entry

Introduction

When you call the DataTable.NewRow() method, the record's status is DataRowState.Detached.

After calling the DataTable.NewRow() method, you can specify the value that the column would carry. To do this, you must specify the table's column whose value you want to provide. You can locate a column based on an index as we mentioned already that the columns of a table are stored in the DataTable.Columnsproperty which is based on the DataColumnCollection class. An example would be rowStudent["First Name"], which specifies the column named First Name. After specifying the column, assign it the desired but appropriate value. Here are examples of assigning values to the columns of a table:

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("First Name");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("Last Name");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
    drStudent["First Name"] = "Pauline";
    drStudent["Last Name"] = "Simms";
    drStudent["Gender"] = "Female";
}

Each column can also be identified by its index in the table.

Adding a Record to a Table

After specifying the value(s) of the column(s), you must add it (them) to the table. To do this, you must call the Add() method of the DataRowCollection class. This method is overloaded with two versions. One of the versions uses the following syntax:

public void Add(DataRow row);

This method expects the name of the record as argument, which would be the value returned by a previous call to the DataTable.NewRow() method. Here is an example:

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("First Name");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("Last Name");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
    drStudent["First Name"] = "Pauline";
    drStudent["Last Name"] = "Simms";
    drStudent["Gender"] = "Female";

    tblRegistration.Rows.Add(drStudent);
}

When the record has been added to the table, the record has a status of DataRowState.Added.

Adding an Array of Records

The above version of the DataRowCollection.Add() method means that you must identify each column before assigning a value to it. If you already know the sequence of columns and don't need to explicitly identify them, you can store all values in an array and simply add the array as a complete record. To support this, the DataRowCollection class provide another version of the .Add() method whose syntax is:

public virtual DataRow Add(object[] values);

Here is an example of calling this method:

@{
    . . . No Change

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);
}

There is an alternative to this second version of the DataRowCollection.Add() method. As opposed to passing an array of values to the Add() method, you can first define an array, assign that array to a DataRow variable, then pass that DataRow object to the Add() method. To support this technique, the DataRow class is equipped with an ItemArray property that expects an array. Here is an example

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("First Name");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("Last Name");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student Registration");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Red Oak High School");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
    drStudent["First Name"] = "Pauline";
    drStudent["Last Name"] = "Simms";
    drStudent["Gender"] = "Female";

    tblRegistration.Rows.Add(drStudent);

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);

    object[] arrStudent = { "Geraldine", "Rodetsky", "Unknown" };

    drStudent = tblRegistration.NewRow();
    drStudent.ItemArray = arrStudent;

    tblRegistration.Rows.Add(drStudent);
}

After creating the records of a table, if a record contains invalid values, the DataRow.HasErrors property can help you identify them.

The Number of Records of a Table

After creating a table and its columns, when adding records to the table, to let you get the number of records that a table contains, the DataRowCollection class provides the Count property.

Saving the Records of a Table

Introduction

As the DataSet class is equipped with all the necessary features to create and manage one or more lists, it also provides a very easy way to save the information stored in its lists.

Saving a Data Set

By default, the DataSet class is equipped to save its lists in a format named XML. XML uses text-based files that have the extension .xml (you don't need to know a single thing about XML to follow this lesson; simply consider that .xml is the file extension of the documents saved by the data set, that's it!). To support this format, the DataSet class is equipped with a method named WriteXml. It is overloaded with various versions. One of the versions of this method uses the following syntax:

public void WriteXml(string fileName);

This method takes as argument the name of the new file or its path. When providing this argument, add the .xml extension to the file name. Here is an example of saving a data set using this method:

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
    drStudent["FirstName"] = "Pauline";
    drStudent["LastName"] = "Simms";
    drStudent["Gender"] = "Female";

    tblRegistration.Rows.Add(drStudent);

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);

    object[] arrStudent = { "Geraldine", "Rodetsky", "Unknown" };

    drStudent = tblRegistration.NewRow();
    drStudent.ItemArray = arrStudent;

    tblRegistration.Rows.Add(drStudent);

    string strStudentsFile = Server.MapPath("~/App_Data/Student.xml");
    dsRedOakHighSchool.WriteXml(strStudentsFile);
}

If you want to control whether the file should be created from scratch, instead of passing the name of the file to this method, first create a stream using a Stream-derived class such as FileStream. This allows specifying the necessary options using the FileMode, FileAccess, and FileShare properties. Once the stream is ready, pass it to the WriteXml(). To support this approach, the DataSet class is equipped with the following version of the WriteXml() method:

public void WriteXml(Stream stream);

Here is an example of calling this version:

@{
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    System.Data.DataRow drStudent = tblRegistration.NewRow();
    drStudent["FirstName"] = "Pauline";
    drStudent["LastName"] = "Simms";
    drStudent["Gender"] = "Female";

    tblRegistration.Rows.Add(drStudent);

    object[] arrRecord = { "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(arrRecord);

    object[] arrStudent = { "Geraldine", "Rodetsky", "Unknown" };

    drStudent = tblRegistration.NewRow();
    drStudent.ItemArray = arrStudent;

    tblRegistration.Rows.Add(drStudent);

    string strRegistrationsFile = Server.MapPath("~/App_Data/Registrations.xml");

    using(FileStream fsRegistrations = new FileStream(strRegistrationsFile,
                                                      FileMode.Create,
                                                      FileAccess.Write))
    {
        dsRedOakHighSchool.WriteXml(fsRegistrations);
    }
}

If you want the file to be formatted as text, you can use the following version of the method:

public void WriteXml(TextWriter writer);

If you prefer to use an XmlWriter variable to manage the file, use the following version of the method:

public void WriteXml(XmlWriter writer);

Obviously to use this method, you must first define an XmlWriter type of variable.

Practical Learning: Creating a Record in a Data Set

1. Change the second Create() method as follows:

   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Data;
   using System.Web.Mvc;
   
   namespace GasDistribution1.Controllers
   {
       public class GasMetersController : Controller
       {
           . . . No Change
   
           // GET: GasMeters/Create
           public ActionResult Create()
           {
               return View();
           }
   
           // POST: GasMeters/Create
           [HttpPost]
           public ActionResult Create(FormCollection collection)
           {
               try
               {
                   // TODO: Add insert logic here
                   string strGasMetersFile = Server.MapPath("~/App_Data/GasMeters.xml");
   
                   if (!string.IsNullOrEmpty(collection["MeterNumber"]))
                   {
                       DataRow drGasMeter = dtGasMeters.NewRow();
   
                       drGasMeter["meter-number"] = collection["MeterNumber"];
                       drGasMeter["make"] = collection["Make"];
                       drGasMeter["model"] = collection["Model"];
                       drGasMeter["counter-value"] = collection["CounterValue"];
   
                       dtGasMeters.Rows.Add(drGasMeter);
   
                       using (FileStream fsGasMeters = new FileStream(strGasMetersFile, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                       {
                           dsGasMeters.WriteXml(fsGasMeters);
                       }
   
                       return RedirectToAction("Create");
                   }
   
                   return RedirectToAction("Index");
               }
               catch
               {
                   return View();
               }
           }
   
           . . . No Change
       }
   }

2. Click the Create.cshtml tab to activate it
3. To execute, on the main menu, click Debug -> Start Without Debugging:

   

4. Enter the following values in the text boxes:
    

   Meter #	Make	Model	Counter Value
   293847-27	Archimeda	LP2066	2866

5. Click Create Gas Meter:

   <services>
     <gas-meter>
       <meter-number>293847-27</meter-number>
       <make>Archimeda</make>
       <model>LP2066</model>
       <counter-value>2866</counter-value>
     </gas-meter>
   </business-tools>

6. Close the browser and return to your programming environment

Reading the XML Records of a Data Set

To let you open a file that contains a list of records, the DataSet class is equipped with a method named ReadXml. It is overloaded with various versions. One of the versions of this method uses the following syntax:

public XmlReadMode ReadXml(string fileName);

This method takes as argument the name of an existing XML file or its path. The method opens the file and provides the XML formatting as it was done when the file was saved. Here is an example of calling this method:

@{
    . . . No Change

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    
    . . . No Change

    string strStudentsFile = Server.MapPath("~/App_Data/Student.xml");

    dsRedOakHighSchool.ReadXml(strStudentsFile);
}

Although this method can read any XML file, if you use it to open a file that was saved by someone else or another application and you want to use it in your application, you should be familiar with the names of its nodes. If it contains names that are not "registered" or recognized by your DataSet object, the tables of your data set compose your application may not be able to read it, not because the document was not formatted appropriately, but because the tables of your data set would be holding different names.

If the file was saved using a Stream-based class, you can pass a stream to the method based on the following syntax:

public XmlReadMode ReadXml(Stream stream);

In the same way, the method provides an equivalent version for the TextWriter and the XmlWriter versions. Their syntaxes are:

public XmlReadMode ReadXml(TextReader reader);
public XmlReadMode ReadXml(XmlReader reader);

To use one of these versions, you must first define a TextWriter or an XmlReader type of variable.

When retrieving the content of the XML file, if you want it delivered as text, call the DataSet.GetXml() method. Its syntax is:

public string GetXml();

As you can see, this method returns a string.

Once a file has been opened, you can explore its content. The most obvious operation related to opening a data set consists of viewing its records.

Practical Learning: Opening the Records for a Data Set

1. Click the GasMetersController.cs tab to activate it
2. Change the second Create() method as follows:

   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Data;
   using System.Web.Mvc;
   
   namespace GasDistribution1.Controllers
   {
       public class GasMetersController : Controller
       {
           . . . No Change
   
           // GET: GasMeters/Create
           public ActionResult Create()
           {
               return View();
           }
   
           // POST: GasMeters/Create
           [HttpPost]
           public ActionResult Create(FormCollection collection)
           {
               try
               {
                   // TODO: Add insert logic here
                   string strGasMetersFile = Server.MapPath("~/App_Data/GasMeters.xml");
   
                   if (!string.IsNullOrEmpty(collection["MeterNumber"]))
                   {
                       if (System.IO.File.Exists(strGasMetersFile))
                       {
                           using (FileStream fsGasMeters = new FileStream(strGasMetersFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                           {
                               dsGasMeters.ReadXml(fsGasMeters);
                           }
                       }
   
                       DataRow drGasMeter = dtGasMeters.NewRow();
   
                       drGasMeter["meter-number"] = collection["MeterNumber"];
                       drGasMeter["make"] = collection["Make"];
                       drGasMeter["model"] = collection["Model"];
                       drGasMeter["counter-value"] = collection["CounterValue"];
   
                       dtGasMeters.Rows.Add(drGasMeter);
   
                       using (FileStream fsGasMeters = new FileStream(strGasMetersFile, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                       {
                           dsGasMeters.WriteXml(fsGasMeters);
                       }
   
                       return RedirectToAction("Create");
                   }
   
                   return RedirectToAction("Index");
               }
               catch
               {
                   return View();
               }
           }
   
           . . . No Change
       }
   }

3. Click the Create.cshtml tab to activate it
4. To execute, on the main menu, click Debug -> Start Without Debugging
5. Enter the following values in the text boxes:
    

   Meter #	Make	Model	Counter Value
   928741-59	EnvioSmart	84-D7903	8016

6. Click the Create Gas Meter button

   <services>
     <gas-meter>
       <meter-number>293847-27</meter-number>
       <make>Archimeda</make>
       <model>LP2066</model>
       <counter-value>2866</counter-value>
     </gas-meter>
     <gas-meter>
       <meter-number>928741-59</meter-number>
       <make>EnvioSmart</make>
       <model>84-D7903</model>
       <counter-value>8016</counter-value>
     </gas-meter>
   </services>

7. Close the browser and return to your programming environment
8. In the Solution Explorer, right-click Controllers -> Add -> New Scaffolded Item...
9. In the left frame of the Add Scaffold dialog box, click MVC
10. In the middle frame, click MVC 5 Controller With Read/Write Actions
11. Click Add
12. Type Customers to get CustomersController
13. Click Add
14. Change the document as follows:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Web;
    using System.Web.Mvc;
    
    namespace GasDistribution1.Controllers
    {
        public class CustomersController : Controller
        {
            private readonly DataColumn dcCity;
            private readonly DataColumn dcState;
            private readonly DataColumn dcCounty;
            private readonly DataColumn dcZIPCode;
            private readonly DataColumn dcAddress;
            private readonly DataColumn dcLastName;
            private readonly DataColumn dcFirstName;
            private readonly DataColumn dcCustomerMeter;
            private readonly DataColumn dcAccountNumber;
    
            public DataTable dtCustomers;
            private DataSet dsCustomers;
    
            public CustomersController()
            {
                dcCity = new DataColumn("city");
                dcState = new DataColumn("state");
                dcCounty = new DataColumn("county");
                dcAddress = new DataColumn("address");
                dcZIPCode = new DataColumn("zip-code");
                dcLastName = new DataColumn("last-name");
                dcFirstName = new DataColumn("first-name");
                dcCustomerMeter = new DataColumn("meter-number");
                dcAccountNumber = new DataColumn("account-number");
    
                dtCustomers = new DataTable("customer");
                dtCustomers.Columns.Add(dcAccountNumber);
                dtCustomers.Columns.Add(dcCustomerMeter);
                dtCustomers.Columns.Add(dcFirstName);
                dtCustomers.Columns.Add(dcLastName);
                dtCustomers.Columns.Add(dcAddress);
                dtCustomers.Columns.Add(dcCity);
                dtCustomers.Columns.Add(dcCounty);
                dtCustomers.Columns.Add(dcState);
                dtCustomers.Columns.Add(dcZIPCode);
    
                dsCustomers = new DataSet("customers");
                dsCustomers.Tables.Add(dtCustomers);
            }
            
            // GET: Customers
            public ActionResult Index()
            {
                return View();
            }
    
            . . . No Change
        }
    }

15. In the document, right-click one of the Create() methods and click Add View...
16. Make sure the text box displays Create and click Add
17. Create a form as follos:

    @{
        ViewBag.Title = "New Customer Account";
    }
    
    <h2 class="text-center common-font bold">New Customer Account</h2>
    
    @using (Html.BeginForm())
    {
        <div class="utility-container">
            <table class="common-font">
                <tr>
                    <td class="left-column bold">Account #:</td>
                    <td>@Html.TextBox("AccountNumber")</td>
                </tr>
                <tr>
                    <td class="bold">Meter #:</td>
                    <td>@Html.TextBox("MeterNumber")</td>
                </tr>
                <tr>
                    <td class="bold">First Name:</td>
                    <td>@Html.TextBox("FirstName")</td>
                </tr>
                <tr>
                    <td class="bold">Last Name:</td>
                    <td>@Html.TextBox("LastName")</td>
                </tr>
                <tr>
                    <td class="bold">Address:</td>
                    <td>@Html.TextBox("Address")</td>
                </tr>
                <tr>
                    <td class="bold">City:</td>
                    <td>@Html.TextBox("City")</td>
                </tr>
                <tr>
                    <td class="bold">County:</td>
                    <td>@Html.TextBox("County")</td>
                </tr>
                <tr>
                    <td class="bold">State:</td>
                    <td>@Html.TextBox("State")</td>
                </tr>
                <tr>
                    <td class="bold">ZIP-Code:</td>
                    <td>@Html.TextBox("ZIPCode")</td>
                </tr>
            </table>
            <hr />
            <p class="text-center"><input type="submit" name="btnCreateCustomerAccount"
                                          value="Create Customer Account" class="btn btn-primary" /></p>
        </div>
    }

Locating Records and their Values

Locating a Record

Consider the following data set:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
    tblRegistration.Rows.Add(objStudents1);
    object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
    object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(objStudents3);
    object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
    tblRegistration.Rows.Add(objStudents4);
    object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };

    string strStudentsFile = Server.MapPath("~/App_Data/Students.xml");
    tblRegistration.Rows.Add(objStudents5);

    using (FileStream fsStudents = new FileStream(strStudentsFile, FileMode.Create,
                                                                   FileAccess.ReadWrite,
                                                                   FileShare.ReadWrite))
    {
        dsRedOakHighSchool.WriteXml(fsStudents);
    }
}

This would produce:

<?xml version="1.0"?>
<Students>
    <Student>
        <StudentNumber>920759</StudentNumber>
        <FirstName>Pauline</FirstName>
        <LastName>Simms</LastName>
        <Gender>Female</Gender>
    </Student>
    <Student>
        <StudentNumber>281174</StudentNumber>
        <FirstName>Geraldine</FirstName>
        <LastName>Rodetsky</LastName>
        <Gender>Unknown</Gender>
    </Student>
    <Student>
        <StudentNumber>400795</StudentNumber>
        <FirstName>Edward</FirstName>
        <LastName>Zaks</LastName>
        <Gender>Male</Gender>
    </Student>
    <Student>
        <StudentNumber>931579</StudentNumber>
        <FirstName>Jeannete</FirstName>
        <LastName>Palau</LastName>
        <Gender>Female</Gender>
    </Student>
    <Student>
        <StudentNumber>315825</StudentNumber>
        <FirstName>Kate</FirstName>
        <LastName>Hooks</LastName>
        <Gender>Unknown</Gender>
    </Student>
</Students>

Before performing any operation on a record, you must be able to locate it among the other records of a table. The records of a table are stored in a list based on the DataRowCollection class. To let you locate a record in the DataTable.Rows collection, the DataRowCollection class has an indexed property that is defined as follows:

public DataRow this[int index] {get;}

The first record has an index of 0. The second record has an index of 1, and so on. Here is an example of identifying each column:

@{
    . . . No Change

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");

    . . . No Change

    using (FileStream fsStudents = new FileStream(strStudentsFile, FileMode.Open,
                                                                   FileAccess.Read,
                                                                   FileShare.Read))
    {
        dsRedOakHighSchool.ReadXml(fsStudents);
    }
}

@for(int i = 0; i < tblRegistration.Rows.Count; i++)
{
    System.Data.DataRow rowStudent = tblRegistration.Rows[i];
}

Each record of a table is an object of type DataRow. When you access a record, the compiler would check whether the record exists. If a record with that index exists, its DataRow value is produced.

Locating a Value by a Column's Index

Once you have located a record, you can find a particular value you are interested in, and you have tremendous options.

To locate a value in a data set, you need two pieces of information: the record and the column. Each value of a table is created under a particular column. Therefore, you must be able to specify the column under which the value exists. To identify the columns of a table, the DataRow class is equipped with the overloaded indexed property which comes in 6 versions.

As seen in previous lessons, the columns of a table are indexed with the first column at 0, the second at 1, and so on. To allow you to identify a column by its index, one of the versions of the DataRow's indexed property uses the following syntax:

public object this[int columnIndex] {get; set;}

This property expects the index of the column. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Red Oak High School</title>
</head>
<body>
<h1>School</h1>

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
    tblRegistration.Rows.Add(objStudents1);
    object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
    object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(objStudents3);
    object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
    tblRegistration.Rows.Add(objStudents4);
    object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };
    tblRegistration.Rows.Add(objStudents5);
}

<table border="5">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @for (int i = 0; i < tblRegistration.Rows.Count; i++)
    {
        System.Data.DataRow drStudent = tblRegistration.Rows[i];

        <tr>
            <td>@drStudent[0]</td>
            <td>@drStudent[1]</td>
            <td>@drStudent[2]</td>
            <td>@drStudent[3]</td>
        </tr>
    }
</table>
</body>
</html>

This would produce:

To access a record directly without first declaring a DataRow variable, the above code can also be written as follows:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
    tblRegistration.Rows.Add(objStudents1);
    object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
    object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(objStudents3);
    object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
    tblRegistration.Rows.Add(objStudents4);
    object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };
    tblRegistration.Rows.Add(objStudents5);
}

<table border="">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @for (int i = 0; i < tblRegistration.Rows.Count; i++)
    {
        <tr>
            <td>@tblRegistration.Rows[i][0]</td>
            <td>@tblRegistration.Rows[i][1]</td>
            <td>@tblRegistration.Rows[i][2]</td>
            <td>@tblRegistration.Rows[i][3]</td>
        </tr>
    }
</table>

You can use a foreach loop to visit the members of a DataColumnCollection collection. Like the DataColumnCollection class, the DataRowCollection class implements the GetEnumerator() method of the IEnumerable interface. This means that you can use the foreach loop on a collection of records to visit each member. Here is an example:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
    tblRegistration.Rows.Add(objStudents1);
    object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
    object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(objStudents3);
    object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
    tblRegistration.Rows.Add(objStudents4);
    object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };
    tblRegistration.Rows.Add(objStudents5);
}

<table border="">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @foreach (System.Data.DataRow drStudent in tblRegistration.Rows)
    {
        <tr>
            <td>@drStudent[0]</td>
            <td>@drStudent[1]</td>
            <td>@drStudent[2]</td>
            <td>@drStudent[3]</td>
        </tr>
    }
</table>

Practical Learning: Locating a Value by a Column's Index

1. Click the CustomersController.cs tab to activate it
2. Change the second Create() method as follows:

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.IO;
   using System.Web.Mvc;
   
   namespace GasDistribution1.Controllers
   {
       public class CustomersController : Controller
       {
           private readonly DataColumn dcCity;
           private readonly DataColumn dcState;
           private readonly DataColumn dcCounty;
           private readonly DataColumn dcZIPCode;
           private readonly DataColumn dcAddress;
           private readonly DataColumn dcLastName;
           private readonly DataColumn dcFirstName;
           private readonly DataColumn dcCustomerMeter;
           private readonly DataColumn dcAccountNumber;
   
           public DataTable dtCustomers;
           private DataSet dsCustomers;
   
           public CustomersController()
           {
               dcCity = new DataColumn("city");
               dcState = new DataColumn("state");
               dcCounty = new DataColumn("county");
               dcAddress = new DataColumn("address");
               dcZIPCode = new DataColumn("zip-code");
               dcLastName = new DataColumn("last-name");
               dcFirstName = new DataColumn("first-name");
               dcCustomerMeter = new DataColumn("meter-number");
               dcAccountNumber = new DataColumn("account-number");
   
               dtCustomers = new DataTable("customer");
               dtCustomers.Columns.Add(dcAccountNumber);
               dtCustomers.Columns.Add(dcCustomerMeter);
               dtCustomers.Columns.Add(dcFirstName);
               dtCustomers.Columns.Add(dcLastName);
               dtCustomers.Columns.Add(dcAddress);
               dtCustomers.Columns.Add(dcCity);
               dtCustomers.Columns.Add(dcCounty);
               dtCustomers.Columns.Add(dcState);
               dtCustomers.Columns.Add(dcZIPCode);
   
               dsCustomers = new DataSet("CustomersSet");
               dsCustomers.Tables.Add(dtCustomers);
           }
           // GET: Customers
           public ActionResult Index()
           {
               return View();
           }
   
           // GET: Customers/Details/5
           public ActionResult Details(int id)
           {
               return View();
           }
   
           // GET: Customers/Create
           public ActionResult Create()
           {
               return View();
           }
   
           // POST: Customers/Create
           [HttpPost]
           public ActionResult Create(FormCollection collection)
           {
               try
               {
                   // TODO: Add insert logic here
                   bool validNumber = false;
                   bool validAccount = false;
                   GasMetersController gasMtrCtrlr = new GasMetersController();
                   string strCustomersFile = Server.MapPath("~/App_Data/Customers.xml");
                   string strGasMetersFile = Server.MapPath("~/App_Data/GasMeters.xml");
   
                   // We want to make sure the user provides an account number for the customer
                   if (!string.IsNullOrEmpty(collection["AccountNumber"]))
                   {
                       validAccount = true;
                   }
   
                   /* We need to make sure that the user provides a valid meter, which is a meter that exists in the list of gas meters.
                    * To start, check whether an XML file for gas meters was previously created. */
                   if (System.IO.File.Exists(strGasMetersFile))
                   {
                       // If such a file exists, open it ...
                       using (FileStream fsGasMeters = new FileStream(strGasMetersFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                       {
                           // ... and store the list of gas meters in the data set
                           gasMtrCtrlr.dsGasMeters.ReadXml(fsGasMeters);
                           // "Scan" the list of gas meters
                           for (int i = 0; i < gasMtrCtrlr.dsGasMeters.Tables[0].Rows.Count; i++)
                           {
                               // When you get to the record of a gas meter, ...
                               DataRow drGasMeter = gasMtrCtrlr.dsGasMeters.Tables[0].Rows[i];
   
                               /* ... find out if the meter number of that gas meter corresponds to 
                                * the meter number the user typed in the form. */
                               if (drGasMeter[0].ToString() == collection["MeterNumber"])
                               {
                                   // If that's the case, make a note
                                   validNumber = true;
                                   break;
                               }
                           }
                       }
                   }
   
                   // Make sure the user provide both an account number and a valid gas meter number
                   if ((validAccount == true) && (validNumber == true))
                   {
                       // If that's the case, check whether an XML file for customers was previously created
                       if (System.IO.File.Exists(strCustomersFile))
                       {
                           // If there is such a file, open it ...
                           using (FileStream fsCustomers = new FileStream(strCustomersFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                           {
                               // and store the list of customers in the Customers data set
                               dsCustomers.ReadXml(fsCustomers);
                           }
                       }
   
                       // Since we have all the necessary information, let's create a record
                       DataRow drCustomer = dtCustomers.NewRow();
   
                       drCustomer["account-number"] = collection["AccountNumber"];
                       drCustomer["meter-number"] = collection["MeterNumber"];
                       drCustomer["first-name"] = collection["FirstName"];
                       drCustomer["last-name"] = collection["LastName"];
                       drCustomer["address"] = collection["Address"];
                       drCustomer["city"] = collection["City"];
                       drCustomer["county"] = collection["County"];
                       drCustomer["state"] = collection["State"];
                       drCustomer["zip-code"] = collection["ZIPCode"];
   
                       dtCustomers.Rows.Add(drCustomer);
   
                       // Save the record
                       using (FileStream fsCustomers = new FileStream(strCustomersFile, FileMode.OpenOrCreate, FileAccess.Write, FileShare.Write))
                       {
                           dsCustomers.WriteXml(fsCustomers);
                       }
                   }
   
                   // Re-display the Create form in case the user wants to create another record
                   return RedirectToAction("Create");
               }
               catch
               {
                   return View();
               }
           }
   
           . . . No Change
       }
   }

3. In the document, right-click one of the Create() methods and click Go To View
4. To execute the project, on the main menu, click Debug -> Start Without Debugging:

   

5. Create two records as follows, using values from each row. Click the Save button after each record:
    

   Account #	Meter #	First Name	Last Name	Address	City	County	State	ZIP Code
   60-9720-824	293847-27	Jefferey	Parriot	688 Gorman St	Rockville	Montgomery	MD	20856
   29-5384-805	928741-59	Marco	Ramos	9012 Jefferson Crt, NW	Washington		DC	20012

   <services>
     <customer>
       <account-number>60-9720-824</account-number>
       <meter-number>293847-27</meter-number>
       <first-name>Jeffrey</first-name>
       <last-name>Parriot</last-name>
       <address>688 Gorman Str</address>
       <city>Rockville</city>
       <county>Montgomery</county>
       <state>MD</state>
       <zip-code>20856</zip-code>
     </customer>
     <customer>
       <account-number>29-5384-805</account-number>
       <meter-number>928741-59</meter-number>
       <first-name>Marco</first-name>
       <last-name>Ramos</last-name>
       <address>9012 Jefferson Crt, NW</address>
       <city>Washington</city>
       <county />
       <state>DC</state>
       <zip-code>20012</zip-code>
     </customer>
   </services>

6. Close the browser and return to your programming environment

Locating a Value by the Column Object Name

Instead of using the index of a column, you can locate a value using the object name of its column. To do this, you can use the following syntax of the DataRow indexed property:

public object this[string ColumnName] {get; set;}

This property expects the object name of the column passed in its square brackets. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Red Oak High School</title>
</head>
<body>
<h1>School</h1>

@{
    int i = 0;
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName");
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender");

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    tblRegistration.Columns.Add(colStudentNumber);
    tblRegistration.Columns.Add(colFirstName);
    tblRegistration.Columns.Add(colLastName);
    tblRegistration.Columns.Add(colGender);

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    dsRedOakHighSchool.Tables.Add(tblRegistration);

    object[] objStudents1 = { "920759", "Pauline", "Simms", "Female" };
    tblRegistration.Rows.Add(objStudents1);
    object[] objStudents2 = { "281174", "Geraldine", "Rodetsky", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
    object[] objStudents3 = { "400795", "Edward", "Zaks", "Male" };
    tblRegistration.Rows.Add(objStudents3);
    object[] objStudents4 = { "931579", "Jeannete", "Palau", "Female" };
    tblRegistration.Rows.Add(objStudents4);
    object[] objStudents5 = { "315825", "Kate", "Hooks", "Unknown" };
    tblRegistration.Rows.Add(objStudents5);
}

<table border="5">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @while(i < tblRegistration.Rows.Count)
    {
        System.Data.DataRow drStudent = tblRegistration.Rows[i];

        <tr>
            <td>@drStudent["StudentNumber"]</td>
            <td>@drStudent["FirstName"]</td>
            <td>@drStudent["LastName"]</td>
            <td>@drStudent["Gender"]</td>
        </tr>
        i++;
    }
</table>
</body>
</html>

Practical Learning: Locating a Value by the Column Object Name

1. In the Solution Explorer, right-click Controllers -> Add -> Controller...
2. In the middle frame of the Add Scaffold dialog box, make sure MVC 5 Controller with Read/Write Actions is selected.
   Click Add
3. Type GasBills to get GasBillsController
4. Click Add
5. Change the document as follows:

   using System;
   using System.Collections.Generic;
   using System.IO;
   using System.Data;
   using System.Web.Mvc;
   
   namespace GasDistribution1.Controllers
   {
       public class GasBillsController : Controller
       {
           private readonly DataColumn dcGasBillID;
           private readonly DataColumn dcAccountNumber;
           private readonly DataColumn dcMeterReadingDate;
           private readonly DataColumn dcConsumptionValue;
           private readonly DataColumn dcCurrentMeterReading;
           private readonly DataColumn dcPreviousMeterReading;
   
           private DataTable dtGasBills;
           private DataSet dsGasBills;
   
           public GasBillsController()
           {
               dcGasBillID = new DataColumn("bill-number");
               dcAccountNumber = new DataColumn("account-number");
               dcMeterReadingDate = new DataColumn("meter-reading-date");
               dcConsumptionValue = new DataColumn("consumption-value");
               dcCurrentMeterReading = new DataColumn("current-meter-reading");
               dcPreviousMeterReading = new DataColumn("previous-meter-reading");
   
               dtGasBills = new DataTable("gas-bill");
   
               dtGasBills.Columns.Add(dcGasBillID);
               dtGasBills.Columns.Add(dcAccountNumber);
               dtGasBills.Columns.Add(dcMeterReadingDate);
               dtGasBills.Columns.Add(dcPreviousMeterReading);
               dtGasBills.Columns.Add(dcCurrentMeterReading);
               dtGasBills.Columns.Add(dcConsumptionValue);
   
               dsGasBills = new DataSet("customers-invoices");
               dsGasBills.Tables.Add(dtGasBills);
           }
   
           // GET: GasBills
           public ActionResult Index()
           {
               return View();
           }
   
           // GET: GasBills/Details/5
           public ActionResult Details(int id)
           {
               return View();
           }
   
           // GET: Services/StartMeterReading
           public ActionResult StartMeterReading()
           {
               return View();
           }
   
           // GET: Services/PrepareMeterReading
           public ActionResult PrepareMeterReading(FormCollection collection)
           {
               int billNbr = 0;
               int previousMeterReading = -1;
               string mtrDetails = string.Empty;
               string meterNumber = string.Empty;
               CustomersController custCtrlr = new CustomersController();
               GasMetersController gasMtrCtrlr = new GasMetersController();
               string strGasBillsFile = Server.MapPath("~/App_Data/GasBills.xml");
               string strGasMetersFile = Server.MapPath("~/App_Data/GasMeters.xml");
               string strCustomersFile = Server.MapPath("~/App_Data/Customers.xml");
   
   
               /* Make sure the user provides a customer account number, the date the meter was read,
                * and the number read on the counter of the meter. If any of these pieces of information 
                * is missing, don't do nothing. */
               if ((!string.IsNullOrEmpty(collection["AccountNumber"])) &&
                   (!string.IsNullOrEmpty(collection["MeterReadingDate"])) &&
                   (!string.IsNullOrEmpty(collection["CurrentMeterReading"])))
               {
                   // If a file that holds the records of customers exist already, ...
                   if (System.IO.File.Exists(strCustomersFile))
                   {
                       // ... open it, ...
                       using (FileStream fsCustomers = new FileStream(strCustomersFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                       {
                           DataSet dsCustomers = new DataSet("CustomersSet");
                           // ... and put the values in the Customers data set.
                           dsCustomers.ReadXml(fsCustomers);
   
                           // Check each record (row) of the table of customers
                           for (int i = 0; i < dsCustomers.Tables[0].Rows.Count; i++)
                           {
                               DataRow drCustomer = dsCustomers.Tables[0].Rows[i];
   
                               // If you find an account number that matches the one from the form, ...
                               if (drCustomer["account-number"].ToString() == collection["AccountNumber"])
                               {
                                   /* ... get the values from the customer account and get ready to send them
                                    * to the form in the SaveMeterReading view. */
                                   meterNumber = drCustomer["meter-number"].ToString();
                                   ViewBag.FirstName = drCustomer["first-name"].ToString();
                                   ViewBag.LastName = drCustomer["last-name"].ToString();
                                   ViewBag.Address = drCustomer["address"].ToString();
                                   ViewBag.City = drCustomer["city"].ToString();
                                   ViewBag.County = drCustomer["county"].ToString();
                                   ViewBag.State = drCustomer["state"].ToString();
                                   ViewBag.ZIPCode = drCustomer["zip-code"].ToString();
   
                                   ViewBag.AccountNumber = collection["AccountNumber"];
   
                                   break;
                               }
                           }
                       }
                   }
   
                   // If a file for customers bills was previously created, ...
                   if (System.IO.File.Exists(strGasBillsFile))
                   {
                       // ... open it, ...
                       using (FileStream fsGasBills = new FileStream(strGasBillsFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                       {
                           dsGasBills.ReadXml(fsGasBills);
   
                           /* If this is the first time the gas meter is read for the current customer 
                            * (this also implies that there is no previous/existing gas bill for this customer), ... */
                           if (dsGasBills.Tables[0].Rows.Count == 0)
                           {
                               // ... then the reading count is 0
                               billNbr = 0;
                           }
                           else
                           {
                               /* If this is not the first time the gas meter is read for the customer 
                                * (which means at least one gas bill had already been issued for this customer), 
                                * check each record... */
                               for (int i = 0; i < dsGasBills.Tables[0].Rows.Count; i++)
                               {
                                   // ... Get the bill (invoice) number from the highest gas-bill-id of the file.
                                   billNbr = int.Parse(dsGasBills.Tables[0].Rows[i]["bill-number"].ToString());
   
                                   if (dsGasBills.Tables[0].Rows[i]["account-number"].ToString() == meterNumber)
                                       previousMeterReading = int.Parse(dsGasBills.Tables[0].Rows[i]["current-meter-reading"].ToString());
                               }
                           }
                       }
                   }
   
                   /* If this is the very first bill for the customer, then we will get the 
                    * starting meter reading from the gas meter that was allocated to the customer, ... */
                   if (previousMeterReading == -1)
                   {
                       if (System.IO.File.Exists(strGasMetersFile))
                       {
                           // ... open it, ...
                           using (FileStream fsGasMeters = new FileStream(strGasMetersFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                           {
                               DataSet dsGasMeters = new DataSet("GasMetersSet");
                               // ... and put the values in the GasBills data set.
                               dsGasMeters.ReadXml(fsGasMeters);
   
                               for (int i = 0; i < dsGasMeters.Tables[0].Rows.Count; i++)
                               {
                                   if (dsGasMeters.Tables[0].Rows[i]["meter-number"].ToString() == meterNumber)
                                   {
                                       previousMeterReading = int.Parse(dsGasMeters.Tables[0].Rows[i]["counter-value"].ToString());
                                       mtrDetails = dsGasMeters.Tables[0].Rows[i]["make"].ToString() + " " + dsGasMeters.Tables[0].Rows[i]["model"].ToString();
                                   }
                               }
                           }
                       }
                   }
   
                   ViewBag.MeterNumber = meterNumber;
                   ViewBag.MeterDetails = mtrDetails;
                   ViewBag.MeterReadingDate = collection["MeterReadingDate"];
                   ViewBag.CurrentMeterReading = collection["CurrentMeterReading"];
   
                   /* At this time, we should have a bill number and a previous meter reading value.
                    * Send both values to the method used to save the record. */
                   ViewBag.BillNumber = (billNbr + 1).ToString(); ;
                   ViewBag.PreviousMeterReading = previousMeterReading.ToString();
                   /* Calculate the difference between the current meter reading and the previous meter reading.
                    * Send the result as the consumption value to the action used to save the record. */
                   ViewBag.ConsumptionValue = (int.Parse(collection["CurrentMeterReading"]) - previousMeterReading).ToString();
   
               }
   
               return View();
           }
   
           // GET: GasBills/Create
           public ActionResult Create()
           {
               return View();
           }
   
           // POST: GasBills/Create
           [HttpPost]
           public ActionResult Create(FormCollection collection)
           {
               try
               {
                   // TODO: Add insert logic here
                   string strGasBillsFile = Server.MapPath("~/App_Data/GasBills.xml");
   
                   // Make sure at least the bill number is provided
                   if (!string.IsNullOrEmpty(collection["BillNumber"]))
                   {
                       // If the file was previously saved, ...
                       if (System.IO.File.Exists(strGasBillsFile))
                       {
                           // ..., open it, ...
                           using (FileStream fsMetersReading = new FileStream(strGasBillsFile, FileMode.Open, FileAccess.Read, FileShare.Read))
                           {
                               // ... and store the records in the data set.
                               dsGasBills.ReadXml(fsMetersReading);
                           }
                       }
   
                       // Create a record using the values from the form
                       DataRow drMeterReading = dtGasBills.NewRow();
   
                       drMeterReading["bill-number"] = collection["BillNumber"];
                       drMeterReading["account-number"] = collection["AccountNumber"];
                       drMeterReading["meter-reading-date"] = collection["MeterReadingDate"];
                       drMeterReading["previous-meter-reading"] = collection["PreviousMeterReading"];
                       drMeterReading["current-meter-reading"] = collection["CurrentMeterReading"];
                       drMeterReading["consumption-value"] = collection["ConsumptionValue"];
                       dtGasBills.Rows.Add(drMeterReading);
   
                       // Save the records
                       using (FileStream fsMetersReading = new FileStream(strGasBillsFile, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
                       {
                           dsGasBills.WriteXml(fsMetersReading);
                       }
                   }
   
                   return RedirectToAction("StartMeterReading");
               }
               catch
               {
                   return View();
               }
           }
   
           . . . No Change
       }
   }

6. In the document, right-click StartMeterReading() and click Add View...
7. Make sure the text box displays StartMeterReading and click Add
8. Create a form as follows:

   @{
       ViewBag.Title = "Meter Reading Start-Up";
   }
   
   <h2 class="text-center common-font bold">Meter Reading Start-Up</h2>
   
   @using (Html.BeginForm("PrepareMeterReading", "GasBills", FormMethod.Post))
   {
       <div class="utility-container common-font">
           <table>
               <tr>
                   <td class="left-col bold">Customer Account #:</td>
                   <td>@Html.TextBox("AccountNumber")</td>
               </tr>
               <tr>
                   <td class="bold">Meter Reading Date:</td>
                   <td>@Html.TextBox("MeterReadingDate")</td>
               </tr>
               <tr>
                   <td class="bold">Current Meter Reading:</td>
                   <td>@Html.TextBox("CurrentMeterReading")</td>
               </tr>
           </table>
           <hr />
           <p class="text-center">
               <input type="submit" name="btnPrepareMeterReading"
                      value="Prepare Meter Reading" class="btn btn-primary" />
           </p>
       </div>
   }

9. In the Solution Explorer, under Views, right-click GasBills -> Add -> New Scaffolded Item...
10. In the middle frame of the Add Scaffold dialog box, click MVC 5 View
11. Click Add
12. Type PrepareMeterReading as the name of the view
13. Click Add
14. Create a form as follows:

    @{
        ViewBag.Title = "Prepare Meter Reading";
    }
    
    <h2 class="text-center common-font bold">Prepare Meter Reading</h2>
    
    <hr />
    
    @using (Html.BeginForm("Create", "GasBills", FormMethod.Post))
    {
        <div class="centralizer common-font">
            <table>
                <tr>
                    <td class="large bold">Account #:</td>
                    <td>@Html.TextBox("AccountNumber", ViewBag.AccountNumber as string, new { @class = "medium" })</td>
                </tr>
                <tr>
                    <td class="bold">Customer Name:</td>
                    <td>
                        @Html.TextBox("FirstName", ViewBag.FirstName as string, new { @class = "medium" })
                        @Html.TextBox("LastName", ViewBag.LastName as string, new { @class = "medium" })
                    </td>
                </tr>
                <tr>
                    <td class="bold">Address:</td>
                    <td>@Html.TextBox("Address", ViewBag.Address as string, new { @class = "x-large" })</td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                    <td>
                        @Html.TextBox("City", ViewBag.City as string, new { @class = "medium" })
                        @Html.TextBox("County", ViewBag.County as string, new { @class = "medium" })
                    </td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                    <td>
                        @Html.TextBox("State", ViewBag.State as string, new { @class = "medium" })
                        @Html.TextBox("ZIPCode", ViewBag.ZIPCode as string, new { @class = "medium" })
                    </td>
                <tr>
                    <td class="bold">Gas Meter:</td>
                    <td>
                        @Html.TextBox("MeterNumber", ViewBag.MeterNumber as string, new { @class = "medium" })
                        @Html.TextBox("MeterDetails", ViewBag.MeterDetails as string, new { @class = "large" })
                    </td>
                </tr>
            </table>
            <hr />
            <table>
                <tr>
                    <td class="large bold">Meter Reading Date:</td>
                    <td>@Html.TextBox("MeterReadingDate", ViewBag.MeterReadingDate as string, new { @class = "medium" })</td>
                </tr>
                <tr>
                    <td class="bold">Previous Meter Reading:</td>
                    <td>
                        @Html.TextBox("PreviousMeterReading", ViewBag.PreviousMeterReading as string, new { @class = "medium" })
                        <span class="bold">Current Meter Reading: </span>@Html.TextBox("CurrentMeterReading", ViewBag.CurrentMeterReading as string, new { @class = "medium" })
                    </td>
                </tr>
            </table>
            <table>
                <tr>
                    <td class="large bold">Invoice #:</td>
                    <td style="width: 100px;">@Html.TextBox("BillNumber", ViewBag.BillNumber as string, new { @class = "medium" })</td>
                    <td class="bold" style="width: 165px;">Consumption Value:</td>
                    <td>@Html.TextBox("ConsumptionValue", ViewBag.ConsumptionValue as string, new { @class = "medium" })</td>
                </tr>
            </table>
            <hr />
        </div>
    
        <p class="text-center">
            <input type="submit" name="btnSaveMeterReading"
                   value="Save Gas Bill" class="btn btn-primary" />
        </p>
    }

15. Click the StartMeterReading.cshtml tab to activate it
16. To execute, on the main menu, click Debug -> Start Without Debugging:

    

17. Enter the following values:

    Customer Account #:    29-5384-805
    Meter Reading Date:    01/01/2018
    Current Meter Reading: 8157

18. Click the Prepare Meter Reading button:

    

19. Click the Save Meter Redcing button:

    

20. In the same way, for every row of the following table, enter the values in the form, then click Prepare Meter Reading. In the next form, click the Save Meter Reading button:
    
    
    

    Account #	Meter Reading Date	Current Meter Reading
    60-9720-824	01/04/2018	2958
    60-9720-824	01/28/2018	3065
    29-5384-805	01/30/2018	8339
    60-9720-824	02/02/2018	3134
    29-5384-805	02/03/2018	8505
    29-5384-805	03/30/2018	8688
    60-9720-824	03/30/2018	3209

21. Close the browser and return to your programming environment

Locating a Value by the Column Variable Name

Instead of using the index or the object name of a column, you can also locate a value using the variable name of its column. To do this, you can use the following syntax of the DataRow indexed property:

public object this[DataColumn column] {get; set;}

This property expects the object name of the column passed in its square brackets. Here are examples:

@{

    . . . No Change

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");

    . . . No Change
}

<table border="5">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @do
    {
        System.Data.DataRow drStudent = tblRegistration.Rows[i];

        <tr>
            <td>@drStudent[colStudentNumber]</td>
            <td>@drStudent[colFirstName]</td>
            <td>@drStudent[colLastName]</td>
            <td>@drStudent[colGender]</td>
        </tr>
        i++;
    } while (i < tblRegistration.Rows.Count);
</table>

Locating a Value From the Column Collection

As mentioned already, to access a record, you can pass its index to the indexed property of the DataRowCollection, which produces a DataRow object. Using these concepts, you can access the values of a table. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Red Oak High School</title>
</head>
<body>

@{
    . . . No Change

    System.Data.DataSet dsRedOakHighSchool = new System.Data.DataSet("Students");
    
    . . . No Change
}

<h1>Student</h1>

<ul>
@foreach (System.Data.DataRow rowStudent in tblRegistration.Rows)
{
    foreach (System.Data.DataColumn col in tblRegistration.Columns)
    {
        <li>@rowStudent[col]</li>
    }
    break;
}
</ul>

</body>
</html>

This would produce:

This code allows you to access a record using a row of a table and to locate a value based on the name of its column, but the above code does not allow you to clearly identify the column whose value you want to access. To clearly locate a value, you should name its column and to do this, you can pass the column name to the indexed property of the record. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Red Oak High School</title>
</head>
<body>

@{
    . . . No Change

    System.Data.DataTable tblRegistration = new System.Data.DataTable("Student");
    
    . . . No Change
}

<h1>School</h1>

<table border="5">
    <tr>
        <td><b>Student #</b></td>
        <td><b>First Name</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
    </tr>
    @foreach (System.Data.DataRow drStudent in tblRegistration.Rows)
    {
        foreach (System.Data.DataColumn col in tblRegistration.Columns)
        {
            <tr>
                <td>@drStudent["StudentNumber"]</td>
                <td>@drStudent["FirstName"]</td>
                <td>@drStudent["LastName"]</td>
                <td>@drStudent["Gender"]</td>
            </tr>
            break;
        }
    }
</table>

</body>
</html>

When using any of these previous techniques (whether using a loop (while, do...while, or for) or foreach), if you specify an index that is either less than 0 or beyond the number of records in the table, the compiler would throw an IndexOutOfRangeException exception.

Practical Learning: Ending the Lesson

• Close your programming environment

Previous	Copyright © 2005-2019, FunctionX	Next