Characteristics of the Columns of a Table

Introduction

When performing data entry, there are various ways you can either assist the user or impose some behaviors on your application. You can:

All these functionalities are already available in the classes of the data set system.

ApplicationPractical Learning: Introducing Tables Columns

  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 GasDistribution2
  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 -> New Scaffolded Item...
  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 GasDistribution2.Controllers
{
    public class GasMetersController : Controller
    {
        private readonly DataColumn dcGasMeterID;
        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");
            dcGasMeterID = new DataColumn("gas-meter-id");
            dcMeterNumber = new DataColumn("meter-number");
            dcCounterValue = new DataColumn("counter-value");

            dtGasMeters = new DataTable("gas-meter");

            dtGasMeters.Columns.Add(dcGasMeterID);
            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

    }
}

The Unique Value of a Column

During data entry, the user is expected to enter various values under each column and each value would belong to a particular record. As a result, it is not unusual to have the same value belonging to different records. In some cases, you may want each record to hold a different value under the same column.

To support unique values, the DataColumn class is equipped with a Boolean property named Unique:

public bool Unique { get; set; }

The default value of this property is false, which means various records can have the same values for a column.

To control the uniqueness of values, assign the desired Boolean value to the Unique property of the column. Here is an example:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    colStudentNumber.Unique = true;
}

After applying this property to True, if you create two records that have the same value for the column, when the webpage displays, you would get a System.Data.ConstraintException exception. Here is an example:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber");
    colStudentNumber.Unique = true;
    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);
    object[] objStudents6 = { "281174", "Robin", "Holms", "Unknown" };
    tblRegistration.Rows.Add(objStudents2);
}

This would produce:

Controlling the Unique Value

