Windows Form SQL APP2 (Store Product)

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 allowing only to add of 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.

We shall use Fluent NHibernate in all of our projects.  Fluent NHibernate is a DLL that can be linked to our projects and all SQL data can be saved or updated or even deleted without knowing the different SQL syntaxes. Of course, we can write our SQL syntaxes but those are error-prone and our focus will be in another direction than the automation.

Fluent NHibernate offers an alternative to NHibernate’s standard XML mapping files. Rather than writing XML documents, you write mappings in strongly typed C# code. This allows for easy refactoring, improved readability, and more concise code.

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:

 

Mapping classes:

The most challenging task in the Fluent NHibernate is modeling and data mapping. Mapping tells how the NHibernate should update the tables. If the mapping is not understood well enough, it will be a problem when doing an insert, delete, etc. Here are the mapping classes for the store product database application (See the discussion parts).

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).Cascade.All();
  }
}
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");
  }
}
Discussion:
The employee has a Store object as a member, which means each employee will be associated with a store, and that ID will be a foreign key in the Employee table. If you don’t have this then Store_id will not have as shown in the following table. 
 
Id FirstName LastName Store_id
1 FirstName LastName 1
2 FirstName LastName 2
3 FirstName LastName 3
This relation (store employee) is expressed by 
References(x => x.Store).Cascade.All();
We can write the above statement as shown below as well, but in this case, we have to do manual work when saving data. By Cascade, we say save all related data for employees.
 
References(x => x.Store);//.Cascade.All();
By the following statement, we say that inside the store there are many employees (another way is not true, inside the employee there are not many stores, so has many is used here)
            HasMany(x => x.Staff).Inverse().Cascade.All();
 See the relation, how to join a table when we have  HasManyToMany relation.
 
            HasManyToMany(x => x.Products)
             .Cascade.All()
             .Table("StoreProduct");

            HasManyToMany(x => x.StoresStockedIn)
              .Cascade.All()
              .Inverse()
              .Table("StoreProduct");
 

What the application will do:

 

LoadData => Load data from database and shows the first set of data to the UI (if there is data)

public int LoadData(string storeName, out string firstName, out string lastName, out string productName, out double price) {
  int ret = -1;
  string firstNamef = "";
  string lastNamef = "";
  string productNamef = "";
  double pricef = 0.0;
  using(var session = NHibernateHelper.OpenSession()) {
    using(var transaction = session.BeginTransaction()) {
      try {
        IList < Store > storesMatches = session.QueryOver < Store > ().Where(c =>c.Name == storeName).List();
        if (storesMatches.Count > 0) {
          firstNamef = storesMatches[0].Staff.FirstOrDefault().FirstName;
          lastNamef = storesMatches[0].Staff.FirstOrDefault().FirstName;
          productNamef = storesMatches[0].Products.FirstOrDefault().Name;
          pricef = storesMatches[0].Products.FirstOrDefault().Price;
          ret = 0;
        }
        else {
          var allStoreData = session.QueryOver < Store > ().List();
          firstNamef = storesMatches[0].Staff.FirstOrDefault().FirstName;
          lastNamef = storesMatches[0].Staff.FirstOrDefault().FirstName;
          productNamef = storesMatches[0].Products.FirstOrDefault().Name;
          pricef = storesMatches[0].Products.FirstOrDefault().Price;
          ret = 0;
        }
      }
      catch(Exception ex) {}
    }
  }
  firstName = firstNamef;
  lastName = lastNamef;
  productName = productNamef;
  price = pricef;
  return ret;
}

 

 

SaveData => Save the current set of data to database as new entry

 

        public void SaveSomething(string aFullText)
        {
            string[] authorsList = aFullText.Split(':');
            string firstname = authorsList[1];
            string lastname = authorsList[2];
            string productname = authorsList[3];
            double productPrice = 0;
            try
            {
                productPrice = double.Parse(authorsList[4], CultureInfo.InvariantCulture);
            }
            catch (Exception) { }
            string storename = authorsList[0];
            using (var session = NHibernateHelper.OpenSession())
            {
                using (var transaction = session.BeginTransaction())
                {
                    bool found = false;
                    Store store = null;
                    Store newstore = new Store { Name = storename };
                    foreach (var item in storeList)
                    {
                        if (item.Name.Contains(storename) == true)
                        {
                            found = true;
                            store = item;
                            break;
                        }
                    }
                    var paramName = new Employee { FirstName = firstname, LastName = lastname };
                    var paramProduct = new Product { Name = productname, Price = productPrice };
                    if (found == true)
                    {
                        AddEmployeesToStore(store, paramName);
                        AddProductsToStore(store, paramProduct);
                        session.SaveOrUpdate(store);
                    }
                    else
                    {
                        AddEmployeesToStore(newstore, paramName);
                        AddProductsToStore(newstore, paramProduct);
                        session.SaveOrUpdate(newstore);
                    }
                    transaction.Commit();
                }
            }
        }
        private void AddProductsToStore(Store store, params Product[] products)
        {
            foreach (var product in products)
            {
                store.AddProduct(product);
            }
        }
        private void AddEmployeesToStore(Store store, params Employee[] employees)
        {
            foreach (var employee in employees)
            {
                store.AddEmployee(employee);
            }
        }

