New Database Tables and NHibernate

[dc8johnson]

We have a site we're upgrading from 7.0.7. We have a number of custom database tables for interfacing with the customer's backend systems. We used the MyGeneration templates to create the DAL for 7.0.7. I see that there are no templates for Gold (viewtopic.php?f=65&t=17362).

We used one of the existing set of DAL files as a test case for one of our simpler tables. When we put the mapping file and the cs file with all the data access code into the App_Code folder, we get these errors:

Code: Select all

'CommerceBuilder.DomainModel.RegisterForAttribute' is inaccessible due to its protection level
The type or namespace name 'RegisterFor' could not be found (are you missing a using directive or an assembly reference?)

Is it necessary to compile our new data access code into the CommerceBuilder.dll? We'd rather keep our code separate to make updating easier.

Thanks

[jmestep]

You do not need to add code to Able's dll. You can use nHibernate code to modify the tables or use custom database code to modify the tables. There is a wiki post about using nHibernate.
http://wiki.ablecommerce.com/index.php/ ... _-_AC_Gold
I have created new dataclasses using nHibernate and once I got the hang of it, it was very easy. I took some of Able's code and changed tables and fields.
You can also use some ISql queries with nHibernate, like the following- these were from my earliest efforts so I might do it differently now.

Code: Select all

public static string GetShippingPrice(ProductVariant variant)
        {
            string shippingPrice = "";
            int variantId = variant.Id;
            ISQLQuery query = NHibernateHelper.CreateSQLQuery("SELECT ShippingPrice FROM W2M_ProductVariants WHERE ProductVariantId = :variantId");
            query.SetParameter("variantId", variant.Id);
            //string name, int value
            shippingPrice = query.List<string>().FirstOrDefault();            
            return shippingPrice;
        }

Code: Select all

                        NHibernateHelper.CreateSQLQuery("Update W2M_ProductVariants SET ShippingPrice=:shippingprice WHERE ProductVariantId = :variantId")
                                   .SetParameter("shippingprice", shippingPrice)
                                   .SetParameter("variantId", variant.Id)
                                   .ExecuteUpdate();

Code: Select all

                        NHibernateHelper.CreateSQLQuery("INSERT INTO W2M_ProductVariants (ProductVariantId,ShippingPrice) Values (:variantId,:shippingPrice)")
                             .SetParameter("variantId", variant.Id)
                             .SetParameter("shippingPrice", shippingPrice)
                             .ExecuteUpdate();

[mazhar]

We have a site we're upgrading from 7.0.7. We have a number of custom database tables for interfacing with the customer's backend systems. We used the MyGeneration templates to create the DAL for 7.0.7. I see that there are no templates for Gold (viewtopic.php?f=65&t=17362).

We used one of the existing set of DAL files as a test case for one of our simpler tables. When we put the mapping file and the cs file with all the data access code into the App_Code folder, we get these errors:

Code: Select all

'CommerceBuilder.DomainModel.RegisterForAttribute' is inaccessible due to its protection level
The type or namespace name 'RegisterFor' could not be found (are you missing a using directive or an assembly reference?)

Is it necessary to compile our new data access code into the CommerceBuilder.dll? We'd rather keep our code separate to make updating easier.

Thanks

RegisterFor attribute is used to implement a Windsor based service. If you are trying to override some default service you just need to make an entry in App_Data/Windsor.config file. This also holds true if you are trying to introduce totally new windsor manged component. We used RegisterFor attribute for default services and its essentially donig same thing which Windsor.config does. System gives preference to a service register via Windsor.config over RegisterFor attribute. This way when ever some one wants to override default service all it needs is an entry in Windsor.config. Please have a look at this topic in WIKI about how create an entry in Windsor.config file
http://wiki.ablecommerce.com/index.php/ ... or_AC_Gold

[dc8johnson]

Thanks Judy and Mazhar!

Using the MyGeneration templates for 7.0.7 we were able to access our new tables using the same functions as the standard tables. I'll take a look at the windsor.config Wiki. If that proves problematic we can fall back to the creating classes with our own custom NHibernate functions.

Thanks again,

[dc8johnson]

Mazhar,

I'm having some problems integrating new data tables into NHibernate. I'm sure it's something simple, but I haven't been able to find the problem. The web application is using WSP mode.