Practical Learning: Controlling the Uniqueness of a Column

  1. Change the GasMetersController class as follows: 
    private readonly DataColumn dcGasMeterID;
        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");
            dcGasMeterID = new DataColumn("gas-meter-id");

            dcMeterNumber = new DataColumn("meter-number");
            dcMeterNumber.Unique = true;

            dcCounterValue = new DataColumn("counter-value");

            dtGasMeters = new DataTable("gas-meter");

            dtGasMeters.Columns.Add(dcGasMeterID);
            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

    }
}
  2. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  3. In the middle list of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected.
    Click Add
  4. Type Customers to get CustomersControllers
  5. Click Add
  6. Change the class as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution2.Controllers
{
    public class CustomersController : Controller
    {
        private readonly DataColumn dcCustomerID;
        private readonly DataColumn dcAccountNumber;
        private readonly DataColumn dcFirstName;
        private readonly DataColumn dcLastName;
        private readonly DataColumn dcAddress;
        private readonly DataColumn dcCity;
        private readonly DataColumn dcCounty;
        private readonly DataColumn dcState;
        private readonly DataColumn dcZIPCode;
        private readonly DataColumn dcCustomerMeter;

        public DataTable dtCustomers;
        private DataSet dsCustomers;

        public CustomersController()
        {
            dcCustomerID = new DataColumn("customer-id");
            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");
            dcAccountNumber.Unique = true;

            dtCustomers = new DataTable("customer");

            dtCustomers.Columns.Add(dcCustomerID);
            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
    }
}

The Data Type of a Column

If you create an application that allows the user to enter some values, you would wish the user enter the right type of data under each column. To assist you with this, the DataColumn class allows you to specify an appropriate or desired data type for each column. The data type of a column allows it to accept or reject an inappropriate value. Although we saw that the name was the most important aspect of a column, in reality, a data type is also required.

To let you specify the data type of a column, the DataColumn class relies on the following .NET Framework structures: Boolean, Byte, Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, String, TimeSpan, UInt16, UInt32, and UInt64. The DataColumn class can also support an array of Byte values, as in Byte[], for a column.

When creating a new column, if you don't specify its data type, it is assumed to be a string and the string data type is automatically applied to it.

To specify the data type of a column, you have two main options. When declaring a variable for a column, to specify its data type, you can initialize the DataColumn variable using the third constructor of the class. Its syntax is:

public DataColumn(string columnName, Type dataType);

To specify a column's data type, select one from the Type class of the System namespace by calling the Type.GetType() method. The GetType() method is overloaded with three versions. The first version has the following syntax:

public static Type GetType(string typeName);

This method expects as argument a valid data type defined in the .NET Framework. The data type must be retrieved from the Type class of the System namespace. The name of the data type must be qualified with a period operator. Here is an example:

@{
    System.Data.DataColumn colStudentNumber = new System.Data.DataColumn("StudentNumber", Type.GetType("System.String"));
}

If you used the default constructor to create a DataColumn, to let you specify its data type, the DataColumn class is equipped with a property named DataType.

If all case, when performing data entry, provide the appropriate value of each column. Here are examples:

@{
    System.Data.DataColumn colEmployeeNumber = new System.Data.DataColumn("StudentNumber", Type.GetType("System.Int32"));
    colEmployeeNumber.Unique = true;
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName", Type.GetType("System.String"));
    System.Data.DataColumn colMiddleInitial = new System.Data.DataColumn("MiddleInitial", Type.GetType("System.Char"));
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    colLastName.DataType = Type.GetType("System.String");
    System.Data.DataColumn colDateHired = new System.Data.DataColumn("DateHired");
    colDateHired.DataType = Type.GetType("System.DateTime");

    System.Data.DataTable tblEmployees = new System.Data.DataTable("EmployeesSet");
    tblEmployees.Columns.Add(colEmployeeNumber);
    tblEmployees.Columns.Add(colFirstName);
    tblEmployees.Columns.Add(colMiddleInitial);
    tblEmployees.Columns.Add(colLastName);
    tblEmployees.Columns.Add(colDateHired);

    System.Data.DataSet dsPersonnel = new System.Data.DataSet("Employees");
    dsPersonnel.Tables.Add(tblEmployees);

    object[] empl293847 = { 293847, "James", 'D', "Clemenson", new DateTime(2012, 6, 24) };
    tblEmployees.Rows.Add(empl293847);
    object[] empl938405 = { 938405, "Jennifer", 'M', "Euel", new DateTime(2015, 11, 4) };
    tblEmployees.Rows.Add(empl938405);
    object[] empl839751 = { 839751, "Ronald", 'E', "Jacobs", new DateTime(2010, 8, 31) };
    tblEmployees.Rows.Add(empl839751);
    object[] empl185829 = { 185829, "Jeannine", 'H', "Simmons", new DateTime(2002, 5, 17) };
    tblEmployees.Rows.Add(empl185829);
    object[] empl372947 = { 372947, "Françoise", 'L', "Hughes", new DateTime(2007, 10, 29) };
    tblEmployees.Rows.Add(empl372947);
}

Practical Learning: Setting the Data Types of Columns

  1. Change the CustomersController class as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution2.Controllers
{
    public class CustomersController : Controller
    {
        private readonly DataColumn dcCustomerID;
        private readonly DataColumn dcAccountNumber;
        private readonly DataColumn dcFirstName;
        private readonly DataColumn dcLastName;
        private readonly DataColumn dcAddress;
        private readonly DataColumn dcCity;
        private readonly DataColumn dcCounty;
        private readonly DataColumn dcState;
        private readonly DataColumn dcZIPCode;
        private readonly DataColumn dcCustomerMeter;

        public DataTable dtCustomers;
        private DataSet dsCustomers;

        public CustomersController()
        {
            dcCustomerID = new DataColumn("customer-id");

            dcCity = new DataColumn("city", Type.GetType("System.String"));
            dcState = new DataColumn("state", Type.GetType("System.String"));
            dcCounty = new DataColumn("county", Type.GetType("System.String"));
            dcAddress = new DataColumn("address", Type.GetType("System.String"));
            dcZIPCode = new DataColumn("zip-code", Type.GetType("System.String"));
            dcLastName = new DataColumn("last-name", Type.GetType("System.String"));
            dcFirstName = new DataColumn("first-name", Type.GetType("System.String"));
            dcCustomerMeter = new DataColumn("meter-number", Type.GetType("System.String"));

            dcAccountNumber = new DataColumn("account-number");
            dcAccountNumber.DataType = Type.GetType("System.String");
            dcAccountNumber.Unique = true;

            dtCustomers = new DataTable("customer");

            dtCustomers.Columns.Add(dcCustomerID);
            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
    }
}
  2. Click the GasMetersController.cs tab to access te file
  3. Change the columns definitions as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution2.Controllers
{
    public class GasMetersController : Controller
    {
        private readonly DataColumn dcGasMeterID;
        private readonly DataColumn dcMake;
        private readonly DataColumn dcModel;
        private readonly DataColumn dcMeterNumber;
        private readonly DataColumn dcCounterValue;

        public DataTable dtGasMeters;
        public DataSet dsGasMeters;

        public GasMetersController()
        {
            dcGasMeterID = new DataColumn("gas-meter-id");

            dcMake = new DataColumn("make");
            dcMake.DataType = Type.GetType("System.String");

            dcModel = new DataColumn("model");
            dcModel.DataType = Type.GetType("System.String");

            dcMeterNumber = new DataColumn("meter-number");
            dcMeterNumber.Unique = true;
            dcMeterNumber.DataType = Type.GetType("System.String");

            dcCounterValue = new DataColumn("counter-value");
            dcCounterValue.DataType = Type.GetType("System.Int32");

            dtGasMeters = new DataTable("gas-meter");

            dtGasMeters.Columns.Add(dcGasMeterID);
            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

  
    }
}
  4. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  5. In the middle list of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected.
    Click Add
  6. Type GasBills to get GasBillsController
  7. Click Add
  8. Change the document as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution2.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("gas-bill-id");            

            dtGasBills = new DataTable("gas-bill");

            dtGasBills.Columns.Add(dcGasBillID);
            dtGasBills.Columns.Add("account-number", Type.GetType("System.String"));
            dtGasBills.Columns.Add(new DataColumn("meter-reading-date", Type.GetType("System.DateTime")));
            dtGasBills.Columns.Add(columnName: "previous-meter-reading", type:Type.GetType("System.Int32"));
            dtGasBills.Columns.Add(type: Type.GetType("System.Int32"), columnName: "current-meter-reading");
            dtGasBills.Columns.Add(new DataColumn("consumption-value", Type.GetType("System.Int32")));

            dsGasBills = new DataSet("customers-invoices");
            dsGasBills.Tables.Add(dtGasBills);
        }

        // GET: GasBills
        public ActionResult Index()
        {
            return View();
        }

       . . . No Change
 
    }
}

