public Table ReadRecord()
{
FileDb _db = new FileDb();
_db.Open(DBFILE, false);
// the easy way, using the expression parser
// FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
//("(FirstName ~= 'andrew' OR FirstName ~= 'nancy') AND LastName = 'Fuller'");
FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
("FirstName = 'Nancy'");
Table table = _db.SelectRecords(srchExpGrp, null, null, false);
//// equivalent building it manually
//var fname1Exp = new FilterExpression
//("FirstName", "andrew", ComparisonOperatorEnum.Equal, MatchTypeEnum.IgnoreCase);
//var fname2Exp = new FilterExpression
//("FirstName", "nancy", ComparisonOperatorEnum.Equal, MatchTypeEnum.IgnoreCase);
//var lnameExp = new FilterExpression
//("LastName", "Fuller", ComparisonOperatorEnum.Equal, MatchTypeEnum.UseCase);
//var fnamesGrp = new FilterExpressionGroup();
//fnamesGrp.Add(BoolOpEnum.Or, fname1Exp);
//fnamesGrp.Add(BoolOpEnum.Or, fname2Exp);
//var allNamesGrp = new FilterExpressionGroup();
//allNamesGrp.Add(BoolOpEnum.And, lnameExp);
//allNamesGrp.Add(BoolOpEnum.And, fnamesGrp);
//// should get the same records
//table = _db.SelectRecords(allNamesGrp, null, null, false);
_db.Dispose();
return(table);
}
private void BtnGetMatchingRecords_Click(object sender, RoutedEventArgs e)
{
try
{
// Use the FilterExpressionGroup's filter parser to create a FilterExpressionGroup
// The syntax is similar to SQL (do not preceed with WHERE)
// Note that there are 2 ways to get a case-INSENSITIVE search:
// Use either ~= or you can prefix the fieldname with ~ to ignore case
// Both methods are shown below
// (FileDb doesn't support UPPER or LOWER)
// Note also that each set of parentheses will create a child FilterExpressionGroup
string filter = "(~FirstName = 'steven' OR [FirstName] ~= 'NANCY') AND LastName = 'Fuller'";
FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse(filter);
Table table = _db.SelectRecords(filterExpGrp);
displayRecords(table);
// we can manually build the same FilterExpressionGroup
var fname1Exp = new FilterExpression("FirstName", "steven", ComparisonOperatorEnum.Equal, MatchTypeEnum.IgnoreCase);
// the following two lines produce the same FilterExpression
var fname2Exp = FilterExpression.Parse("FirstName ~= 'NANCY'"); // equal/ignore case
fname2Exp = new FilterExpression("FirstName", "NANCY", ComparisonOperatorEnum.Equal, MatchTypeEnum.IgnoreCase);
var lnameExp = new FilterExpression("LastName", "Fuller", ComparisonOperatorEnum.Equal, MatchTypeEnum.UseCase);
// NOTE: FileDb now supports CONTAINS which is very fast, so you could now do this too
//fname2Exp = FilterExpression.Parse( "~FirstName CONTAINS 'NANCY'" ); // equal/ignore case
//fname2Exp = new FilterExpression( "FirstName", "NANCY", ComparisonOperatorEnum.Contains, MatchTypeEnum.IgnoreCase );
var fnamesGrp = new FilterExpressionGroup();
fnamesGrp.Add(BoolOpEnum.Or, fname1Exp);
fnamesGrp.Add(BoolOpEnum.Or, fname2Exp);
var allNamesGrp = new FilterExpressionGroup();
allNamesGrp.Add(BoolOpEnum.And, lnameExp);
allNamesGrp.Add(BoolOpEnum.And, fnamesGrp);
table = _db.SelectRecords(allNamesGrp);
displayRecords(table);
// or just pass the string expression directly
table = _db.SelectRecords(filter);
displayRecords(table);
// get the same records again as a typed list using your own custom class
IList <Person> persons = _db.SelectRecords <Person>(filter);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void BtnGetMatchingRecords_Click(object sender, RoutedEventArgs e)
{
try
{
// Use the FilterExpressionGroup's filter parser to create a FilterExpressionGroup
// The syntax is similar to SQL (do not preceed with WHERE)
// Note that we prefix the fieldname with ~ to get a case-INSENSITIVE search
// (FileDb doesn't currently support UPPER or LOWER)
// Note that we can also use LIKE when we want to ignore case, but the difference
// is that LIKE will create a RegEx search which would be a little slower
// Note also that each set of parentheses will create a child FilterExpressionGroup
string filter = "(~FirstName = 'steven' OR [FirstName] LIKE 'NANCY') AND LastName = 'Fuller'";
FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse(filter);
Table table = _db.SelectRecords(filterExpGrp);
displayRecords(table);
// we can manually build the same FilterExpressionGroup
var lnameExp = new FilterExpression("LastName", "Fuller", EqualityEnum.Equal, MatchTypeEnum.UseCase);
var fname1Exp = new FilterExpression("FirstName", "steven", EqualityEnum.Equal, MatchTypeEnum.IgnoreCase);
// the following two lines produce the same FilterExpression
var fname2Exp = FilterExpression.Parse("FirstName LIKE 'NANCY'");
fname2Exp = new FilterExpression("FirstName", "NANCY", EqualityEnum.Like);
var fnamesGrp = new FilterExpressionGroup();
fnamesGrp.Add(BoolOpEnum.Or, fname1Exp);
fnamesGrp.Add(BoolOpEnum.Or, fname2Exp);
var allNamesGrp = new FilterExpressionGroup();
allNamesGrp.Add(BoolOpEnum.And, lnameExp);
allNamesGrp.Add(BoolOpEnum.And, fnamesGrp);
table = _db.SelectRecords(allNamesGrp);
displayRecords(table);
// or just pass the string expression directly
table = _db.SelectRecords(filter);
displayRecords(table);
// get the same records again as a typed list using your own custom class
IList <Person> persons = _db.SelectRecords <Person>(filter);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public int DeleteRecord()
{
FileDb _db = new FileDb();
_db.Open(DBFILE, false);
// the easy way, using the expression parser
// FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
//("(FirstName ~= 'andrew' OR FirstName ~= 'nancy') AND LastName = 'Fuller'");
FilterExpressionGroup srchExpGrp = FilterExpressionGroup.Parse
("FirstName = 'Nancy'");
var ret = _db.DeleteRecords(srchExpGrp);
_db.Dispose();
return(ret);
}
private void BtnUpdateMatchingRecords_Click(object sender, RoutedEventArgs e)
{
try
{
string filter = "(~FirstName = 'andrew' OR ~FirstName = 'nancy') AND ~LastName = 'fuller'";
FilterExpressionGroup filterExpGrp = FilterExpressionGroup.Parse(filter);
Table table = _db.SelectRecords(filterExpGrp);
displayRecords(table);
// equivalent building it manually
var fname1Exp = new FilterExpression("FirstName", "andrew", EqualityEnum.Equal, MatchTypeEnum.IgnoreCase);
// the following two lines produce the same FilterExpression
var fname2Exp = FilterExpression.Parse("~FirstName = nancy");
fname2Exp = new FilterExpression("FirstName", "nancy", EqualityEnum.Equal, MatchTypeEnum.IgnoreCase);
var lnameExp = new FilterExpression("LastName", "fuller", EqualityEnum.Equal, MatchTypeEnum.IgnoreCase);
var fnamesGrp = new FilterExpressionGroup();
fnamesGrp.Add(BoolOpEnum.Or, fname1Exp);
fnamesGrp.Add(BoolOpEnum.Or, fname2Exp);
var allNamesGrp = new FilterExpressionGroup();
allNamesGrp.Add(BoolOpEnum.And, lnameExp);
allNamesGrp.Add(BoolOpEnum.And, fnamesGrp);
var fieldValues = new FieldValues();
fieldValues.Add("IsCitizen", false);
int nRecs = _db.UpdateRecords(allNamesGrp, fieldValues);
table = _db.SelectRecords(allNamesGrp);
displayRecords(table);
// or easiest of all use the filter string directly
table = _db.SelectRecords(filter);
displayRecords(table);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}