Here is my new table mapping file which I have tried in several folders but is currently in a "Mappings" folder

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="MEDCOData.Rebate" table="`MEDCO_Rebates`" lazy="true">
    <id name="Id" column="RebateId" type="int">
      <generator class="native" />
    </id>
    <property type="string" name="Name">
      <column name="Name" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="int" not-null="true" name="ManufacturerId" column="`ManufacturerId`" />
    <property type="string" name="ManufacturerLogoUrl">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="DocumentUrl">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="Description">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="ProductCodes">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="LineCode">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="char(3)" />
    </property>
    <property type="DateTime" not-null="true" name="ExpirationDate" column="`ExpirationDate`" />
    <property type="DateTime" not-null="true" name="CreatedDate" column="`CreatedDate`" />
    <property type="DateTime" name="DisplayDate" column="`DisplayDate`" />
  </class>
</hibernate-mapping>

Here is my class file which I have put in the App_Code folder:

Code: Select all

namespace MEDCOData
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using CommerceBuilder.DomainModel;
    using System.ComponentModel;
    using NHibernate.Criterion;
    using NHibernate;
    using CommerceBuilder.Products;
    using CommerceBuilder.Common;

    [Serializable]
    public partial class Rebate
    {
        public virtual int RebateId { get; set; }
        public virtual string Name { get; set; }
        public virtual int ManufacturerId { get; set; }
        public virtual string ManufacturerLogoUrl { get; set; }
        public virtual string DocumentUrl { get; set; }
        public virtual string Description { get; set; }
        public virtual string ProductCodes { get; set; }
        public virtual string LineCode { get; set; }
        public virtual DateTime ExpirationDate { get; set; }
        public virtual DateTime CreatedDate { get; set; }
        public virtual DateTime DisplayDate { get; set; }

        public Rebate()
        {
        }

        [DataObjectMethod(DataObjectMethodType.Select)]
        public static IList<Rebate> LoadForStore(int maximumRows, int startRowIndex, string sortExpression)
        {
            return NHibernateHelper.CreateCriteria<Rebate>(maximumRows, startRowIndex, sortExpression)
                .List<Rebate>();

        }

        public static int CountForStore()
        {
            return NHibernateHelper.CreateCriteria<Rebate>()
                .SetProjection(Projections.RowCount())
                .UniqueResult<int>();
        }

        public static int InsertRebate(Rebate _rebate)
        {
            return NHibernateHelper.CreateSQLQuery("INSERT INTO MEDCO_Rebates (name, manufacturerId, manufacturerLogoUrl, documentUrl, description, productCodes, lineCode, expirationDate, createdDate, displayDate) VALUES (:name, :manufacturerId, :manufacturerLogoUrl, :documentUrl, :description, :productCodes, :lineCode, :expirationDate, :createdDate, :displayDate)")
                .SetParameter("name", _rebate.Name)
                .SetParameter("manufacturerId", _rebate.ManufacturerId)
                .SetParameter("manufacturerLogoUrl", _rebate.ManufacturerLogoUrl)
                .SetParameter("documentUrl", _rebate.DocumentUrl)
                .SetParameter("description", _rebate.Description)
                .SetParameter("productCodes", _rebate.ProductCodes)
                .SetParameter("lineCode", _rebate.LineCode)
                .SetParameter("expirationDate", _rebate.ExpirationDate)
                .SetParameter("createdDate", _rebate.CreatedDate)
                .SetParameter("displayDate", _rebate.DisplayDate)
                .ExecuteUpdate();
        }

    }
}

When I run this next code I get no results. If I step through the class code it is returning 0 records from the NHibernate CreateCriteria call.

Code: Select all

IList<MEDCOData.Rebate> _pzFiles = MEDCOData.Rebate.LoadForStore(10, 0, "Name");

If I run the code below, the call to InsertRebate works fine. But the session.Save returns a "No persister for: MEDCOData.Rebate".

Code: Select all

MEDCOData.Rebate _rebate = new MEDCOData.Rebate();
_rebate.Name = "Test " + DateTime.Now.ToString();
_rebate.ProductCodes = "1,2,3";
_rebate.ManufacturerId = 1;
_rebate.ManufacturerLogoUrl = "URL";
_rebate.DocumentUrl = "DocURL";
_rebate.LineCode = "ATD";
_rebate.ExpirationDate = DateTime.Now.AddMonths(1);
_rebate.CreatedDate = DateTime.Now;
_rebate.DisplayDate = DateTime.Now;

