This
has growing transaction tables with same structure on year basis like
Jan2008/Jan 2009
This has different DB for different countries
but the schema and table structure are same. Application has different
connection for different host but the underlying tables are having same
designed, only its name gets prefixed with country code. To make this I have
used NHibernate Conventions
Better
to create sesionfactory for each connection and design the NHibernate classes
and Class map such a way that at runtime the classes name will have right table
name for the right sessionfactory created for it’s corresponding connection.
Named
instace of repository got created, this will invoke Appsessionfactory to return
the right instance of sessionfactory and it’s session.
And
named instance of repository will becreated depending upon the parameters
provided by client.
NHibernateSessionfactory
will hold the collection of connection key and it’s session factory
AppSessionfacotry
will hold the collection of NHibernateSessionFactory and will return the right
session factory depending upon the type of repository instace created.
NHibernateSessionFactories
– to be initialized only once will be saved in Structuremap and will be called
at run time.
And
AppSessionfactory will hold the session factory
and will return the right session for the right connection
string/object/connection key.
Single
Instance of Session Factory:
Global.ASAX:
protected void Application_Start(object
sender, EventArgs e)
{
commonModel.ServerName
= this.Server.MachineName;
IDictionary<string, string>
objConfigConnSchema = new Dictionary<string,
string>();
//Register
all connectionKeys
//This is to get all
connection defined in config section along with it’s countrycode which will be
prefixed in table name .
//So class will have
the right table name for the right session factory. And this will create the
session factory for all the connection defined.
foreach (ConnectionStringSettings location in ConfigurationManager.ConnectionStrings)
{
ConnectionStringSettings
locacationsettings = location;
objConfigConnSchema.Add(locacationsettings.Name,
location.ConnectionString);
}
//Sets DB
Name for ConnKey
getDBName(ref
objConfigConnSchema);
//Bootstrapper.RegisterIoC();
NHibernateManager.CreateNHibernateSessoinFactories(objConfigConnSchema);
}
private
void getDBName(ref
IDictionary<string,
string> _configConnSchema)
{
IDictionary<string, string>
_tmpConfigConnSchema = new Dictionary<string,
string>();
//envCntry
= countryCode + "_PRD";
foreach
(string connKey in
_configConnSchema.Keys)
{
_tmpConfigConnSchema.Add(connKey, ConfigurationManager.AppSettings[connKey]);
//_tmpConfigConnSchema[connKey]=
ConfigurationManager.AppSettings[connKey];
//
_configConnSchema[connKey] = ConfigurationManager.AppSettings[connKey];
}
_configConnSchema=_tmpConfigConnSchema;
}
NHibernate
Manager:
public static class NHibernateManager
{
//this can
build sessionfactory at runtime to retrun sessionfactory
public static IList<NHibernateSessionFactories>
NHibernateSessionFactoriesList;
private
static object
syncRoot = new Object();
//Called from
Global.asax file and being used
//Creates
named instance of sessionfactory and repository class
//name being
the same as defined in connection strings key in config
public static void
CreateNHibernateSessoinFactories(IDictionary<string, string>
_objDctConfigKeys) //this will be called once from
global asax.
{
ObjectFactory.Initialize(x
=>
{
foreach
(string connname in
_objDctConfigKeys.Keys)
{
string
key = connname;
string
dbSchema = _objDctConfigKeys[key];
if
(!key.Contains("Local"))
{
x.For<INHibernateSessionFactories>().Singleton()
.Use(y
=> new NHibernateSessionFactories(key,
BuildSessionFactories(key,dbSchema)));
x.For<IRepository>().AddInstances(ctx =>
ctx.ConstructedBy(() => new Repository(ObjectFactory.GetInstance<IAppSessionFactories>(), key)).Named(key));
}
}
x.For<IAppSessionFactories>().Singleton().Use<AppSessionFactories>();
});
}
private
static ISessionFactory
BuildSessionFactories(string _connStrKey, string _schema)
{
return
new NHibernateHelper().BuildSessionfactory(_connStrKey,_schema);
}
NHibernateHelper:
public class NHibernateHelper
{
public ISessionFactory BuildSessionfactory(string _connString, string
_schema)
{
switch
(_schema)
{
case
"BR":
return
BuildProdBRSessionfactory(_connString);
case
"IN":
return
BuildProdINSessionfactory(_connString);
case
"K1":
return
BuildProdK1Sessionfactory(_connString);
case
"K2":
return
BuildProdK2Sessionfactory(_connString);
case
"JP":
return
BuildProdJPSessionfactory(_connString);
default:
return
null;
}
}
private
ISessionFactory BuildProdINSessionfactory(string _connString)
{
NHibernate.Cfg.Configuration config = null;
config = Fluently.Configure()
.Database(FluentNHibernate.Cfg.Db.DB2Configuration.Standard.Dialect<DB2400Dialect>()
.Driver<OdbcDriver>()
//
.DefaultSchema(_schema)
.ConnectionString(c =>
c.FromConnectionStringWithKey(_connString))
.IsolationLevel(System.Data.IsolationLevel.ReadUncommitted).ShowSql())
// .Mappings(mapping =>
mapping.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
.Mappings(mapping =>
mapping.FluentMappings.AddFromAssemblyOf<MSHelper>()
.Conventions.Add<TableNameProdINSchemaConvention>()
)
.CurrentSessionContext<WcfOperationSessionContext>()
.ProxyFactoryFactory<ProxyFactoryFactory>()
.BuildConfiguration();
return
config.BuildSessionFactory();
}
private
ISessionFactory BuildProdBRSessionfactory(string _connString)
{
NHibernate.Cfg.Configuration config = null;
config = Fluently.Configure()
.Database(FluentNHibernate.Cfg.Db.DB2Configuration.Standard.Dialect<DB2400Dialect>()
.Driver<OdbcDriver>()
//
.DefaultSchema(_schema)
.ConnectionString(c =>
c.FromConnectionStringWithKey(_connString))
.IsolationLevel(System.Data.IsolationLevel.ReadUncommitted).ShowSql())
// .Mappings(mapping =>
mapping.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
.Mappings(mapping =>
mapping.FluentMappings.AddFromAssemblyOf<MSHelper>()
.Conventions.Add<TableNameProdBRchemaConvention>()
)
.CurrentSessionContext<WcfOperationSessionContext>()
.ProxyFactoryFactory<ProxyFactoryFactory>()
.BuildConfiguration();
return
config.BuildSessionFactory();
}
private
ISessionFactory BuildProdJPSessionfactory(string _connString)
{
NHibernate.Cfg.Configuration config = null;
config = Fluently.Configure()
.Database(FluentNHibernate.Cfg.Db.DB2Configuration.Standard.Dialect<DB2400Dialect>()
.Driver<OdbcDriver>()
//
.DefaultSchema(_schema)
.ConnectionString(c =>
c.FromConnectionStringWithKey(_connString))
.IsolationLevel(System.Data.IsolationLevel.ReadUncommitted).ShowSql())
// .Mappings(mapping =>
mapping.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly()))
.Mappings(mapping =>
mapping.FluentMappings.AddFromAssemblyOf<MSHelper>()
.Conventions.Add<TableNameProdJPSchemaConvention>()
)
.CurrentSessionContext<WcfOperationSessionContext>()
.ProxyFactoryFactory<ProxyFactoryFactory>()
.BuildConfiguration();
return
config.BuildSessionFactory();
}
public class TableNameProdINSchemaConvention
: IClassConvention
{
#region
IConvention<IClassInspector,IClassInstance> Members
public void Apply(FluentNHibernate.Conventions.Instances.IClassInstance instance)
{
// throw new NotImplementedException();
instance.Table("IN" + ClassMapTableFactories.ClassTableNameMappingList.Where<ClassTableMapping>
(clstbl => clstbl.ClassName ==
instance.EntityType.Name).Single().TableName);
}
#endregion
}
public class TableNameProdBRchemaConvention
: IClassConvention
{
#region
IConvention<IClassInspector,IClassInstance> Members
public void Apply(FluentNHibernate.Conventions.Instances.IClassInstance instance)
{
// throw new NotImplementedException();
instance.Table("BR" + ClassMapTableFactories.ClassTableNameMappingList.Where<ClassTableMapping>
(clstbl => clstbl.ClassName ==
instance.EntityType.Name).Single().TableName);
}
#endregion
}
public class TableNameProdK1SchemaConvention
: IClassConvention
{
#region IConvention<IClassInspector,IClassInstance>
Members
public void Apply(FluentNHibernate.Conventions.Instances.IClassInstance instance)
{
// throw new NotImplementedException();
instance.Table("K1" + ClassMapTableFactories.ClassTableNameMappingList.Where<ClassTableMapping>
(clstbl => clstbl.ClassName ==
instance.EntityType.Name).Single().TableName);
}
#endregion
}
public class TableNameProdK2SchemaConvention
: IClassConvention
{
#region
IConvention<IClassInspector,IClassInstance> Members
public void Apply(FluentNHibernate.Conventions.Instances.IClassInstance instance)
{
// throw new NotImplementedException();
instance.Table("K2" + ClassMapTableFactories.ClassTableNameMappingList.Where<ClassTableMapping>
(clstbl => clstbl.ClassName ==
instance.EntityType.Name).Single().TableName);
}
#endregion
}
public class TableNameProdJPSchemaConvention
: IClassConvention
{
#region
IConvention<IClassInspector,IClassInstance> Members
public void Apply(FluentNHibernate.Conventions.Instances.IClassInstance instance)
{
// throw new NotImplementedException();
instance.Table("JP" + ClassMapTableFactories.ClassTableNameMappingList.Where<ClassTableMapping>
(clstbl => clstbl.ClassName ==
instance.EntityType.Name).Single().TableName);
}
#endregion
}
public interface INHibernateSessionFactories
{
string
ConnKey { get;
}
ISessionFactory
SessionFactory {get;}
}
public class NHibernateSessionFactories
: INHibernateSessionFactories
{
public string ConnKey { get;
private set; } // will contain name of config key -connection key
public ISessionFactory SessionFactory { get; private set; }
public
NHibernateSessionFactories(string _connKey, ISessionFactory _sessionFactory)
{
ConnKey = _connKey;
SessionFactory = _sessionFactory;
}
}
public interface IAppSessionFactories
{
ISession
GetSessionForLocation(string _connKey);
}
public class AppSessionFactories
: IAppSessionFactories
{
private
readonly IList<INHibernateSessionFactories> _factories;
public
AppSessionFactories(IEnumerable<INHibernateSessionFactories> _hibernateFactories)
{
_factories = new
List<INHibernateSessionFactories>(_hibernateFactories);
//NHibernateManager.NHibernateSessionFactoriesList
.Add(_hibernateFactories);
}
public ISession GetSessionForLocation(string _connKey)
{
var
sessionFactory = _factories.Where(x => x.ConnKey ==
_connKey).Single().SessionFactory;
return
sessionFactory.OpenSession();
}
}
}
This
is to store the class and it’s table map: instead of writing it in classmap , I
got this in static object and have used the conventions to replace the class
name to right table depending upon the type of repository intanse created.
public class ClassTableMapping
{
private
ClassTableMapping()
{
}
public string ClassName { get;
private set; }
public string TableName
{ get; private
set; }
public ClassTableMapping(string
_className, string _tblName)
{
ClassName = _className;
TableName = _tblName;
}
}
public static class ClassMapTableFactories
{
private static object
syncRoot = new Object();
private static IList<ClassTableMapping> lstClassAndTableName;
public static IList<ClassTableMapping> ClassTableNameMappingList
{ get { return lstClassAndTableName; } }
static
ClassMapTableFactories()
{
lock
(syncRoot)
{
lstClassAndTableName = new List<ClassTableMapping>();
//Add
all the mapping of Entity to DB tables
MapClassAndTableName();
}
}
private static void
MapClassAndTableName()
{
lstClassAndTableName.Add(new ClassTableMapping("CountryDepartment", "MDSRPT.Cntry_Dept"));
lstClassAndTableName.Add(new ClassTableMapping("CountryDepartmentText", "MDSRPT.Cntry_Dept_Txt"));
lstClassAndTableName.Add(new ClassTableMapping("SamsItem", "SAMITM.Item"));
lstClassAndTableName.Add(new ClassTableMapping("SamsPlanner", "SMDSRP.Sams_Planner"));
Now let’s
create Repostiory;
This
will be based upon the input parameter.
Static
Void Main ()
{
private void CommitTransaction()
{
ISession
session = ((Hierarchy_Services.Infrastructure.BusinessLogic.Repository)_repository)._session;
if
(session != null)
{
try
{
if
(session.IsOpen)
{
if (session.Transaction.IsActive)
session.Transaction.Commit();
}
}
catch
(Exception ex)
{
logger.Error(GetErrorMessage(ex));
RollbackTransaction();
}
}
}
private
void RollbackTransaction()
{
ISession
session = ((Hierarchy_Services.Infrastructure.BusinessLogic.Repository)_repository)._session;
if
(session.IsOpen)
{
if
(session.Transaction != null &&
session.Transaction.IsActive)
{
session.Transaction.Rollback();
}
}
}
private
void CloseSession()
{
ISession
session = ((Hierarchy_Services.Infrastructure.BusinessLogic.Repository)_repository)._session;
if
(session != null && session.IsOpen)
{
if
(session.Transaction.IsActive)
session.Transaction.Rollback();
if
(session.Connection != null)
{
session.Connection.Close();
}
session.Close();
session.Dispose();
}
}
}
private IRepository _repository;
private void setCountryConfig(string
countryCode, int divNo)
{
commonModel.CountryCode
= countryCode;
// commonModel.DBName = getDBName(countryCode);
// commonModel.EnvCntConnection =
getEnvCntConnectionKey(countryCode,divNo);
_repository = ObjectFactory.GetNamedInstance<IRepository>(getEnvCntConnectionKeyFromConfig(countryCode,
divNo));
}
public class Repository :
IRepository, IDisposable
{
public ISession _session { get;
set; }
// public
Repository(ISession session) { _session = session; }
//This will
be called from ObjectFactory
public
Repository(IAppSessionFactories factory, string key)
{
_session =
factory.GetSessionForLocation(key);
_session.FlushMode = FlushMode.Commit;
_session.BeginTransaction();
}
private
bool isDisposed = false;
public IQueryable<T>
Find<T>(System.Linq.Expressions.Expression<Func<T, bool>>
predicate) where T : IEntity
{
if
(isDisposed)
{
throw new
ObjectDisposedException(GetType().Name);
}
return
_session.Query<T>().Where(predicate);
}
public IQueryable<T> FindAll<T>() where T : IEntity
{
if
(isDisposed)
{
throw
new ObjectDisposedException(GetType().Name);
}
return
_session.Query<T>();
}
protected
virtual void
Dispose(bool disposing)
{
if
(disposing)
{
//Dispose Managed
Resources
_session.Close();
if
(!this.isDisposed)
isDisposed = true;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
Code:
Query to
class conversion:
public List<ProgramInterfaceWs_SamsPricingResponsePostns>
PricingManagerCursor(string countryCode, string userId, long
positionId)
{
var
elementList = _repository.FindAll<SamsPricingManager>()
.Where(e =>
e.PricingManagerUserId.ToUpper() == userId && e.CountryCode ==
countryCode &&
e.PricingManagerReportPositionId == positionId)
.Select(k => new
{
PricingReportPositionId
= k.PricingManagerReportPositionId,
PricingUserId =
k.PricingManagerUserId,
PricingFullName =
k.PricingManagerFullName,
PricingReportName =
k.PricingManagerReportPositionDescription,
srPricingReportPositionId = k.SeniorDirectorReportPositionId,
srPricingUserId =
k.SeniorDirectorUserId,
srPricingFullName =
k.SeniorDirectorFullName,
srPricingReportName =
k.SeniorDirectorReportPositionDescription
})
.ToList();
return
elementList.Select(e => new ProgramInterfaceWs_SamsPricingResponsePostns
{
pmgr_postn_id = (ulong)e.PricingReportPositionId,
pmgr_userid =
e.PricingUserId,
pmgr_full_name =
e.PricingFullName == null ? " " : e.PricingFullName,
pmgr_postn_name =
e.PricingReportName,
sdir_postn_id = (ulong)e.srPricingReportPositionId,
sdir_userid =
e.srPricingUserId,
sdir_full_name =
e.srPricingFullName == null ? " " : e.srPricingFullName,
sdir_postn_name =
e.srPricingReportName
}).ToList<ProgramInterfaceWs_SamsPricingResponsePostns>();
}
SP:
public static IList<UserVNumber> GetUserVNumber(this IRepository
repository, string UserId)
{
var
result = repository.ExecuteStoredProcedure<UserVNumber>("UserVNumberQuery", UserId);
return
result;
}
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.2"
assembly="Coree"
namespace=".Core.Domain.Model.Suggest">
<sql-query name="UserVNumberQuery">
<return alias="UserVNumber" class="UserVNumber"></return>
exec
appl_security.rlsys.reg_User_get_vnumbers :parameters
</sql-query>
</hibernate-mapping>
public void TestAllMappings()
{
//commonModel.DBName
= "VD";
IEnumerable<Type> types = Assembly.GetAssembly(typeof(CountryDepartmentMap)).GetTypes();
types = types.Where(e =>
e.Namespace == "Hierarchy_Services.Infrastructure.DataAccess.Mappings");
foreach
(Type t in
types)
{
object
o1 = Activator.CreateInstance(t);
}
}
Examples with repository an dNhibernate using storedprocedure
Have
used extension method: Contacts with no table and MyContacts with table.
public static IList<Contacts> GetMyContacts(this IRepository repository, string UserId)
{
var
contacts = repository.ExecuteStoredProcedure<Contacts>("ShowMyContactsQuery", UserId);
return
contacts;
}
public
static bool
DeleteMyContact(this IRepository
repository, int ContactId, string UserId)
{
try
{
if
(!string.IsNullOrEmpty(UserId) &&
ContactId > 0)
{
var contact = repository.Get<MyContacts>(x
=> x.UserId == UserId && x.ContactId == ContactId);
repository.Delete<MyContacts>(contact);
repository.SubmitChanges();
}
return
true;
}
catch
{
return
false;
}
}
public
static bool
SaveMyContacts(this IRepository
repository, int ContactId, string UserId, string
Email, string FName, string
LName, string phone, bool
isUpdate)
{
try
{
if
(isUpdate == false)
{
repository.ExecuteNonQuery("AddMyContactsQuery",
UserId, Email, FName, LName, phone);
}
else
{
if (ContactId > 0)
{
var contact = repository.FindAll<MyContacts>().Where(x => x.ContactId ==
ContactId && x.UserId == UserId).SingleOrDefault<MyContacts>();
if (contact != null)
{
contact.ContactFirst =
FName;
contact.ContactLast = LName;
contact.ContactPhone = phone;
contact.Email =
Email;
}
repository.Save<MyContacts>(contact);
repository.SubmitChanges();
}
}
return
true;
}
catch
{
return
false;
}
}
public
static Contacts
GetMyContact(this IRepository
repository, int ContactId, string UserId)
{
var
contact = repository.ExecuteStoredProcedure<Contacts>("ShowMyContactQuery", UserId,
ContactId).SingleOrDefault<Contacts>();
return
contact;
}
public class MyContacts: IEquatable<MyContacts>,
IEntity
{
public virtual string UserId
{ get; set; }
public virtual int ContactId
{ get; set; }
public virtual string Email
{ get; set; }
public virtual string
ContactFirst { get; set;
}
public virtual string
ContactLast { get; set;
}
public virtual string
ContactPhone { get; set;
}
public virtual MyContacts
MyContactsObject { get; set; }
public override bool Equals(object obj)
{
return
Equals(obj as MyContacts);
}
public virtual bool Equals(MyContacts gd)
{
if
(ReferenceEquals(null, gd)) return false;
if
(ReferenceEquals(this, gd)) return true;
return
Equals(ContactId, gd.ContactId) && Equals(UserId, gd.UserId);
}
public override int
GetHashCode()
{
unchecked
{
int
result = ContactId;
result = (result * 397) ^
(UserId == null ? 0 : UserId.GetHashCode());
return
result;
}
}
}
public class Contacts:IEntity
{
public virtual string UserId
{ get; set; }
public virtual int ContactId
{ get; set; }
public virtual string Email
{ get; set; }
public virtual string
ContactFirstName { get; set; }
public virtual string
ContactLastName { get; set;
}
public virtual string Phone
{ get; set; }
}
public class ContactsMap:ClassMap<Contacts>
{
public
ContactsMap()
{
Id(x => x.ContactId, "contactid");
Map(x => x.ContactFirstName, "contactfirst");
Map(x => x.UserId, "userid");
Map(x => x.ContactLastName, "contactlast");
Map(x => x.Phone, "contactphone");
Map(x => x.Email, "email");
}
}
public class MyContactsMap:
ClassMap<MyContacts>
{
public
MyContactsMap()
{
Table("TableName");
CompositeId()
.KeyProperty(x => x.UserId, "userid")
.KeyProperty(x =>
x.ContactId, "contactid");
Map(x => x.ContactFirst, "contactfirst");
Map(x => x.ContactLast, "contactlast");
Map(x => x.Email, "email");
Map(x => x.ContactPhone, "contactphone");
}
}
Query:
ShowMyContactsQuery:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.2"
assembly="Home.Core"
namespace=" Core.Domain.Model.Portlets">
<sql-query name="ShowMyContactsQuery">
<return alias="Contacts" class="Contacts"></return>
exec db.sp_name :parameters
</sql-query>
</hibernate-mapping>
<hibernate-mapping
xmlns="urn:nhibernate-mapping-2.2"
assembly="Home.Core"
namespace=".Home.Core">
<sql-query name="AddMyContactsQuery">
exec sp_name_Add :parameters
</sql-query>
</hibernate-mapping>
CREATE PROCEDURE sp_name_Add
@userid varchar(32),
@email varchar(255) = null,
@contactFirst varchar(64) = null,
@contactLast varchar(64)= null,
@contactPhone varchar(32)= null
as
if (select count(*) from my_contacts (nolock)
where userid = @userid
and email=@email
and
contactfirst = @contactFirst
and
contactlast = @contactLast
and
contactphone = @contactPhone) = 0
insert into my_contacts
(userid,email,contactfirst,contactlast,contactphone)
values(@userid,@email,@contactfirst,@contactlast,@contactphone)
Without
SP - Add:
AccountRequestSignature sig
= new AccountRequestSignature()
{
RequestID = requestID,
SigneeName = Name,
SigneePhone = HRPhone,
SigneeEmail = Email,
SigneeTitle = Title,
DateSigned = DateTime.Now,
SigneeCOEmail = COEmail
};
//Get the
request and set the status to ready for approval
var
accountRequest = _repository.Get<AccountRequest>(x
=> x.RequestID == requestID);
string
SetStatus;
if
(string.IsNullOrEmpty(COEmail))
{
SetStatus = "r";
}
else
{ SetStatus = "c"; }
accountRequest.Status = SetStatus;
_repository.Save(accountRequest);
_repository.Save(sig);
_repository.SubmitChanges(); //this
commits all the transaction.
Repository Defined:
Repostiory is of
Generic type because the registry can have different type for Informix/SQL and
DB2. So those type of repository needs to be created.
No comments:
Post a Comment