If you are starting a new application and you have the need for data you will probably think of storing your data in a database. If it is a small amount of data, and you are using .Net you may think to use SQL Express, or SQL Server Compact or even SqLite. This may not be the best way.
Let’s say you are building a database and you are keeping track of people’s phone numbers, your CD collection, a To-Do list, Password collection, Home Inventory, Bookmarks, FTP sites, or Stats for the Cows in your Herd. Now each record would be less than 1,000 bytes in size and you would have less than 1,000 records then your total memory size (if all the records were kept in memory) is only 1MB; much smaller than a DB engine. DataBase Engines like SQL Express may be easy to develop for, but very difficult to deploy.
What I do in cases like this is to use the DataTable as a Data-Store and not attach it to a DataBase. I save and restore the data from a hard drive (or network) using XML. One may use a a Serialized collection for the same purpose but this will not connect to any of .Net built in Data Components like the DataGridView.
Here is a quick tutorial.
For example: say you need a database to keep track of Phone Numbers, Email Address and twitter accounts of your friends. The first thing to do is to make a class that would hold the information.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Friends
{
class Friend
{
// constructors
public Friend():
this(String.Empty, String.Empty, String.Empty, String.Empty)
{
}
public Friend(String Name, String Phone,
String Email, String Twitter)
{
fName = Name;
fPhone = Phone;
fEmail = Email;
fTwitter = Twitter;
}
// properties
private int fID;
public int ID
{
get { return fID; }
set { fID = value; }
}
private String fName;
public String Name
{
get { return fName; }
set { fName = value; }
}
private String fPhone;
public String Phone
{
get { return fPhone; }
set { fPhone = value; }
}
private String fEmail;
public String Email
{
get { return fEmail; }
set { fEmail = value; }
}
private String fTwitter;
public String Twitter
{
get { return fTwitter; }
set { fTwitter = value; }
}
// methods
public override string ToString()
{
return String.Format(
"ID={0} Name={1} Phone={2} Email={3} Twitter={4}",
fID, fName, fPhone, fEmail, fTwitter);
}
}
}
Create a new Class called DBEngine and create a DataTable Object called FriendsTable. We are going to add several methods to this class.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace Friends
{
class DBEngine
{
public DataTable FriendsTable;
}
}
Add a method to create the Fields (Columns) that you need in the Table.
// build the Friends Table
public void BuildFriendsTable()
{
FriendsTable = new DataTable("Friends"); // name is needed
DataColumn c;
// add unique ID
c = FriendsTable.Columns.Add("ID", typeof(Int32));
c.AllowDBNull = false;
c.Unique = true;
c.AutoIncrement = true;
c.AutoIncrementStep = 1;
c.AutoIncrementSeed = 1;
c = FriendsTable.Columns.Add("Name", typeof(String));
c.AllowDBNull = false;
c = FriendsTable.Columns.Add("Phone", typeof(String));
c.AllowDBNull = true;
c = FriendsTable.Columns.Add("EMail", typeof(String));
c.AllowDBNull = true;
c = FriendsTable.Columns.Add("Twitter", typeof(String));
c.AllowDBNull = true;
}
Now add the methods to save and load the Table to the Hard Disk.
// load all of your friends from a file
public bool LoadFriendsTable(
String friendsFileName, String friendsShemaFileName)
String friendsFileName, String friendsShemaFileName)
{
// return false if the file or Schema does not exist
if (!File.Exists(friendsFileName))
return false;
if (!File.Exists(friendsShemaFileName))
return false;
// load the schema and file
try
{
FriendsTable = new DataTable();
FriendsTable.ReadXmlSchema(friendsShemaFileName);
FriendsTable.ReadXml(friendsFileName);
}
catch(Exception e)
{
Debug.WriteLine(e.Message);
return false;
}
return true;
}
// save all of your friends to a file.
public bool SaveFriendsTable(
String friendsFileName, String friendsShemaFileName)
String friendsFileName, String friendsShemaFileName)
{
// write out the schema and file
try
{
FriendsTable.WriteXmlSchema(friendsShemaFileName);
FriendsTable.WriteXml(friendsFileName);
}
catch(Exception e)
{
Debug.WriteLine(e.Message);
return false;
}
return true;
}
The DataTable class’s native data container is the DataRow. To get data from a data row you can use statement like this one:
String Name = row["Name"].ToString();
And to set a field in a row you can use a statement like this:
row["Name"] = "Hammer Head Jones";
I never, ever use this approach outside of the DataEngine class. I always pass a class or receive a class that represents the Data; in this project the class is called ‘Friend’.
The reason for this is that it is much too easy to do this:
row["name"] = "Hammer Head Jones"; // name is wrong
when you wanted to do this:
row["Name"] = "Hammer Head Jones"; // Name is correct
This error will not be found by the compiler and won’t be found until run time.
If you use a class to represent your data then something like this will always be caught by the compiler.
Friend f = new Friend();
f.name = "Hammer Head Jones"; // WRONG: Should be f.Name
With that in mind; here are the methods to get a Friend or all of your Friends. Notice they return a Friend Class or a List of Friends, not a row or row[];
// get a friend by their unique ID
public Friend GetFriendByID(int id)
{
// if record does not exist then return false
DataRow[] rows = FriendsTable.Select("ID=" + id.ToString());
if (rows.Length != 1)
return null;
// get record and generat Friend to put it in
DataRow r = rows[0];
Friend f = new Friend();
// load the data
f.ID = Convert.ToInt32(r["ID"]);
f.Name = r["Name"].ToString();
f.Phone = r["Phone"].ToString();
f.Email = r["Email"].ToString();
f.Twitter = r["Twitter"].ToString();
return f;
}
// get a list of all of your friends
public List<Friend> GetAllFriends()
{
// create a list of Friend(s)
List<Friend> friendList = new List<Friend>();
foreach (DataRow r in FriendsTable.Rows)
{
// create a new friend and load data into it
Friend f = new Friend();
f.ID = Convert.ToInt32(r["ID"]);
f.Name = r["Name"].ToString();
f.Phone = r["Phone"].ToString();
f.Email = r["Email"].ToString();
f.Twitter = r["Twitter"].ToString();
// add the friend to the list
friendList.Add(f);
}
return friendList;
}
Lastly I three routines to Add, Delete or Modify a Friend.
// make a new entry and then add it
// friend will have the unique ID in it when it returns
public bool AddFriend(ref Friend f)
{
// generate a new datarow
DataRow row = FriendsTable.NewRow();
f.ID = Convert.ToInt32(row["ID"]); // get unique ID
// store data into the new row
row["Name"] = f.Name;
row["Phone"] = f.Phone;
row["Email"] = f.Email;
row["Twitter"] = f.Twitter;
// add new Friend to table
FriendsTable.Rows.Add(row);
return true;
}
// delete a friend
public bool DeleteFriendByID(int id)
{
// if record does not exist then return false
DataRow[] rows = FriendsTable.Select("ID=" + id.ToString());
if (rows.Length != 1)
return false;
// delete entry and then save it
rows[0].Delete();
FriendsTable.AcceptChanges();
return true;
}
// change data for a specific friend.
public bool ModifyFriend(Friend f)
{
// if record does not exist then return false
DataRow[] rows = FriendsTable.Select("ID=" + f.ID.ToString());
if (rows.Length != 1)
return false;
// modify entry and then save it.
DataRow row = rows[0];
row.BeginEdit();
row["Name"] = f.Name;
row["Phone"] = f.Phone;
row["Email"] = f.Email;
row["Twitter"] = f.Twitter;
row.EndEdit();
FriendsTable.AcceptChanges();
return true;
}
One thing worth mentioning is the Add class has ref parameter:
public bool AddFriend(ref Friend f)
The DataTable’s ID Field has been set up to auto-generate a unique ID. Once this method is called that unique ID will be in the f.ID field. In my code keeping this id can be pretty handy.
The DataEngine class has an exposed (Public) DataTable called FriendsTable. One can attach this table to any DataObject, for example; to connect it to a DataGridView:
DBEngine db = new DBEngine();
dataGridView1.DataSource = db.FriendsTable;
The DataTable has a powerful ‘Select’ statement which is similar to the ‘WHERE’ clause in a SQL statement. If you need to filter data further or sort the data you can connect it to a DataView as well:
DBEngine db = new DBEngine();
DataView dv = new DataView(db.FriendsTable);
dataGridView1.DataSource = dv;
That completes the DBEngine and Friend Class. Most of the time you will have more than one table in your database. When this happens I will make a separate class for the Data but add all of the Methods to the same DBEngine. For Example: Assume that you want to have another table for your friend’s cars that has make, model, color and year. I would make a separate Car class for the data but my DBEngine would now have these calls.
// Friends
BuildFriendsTable();
LoadFriendsTable();
SaveFriendsTable();
GetFriendByID();
GetAllFriends();
AddFriend();
DeleteFriendByID();
ModifyFriend();
// Cars
BuildCarsTable();
LoadCarsTable();
SaveCarsTable();
GetCarByID();
GetAllCars();
AddCar();
DeleteCarByID();
ModifyCar();
It is not uncommon to me to have a method that returns a joined result like a Friend and their primary car.
GetFriendWithPrimaryCarByID(int id);
In that case I will make a third class to represent that data and use the GetFriendByID and GetCarByID routines to populate the class.
Well that’s it; Lightweight Data Storage without the huge overhead of database; and it works with all of the existing Data Components. I use this technique so often that I have considered writing a program that one would enter the Class Name and the fields and it would generate the code.
For completeness I wrote a Test Class called DBEngineTest. Here is the code for this.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Windows.Forms;
using System.IO;
namespace Friends
{
class DBEngineTest
{
DBEngine db;
// Testing
public DBEngine Test()
{
db = new DBEngine();
String AppStorageDirectory = GetThisApplicationDirectory();
if (!Directory.Exists(AppStorageDirectory))
Directory.CreateDirectory(AppStorageDirectory);
String FriendsFileName = AppStorageDirectory
+ @"\Friends.xml";
+ @"\Friends.xml";
String FriendsSchemaFileName = AppStorageDirectory
+ @"\FriendsSchema.xsd";
+ @"\FriendsSchema.xsd";
db.BuildFriendsTable();
// make sure you can save and load
db.SaveFriendsTable(FriendsFileName, FriendsSchemaFileName);
db.LoadFriendsTable(FriendsFileName, FriendsSchemaFileName);
int billid, tomid, bobid, annid, jackid;
// add a few people then print.
Friend f;
f = new Friend("Bill Smith", "919-555-1234",
"bills@noplace","@billysmitty");
"bills@noplace","@billysmitty");
db.AddFriend(ref f);
billid = f.ID;
f = new Friend("Tom Smith", "919-555-1235",
"toms@noplace", "@tomaderson");
"toms@noplace", "@tomaderson");
db.AddFriend(ref f);
tomid = f.ID;
f = new Friend("Bob Marsh", "919-555-1236",
"", "");
"", "");
db.AddFriend(ref f);
bobid = f.ID;
f = new Friend("Ann Marsh", "919-555-1237",
"", "");
"", "");
db.AddFriend(ref f);
annid = f.ID;
f = new Friend("Jack Russle", "919-555-1238",
"", "@russle");
"", "@russle");
db.AddFriend(ref f);
jackid = f.ID;
PrintAllFriends();
// Remove Ann and Bob
db.DeleteFriendByID(annid);
db.DeleteFriendByID(bobid);
Debug.WriteLine("Removed Ann and Bob");
PrintAllFriends();
// Change Tom Smith to Tom Anderson
f = db.GetFriendByID(tomid);
f.Name = "Tom Anderson";
db.ModifyFriend(f);
Debug.WriteLine("Changed Tom Smith to Tom Anderson");
PrintAllFriends();
// Save Result
db.SaveFriendsTable(FriendsFileName, FriendsSchemaFileName);
return db;
}
// Print all Entries
public void PrintAllFriends()
{
List<Friend> Friends = db.GetAllFriends();
foreach (Friend f in Friends)
Debug.WriteLine(f.ToString());
}
public String GetThisApplicationDirectory()
{
return System.IO.Path.Combine(
GetApplicationDirectory(), GetProgramName());
GetApplicationDirectory(), GetProgramName());
}
public String GetProgramName()
{
return Application.ProductName;
}
public String GetApplicationDirectory()
{
return Environment.GetFolderPath(
Environment.SpecialFolder.ApplicationData);
Environment.SpecialFolder.ApplicationData);
}
}
}
To test this code create a form with a button and datagridview on it. Use this as your button_Click event.
private void btnTest_Click(object sender, EventArgs e)
{
DBEngineTest dbt = new DBEngineTest();
DBEngine db = dbt.Test();
DataView dv = new DataView(db.FriendsTable);
dataGridView1.DataSource = dv;
}