Automatically Incrementing the Value of a Column

The data set can assist you with specifying the unique value of a column. Probably the easiest way is to apply an integral type to the column. Then, every time a new record is added, the valumn is incremented. To support this technique, the DataColumn class is equipped with a Boolean property named AutoIncrement. The default value of this property is false. When this property is set to true, when a new record is added, the value of the column is incremented. Here is an example:

@{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
}

By default, if the DataColumn.AutoIncrement property is set to true, before any record is created on the table, the initial value of the column is set to 0 and that would be the first value of the column. If you want, you can start the records with another value. To support this, the DataColumn class is equipped with a property named AutoIncrementSeed, which is a Long integral type. To specify the starting value of the column, assign the desired value to this property. Here is an example:

@{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
}

By default, if the AutoIncrement property of the DataColumn class is set to true, when a record is created, the value of the column is incremented by 1. To let you incremented by a value of your choice, the DataColumn class is equipped with a property named AutoIncrementStep. Assign the desired number to this property. Here is an example:

@{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
    colStudentID.AutoIncrementStep = 5;
}

Practical Learning: Setting the Incrementing Values of a Column

  1. Change the GasBillsController class as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution2.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("gas-bill-id");
            dcGasBillID.AutoIncrement = true;
            dcGasBillID.AutoIncrementSeed = 1;
            dcGasBillID.AutoIncrementStep = 1;


            dtGasBills = new DataTable("gas-bill");

            dtGasBills.Columns.Add(dcGasBillID);
            dtGasBills.Columns.Add("account-number", Type.GetType("System.String"));
            dtGasBills.Columns.Add(new DataColumn("meter-reading-date", Type.GetType("System.DateTime")));
            dtGasBills.Columns.Add(columnName: "previous-meter-reading", type:Type.GetType("System.Int32"));
            dtGasBills.Columns.Add(type: Type.GetType("System.Int32"), columnName: "current-meter-reading");
            dtGasBills.Columns.Add(new DataColumn("consumption-value", Type.GetType("System.Int32")));

            dsGasBills = new DataSet("customers-invoices");
            dsGasBills.Tables.Add(dtGasBills);
        }

        // GET: GasBills
        public ActionResult Index()
        {
            return View();
        }

        . . . No Change
    }
}
  2. In the Solution Explorer, right-click Controllers -> Add -> Controller...
  3. In the middle list of the Add Scaffold dialog box, make sure MVC 5 Controller With Read/Write Actions is selected. Click Add
  4. Press Enter
  5. Type Payments to get PaymentsController
  6. Press Enter
  7. Change the class as follows: 
    using System.Data;
