The table has fields Id(identity column for table)and Name. I want to add the instead of trigger that will append date to the Name before adding to table. Now, think the mapping is same as explained previously.
What is happenning here is, while i was inserting a new record through Session.Save(EmpItem) the exception showing "Can not convert DBNull to int32, int64" is being thrown.
When i have seen with NHibernate source code while debugging, the Nhibernate is creating insert query + query to retrun the identity to add the record. The query is like this...... "insert ........ into Employee1;select SCOPE_IDENTITY()" .
The above insert query is returning null value as identity... when the second query "select SCOPE_IDENTITY() " is exceuted.
The reason what i found is, when above two queruies executes under one scope, it will return the proper identity. But, as the table has trigger, the second qyuery "select SCOPE_IDENTITY()" is getting executed under another scope and returning the null value.
Now, when the NHibernate trying to cast the value to Int32, thats thowing the exception.
whats my requrement is to migrate our project so we must not do any changes to the Database . so i request u to follow the steps exactly then u might understand the problem iam facing.
Database script: run it in PUBS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GRP_INSERT]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[GRP_INSERT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employee1]
GO
CREATE TABLE [dbo].[Employee1] (
[Id] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
cREATE TRIGGER GRP_INSERT ON Employee1
INSTEAD OF INSERT
AS
INSERT INTO Employee1 (NAME)
SELECT NAME FROM INSERTED
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------
actually the fuction of trigger is to insert current date from database server.just i created a dummy trigger.
-------------------------------------------------------------
take a project as ECM.Domain and add the below files
step 2:mapping file Employee.hbm.xml
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="ECM.Domain" namespace="ECM.Domain" >
<class name="Employee" table="Employee1" >
<id name="Id" column="Id" type="Int32" unsaved-value="0">
<generator class="identity"/>
</id>
<property name="Name" column="Name" type="String" length="100" not-null="true" />
</class>
</hibernate-mapping>
-----------------------------------------------------
cs file: Employee.cs
namespace ECM.Domain
{
[Serializable]
public class Employee
{
private int id;
public int Id
{
get { return id; }
set { id = value; }
}
private String name;
public String Name
{
get { return name; }
set { name = value; }
}
}
}
--------------------------------------------------------------------
create a web project
In appcode create a class with name EmployeeRegister.cs
using NHibernate;
using NHibernate.Expression;
using ECM.Domain;
/// <summary>
/// Summary description for EmployeeRegister
/// </summary>
public class EmployeeRegister
{
ISessionFactory sessionFactory;
ITransaction tx;
private const string id = "Name";
public EmployeeRegister()
{
//
// TODO: Add constructor logic here
//
}
public int CreateEmployee(string Name)
{
sessionFactory = new NHibernate.Cfg.Configuration().Configure().BuildSe ssionFactory();
ISession dataSession;
Employee EmployeeItem = null;
try
{
dataSession = sessionFactory.OpenSession();
EmployeeItem = (Employee)dataSession.CreateCriteria(typeof(Employ ee))
.Add(Expression.Eq(id, Name)).UniqueResult();
if (EmployeeItem != null)
return 2;
EmployeeItem = new Employee();
EmployeeItem.Name = Name;
tx = dataSession.BeginTransaction();
dataSession.Save(EmployeeItem);
tx.Commit();
dataSession.Refresh(EmployeeItem);
return 1;
}
catch (Exception ex)
{
}
return 3;
}
}
In Bin hibernate.cfg.xml look like this
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.0">
<session-factory name="QandASessionFactory">
<!-- properties -->
<property name="connection.provider">NHibernate.Connection.D riverConnectionProvider</property>
<property name="connection.driver_class">NHibernate.Driver.S qlClientDriver</property>
<!-- Data Source, User Name, Database Name and password should be changed -->
<property name="connection.connection_string">Data Source=(local);initial catalog=pubs;trusted_connection=true;</property>
<property name="show_sql">true</property>
<property name="dialect">NHibernate.Dialect.MsSql2000Dialect </property>
<property name="use_outer_join">true</property>
<property name="query.substitutions">true 1, false 0, yes 'Y', no 'N'</property>
<property name="hibernate.connection.release_mode">on_close</property>
<mapping assembly="ECM.Domain" />
</session-factory>
</hibernate-configuration>
Next in default.aspx
keep one textbox and button.
in code behind
protected void Button1_Click(object sender, EventArgs e)
{
EmployeeRegister er = new EmployeeRegister();
er.CreateEmployee(TextBox1.Text.ToString());
}
I tried with both "native" and Identity in hbm file.
Run it u can find the problem.If trigger is not there it is working fine.but i can't remove the trigger.I hope u understand the problem.
Thanks,
venkat
Login
FAQ
Search