Windows Form SQL and DLL
Preconditions:
=> Database has been installed according to https://www.hemelix.com/sql/sql-server-installation/
=> Database has been created with the following connection string information:
"Server=(local);initial catalog=MyDatabase; user=my_user_id;password=my_password;"
In the previous sample, we had a very simple operation, it was allowed only to add data to the SQL server. We shall now update the application for Store management. In this sample, we shall have information about how to edit data, delete data, and so on. The sample has been tested with VS2019 and the full source code is available for download. The only difference is that actual functionality has been transferred to a DLL.
The Sample
Conventional store and worker (adapted from https://github.com/nhibernate/fluent-nhibernate/wiki/Getting-started)
For this example, we’re going to be mapping a simple domain for a retail company. The company has a couple of stores, each with products in (some products are in both stores, some are exclusive), and each with employees. In database terms, that’s a Store, Employee, and Product table, with a many-to-many table between Store and Product.
First, we need the necessary libraries that support Windows, normally we can add those by Visual Studio Nuget Packages Management or we can download the libraries and use them for our project. Normally, I create a folder called Lib in my projects and I store it there. We shall store those libraries which come as part of Nuget Packages in the Lib folder.
In this example, we shall display the data from the SQL server to our Windows Form Application and give the user options to add, delete, and update the SQL database. The application is a Windows Form Application the database is managed in separate DLL.
The main UI of the sample application is like this:
What the application will do
SaveData => Creates some random data and store those to the SQL Server (check with Management Studio)
ReadData => Read the current contents (Check the console or via VS debugger or add it to this app)
Delete Employee => Delete the given employee based on the given id (no error check, check with Management Studio)
Delete Store => Delete the given store and associated products based on the given id (no error check)
Delete Product => Delete the given product based on the given id (no error check, check with Management Studio)
Add Employee => Add the given employee based on the given name and store id (no error check, check with Management Studio)
Add Store => Add the given store based on name (no error check, check with Management Studio)
Add Product => Add the given product based on the given name/price and store id (no error check, check with Management Studio)
Data Modelling
namespace fluent_own_dll
{
public class Employee
{
public virtual int Id { get; protected set; }
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }
public virtual Store Store { get; set; }
}
public class Product
{
public virtual int Id { get; protected set; }
public virtual string Name { get; set; }
public virtual double Price { get; set; }
public virtual IList<Store> StoresStockedIn { get; protected set; }
public Product()
{
StoresStockedIn = new List<Store>();
}
}
public class Store
{
public virtual int Id { get; protected set; }
public virtual string Name { get; set; }
public virtual IList<Product> Products { get; set; }
public virtual IList<Employee> Staff { get; set; }
public Store()
{
Products = new List<Product>();
Staff = new List<Employee>();
}
public virtual void AddProduct(Product product)
{
product.StoresStockedIn.Add(this);
Products.Add(product);
}
public virtual void AddEmployee(Employee employee)
{
employee.Store = this;
Staff.Add(employee);
}
}
public class EmployeeMap : ClassMap<Employee>
{
public EmployeeMap()
{
Id(x => x.Id);
Map(x => x.FirstName);
Map(x => x.LastName);
References(x => x.Store);
}
}
public class StoreMap : ClassMap<Store>
{
public StoreMap()
{
Id(x => x.Id);
Map(x => x.Name);
HasMany(x => x.Staff)
.Inverse()
.Cascade.All();
HasManyToMany(x => x.Products)
.Cascade.All()
.Table("StoreProduct");
}
}
public class ProductMap : ClassMap<Product>
{
public ProductMap()
{
Id(x => x.Id);
Map(x => x.Name);
Map(x => x.Price);
HasManyToMany(x => x.StoresStockedIn)
.Cascade.All()
.Inverse()
.Table("StoreProduct");
}
}
}
Class1 has all the functionalities such as delete, add, etc implementation.
namespace fluent_own_dll
{
//Actual functionalities such as delete, add etc are implemented here
public class Class1
{
public static void AddProductsToStore(Store store, params Product[] products)
{
foreach (var product in products)
{
store.AddProduct(product);
}
}
public static void AddEmployeesToStore(Store store, params Employee[] employees)
{
foreach (var employee in employees)
{
store.AddEmployee(employee);
}
}
public void RemoveEmployee(int id)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var product = session.Get<Employee>(id);
session.Delete(product);
transaction.Commit();
session.Flush();
}
}
}
catch (Exception ex) {}
}
public void RemoveProduct(int id)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var myProduct = session.Get<Product>(id);
myProduct.StoresStockedIn.Clear();
session.Delete(myProduct);
transaction.Commit();
session.Flush();
}
}
}
catch (Exception ex) { }
}
public void RemoveStore(int id)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var myStore = session.Load<Store>(id);
if (myStore.Products.Count > 0)
{
foreach (var item in myStore.Products)
{
RemoveProduct(item.Id);
}
}
if (myStore.Staff.Count > 0)
{
foreach (var item in myStore.Staff)
{
RemoveEmployee(item.Id);
}
}
myStore.Products.Clear();
myStore.Staff.Clear();
session.Delete(myStore);
session.Flush();
transaction.Commit();
}
}
}
catch (Exception ex) { }
}
public void AddProduct(string productName, Double productPrice, int storeid)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var fish = new Product { Name = productName, Price = productPrice };
Store aStore = session.Load<Store>(storeid);
AddProductsToStore(aStore, fish);
session.SaveOrUpdate(aStore);
session.Flush();
transaction.Commit();
}
}
}
catch (Exception ex)
{
}
}
public void AddEmployees(string firstname, string lastName, int storeid)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var daisy = new Employee { FirstName = firstname, LastName = lastName };
Store aStore = session.Load<Store>(storeid);
AddEmployeesToStore(aStore, daisy);
session.SaveOrUpdate(aStore);
session.Flush();
transaction.Commit();
}
}
}
catch (Exception ex)
{
}
}
public void ReadData()
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
//var store = session.Query<Store>().;
//string json = JsonConvert.SerializeObject(employee);
var list = session.QueryOver<Product>().List();
var list1 = session.QueryOver<Employee>().List();
var list2 = session.QueryOver<Store>().List();
}
}
}
catch (Exception ex)
{
}
}
public void doWork()
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
// create a couple of Stores each with some Products and Employees
var barginBasin = new Store { Name = "Bargin Basin" };
var superMart = new Store { Name = "SuperMart" };
var potatoes = new Product { Name = "Potatoes", Price = 3.60 };
var fish = new Product { Name = "Fish", Price = 4.49 };
var milk = new Product { Name = "Milk", Price = 0.79 };
var bread = new Product { Name = "Bread", Price = 1.29 };
var cheese = new Product { Name = "Cheese", Price = 2.10 };
var waffles = new Product { Name = "Waffles", Price = 2.41 };
var daisy = new Employee { FirstName = "Daisy", LastName = "Harrison" };
var jack = new Employee { FirstName = "Jack", LastName = "Torrance" };
var sue = new Employee { FirstName = "Sue", LastName = "Walkters" };
var bill = new Employee { FirstName = "Bill", LastName = "Taft" };
var joan = new Employee { FirstName = "Joan", LastName = "Pope" };
// add products to the stores, there's some crossover in the products in each
// store, because the store-product relationship is many-to-many
AddProductsToStore(barginBasin, potatoes, fish, milk, bread, cheese);
AddProductsToStore(superMart, bread, cheese, waffles);
// add employees to the stores, this relationship is a one-to-many, so one
// employee can only work at one store at a time
AddEmployeesToStore(barginBasin, daisy, jack, sue);
AddEmployeesToStore(superMart, bill, joan);
// save both stores, this saves everything else via cascading
session.SaveOrUpdate(barginBasin);
session.SaveOrUpdate(superMart);
transaction.Commit();
//Changed after commit to test, use another trasaction
using (var transaction2 = session.BeginTransaction())
{
var billTwo = new Employee { FirstName = "BillTwo", LastName = "TaftTwo" };
AddEmployeesToStore(superMart, bill, joan, billTwo);
session.SaveOrUpdate(superMart);
transaction2.Commit();
}
}
}
}
public void AddStrore(string s)
{
try
{
using (var session = NHibernateHelper.OpenSession())
{
using (var transaction = session.BeginTransaction())
{
var myStore = new Store { Name = s };
session.SaveOrUpdate(myStore);
session.Flush();
transaction.Commit();
}
}
}
catch (Exception ex) { }
}
}
}
Download the source code:
References:
Download the sample from the link given above.
Next, let’s try to understand how Nuget Package works at https://www.hemelix.com/scada-hmi/twincat-hmi/nuget-package-management/
Ask questions related to Hemelix sample code and design at Google group https://groups.google.com/g/hemelix