int result = MEDCOData.Rebate.InsertRebate(_rebate);

ISession session = AbleContext.Current.Database.GetSession();
_rebate.Name = "Test " + DateTime.Now.ToString();
session.Save(_rebate);

I've tried many different ways of setting this up and I think I'm missing something simple with the persister issue and I'm pretty sure that same issue is why I get no data back from the CreateCriteria call. A lot of Google searches mention making sure the mapping file is an embedded resource, but that's not possible with a WSP app.

Any help you can provide would be very appreciated. I'm sure there will be others who would like to create custom tables and manage them using the same type of interface as the Able tables.

Thanks!

[mazhar]

Make sure your HBM file is marked as EmbededResource in visual studio.

[dc8johnson]

You can't mark a file as an embedded resource using the WSP version. That's not available in the properties tab. Is there another way to embed the file?

[dc8johnson]

I've tried adding NHibernate.Mapping.Attributes and using that syntax as shown below. Same error: No persister for: MEDCOData.Rebate

Code: Select all

    [Class(NameType = typeof(Rebate),
       Table = "`MEDCO_Rebates`", Lazy = true)]
    public class Rebate
    {
        [Id(0, Name = "Id", Column = "RebateId")]
        [Generator(1, Class = "native")]
        public virtual int Id { get; set; }

        [Property(Column = "Name")]
        public virtual string Name { get; set; }

        [Property(Column = "ManufacturerId")]
        public virtual int ManufacturerId { get; set; }

        [Property(Column = "ManufacturerLogoUrl")]
        public virtual string ManufacturerLogoUrl { get; set; }

        [Property(Column = "DocumentUrl")]
        public virtual string DocumentUrl { get; set; }

        [Property(Column = "Description")]
        public virtual string Description { get; set; }

        [Property(Column = "ProductCodes")]
        public virtual string ProductCodes { get; set; }

        [Property(Column = "LineCode")]
        public virtual string LineCode { get; set; }

        [Property(Column = "ExpirationDate")]
        public virtual DateTime ExpirationDate { get; set; }

        [Property(Column = "CreatedDate")]
        public virtual DateTime CreatedDate { get; set; }

        [Property(Column = "DisplayDate")]
        public virtual DateTime DisplayDate { get; set; }
    }

This seems like it has to be something simple.

Thanks,

[mazhar]

In case of website build the easy option is to place your code in Class library project and reference it in your website project. You will need to update App_Data/nhibernate.config file and tell about your new DLL containing your mappings like

Code: Select all

<mapping assembly="MyCustomDLL"/>

The other option maybe to manually add your mappings to nhibernate configurations. Have a look at this article http://www.klopfenstein.net/lorenz.aspx ... ts-express

[jmestep]

I got that same error a few times when I have forgotten to register an assembly in the nHibernate.config file.
I still forget at times, but at least I recognize it now.

The really neat thing about the way Able has things set up is that you can get basic data access methods without generating or writing a bunch of code. For example, one of my plugins has this in the datasource file:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CommerceBuilder.DomainModel;

namespace Web2Market.W2MReportWizard
{
      public class W2MWizardReportDataSource : DataSource<W2MWizardReport, IRepository<W2MWizardReport>>
     {

    }
}

Here are the methods that I get by doing the above. Then if I need others, I add them.

9-30-2013 8-21-51 AM.jpg

[dc8johnson]

Mazhar and Judy - thank you for your help!

I've tried everything(?) from both of your suggestions and I'm still having problems. I looked at the link from Mazhar and added the mapping files to the nhibernate configuration. Here's that code:

Code: Select all

NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
cfg.Configure(ConfigUtility.LocateConfigFile("nhibernate.config"));
string mappingFile = Server.MapPath("App_Data\\") + "Rebate.hbm.xml";
cfg.AddFile(mappingFile);
ISessionFactory factory = AbleContext.Current.Database.GetSession().SessionFactory;
factory = cfg.BuildSessionFactory();

I put a break point just after this code and looked at the ClassMappings in the cfg variable and it had added my new mapping. Here are a few of the values from that ClassMapping. I compared them others and they were similar although I didn't compare property by property. But it looks like the mapping took and

Code: Select all

ClassName: "MEDCOData.Rebate, App_Code.jehzjm01, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null"
Table: "NHibernate.Mapping.Table(MEDCO_Rebates)"
EntityName: "MEDCOData.Rebate"
HasPocoRepresentation: true
MappedClass: "MEDCOData.Rebate"
ProxyInterfaceName: "MEDCOData.Rebate, App_Code.jehzjm01, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null"
Key: "NHibernate.Mapping.SimpleValue(NHibernate.Mapping.Column(RebateId))"