using System.Web.Mvc;

namespace GasDistribution1.Controllers
{
    public class PaymentsController : Controller
    {
        private readonly DataColumn dcPaymentID;
        private readonly DataColumn dcGasBillID;

        private DataTable dtPayments;
        private DataSet   dsPayments;

        public PaymentsController()
        {
            dcPaymentID = new DataColumn()
            {
                ColumnName = "payment-id",
                AutoIncrement = true,
                AutoIncrementSeed = 100001,
                AutoIncrementStep = 1
            };

            dcGasBillID = new DataColumn()
            {
                ColumnName = "gas-bill-id",
                DataType = Type.GetType("System.Int32")
            };

            dtPayments = new DataTable("payment");
            dtPayments.Columns.Add(dcPaymentID);
            dtPayments.Columns.Add(dcGasBillID);

            dtPayments.Columns.Add(new DataColumn()
            {
                ColumnName = "payment-date",
                DataType = Type.GetType("System.DateTime")
            });

            dtPayments.Columns.Add(columnName: "payment-amount",
                                   type: Type.GetType("System.Decimal"));

            dsPayments = new DataSet("payments");
            dsPayments.Tables.Add(dtPayments);
        }
        // GET: Payments
        public ActionResult Index()
        {
            return View();
        }

        . . . No Change
    }
}
  8. Click the CustomersController.cs tab to access it
  9. Change the definition of the CustomerID column as follows: 
    using System;
using System.IO;
using System.Data;
using System.Web.Mvc;

namespace GasDistribution1.Controllers
{
    public class CustomersController : Controller
    {
        . . . No Change

        public DataTable dtCustomers;
        private DataSet dsCustomers;

        public CustomersController()
        {
            dcCustomerID = new DataColumn()
            {
                ColumnName = "customer-id",
                AutoIncrement = true,
                AutoIncrementStep = 1,
                AutoIncrementSeed = 1
            };

            dcCity = new DataColumn("city", Type.GetType("System.String"));
            dcState = new DataColumn("state", Type.GetType("System.String"));
            . . . No Change

            dsCustomers = new DataSet("customers");
            dsCustomers.Tables.Add(dtCustomers);
        }

        // GET: Customers
        public ActionResult Index()
        {
            return View();
        }

        . . . No Change
                 
    }
}
  10. Click the GasMetersController.cs tab to access it
  11. Change the definition of the GasMeterID column as follows: 
    using System;
using System.IO;
using System.Data;
using System.Web.Mvc;

namespace GasDistribution1.Controllers
{
    public class GasMetersController : Controller
    {
        private readonly DataColumn dcGasMeterID;
        private readonly DataColumn dcMake;
        private readonly DataColumn dcModel;
        private readonly DataColumn dcMeterNumber;
        private readonly DataColumn dcCounterValue;

        public DataTable dtGasMeters;
        public DataSet dsGasMeters;

        public GasMetersController()
        {
            dcGasMeterID = new DataColumn("gas-meter-id");
            dcGasMeterID.AutoIncrement = true;
            dcGasMeterID.AutoIncrementSeed = 1;
            dcGasMeterID.AutoIncrementStep = 1;

            . . . No Change
        }

        . . . No Change

    }
}

The Default Value of a Column

When performing data entry, the user is expected to enter a value for each column. Sometimes, most values under a certain column would be the same. For this reason, you can provide a default value so that, if the user does not enter it, it would be used.

A default value is one that is automatically applied to a column so the user can simply accept it but the user can change it if it does not apply.