Edit Data => Pick the data based on ‘Store Name’  and edit those and update the data.

 

        public void EditCurrent(string aFullText)
        {
            string[] authorsList = aFullText.Split(':');
            string storename = authorsList[0];
            string firstname = authorsList[1];
            string lastname = authorsList[2];
            string productname = authorsList[3];
            double productPrice = 0;
            try
            {
                productPrice = double.Parse(authorsList[4], CultureInfo.InvariantCulture);
            }
            catch (Exception) { }
            using (var session = NHibernateHelper.OpenSession())
            {
                //Bring all from the Store table
                List<Store> storesAll = (List<Store>)session.QueryOver<Store>().List<Store>();
                //Bring all where the storename matches
                IList<Store> storesMatches = session.QueryOver<Store>().Where(c => c.Name == storename).List();
                if (storesMatches.Count > 0)
                {
                    using (var transaction = session.BeginTransaction())
                    {
                        storesMatches[0].Staff.FirstOrDefault().FirstName = firstname;
                        storesMatches[0].Staff.FirstOrDefault().FirstName = lastname;
                        storesMatches[0].Products.FirstOrDefault().Name = productname;
                        storesMatches[0].Products.FirstOrDefault().Price = productPrice;
                        session.Update(storesMatches[0]);
                        transaction.Commit();
                        session.Flush();
                    }
                }
            }
        }

Delete Data => Delete the current set of data from database

 

public void RemoveStore(int id)
{
    try
    {
        using (var session = NHibernateHelper.OpenSession())
        {
            using (var transaction = session.BeginTransaction())
            {
                //Some test stuff included
                var list = session.Query<Store>().ToArray().Where(x => (x.Id == id));
                var vv = list.ToArray()[0];
                var stores = session.Query<Store>().Where(x => (x.Id == id)).ToArray();
                var myStore = session.Load<Store>(id);
                myStore.Products.Clear();
                session.Delete(myStore);
                session.Flush();
                transaction.Commit();
            }
        }
    }
    catch (Exception ex) { }
}

 

Init Database => Remove existing database and reinitialize the database by running the script.

If I run the script it looks the app throw exception and when I save new data. (but after restart OK, if you find the solution let’s me know, please drop a info@hemelixdotcom)

            if (databaseClass != null)
            {               
                databaseClass.DeleteExistingData();
            }
            try
            {
                System.Diagnostics.Process proc2 = new System.Diagnostics.Process();
                proc2.EnableRaisingEvents = false;
                proc2.StartInfo.FileName = "initmydatabase.bat";
                proc2.Start();
                Thread.Sleep(500);
                databaseClass = new DatabaseClass();
                databaseClass.InitDataBase();
            }
            catch (Exception ex)
            {
                //log.Error("Failed to execute recreatelinkoping.bat " + ex.Message);
            }

 

Download the sample project (tested with VS2019) 

Download the bat file for database reset and clean.

See Sample APP 


Tips

=> Fluent NHibernate uses different log4net file. If you are using log4net in the extension then, you can’t open the session in the NHibernate. One way to disable the log4net in the extension DLL.

=> Missing Data in UI, command.red value is used for reading data from the extension to the UI. If we put the line inside the write block then the data may be missed. The reason is that write is executed only once.

        private ErrorValue ComboIndex(Command command)
        {
            if (command.WriteValue.IsSet && command.WriteValue.Type == TcHmiSrv.ValueType.ValueType_Int)
            {
                _data.ComboIndex = command.WriteValue;
                List authors = new List();
                for (int i = 0; i < _data.ComboIndex; i++)
                {
                    authors.Add((i + 1).ToString());
                }
                _data.ComboString = JsonConvert.SerializeObject(authors);
            }  // end of write
            command.ReadValue = _data.ComboIndex; // read always and continuously executed. 
            command.ExtensionResult = ExtensionErrorValue.HMI_EXT_SUCCESS;
            return ErrorValue.HMI_SUCCESS;
        }

Tips 01: Differences between Load/Get

These are two very similar APIs provided by ISession for loading an object by primary key.

Load − it will return the object or it will throw an ObjectNotFoundException.

Get − it will return the object or a null.

Load: It’s because Load can optimize database round trips much more efficiently.Load actually returns a proxy object and doesn’t need to access the database right when you issue that Load call. When you access that proxy, the object doesn’t happen to be in the database, it can throw an ObjectNotFoundException at that point.

Get:  Conversely, with Get because of limitations of the CLR or Common Language Runtime and NHibernate must go to the database immediately, check if the objects are there and return null, if it’s not present. It doesn’t have the object option of delaying that fetch, that roundtrip to the database to a later time because it cannot return a proxy object and that swapped that proxy object out for a null, when the user actually accesses it.

https://stackoverflow.com/questions/37178455/nhibernate-difference-between-queryt-gett-and-loadt

 var myStore = session.Load<Store>(id); //object or ObjectNotFoundException
var customer= session.Get<Customer>(id1); //null or object

Tips 02: Differences between Query/QueryOver

You should not use this method to determine if an instance exists (use a query or NHibernate.ISession.Get“1(System.Object) instead). Use this only to retrieve an instance that you assume exists, where non-existence would be an actual error.

 

Tips 03

See the following code, Inverse() and Cascade.All() are commented out. We shall get an error related that says that the transient state has not been saved. In that case, we need to save Staff related entities by ourselves to continue.

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");
  }
}

By Inverse() we say Nhibernate that another side of the relation is responsible for saving data, in this case, the other side of x.Staff is a Store object, so the store object will save the data. Cascade.All() says that save all data in the relationship hierarchy of the Staff.

You can use Cascade.None() then the object will not be saved.