Here is the mapping file:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="MEDCOData.Rebate, __code" table="`MEDCO_Rebates`" lazy="true" dynamic-update="true">
    <id name="Id" column="RebateId" type="int">
      <generator class="native" />
    </id>
    <property type="string" name="Name">
      <column name="Name" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="int" not-null="true" name="ManufacturerId" column="`ManufacturerId`" />
    <property type="string" name="ManufacturerLogoUrl">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="DocumentUrl">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="Description">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="ProductCodes">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="varchar(255)" />
    </property>
    <property type="string" name="LineCode">
      <column name="ManufacturerLogoUrl" not-null="true" sql-type="char(3)" />
    </property>
    <property type="CommerceBuilder.DomainModel.LocalDateTime, CommerceBuilder" not-null="true" name="ExpirationDate" column="ExpirationDate" />
    <property type="CommerceBuilder.DomainModel.LocalDateTime, CommerceBuilder" not-null="true" name="CreatedDate" column="CreatedDate" />
    <property type="CommerceBuilder.DomainModel.LocalDateTime, CommerceBuilder" name="DisplayDate" column="DisplayDate" />
  </class>
</hibernate-mapping>

and here is Rebate class.

Code: Select all

namespace MEDCOData
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using CommerceBuilder.DomainModel;
    using System.ComponentModel;
    using NHibernate.Criterion;
    using NHibernate;
    using NHibernate.Mapping.Attributes;
    using CommerceBuilder.Products;
    using CommerceBuilder.Common;

    public partial class Rebate : Entity
    {
        public override int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual int ManufacturerId { get; set; }
        public virtual string ManufacturerLogoUrl { get; set; }
        public virtual string DocumentUrl { get; set; }
        public virtual string Description { get; set; }
        public virtual string ProductCodes { get; set; }
        public virtual string LineCode { get; set; }
        public virtual DateTime ExpirationDate { get; set; }
        public virtual DateTime CreatedDate { get; set; }
        public virtual DateTime DisplayDate { get; set; }

        [System.Obsolete("Use the Id property instead.")]
        public virtual int RebateId
        {
            get { return this.Id; }
            set { this.Id = value; }
        }
        
        public Rebate()
        {
        }

        public Rebate(string name, int manufacturerId, string manufacturerLogoUrl, string documentUrl, string description, string productCodes, string lineCode, DateTime expirationDate, DateTime createdDate, DateTime? displayDate)
        {
            this.Name = name;
            this.ManufacturerId = manufacturerId;
            this.ManufacturerLogoUrl = manufacturerLogoUrl;
            this.DocumentUrl = documentUrl;
            this.Description = description;
            this.ProductCodes = productCodes;
            this.LineCode = lineCode;
            this.ExpirationDate = expirationDate;
            this.CreatedDate = createdDate;
            this.DisplayDate = (DateTime)displayDate;
        }
    }

    public partial class RebateDataSource : DataSource<Rebate, IRepository<Rebate>>
    {
    }
}

Using Judy's suggestion I added the RebateDataSource code and it does indeed make the methods show up in Visual Studio. But when I run CountAll or LoadAll I get 0 records even though there are several records in the database. Here are those calls:

Code: Select all

int totalRows = RebateDataSource.CountAll();
IList<Rebate> _rebates = RebateDataSource.LoadAll();

I created a function that uses the CreateSQLQuery and it properly inserts into the database. It returns the ID of the new record.

Code: Select all

public static int InsertRebate(Rebate _rebate)
{
    return NHibernateHelper.CreateSQLQuery("INSERT INTO MEDCO_Rebates (name, manufacturerId, manufacturerLogoUrl, documentUrl, description, productCodes, lineCode, expirationDate, createdDate, displayDate) VALUES (:name, :manufacturerId, :manufacturerLogoUrl, :documentUrl, :description, :productCodes, :lineCode, :expirationDate, :createdDate, :displayDate); SELECT CAST(@@IDENTITY AS INT) AS 'Identity';")
        .SetParameter("name", _rebate.Name)
        .SetParameter("manufacturerId", _rebate.ManufacturerId)
        .SetParameter("manufacturerLogoUrl", _rebate.ManufacturerLogoUrl)
        .SetParameter("documentUrl", _rebate.DocumentUrl)
        .SetParameter("description", _rebate.Description)
        .SetParameter("productCodes", _rebate.ProductCodes)
        .SetParameter("lineCode", _rebate.LineCode)
        .SetParameter("expirationDate", _rebate.ExpirationDate)
        .SetParameter("createdDate", _rebate.CreatedDate)
        .SetParameter("displayDate", _rebate.DisplayDate)
        .List<int>()
        .FirstOrDefault();
}