To specify the default value, assign the desired value to the DefaultValue property of the data column variable. He is an example:

<!DOCTYPE html>
<html>
<head>
<title>Employees</title>
</head>
<body>

@{
    System.Data.DataColumn colEmployeeNumber = new System.Data.DataColumn("StudentNumber", Type.GetType("System.Int32"));
    colEmployeeNumber.Unique = true;
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName", Type.GetType("System.String"));
    System.Data.DataColumn colMiddleInitial = new System.Data.DataColumn("MiddleInitial", Type.GetType("System.Char"));
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    colLastName.DataType = Type.GetType("System.String");
    System.Data.DataColumn colDateHired = new System.Data.DataColumn("DateHired");
    colDateHired.DataType = Type.GetType("System.DateTime");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender", Type.GetType("System.String"));
    colGender.DefaultValue = "Unknown";

    System.Data.DataTable tblEmployees = new System.Data.DataTable("EmployeesSet");
    tblEmployees.Columns.Add(colEmployeeNumber);
    tblEmployees.Columns.Add(colFirstName);
    tblEmployees.Columns.Add(colMiddleInitial);
    tblEmployees.Columns.Add(colLastName);
    tblEmployees.Columns.Add(colDateHired);
    tblEmployees.Columns.Add(colGender);

    System.Data.DataSet dsPersonnel = new System.Data.DataSet("Employees");
    dsPersonnel.Tables.Add(tblEmployees);

    object[] empl293847 = { 293847, "James", 'D', "Clemenson", new DateTime(2012, 6, 24), "Male" };
    tblEmployees.Rows.Add(empl293847);
    object[] empl938405 = { 938405, "Jennifer", 'M', "Euel", new DateTime(2015, 11, 4), "Female" };
    tblEmployees.Rows.Add(empl938405);
    object[] empl839751 = { 839751, "Chris", 'E', "Jacobs", new DateTime(2010, 8, 31) };
    tblEmployees.Rows.Add(empl839751);
    object[] empl185829 = { 185829, "Jeannine", 'H', "Simmons", new DateTime(2002, 5, 17), "Female" };
    tblEmployees.Rows.Add(empl185829);
    object[] empl372947 = { 372947, "Françoise", 'L', "Hughes", new DateTime(2007, 10, 29), "Female" };
    tblEmployees.Rows.Add(empl372947);
    object[] empl83948 = { 83948, "Robin", 'F', "Peters", new DateTime(2010, 2, 23) };
    tblEmployees.Rows.Add(empl83948);
}

<h1>Employees</h1>

<table border="5">
    <tr>
        <td><b>Employee #</b></td>
        <td><b>First Name</b></td>
        <td><b>MI</b></td>
        <td><b>Last Name</b></td>
        <td><b>Gender</b></td>
        <td><b>Date Hired</b></td>
    </tr>
    @foreach (System.Data.DataRow drEmployee in tblEmployees.Rows)
    {
        foreach (System.Data.DataColumn col in tblEmployees.Columns)
        {
            <tr>
                <td>@drEmployee["StudentNumber"]</td>
                <td>@drEmployee["FirstName"]</td>
                <td>@drEmployee["MiddleInitial"]</td>
                <td>@drEmployee["LastName"]</td>
                <td>@drEmployee["Gender"]</td>
                <td>@DateTime.Parse(drEmployee["DateHired"].ToString()).ToShortDateString()</td>
            </tr>
            break;
        }
    }
</table>

</body>
</html>

This would produce:

The Default Value

The Maximum Text Length of a Column

If a column is configured to receive text, that is, if its data type is set to string, by default, it can hold 0 to 32767 characters. This is (too) long for most cases. To support the ability to control the number of characters that a text-based column would allow, the DataColumn class is equipped with a property named MaxLength. The default value of this property is -1, which means there is no limit. Here are examples of setting this property:

