Introduction
In this article is fully based on how to querying the flat file database (i.e. text file database). The flat file database querying is quite difficult. I write this article to create a simple application with ability to manipulate the flatfile database (i.e. text file).
System Analysis
FlatFile Database:
This is the basic database system. It makes easier our work while comparing writing the data's in paper. It saves the paper. A flat file database is the simplest form of database systems. There is no posiblity to access the multiple tables like a RDBMS. Because it uses the simple strucutre, A textfile considered as a table. Every line of the text file is rows of table and the columns are separetd by delimiters like comma(,), tab, and some special characters. The database doesnot have specific datatype. It Only supports strings.
System Design
The system based on File Input Output processing and Streams. This system also same as flatfile database the difference is we can use multiple tables. The similarity is the multiple table querying such as Join queries are not proccessed currently.
Database:
The database is nothing but, It creates the folder for a database Name in application startup folder.
Tables:
In this system the tables are holding all the datas. In this System the table structure has two parts
1. Header Row
2. Data Rows
Header Row consists the column names. Data Rows consist the rcords related to the columns. Here Some special symbols are used as a delimiters.
Symbols:
├ - Header Row Begining
┤ - Header Row End
└ - Data Row Begining
┘ - Data Row End
│ - Column Separator
Fig. Overview of System
Querying:
The system support 15 and more SQL Type Queries to querying the Database. The following queries are used in this system.
1) CREATE DATABASE StudentDB
This is the query for creating the database named "StudendDB".
2) LIST DATABASES
This query shows list of database in the system.
3) DROP DATABASE StudentDB
This query deletes the Database StudentDB.
4) USE StudentDB
This query holds the StudentDB database name for further database manipulation quries such as INSERT, DELETE, UPDATE, SELECT, etc.,
5) LIST TABLES
This query shows the tables in the database which is currenlty hold for manipulation.
6) DROP TABLE tblMarkSheet
This query delete the table from the database.
7) CREATE TABLE tblMarkSheet (RollNo,StudentName,ClassStd,Mark1,Mark2,Mark3,Total,Avg)
This query crates tblMarkSheet table with Columns of RollNo, StudentName, ClassStd, Mark1, Mark2, Mark3, Total and Avg. In Background It creates one file named "tblMarkSheet.fdb" in "StudentDB" Folder.
8) 1. INSERT INTO tblMarkSheet (RollNo,StudentName,ClassStd,Mark1,Mark2,Mark3,Total,Avg) VALUES (1, ANAND,10TH,85,48,59,192,64)
This query insert a record in tblMarkSheet table.
2. INSERT INTO tblMarkSheet VALUES (1, ANAND,10TH,85,48,59,192,64)
This query also same as previous insert query. The difference is we don't need to mention the column names.
9) 1. DELETE FROM tblMarkSheet WHERE RollNo=10
This query deletes the record which record column values is equal to 10 in tblMarkSheet table.
2. DELETE FROM tblMarkSheet
This query deletes all the record from tblMarkSheet Table.
10) 1. UPDATE tblMarkSheet SET (ClassStd) VALUES (11TH) WHERE RollNo=1
This query modify the ClassStd field of the record which holds RollNo has 1.
1.1. UPDATE tblMarkSheet SET (ClassStd) VALUES (11TH)
2. UPDATE tblMarkSheet SET VALUES (1, XXX,5TH,40) WHERE RollNo=1
This query sequentiay updates the record which holds the RollNo has 1. Here we don't need to mention the updating columns, it automatically updates from the sequence and missed column values are remain in the same in previous.
2.1. UPDATE tblMarkSheet SET VALUES (1, XXX,5TH,40)
Note: The queries 11 of 1.1 and 2.1 are executed without condition.
11) 1. SELECT * FROM tblMarkSheet
2. SELECT * FROM tblMarkSheet WHERE RollNo=1
3. SELECT RollNo,StudentName,Total FROM tblMarkSheet WHERE RollNo>5
4. SELECT RollNo,StudentName,Avg FROM tblMarkSheet WHERE Avg>=60
This query using to view the records of the table. Here we can filter the records by using relational operators.
Bugs and Flaws in Queries:
- In UPDATE and DELETE queries it supports only Equal (=) condition. It is not support for other operators like <, >, <=, etc.,
- In SELECT query the data filter feature is held by use of datatable and dataview. Need to provide native support.
- There is no JOIN queries when compared to RDBMS.
Advantage:
- It is our own database system. So we can modify the transaction of the database system.
- When compared to other database system is quite little and slower but security things are ours. So we feel free about data security.
- We can ensure the security by using cryptography things in this system.
- We can modify the table structure in encoded text. So that no one knows the inner structure of the database. There is no data theft.
Future Enhancement:
- In the future it will portable for more security and fully enhanced database structure. It could be supports JOIN quries.
System Implementation
From the system analysis and system design the code has been written. The system has IntelliSense TextBox for lesser the query writing, It has been taken from my previously posted tip IntelliSense TextBox in C#.
Querying Method:
The below method is using to parsing the raw query from the client.
public static string DataBase=null;
public static dynamic QueryExecution(string query)
{
object ValueObj = null;
string[] QueryArray = query.Split(new char[1] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
switch (QueryArray[0])
{
case "CREATE":
if (QueryArray[1] == "DATABASE")
ValueObj = CreateDB(QueryArray[2]);
else if (QueryArray[1] == "TABLE")
ValueObj = CreateTable(QueryArray[2], DataBase, StringArrayToString(QueryArray[3].Split(new char[3] { '(', ',', ')' }, StringSplitOptions.RemoveEmptyEntries)));
break;
case "DROP":
if (QueryArray[1] == "DATABASE")
ValueObj = DropDB(QueryArray[2]);
else if (QueryArray[1] == "TABLE")
ValueObj = DropTable(QueryArray[2], DataBase);
break;
case "USE":
ValueObj = UseDB(QueryArray[1]);
break;
case "LIST":
if (QueryArray[1] == "DATABASES")
ValueObj = ListDB();
else if (QueryArray[1] == "TABLES")
{
if (DataBase != null)
{
ValueObj = ListTables(DataBase);
}
else
{
MessageBox.Show("DATABASE NOT IN USE" + Environment.NewLine + "SELECT THE DATABASE, THEN EXECUTE THIS QUERY", "FFDB", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
break;
case "DELETE":
if (QueryArray.Length >= 5)
{
string[] tdcon = QueryArray[4].Split('=');
ValueObj = DeleteRecord(QueryArray[2], DataBase, tdcon[0], tdcon[1]);
}
else
ValueObj = DeleteRecord(QueryArray[2], DataBase);
break;
case "INSERT":
string[] valArray = query.Substring((query.LastIndexOf("VALUES") + 7)).Split(new char[4] { '(', ',', '\'', ')' }, StringSplitOptions.RemoveEmptyEntries);
if (QueryArray[4] == "VALUES")
{
string[] colArray = QueryArray[3].Split(new char[3] { '(', ',', ')' }, StringSplitOptions.RemoveEmptyEntries);
ValueObj = InsertRecord(QueryArray[2], DataBase, StringArrayToString(valArray), StringArrayToString(colArray));
}
else
{
ValueObj = InsertRecord(QueryArray[2], DataBase, StringArrayToString(valArray));
}
break;
case "UPDATE":
string[] tiv = query.Substring(query.LastIndexOf("VALUES") + 7).Split(new char[4] { '(', ',','\'', ')' }, StringSplitOptions.None);
if (query.LastIndexOf("WHERE") > -1)
{
if (QueryArray[4] == "VALUES")
{
string[] tcon = QueryArray[7].Split('=');
string[] tic = QueryArray[3].Split(new char[3] { '(', ',', ')' }, StringSplitOptions.RemoveEmptyEntries);
ValueObj = UpdateRecord(QueryArray[1], DataBase, StringArrayToString(tiv), StringArrayToString(tic), tcon[0], tcon[1]);
}
else
{
string[] tcon = QueryArray[6].Split('=');
ValueObj = UpdateRecord(QueryArray[1], DataBase, StringArrayToString(tiv), null, tcon[0], tcon[1]);
}
}
else
{
if (QueryArray[4] == "VALUES")
{
string[] tic = QueryArray[3].Split(new char[3] { '(', ',', ')' }, StringSplitOptions.RemoveEmptyEntries);
ValueObj = UpdateRecord(QueryArray[1], DataBase, StringArrayToString(tiv), StringArrayToString(tic));
}
else
{
ValueObj = UpdateRecord(QueryArray[1], DataBase, StringArrayToString(tiv), null);
}
}
break;
case "SELECT":
ValueObj = SelectFilter(query, DataBase);
break;
default:
ValueObj=false;
break;
}
return ValueObj;
}
Converting String Array to String Method:
The following code for coverting the string array to string
public static string StringArrayToString(string[] strArray)
{
string Str = "";
for (int i = 0; i < strArray.Length; i++)
{
Str += strArray[i];
if (i < strArray.Length - 1)
Str += ",";
}
return Str;
}
Database Creation Method:
public static bool CreateDB(string dbName)
{
if (Directory.Exists(Application.StartupPath + "\\" + dbName))
{
return false;
}
else
{
Directory.CreateDirectory(Application.StartupPath + "\\" + dbName);
return true;
}
}
Database Drop Method:
public static bool DropDB(string dbName)
{
if (!Directory.Exists(Application.StartupPath + "\\" + dbName))
{
return false;
}
else
{
Directory.Delete(Application.StartupPath + "\\" + dbName, true);
return true;
}
}
Displaying Database Method:
public static DataTable ListDB()
{
DirectoryInfo dir = new DirectoryInfo(Application.StartupPath);
DataTable dtData = new DataTable();
dtData.Columns.Add(new DataColumn("DATABASES_IN_FFDB", typeof(String)));
DataRow drRow;
object[] ObjArray =new object[1];
foreach (DirectoryInfo g in dir.GetDirectories())
{
ObjArray[0] = g.Name;
drRow = dtData.NewRow();
drRow.ItemArray = ObjArray;
dtData.Rows.Add(drRow);
}
return dtData;
}
USE DB Method:
Method for holding the current database for operation.
public static bool UseDB(string dbName)
{
bool tempVal = false;
DirectoryInfo dir = new DirectoryInfo(Application.StartupPath);
foreach (DirectoryInfo g in dir.GetDirectories())
{
if (dbName == g.Name)
{
DataBase = g.Name;
tempVal = true;
break;
}
else
{
tempVal = false;
continue;
}
}
if (tempVal == false)
{
MessageBox.Show(DataBase + " DATABASE is Not Exist!", "FFDB", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
return tempVal;
}
Displaying Table in Database:
public static DataTable ListTables(string dbName)
{
DirectoryInfo dir = new DirectoryInfo(Application.StartupPath+"\\"+dbName);
DataTable dtData = new DataTable();
dtData.Columns.Add(new DataColumn("TABLES_IN_"+dbName+"_DATABASE", typeof(String)));
DataRow drRow;
object[] ObjArray = new object[1];
foreach (FileInfo f in dir.GetFiles("*.fdb"))
{
ObjArray[0] = f.Name.Split(new string[1] {".fdb"},StringSplitOptions.RemoveEmptyEntries)[0];
drRow = dtData.NewRow();
drRow.ItemArray = ObjArray;
dtData.Rows.Add(drRow);
}
return dtData;
}
Table Creation and Drop Methods:
public static bool CreateTable(string tblName, string dbName, string tblColumns)
{
if (File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
return false;
else
{
FileStream fs = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
StreamWriter sw = new StreamWriter(fs);
string[] ColsArray = tblColumns.Split(',');
sw.Write("├");
for (int i = 0; i < ColsArray.Length; i++)
{
if (i < ColsArray.Length - 1)
ColsArray[i] += "│";
sw.Write(ColsArray[i]);
}
sw.Write("┤");
sw.Close();
return true;
}
}
//Table Drop Method
public static bool DropTable(string tblName, string dbName)
{
if (!File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
{
return false;
}
else
{
File.Delete(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb");
return true;
}
}
Filltering Record Method:
The following method filltering the records when executing the SELECT query.
public static DataTable SelectFilter(string unmodifiedQuery,string dbName)
{
string[] UQStr = unmodifiedQuery.Split(new char[1] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
DataTable dtTemp = SelectRecord(UQStr[3], dbName);
DataView dv = new DataView(dtTemp);
if (UQStr.Length==6)
{
dv.RowFilter = unmodifiedQuery.Substring(unmodifiedQuery.LastIndexOf("WHERE") + 6);
dtTemp = dv.ToTable();
}
if (UQStr[1] != "*")
{
dtTemp = dv.ToTable(false, UQStr[1].Split(new char[1] { ',' }, StringSplitOptions.RemoveEmptyEntries));
}
return dtTemp;
}
Select Record Method:
public static DataTable SelectRecord(string tblName, string dbName)
{
if (!File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
{
return null;
}
else
{
FileStream fs = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", FileMode.Open, FileAccess.Read, FileShare.Read, 10000, FileOptions.Asynchronous);
StreamReader sr = new StreamReader(fs);
string[] ColsArray = sr.ReadLine().Split(new char[3] { '├', '┤', '│' }, StringSplitOptions.RemoveEmptyEntries);
DataTable dtData = new DataTable();
for (int i = 0; i < ColsArray.Length; i++)
{
dtData.Columns.Add(new DataColumn(ColsArray[i], typeof(String)));
}
DataRow drRow;
object[] objRowArray = new object[ColsArray.Length];
while(!sr.EndOfStream)
{
string[] RowArray = sr.ReadLine().Split(new char[3] { '└', '┘', '│' }, StringSplitOptions.RemoveEmptyEntries);
for (int j = 0; j < ColsArray.Length; j++)
{
if (RowArray[j] != "■")
objRowArray[j] = RowArray[j];
else
objRowArray[j] = "";
}
drRow = dtData.NewRow();
drRow.ItemArray = objRowArray;
dtData.Rows.Add(drRow);
}
sr.Close();
fs.Close();
return dtData;
}
}
Inserting Record Method:
public static bool InsertRecord(string tblName, string dbName, string iValues, string iColumns=null)
{
if (!File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
return false;
else
{
FileStream fs = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
StreamWriter sw = new StreamWriter(fs);
StreamReader sr = new StreamReader(fs);
string[] ColsArray = sr.ReadLine().Split(new char[3] { '├', '┤', '│' }, StringSplitOptions.RemoveEmptyEntries);
string[] TempColsArray=null;
int TempiCols = 0;
string[] TempcValues = iValues.Split(new char[1]{','},StringSplitOptions.RemoveEmptyEntries);
TempiCols=TempcValues.Length;
if (iColumns != null)
{
TempColsArray = iColumns.Split(',');
TempiCols = TempColsArray.Length;
}
sw.Write(sw.NewLine+"└");
for (int i = 0; i < ColsArray.Length; i++)
{
if (TempColsArray != null)
{
for (int j = 0; j < TempiCols; j++)
{
if (ColsArray[i] == TempColsArray[j])
{
if (TempcValues[j].Trim() == "")
TempcValues[j] = "■";
if (i < ColsArray.Length - 1)
{
TempcValues[j] += "│";
}
sw.Write(TempcValues[j]);
break;
}
else
{
if (j == TempiCols - 1)
{
sw.Write("■");
if (i < ColsArray.Length - 1)
{
sw.Write("│");
}
}
}
}
}
else
{
if (TempcValues.Length > i)
{
if (TempcValues[i].Trim() == "")
TempcValues[i] = "■";
if (i < ColsArray.Length - 1)
{
TempcValues[i] += "│";
}
sw.Write(TempcValues[i]);
}
else
{
sw.Write("■");
if (i < ColsArray.Length - 1)
{
sw.Write("│");
}
}
}
}
sw.Write("┘");
sw.Close();
sr.Close();
fs.Close();
return true;
}
}
Updating Record Method:
public static bool UpdateRecord(string tblName, string dbName, string iValues, string iColumns = null, string cField=null, string cValue=null)
{
if (!File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
return false;
else
{
FileStream fsr = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
FileStream fsw = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
StreamWriter sw = new StreamWriter(fsw);
StreamReader sr = new StreamReader(fsr);
fsw.Seek(0, SeekOrigin.End);
string tCol = sr.ReadLine();
string[] ColsArray = tCol.Split(new char[3] { '├', '┤', '│' }, StringSplitOptions.RemoveEmptyEntries);
sw.Write(tCol);
string[] iColumnsArray = null;
int TempiCols = 0;
string modifiedRow = "";
string[] iValuesArray = iValues.Split(new char[1] { ',' }, StringSplitOptions.RemoveEmptyEntries);
TempiCols = iValuesArray.Length;
if (iColumns != null)
{
iColumnsArray = iColumns.Split(',');
TempiCols = iColumnsArray.Length;
}
if (cField != null || cValue != null)
{
int cfi = Array.IndexOf(ColsArray, cField);
while (!sr.EndOfStream)
{
string TempStr = sr.ReadLine();
string[] RowArray = TempStr.Split(new char[3] { '└', '┘', '│' }, StringSplitOptions.RemoveEmptyEntries);
if (RowArray[cfi].Trim() == cValue.Trim())
{
modifiedRow = "";
int k = 0;
for (int i = 0; i < ColsArray.Length; i++)
{
if (iColumnsArray != null)
{
for (int j = 0; j < TempiCols; j++)
{
if (ColsArray[i] == iColumnsArray[j])
{
modifiedRow += iValuesArray[j]+"~";
k++;
break;
}
else
{
if (j == TempiCols - 1)
{
modifiedRow += RowArray[i] + "~";
break;
}
}
}
}
}
string[] TempcValues = modifiedRow.Split('~');
string TempModStr = "└";
for (int j = 0; j < TempcValues.Length; j++)
{
if (TempcValues[j].Trim() == "")
TempcValues[j] = "■";
if (j < TempcValues.Length - 1)
{
TempcValues[j] += "│";
}
TempModStr += TempcValues[j];
}
TempModStr += "┘";
TempStr = TempModStr;
}
sw.Write(sw.NewLine + TempStr);
}
}
else
{
while (!sr.EndOfStream)
{
string TempStr = sr.ReadLine();
string[] RowArray = TempStr.Split(new char[3] { '└', '┘', '│' }, StringSplitOptions.RemoveEmptyEntries);
modifiedRow = "";
int k = 0;
for (int i = 0; i < ColsArray.Length; i++)
{
for (int j = 0; j < TempiCols; j++)
{
if (k<=iValuesArray.Length-1)
{
modifiedRow += iValuesArray[j]+"~";
k++;
break;
}
else
{
if (j <= TempiCols - 1)
{
modifiedRow += RowArray[i]+"~";
break;
}
}
}
}
//continue;
string[] TempcValues = modifiedRow.Split('~');
string TempModStr = "└";
for (int j = 0; j < TempcValues.Length; j++)
{
if (TempcValues[j].Trim() == "")
TempcValues[j] = "■";
if (j < TempcValues.Length - 1)
{
TempcValues[j] += "│";
}
TempModStr += TempcValues[j];
}
TempModStr += "┘";
TempStr = TempModStr;
sw.Write(sw.NewLine + TempStr);
}
}
sw.Close();
sr.Close();
fsr.Close();
fsw.Close();
File.Copy(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp", Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", true);
File.Delete(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp");
return true;
}
}
Deleting Record Method:
public static bool DeleteRecord(string tblName, string dbName, string cField=null, string cValue=null)
{
if (!File.Exists(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb"))
return false;
else
{
FileStream fsr = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
FileStream fsw = new FileStream(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite, 10000, FileOptions.Asynchronous);
StreamWriter sw = new StreamWriter(fsw);
StreamReader sr = new StreamReader(fsr);
fsw.Seek(0, SeekOrigin.End);
string tCol = sr.ReadLine();
string[] ColsArray = tCol.Split(new char[3] { '├', '┤', '│' }, StringSplitOptions.RemoveEmptyEntries);
sw.Write(tCol);
if (cField != null || cValue != null)
{
int cfi = Array.IndexOf(ColsArray, cField);
while (!sr.EndOfStream)
{
string TempStr = sr.ReadLine();
string[] RowArray = TempStr.Split(new char[3] { '└', '┘', '│' }, StringSplitOptions.RemoveEmptyEntries);
if (RowArray[cfi].Trim() == cValue.Trim())
{
continue;
}
sw.Write(sw.NewLine + TempStr);
}
}
sw.Close();
sr.Close();
fsr.Close();
fsw.Close();
File.Copy(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp", Application.StartupPath + "\\" + dbName + "\\" + tblName + ".fdb", true);
File.Delete(Application.StartupPath + "\\" + dbName + "\\" + tblName + ".tmp");
return true;
}
}
Addition Methods:
In this system I add one method for converting the textbox that only accepts the number. The code has follows:
Accepting Number Only TextBox Method:
public static void NumberTextBox(params TextBox[] txtControl)
{
foreach (TextBox txt in txtControl)
{
txt.KeyPress += (s, e) =>
{
if (!char.IsControl(e.KeyChar) && !char.IsDigit(e.KeyChar) && e.KeyChar != '.')
{
e.Handled = true;
}
if (e.KeyChar == '.' && (s as TextBox).Text.IndexOf('.') > -1)
{
e.Handled = true;
}
};
}
}
We can call this method has follows:
clsIntelliSense.NumberTextBox(txtTamil, txtEnglish,txtMaths,txtScince,txtSocialScience);
Need to write in form constructor.
Demo Form Desing:
The demo form has been designed to examine the flat file database opeartion. It has database creation, table creation, record insertion, record deletion, record updation, loading records and custom query execution. In additionally It has IntelliSense TextBox for easy query writing. The form layout is fluid and exactly fit for all screens.
Dedication
I planned to post this article on 14th March 2014. I dedicate this article to my girlfrind(Ammu Kutti) birthday but missed. If I posted on that date, I may be got a big gift.
History
- Now currently this system shows how we can manipulate the flatfile database in C# with simple fature.
- In the future it will be availabe with more security and fully enhanced database structure. It would be available with support of JOIN quries.
