SQL Sample APP Car Model

Windows Form SQL APP1 (CAR MANUFACTURING MODELLING)

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 (you can have your own but those should be adjusted as necessary):

"Server=(local);initial catalog=MyDatabase; user=my_user_id;password=my_password;"

We shall add several complete samples that will help us to learn the data modeling. We shall use a car example and do a model with Fluent NHibernate

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.

Windows sample application (simple form application just for inserting data to SQL, sample 1)

First, we create a Windows form application with a Button and 3 text boxes. 3 edit boxes will have the Model, Manufacturer, and Name of the car. If we press the save button then we pick the data from these edit boxes and store it in the database by using Fluent NHibernate.

=> We can install the Fluent NHibernate library by using the Nuget package and linking those libraries to references.

=> We need to initialize the database by using SQL management studio or executing the script from a command prompt with the -r option

Relations of the car, model, and manufacturer are shown in the following code snippet.

    public class Make
    {
        public virtual int Id { get; set; }
        public virtual string Name{ get; set; }
    }
    public class Model
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual Make Make { get; set; }
    }
    public class Car
    {
        public virtual int Id { get; set; }
        public virtual string Title { get; set; }
        public virtual Make Make { get; set; }
        public virtual Model Model { get; set; }
    }

Note that each class has an Id and all properties are declared as virtual.

If we execute the bat file we have a empty database as shown in the above picture. At this point we are ready to save any data by the application.

The user interface of car inventory SQL application

Now that we’ve got our classes created, it’s time to map them using Fluent NHibernate. To map an entity, we have to create a dedicated mapping class. Lets defines a mapping for all classes which we have created. We shall derive from this class to create a mapping, and use the constructor to control how our entity is persisted.

  public class MakeMap: ClassMap < Make > {
    public MakeMap() {
      Id(x =>x.Id);
      //Id(x => x.Name);
      Map(x =>x.Name);
    }
  }
  public class ModelMap: ClassMap < Model > {
    public ModelMap() {
      Id(x =>x.Id);
      //Id(x => x.Name);
      Map(x =>x.Name);
      References(x =>x.Make).Cascade.None();
    }
  }
  public class CarMap: ClassMap < Car > {
    public CarMap() {
      Id(x =>x.Id);
      //Id(x => x.Title);
      Map(x =>x.Title);
      References(x =>x.Make).Cascade.All();
      References(x =>x.Model).Cascade.All();
    }
  }

We shall create a helper class which will established a connection to the Fluent NHibernate library.

    public class NHibernateHelper
    {
        private static ISessionFactory _sessionFactory;
        private static ISessionFactory SessionFactory
        {
            get
            {
                if (_sessionFactory == null)
                    InitializeSessionFactory();
                return _sessionFactory;
            }
        }
        private static void InitializeSessionFactory()
        {
            _sessionFactory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql7
                  .ConnectionString(
                  @"Server=(local);initial catalog=MyDatabase;
		user=my_user_id;password=my_password;")
                              .ShowSql()
                )
                .Mappings(m =>
                          m.FluentMappings
                              .AddFromAssemblyOf<Car>())
                .ExposeConfiguration(cfg => new SchemaExport(cfg)
                                                .Create(true, true))
                .BuildSessionFactory();
        }
        public static ISession OpenSession()
        {
            return SessionFactory.OpenSession();
        }
    }

We can create the data by the following code:

string manufacturer = textBoxManuf.Text;
string model = textBoxModel.Text;
string carName = textBoxCarName.Text;
using(var session = NHibernateHelper.OpenSession()) {
  using(var transaction = session.BeginTransaction()) {
    var fordMake = new Make {
      Name = manufacturer
    };
    var fiestaModel = new Model {
      Name = model,
      Make = fordMake
    };
    var car = new Car {
      Title = carName,
      Model = fiestaModel,
      Make = fordMake
    };
    session.Save(car);
    transaction.Commit();
  }
}

We can the data by the following code:

The UI is a simple one, takes an ID, and 1, 2, or 3. If you put 1 or 2 then you notice that due to foreign key violence, it will through exception, basically we have to delete via only 3.

using(var session = NHibernateHelper.OpenSession()) {
  using(var transaction = session.BeginTransaction()) {
    if (entityType == 1) {
      IList < Model > models = session.QueryOver < Model > ().Where(c =>c.Id == deleteID).List();
      if (models.Count > 0) {
        session.Delete(models[0]);
        session.Flush();
      }
    }
    if (entityType == 2) {
      IList < Make > makers = session.QueryOver < Make > ().Where(c =>c.Id == deleteID).List();
      if (makers.Count > 0) {
        session.Delete(makers[0]);
        session.Flush();
      }
    }
    if (entityType == 3) {
      IList < Car > cars = session.QueryOver < Car > ().Where(c =>c.Id == deleteID).List();
      if (cars.Count > 0) {
        session.Delete(cars[0]);
        session.Flush();
      }
    }
    transaction.Commit();
  }
}

We compile the project and press the save button, we shall have the following SQL database structure. If we expand the table we see we have a table with the data that was visible in the UI.

Download the sample project (tested with VS2019)

Download the bat file for database reset and clean.

Discussions

We have 3 classes in this application and these will creates 3 tables in the database.

References create the foreign key relationship among tables, if we comment like the following then the Make and the Model table will not be updated. With having References, single Save call will save all these 3 classes.

//References(x => x.Make).Cascade.All();
//References(x => x.Model).Cascade.All();

With this situation, if we still want to update the Make and Model table we can do it by ourselves manually as shown in the following code.

session.Save(fordMake);
session.Save(fiestaModel);

If we want to make sure that data base is not deleted when we start the program then it should created with false option

Create(true, false)) //try true and false

If we have used true for Create then the ddl should be executed against the Database. At least we have to use it for first time. If it has false it can causes different kinds of error related to objects and it can be hard to know the actual reasons.

private static void InitializeSessionFactory() {
  _sessionFactory = Fluently.Configure().Database(MsSqlConfiguration.MsSql7.ConnectionString(@"Server=(local);initial catalog=MyDatabase;user=my_user_id;password=my_password;").ShowSql()).Mappings(m =>m.FluentMappings.AddFromAssemblyOf < Car > ()).ExposeConfiguration(cfg =>new SchemaExport(cfg).Create(true, false)).BuildSessionFactory();
}

If we use the following (see the All has been changed to None)

References(x => x.Make).Cascade.None();
References(x => x.Model).Cascade.None();

In this case, we get the following error.

	Name	Value	Type
	Message	“Error dehydrating property value for FluentNHibernateSample.Car.Make”	string

4: Fluent NHibernate uses different log4net files. 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. (see how to enable/disable NHibernate log at https://www.hemelix.com/sql/)

5: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect):

List < Device > allDevices = new List < Device > ();
//Add Devices to the list
allDevices.Add(aDevice);
//Add allDevices  to  database //Will work fine
// allDevices.Clear(); //Should be used here
//Later  we add more devices
allDevices.Add(anotherDevice);
//Add allDevices to Database  -> We get the error
//Solution to this  
allDevices.Clear(); //Before adding new transaction with the oldData

6: Lazy initialization error or -failed to lazily initialize a collection of role. If we see the Make object returned from the Cars and the session was closed. We are trying to use the object after closing the session. This can be thrown an exception with message such as no session.

=>We can make a copy of the object and return it for example (any other solution please comment in the Google groups)

See SQL Sample application 2 (Traditional Store Employee example) (We can add, delete, update all operations with SQL Database)