@{
    System.Data.DataColumn colEmployeeNumber = new System.Data.DataColumn("StudentNumber", Type.GetType("System.Int32"));
    colEmployeeNumber.Unique = true;
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName", Type.GetType("System.String"));
    colFirstName.MaxLength = 25;
    System.Data.DataColumn colMiddleInitial = new System.Data.DataColumn("MiddleInitial", Type.GetType("System.Char"));
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    colLastName.DataType = Type.GetType("System.String");
    colLastName.MaxLength = 25;
    System.Data.DataColumn colDateHired = new System.Data.DataColumn("DateHired");
    colDateHired.DataType = Type.GetType("System.DateTime");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender", Type.GetType("System.String"));
    colGender.DefaultValue = "Unknown";
    colGender.MaxLength = "Unknown".Length;

    System.Data.DataColumn colFullName = new System.Data.DataColumn("FullName");
    colFullName.DataType = Type.GetType("System.String");
    colFullName.Expression = "FirstName + ' ' + LastName";
}

Practical Learning: Setting the Maximum Lengths of Columns

Column Values and Nullity

When performing data entry, if the user does not have a value for a certain column, he or she may skip it. In some cases, you may want a value to be required; that is, you would not let the column to be left empty. When a column is left empty, it is referred to as null.

To support the ability to have a null value or to require it, the DataColumn class is equipped with a Boolean property named AllowDBNull:

public bool AllowDBNull { get; set; }

If you want the user to be able to skip a column and not provide a value, you can ignore this property or set it to true. To require a value for a column, set this property to false.

Practical Learning: Setting the Maximum Lengths of Columns

  1. Change the GasMetersController class as follows: 
    using System;
using System.IO;
using System.Data;
using System.Web.Mvc;

namespace GasDistribution1.Controllers
{
    public class CustomersController : Controller
    {
        . . . No Change

        public CustomersController()
        {
            dcCustomerID = new DataColumn()
            {
                ColumnName = "customer-id",
                AutoIncrement = true,
                AutoIncrementStep = 1,
                AutoIncrementSeed = 1
            };

            dcCity = new DataColumn("city", Type.GetType("System.String"));
            dcState = new DataColumn("state", Type.GetType("System.String"));
            // To calculate state taxes, we need the name of a state.
            dcState.AllowDBNull = false;
            dcCounty = new DataColumn("county", Type.GetType("System.String"));
            dcAddress = new DataColumn("address", Type.GetType("System.String"));
            dcZIPCode = new DataColumn("zip-code", Type.GetType("System.String"));
            /* To calculate the local taxes, we may use the name of the County, but in 
             * some cases, different states have counties that use the same name.
             * Also, in some cases (such as New york, there are no counties per se 
             * (instead, they use the name borough or something else.
             Also, in some cases (check Virginia, etc), some cities don't belong to a county 
             (they are independent cities). Therefore, another option to calculate local taxes 
             is to use a ZIP Code (certainly every city belongs to ZIP Code)*/
            dcZIPCode.AllowDBNull = false;
            dcLastName = new DataColumn("last-name", Type.GetType("System.String"));
            dcLastName.MaxLength = 20;
            dcLastName.AllowDBNull = false;

            dcFirstName = new DataColumn("first-name", Type.GetType("System.String"));
            dcFirstName.MaxLength = 20;
            dcCustomerMeter = new DataColumn("meter-number", Type.GetType("System.String"));

            . . . No Change
        }

        // GET: Customers
        public ActionResult Index()
        {
            return View();
        }

        . . . No Change

    }
}

The Expression of a Column

Introduction

So far, to perform data entry, we created the data fields and expected the user to enter values in them. In some cases, instead of the user typing data, you may want to specify your own constant value or you may want to combine some values. An expression can be:

Besides the items in this list, you can also use some functions and/or combine them with the items in the above list. The expression then creates or represents a value. To create an expression, there are various rules you must follow:

To specify the expression used on a column, assign the expression, as a string, to its variable name. Here is an example:

<!DOCTYPE html>
<html>
<head>
<title>Employees</title>
</head>
<body>

