Working with Data—Roll Your Own Database

Microsoft .NET Framework, ASP.NET, Visual C# (CSharp, C Sharp, C-Sharp) Developer Training, Visual Studio

CSharp-Online.NET:Articles

Database Articles

Working with Data Databases Intro to SQL ADO.NET Classes Roll Your Own Database Define Table Structure Reading Rows Adding Rows Storing as XML Filtering Data Connect to Access Connect to SQL Server Data Binding Using DataGridView

© 2006 Jeffery Suddeth

Roll Your Own Database

In this section, we use the ADO.NET classes from Table 15.3 to define a database of customer information. We will define the structure of the database by adding columns to a table named Customers. Then, we will load some sample data into the table and persist the data to an XML file. When the program runs again, we will load the database from the XML file and print the data that it contains. The complete listing is shown in Example15_1.cs.

Listing 15.1

// Example15_1.cs
using System;
using System.Text;
using System.Data;
using System.IO;
 
namespace csbook.ch15 {
 
   class CustomerDatabase {
 
      public void MakeFile() {
         DataSet ds = new DataSet();
         ds.DataSetName = "CustomerDatabase";
 
         DataTable custTable = new DataTable();
         custTable.TableName = "Customers";
 
         // this will be the primary key
         DataColumn custId = new DataColumn("CustID", typeof(int));
 
         custId.AutoIncrement = true;
         custId.AutoIncrementSeed = 101;
         custId.AutoIncrementStep = 1;
         custTable.Columns.Add(custId);
 
         // make this the primary key
         custTable.PrimaryKey = new DataColumn[] { custId };
 
         // add some fields for customer data
         custTable.Columns.Add(new DataColumn("Name", typeof(string)));
 
         custTable.Columns.Add(new DataColumn("Phone", typeof(string)));
 
         custTable.Columns.Add(new DataColumn("Email", typeof(string)));
 
         // add the table to the DataSet
         ds.Tables.Add(custTable);
 
 
         // add some records to the table
         DataRow row;
         row = custTable.NewRow();
         row["Name"] = "Evy";
         row["Phone"] = "123-555-9876";
         row["Email"] = "evy@evymail.com";
         custTable.Rows.Add(row);
 
         row = custTable.NewRow();
         row["Name"] = "Jon";
         row["Phone"] = "123-444-7384";
         row["Email"] = "jon@jonsmail.com";
         custTable.Rows.Add(row);
 
         row = custTable.NewRow();
         row["Name"] = "Eric";
         row["Phone"] = "123-666-7398";
         row["Email"] = "eric@ericsmail.com";
         custTable.Rows.Add(row);
 
         // dump to XML
         ds.WriteXml("customers.xml");
 
         Console.WriteLine("The file was created");
         }
 
      public void ReadFile() {
         DataSet ds = new DataSet();
         ds.ReadXml("customers.xml");
 
         foreach (DataRow row in ds.Tables[0].Rows) {
            // get the data
            string name = (string)row["Name"];
            string phone = (string)row["Phone"];
            string email = (string)row["Email"];
 
            // build the string
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat
               ("Name: {0}\t Phone: {1}\t Email: {2}", 
                 name, phone, email);
            // write the string
            Console.WriteLine(builder.ToString());
            }
         }
 
      public void Test() {
         if (File.Exists("customers.xml")) {
            ReadFile();
            }
         else {
            MakeFile();
            }
         }
 
      static void Main(string[] args) {
         CustomerDatabase db = new CustomerDatabase();
         db.Test();
         }
      }
   }

Listing 15.1 defines the Customers table to have the structure shown below.

Table 15.4 – The Customers Table

Field Name		Data Type
CustID		int, auto increment, primary key
Name		string
Phone		string
Email		string

To define the table, we create a DataTable object and set its TableName property to "Customers". The DataTable has a property named Columns, which is a collection of DataColumn objects that define the columns of the table. Another important property of the DataTable is the PrimaryKey property. The PrimaryKey property is an array of DataColumn objects. You can set the primary key by creating an array of one or more DataColumns from the table and assigning a reference to that array in the PrimaryKey property.

The DataColumn class has many properties, many of which you won’t care about for the examples here. However, two important properties are the Name and DataType.

The Name property is a string value that names the field. When you access a column of a DataRow you can use this name as an index instead of an integer; so, you don’t have to remember the order of the columns.

The DataType property stores an instance of the Type class containing the type of data the column will store. The data are stored in the fields as object references so you can use any C# data type. If the DataType of a column is int, then you can set the column’s AutoIncrement property to true. If the AutoIncrement property is true, then the column’s value will be set automatically from a counter variable that is incremented every time a new row is created. This guarantees that the field will hold a unique value for each row in the table and makes the field a good candidate to use as a primary key.