I created another function to see if I could return the database records through a SQL query. Here's the code:

Code: Select all

public static IList<Rebate> SelectAll()
{
    return NHibernateHelper.CreateSQLQuery("select rebateid, name, manufacturerId, manufacturerLogoUrl, documentUrl, description, productCodes, lineCode, expirationDate, createdDate, displayDate from MEDCO_Rebates")
        .AddEntity(typeof(Rebate))
        .List<Rebate>();
}

and it returned this error:

Code: Select all

MappingException was unhandled by user code. No persister for: MEDCOData.Rebate

Bottom line is that it doesn't look like NHibernate has all it needs to work with my new database table.

I also tried creating a separate project that would create a new assembly with my mapping file and class. It worked just as well/poorly as the above code. I could recreate instances of the rebate but not invoke the Save. I could run the CountAll and LoadAll methods but they returned no results. I could run the same InsertRebate method and it also worked there. And I still got the "no persister" error at the same places as the first set of code.

Any other thoughts? Seems a shame to drop back to basic SQL statements and negates some of the usefulness of having NHibernate.

Thanks again for any help you might be able to provide!

[jmestep]

I remember I tried to find a way to add mappings via code and ran into some problems, but I don't remember exactly what they were. I had been going thru some nHibernate books that explained how to do that and I think the problem I ran into was that there already was an nhibernate.config file. So I went back to creating a dll, which I needed to do for our plugins to handle our licensing code. Here is an outline of what I did (what I remember). This plugin wasn't a complicated one- only one small table involved.
I created the hbm.xml file and set it as an embedded resource in the dll. I remember the tricky part with it after I set it embedded was in coordinating the assembly name with the assembly name that would be put in the site's nhibernate.config file. I kept having trouble with that- it was information overload when I first started with nHibernate. I actually took a sample that Able had provided and kept the name of the assembly the same, but changed the guts. Then when I got that working, I redid it with a new assembly name. For this one, I ended up with

Code: Select all

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="Web2Market.W2MReportWizard" assembly="W2MReportWizard">

The name of the dll was W2MReportWizard.dll. Mapping tag ended up being <mapping assembly="W2MReportWizard" />

I've already posted the datasource file. The W2MWizardReport.cs started out

Code: Select all

namespace Web2Market.W2MReportWizard
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using CommerceBuilder.Common;
    using CommerceBuilder.DomainModel;
    using CommerceBuilder.Shipping;

    /// <summary>
    /// Address object for NHibernate mapped table 'W2M_WizardReports'.
    /// </summary>
    [Serializable]
    public class W2MWizardReport : Entity
    {

The properties of the class were public virtual- I would have to look at my notes but I think that was to take advantage of the lazy loading.
Here is some of it:

Code: Select all

  /// <summary>
        /// Gets or sets the Id
        /// </summary>
        public override int Id { get; set; }

       
        /// <summary>
        /// Gets or sets the ReportName
        /// </summary>
        public virtual string ReportName { get; set; }

I added an override of ToString() and an override of Save() because that is where I checked the license file.
If I take out those overrides and the the documentation and blank space, the class would have about 20 lines of code. So with 3 files and the largest having 20 lines of code, I've got a fully-functioning object.

I have run into problems with nhibernate performance if I use it for a large report or an admin page that does a lot of data crunching but have not had problems with basic code. I think I might be able to tweak some of the code I had problems with to perform better, but I already switched to ado code for that. I just haven't had a reason to try it again.

[dc8johnson]

Judy - THANKS VERY MUCH!! I now have the basics working.

I ended up creating the separate assembly. I did have some naming issues between the namespace and assembly name in the mapping file and nhibernate config file in my previous attempts, so that was the main problem.

Still some things to figure out, but at least I can now load and save data.

I'll come back and update this post with my final setup so the next person to try this will have a more complete example.

Thanks again to both you and Mazhar for your help!