@{
    System.Data.DataColumn colEmployeeNumber = new System.Data.DataColumn("StudentNumber", Type.GetType("System.Int32"));
    colEmployeeNumber.Unique = true;
    System.Data.DataColumn colFirstName = new System.Data.DataColumn("FirstName", Type.GetType("System.String"));
    System.Data.DataColumn colMiddleInitial = new System.Data.DataColumn("MiddleInitial", Type.GetType("System.Char"));
    System.Data.DataColumn colLastName = new System.Data.DataColumn("LastName");
    colLastName.DataType = Type.GetType("System.String");
    System.Data.DataColumn colDateHired = new System.Data.DataColumn("DateHired");
    colDateHired.DataType = Type.GetType("System.DateTime");
    System.Data.DataColumn colGender = new System.Data.DataColumn("Gender", Type.GetType("System.String"));
    colGender.DefaultValue = "Unknown";

    System.Data.DataColumn colFullName = new System.Data.DataColumn("FullName");
    colFullName.DataType = Type.GetType("System.String");
    colFullName.Expression = "FirstName + ' ' + LastName";

    System.Data.DataTable tblEmployees = new System.Data.DataTable("EmployeesSet");
    tblEmployees.Columns.Add(colEmployeeNumber);
    tblEmployees.Columns.Add(colFirstName);
    tblEmployees.Columns.Add(colMiddleInitial);
    tblEmployees.Columns.Add(colLastName);
    tblEmployees.Columns.Add(colDateHired);
    tblEmployees.Columns.Add(colGender);
    tblEmployees.Columns.Add(colFullName);

    System.Data.DataSet dsPersonnel = new System.Data.DataSet("Employees");
    dsPersonnel.Tables.Add(tblEmployees);

    object[] empl293847 = { 293847, "James", 'D', "Clemenson", new DateTime(2012, 6, 24), "Male" };
    tblEmployees.Rows.Add(empl293847);
    object[] empl938405 = { 938405, "Jennifer", 'M', "Euel", new DateTime(2015, 11, 4), "Female" };
    tblEmployees.Rows.Add(empl938405);
    object[] empl839751 = { 839751, "Chris", 'E', "Jacobs", new DateTime(2010, 8, 31) };
    tblEmployees.Rows.Add(empl839751);
    object[] empl185829 = { 185829, "Jeannine", 'H', "Simmons", new DateTime(2002, 5, 17), "Female" };
    tblEmployees.Rows.Add(empl185829);
    object[] empl372947 = { 372947, "Françoise", 'L', "Hughes", new DateTime(2007, 10, 29), "Female" };
    tblEmployees.Rows.Add(empl372947);
    object[] empl83948 = { 83948, "Robin", 'F', "Peters", new DateTime(2010, 2, 23) };
    tblEmployees.Rows.Add(empl83948);
}

<h1>Employees</h1>

<table border="5">
    <tr>
        <td><b>Employee #</b></td>
        <td><b>Full Name</b></td>
        <td><b>Gender</b></td>
        <td><b>Date Hired</b></td>
    </tr>
    @foreach (System.Data.DataRow drEmployee in tblEmployees.Rows)
    {
        foreach (System.Data.DataColumn col in tblEmployees.Columns)
        {
            <tr>
                <td>@drEmployee["StudentNumber"]</td>
                <td>@drEmployee["FullName"]</td>
                <td>@drEmployee["Gender"]</td>
                <td>@DateTime.Parse(drEmployee["DateHired"].ToString()).ToShortDateString()</td>
            </tr>
            break;
        }
    }
</table>

</body>
</html>

This would produce:

Using Operators and Expressions

To create an expression, you use the logical operators from the Visual Basic language. Most of the operators are the same you are already familiar with from your knowledge of C#, except as follows:

Operator Name C# Visual Basic
Equal == =
Less Than < <
Less Than Or Equal To <= <=
Greater Than > >
Greater Than Or Equal To >= >=
Not Equal != <>

You can also use the other logical operators, such as the negation operator, the conjunction operator, and the disjunction operator. Once again, you must use them as they are implemented in the Visual Basic language as follows:

Operator Name C# Visual Basic
Negation ! NOT
Logical Conjunction && AND
Logical Disjunction || OR

Using Functions

The .NET Framework provides various functions (or methods) you can use for data filtering. The following logical functions available are (although the names are given here in uppercase, the language is actually case-insensitive):

The following functions are used to evaluate the values from one particular column. They are called aggregate functions and they are:

To manipulate strings, the following functions are available (although the names are given here in uppercase, the language is actually case-insensitive):
Previous Copyright © 2005-2019, FunctionX Next