private void Close()
{
if (fileDb.IsOpen)
{
fileDb.Close();
}
}
internal void CloseAll()
{
foreach (TreeNode node in _rootNode.Nodes)
{
NodeInfo nodeInfo = node.Tag as NodeInfo;
FileDb fileDb = nodeInfo.Tag as FileDb;
fileDb.Close();
}
}
private void BtnClose_Click(object sender, RoutedEventArgs e)
{
try
{
_db.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void DoSubmitBook(object sender, RoutedEventArgs e)
{
//Reload:
//FileDb booksDb = new FileDb();
//booksDb.Open("Books.fdb", false);
//if (booksDb.IsOpen)
//{
// BookViewModel.BookList = booksDb.SelectAllRecords().ToList();
//}
//else
//{
//create database file
CreateAnOpenDataBaseFile();
_booksDb = new FileDb();
_booksDb.Open("Books.fdb", false);
Book book = new Book
{
AuthorsName = txtAuthorsName.Text,
Mvp = txtMvp.Text,
Title = txtTitle.Text
};
int p = PostData(book, _booksDb);
if (p > 0)
{
MessageBox.Show("Entry Successful!");
}
if (_booksDb.IsOpen)
{
BookViewModel.BookList = _booksDb.SelectAllRecords().ToList();
}
_booksDb.Close();
}
private void CreateAnOpenDataBaseFile()
{
FileDb booksDb = new FileDb();
Field field;
var fieldLst = new List <Field>(20);
field = new Field("ID", DataTypeEnum.Int32)
{
AutoIncStart = 0,
IsPrimaryKey = true
};
fieldLst.Add(field);
field = new Field("AuthorsName", DataTypeEnum.String);
fieldLst.Add(field);
field = new Field("Age", DataTypeEnum.String);
fieldLst.Add(field);
field = new Field("Title", DataTypeEnum.String);
fieldLst.Add(field);
field = new Field("Mvp", DataTypeEnum.String);
fieldLst.Add(field);
booksDb.Create("Books.fdb", fieldLst.ToArray());
booksDb.Close();
}
/// <summary>
/// Showcase what we can do with FileDBNs [everything not in yet]
/// </summary>
public void CreateDBExample()
{
if (!Directory.Exists(Directory.GetCurrentDirectory() + "\\DB\\"))
{
Directory.CreateDirectory(Directory.GetCurrentDirectory() + "\\DB\\");
}
DB.Create(Names.Issue2Test, new [] {
new Field("TestID", DataTypeEnum.Int32)
{
AutoIncStart = 1, IsPrimaryKey = true
},
new Field("TestName", DataTypeEnum.String)
});
var test = new SQLObject()
{
Name = Names.Issue2Test
};
test.AddRecord(DB, (new Issue2Test()
{
TestName = "Taz"
}).Value());
// DEBUG HERE TO SEE THE RETURN.
var result = DB.SelectRecords <Issue2Test>("TestName = 'Taz'").FirstOrDefault <Issue2Test>();
DB.Close();
DB.Create(Names.Issue2Test2, new[] {
new Field("Test2ID", DataTypeEnum.Int32)
{
AutoIncStart = 1, IsPrimaryKey = true
},
new Field("TestID", DataTypeEnum.Int32),
new Field("TestProfession", DataTypeEnum.String)
});
test = new SQLObject()
{
Name = Names.Issue2Test2
};
test.AddRecord(DB, (new Issue2Test2()
{
TestID = result.TestID, TestProfession = "MY PROFESSION?!?!? Code monkey."
}).Value());
DB.Close();
// Now lets 'pretend' we need some data on the fly.
DB.Open(Names.Issue2Test2, true);
// We stil have Taz (result) from earlier.
var tazProfession = DB.SelectRecords <Issue2Test2>(string.Format("TestID = {0}", result.TestID))
.FirstOrDefault <Issue2Test2>();
DB.Close();
// confirmation
MessageBox.Show(tazProfession.TestProfession);
//: Potential of SQLObject
test = new SQLObject()
{
Name = Names.Issue2Test
};
//: Get our table
var table = test.GetInfoByID(DB, tazProfession.TestID);
//: consider making a wrapper for this, writing this everywhere could get sloppy.
if (table.Count > 0)
{
//: We have rows in JSON format
foreach (var record in table)
{
//: You can have a foreach ( value in record )
MessageBox.Show(record.Data.ToString());
}
}
}
// Custom class version
//
void LinqAggregates_Custom()
{
FileDb customersDb = new FileDb(),
ordersDb = new FileDb(),
orderDetailsDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
customersDb.Open( path + "Customers.fdb", true );
ordersDb.Open( path + "Orders.fdb", true );
orderDetailsDb.Open( path + "OrderDetails.fdb", true );
try
{
// Step 1: Get the Tables we will use with Linq
// Note: we only get the columns we will be using so as to keep the size of the table data small
// get all Customer records
FilterExpression filterExp;
IList<Customer> customers = customersDb.SelectAllRecords<Customer>( new string[] { "CustomerID", "CompanyName" } );
// now get only Order records for the target Customer records using FilterExpression.CreateInExpressionFromTable
// this method creates a HastSet which is extremely efficient when used by FileDb.SelectRecords
filterExp = FilterExpression.CreateInExpressionFromTable<Customer>( "CustomerID", customers, "CustomerID" );
IList<Order> orders = ordersDb.SelectRecords<Order>( filterExp, new string[] { "CustomerID", "OrderID", "OrderDate" } );
// now get only OrderDetails records for the target Order records
filterExp = FilterExpression.CreateInExpressionFromTable<Order>( "OrderID", orders, "OrderID" );
IList<OrderDetail> orderDetails = orderDetailsDb.SelectRecords<OrderDetail>( filterExp, new string[] { "OrderID", "ProductID", "UnitPrice", "Quantity" } );
// Step 2: Use Linq to generate the objects for us
var customerOrders =
from c in customers
join o in orders on c.CustomerID equals o.CustomerID
into ordersGrp // "into" gives us hierarchical result sequence
select new
{
CustomerId = c.CustomerID,
CustomerName = c.CompanyName,
Orders =
from order in ordersGrp
join od in orderDetails on order.OrderID equals od.OrderID
into orderDetailsGrp // "into" gives us hierarchical result sequence
select new
{
OrderId = order.OrderID,
OrderDate = order.OrderDate,
NumItems = orderDetailsGrp.Count(),
TotalSale = orderDetailsGrp.Sum( od => od.UnitPrice * od.Quantity ),
AverageSale = orderDetailsGrp.Average( od => od.UnitPrice * od.Quantity )
}
};
MessageBox.Show( "Look in your debugger Output window to see the results" );
foreach( var custOrder in customerOrders )
{
Debug.WriteLine( custOrder.CustomerName );
foreach( var order in custOrder.Orders )
{
Debug.WriteLine( "\tOrder ID: {0}\t NumItems: {2}\t Total Sale: ${1:0.00}\t Average Sale: ${3:0.00}",
order.OrderId, order.NumItems, order.TotalSale, order.AverageSale );
}
Debug.WriteLine( string.Empty );
}
}
finally
{
customersDb.Close();
ordersDb.Close();
orderDetailsDb.Close();
}
}
/// <summary>
/// Here we see how to convert a Table directly to a new database just by calling Table.SaveToDb.
/// </summary>
///
private void BtnTableToDB_Click( object sender, RoutedEventArgs e )
{
try
{
FileDb employeesDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
try
{
employeesDb.Open( path + "Employees.fdb", true );
string searchVal = @"\bFull";
var fieldSearchExp = new FilterExpression( "LastName", searchVal, EqualityEnum.Like );
FileDbNs.Table table = employeesDb.SelectRecords( fieldSearchExp );
FileDb newDb = table.SaveToDb( "Employees2.fdb" );
table = newDb.SelectRecords( fieldSearchExp );
displayRecords( table );
newDb.Close();
}
finally
{
employeesDb.Close();
}
}
catch( Exception ex )
{
MessageBox.Show( ex.Message );
}
}
// Custom class version
//
void LinqHierarchicalObjects_Custom()
{
FileDb customersDb = new FileDb(),
ordersDb = new FileDb(),
orderDetailsDb = new FileDb(),
productsDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
customersDb.Open( path + "Customers.fdb", true );
ordersDb.Open( path + "Orders.fdb", true );
orderDetailsDb.Open( path + "OrderDetails.fdb", true );
productsDb.Open( path + "Products.fdb", true );
try
{
// Step 1: Get the Tables we will use with Linq
// Note: we only get the columns we will be using so as to keep the size of the table data small
// get all Customer records
FilterExpression filterExp;
IList<Customer> customers = customersDb.SelectRecords<Customer>( "CustomerID = ALFKI", new string[] { "CustomerID", "CompanyName" } );
// now get only Order records for the target Customer records using FilterExpression.CreateInExpressionFromTable
// this method creates a HastSet which is extremely efficient when used by FileDb.SelectRecords
filterExp = FilterExpression.CreateInExpressionFromTable( "CustomerID", customers, "CustomerID" );
IList<Order> orders = ordersDb.SelectRecords<Order>( filterExp, new string[] { "CustomerID", "OrderID", "OrderDate" } );
// now get only OrderDetails records for the target Order records
filterExp = FilterExpression.CreateInExpressionFromTable( "OrderID", orders, "OrderID" );
IList<OrderDetail> orderDetails = orderDetailsDb.SelectRecords<OrderDetail>( filterExp, new string[] { "OrderID", "ProductID", "UnitPrice", "Quantity" } );
// now get only Product records for the target OrderDetails records
filterExp = FilterExpression.CreateInExpressionFromTable( "ProductID", orderDetails, "ProductID" );
IList<Product> products = productsDb.SelectRecords<Product>( filterExp, new string[] { "ProductID", "ProductName" } );
// Step 2: Use Linq to generate the objects for us
var customerOrders =
from c in customers
join o in orders on c.CustomerID equals o.CustomerID
into ordersGrp // "into" gives us hierarchical result sequence
select new
{
CustomerId = c.CustomerID,
CustomerName = c.CompanyName,
Orders =
from order in ordersGrp
join od in orderDetails on order.OrderID equals od.OrderID
into orderDetailsGrp // "into" gives us hierarchical result sequence
select new
{
OrderId = order.OrderID,
OrderDate = order.OrderDate,
OrderDetails =
from ood in orderDetailsGrp
join p in products on ood.ProductID equals p.ProductID // pull in the Product name
select new
{
ProductName = p.ProductName,
UnitPrice = ood.UnitPrice,
Quantity = ood.Quantity
}
}
};
MessageBox.Show( "Look in your debugger Output window to see the results" );
foreach( var custOrder in customerOrders )
{
Debug.WriteLine( custOrder.CustomerName );
foreach( var order in custOrder.Orders )
{
Debug.WriteLine( '\t' + order.OrderId.ToString() + " - " + order.OrderDate.ToString( "dd MMM yyy" ) );
foreach( var orderDetail in order.OrderDetails )
{
Debug.WriteLine( "\t\t{0}\t Qty: {1}\t Unit Price: {2:0.00}\t Total Sale: ${3:0.00}",
orderDetail.ProductName, orderDetail.Quantity, orderDetail.UnitPrice, (orderDetail.Quantity * orderDetail.UnitPrice) );
}
Debug.WriteLine( string.Empty );
}
}
}
finally
{
customersDb.Close();
ordersDb.Close();
orderDetailsDb.Close();
productsDb.Close();
}
}
// Custom class version
//
void LinqGroupBy_Custom()
{
FileDb employeesDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
employeesDb.Open( path + "Employees.fdb", true );
try
{
IList<Employee> employees = employeesDb.SelectAllRecords<Employee>();
var employeeGrps =
from e in employees
group e by e.Country into empGrp
select new
{
Country = empGrp.Key,
Employees = empGrp // this will contain a list of Employee objects
};
MessageBox.Show( "Look in your debugger Output window to see the results" );
foreach( var grp in employeeGrps )
{
Debug.WriteLine( grp.Country );
foreach( var employee in grp.Employees )
{
Debug.WriteLine( employee.FirstName + " " + employee.LastName + " - " + employee.Title );
}
}
}
finally
{
employeesDb.Close();
}
}
// Custom class version
//
void LinqJoin_Custom()
{
FileDb customersDb = new FileDb(),
ordersDb = new FileDb(),
orderDetailsDb = new FileDb(),
productsDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
customersDb.Open( path + "Customers.fdb", true );
ordersDb.Open( path + "Orders.fdb", true );
orderDetailsDb.Open( path + "OrderDetails.fdb", true );
productsDb.Open( path + "Products.fdb", true );
try
{
// Step 1: Get the Tables we will use with Linq
// Note: we only get the columns we will be using so as to keep the size of the table data small
// get all Customer records
FilterExpression filterExp;
IList<Customer> customers = customersDb.SelectRecords<Customer>( "CustomerID = ALFKI", new string[] { "CustomerID", "CompanyName" } );
// now get only Order records for the target Customer records using FilterExpression.CreateInExpressionFromTable
// this method creates a HastSet which is extremely efficient when used by FileDb.SelectRecords
filterExp = FilterExpression.CreateInExpressionFromTable<Customer>( "CustomerID", customers, "CustomerID" );
IList<Order> orders = ordersDb.SelectRecords<Order>( filterExp, new string[] { "CustomerID", "OrderID", "OrderDate" } );
// now get only OrderDetails records for the target Order records
filterExp = FilterExpression.CreateInExpressionFromTable<Order>( "OrderID", orders, "OrderID" );
IList<OrderDetail> orderDetails = orderDetailsDb.SelectRecords<OrderDetail>( filterExp, new string[] { "OrderID", "ProductID", "UnitPrice", "Quantity" } );
// now get only Product records for the target OrderDetails records
filterExp = FilterExpression.CreateInExpressionFromTable<OrderDetail>( "ProductID", orderDetails, "ProductID" );
IList<Product> products = productsDb.SelectRecords<Product>( filterExp, new string[] { "ProductID", "ProductName" } );
// produces a flat/normalized sequence
var custOrderDetails =
from c in customers
join o in orders on c.CustomerID equals o.CustomerID
join od in orderDetails on o.OrderID equals od.OrderID
join p in products on od.ProductID equals p.ProductID
select new
{
ID = c.CustomerID,
CompanyName = c.CompanyName,
OrderID = o.OrderID,
OrderDate = o.OrderDate,
ProductName = p.ProductName,
UnitPrice = od.UnitPrice,
Quantity = od.Quantity
};
grid.Columns.Clear();
grid.ItemsSource = custOrderDetails.ToList();
}
finally
{
customersDb.Close();
ordersDb.Close();
orderDetailsDb.Close();
productsDb.Close();
}
}
// Custom class version
//
void LinqSelect_Custom()
{
FileDb employeesDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
employeesDb.Open( path + "Employees.fdb", true );
try
{
FilterExpression filterExp = FilterExpression.Parse( "LastName in ('Fuller', 'Peacock')" );
IList<Employee> employees = employeesDb.SelectRecords<Employee>( filterExp );
var records =
from e in employees
select new
{
ID = e.EmployeeID,
Name = e.FirstName + " " + e.LastName,
Title = e.Title
};
grid.Columns.Clear();
grid.ItemsSource = records.ToList();
}
finally
{
employeesDb.Close();
}
}
/// <summary>
/// One very powerful feature of FileDb is the ability to select records
/// from a Table object. This would be very handy if you have a Table
/// and you want to perform Select operations against it directly rather
/// than the database.
/// </summary>
///
private void BtnTableFromTable_Click( object sender, RoutedEventArgs e )
{
try
{
FileDb customersDb = new FileDb();
string path = System.IO.Path.Combine( AppDomain.CurrentDomain.BaseDirectory, StrNorthwindRelPath );
path = System.IO.Path.GetFullPath( path );
try
{
customersDb.Open( path + "Customers.fdb", true );
FileDbNs.Table customers = customersDb.SelectAllRecords();
FileDbNs.Table subCusts = customers.SelectRecords( "CustomerID <> 'ALFKI'",
new string[] { "CustomerID", "CompanyName", "City" }, new string[] { "~City", "~CompanyName" } );
displayRecords( subCusts );
}
finally
{
customersDb.Close();
}
}
catch( Exception ex )
{
MessageBox.Show( ex.Message );
}
}
