public bool ModifyPerson(Person mod)
{
SqlConnection con = new SqlConnection(DBHelper.ConnectionString);
try
{
//mod.ID = GetNextEntityId(mod.SiteCode, mod.EntityTypeCode);
StringBuilder sb = new StringBuilder();
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required))
{
EntityDE ety = mod.ToEntity();
if (ModifyEntity(ety))
{
SqlCommand cmd = new SqlCommand();
#region CommandText & Params
sb.Append("UPDATE [dbo].[Person] SET ");
sb.Append(" [ParentId] = @ParentId");
sb.Append(",[ParentRelationId] = @ParentRelationId");
sb.Append(",[MeritalStatusId] = @MeritalStatusId");
sb.Append(",[DOB] = @DOB");
sb.Append(",[Age] = @Age");
sb.Append(",[Picture] = @Picture");
sb.Append(",[Gender] = @Gender");
sb.Append(",[Signature] = @Signature");
sb.Append(",[ThumbImpression] = @ThumbImpression");
sb.Append(",[Phone] = @Phone");
sb.Append(",[NationalId] = @NationalId");
sb.Append(" WHERE ");
sb.Append(" [SiteCode] = @SiteCode");
sb.Append(" AND [PersonId] = @PersonId");
sb.Append(" AND [EntityTypeCode] = @EntityTypeCode");
cmd.Parameters.AddWithValue("@SiteCode", mod.SiteCode);
cmd.Parameters.AddWithValue("@EntityTypeCode", mod.EntityTypeCode);
cmd.Parameters.AddWithValue("@PersonId", mod.ID);
cmd.Parameters.AddWithValue("@ParentId", mod.ParentId);
cmd.Parameters.AddWithValue("@ParentRelationId", mod.ParentRelationId);
cmd.Parameters.AddWithValue("@MeritalStatusID", mod.MeritalStatusId);
cmd.Parameters.AddWithValue("@DOB", mod.DOB);
cmd.Parameters.AddWithValue("@Age", mod.Age);
cmd.Parameters.AddWithValue("@Picture", mod.Picture);
cmd.Parameters.AddWithValue("@Gender", mod.Gender);
cmd.Parameters.AddWithValue("@Signature", mod.Signature);
cmd.Parameters.AddWithValue("@ThumbImpression", mod.ThumbImpression);
cmd.Parameters.AddWithValue("@NationalId", mod.NationalId);
cmd.Parameters.AddWithValue("@Phone", mod.Phone);
#endregion
cmd.Connection = con;
cmd.CommandText = sb.ToString();
con.Open();
cmd.ExecuteNonQuery();
trans.Complete();
}
}
}
catch (Exception ex)
{
return false;
}
finally
{
con.Close();
}
return true;
}
public int AddPerson(Person mod)
{
//mod.ID = GetNextEntityId(mod.SiteCode, mod.EntityTypeCode);
SqlConnection con = new SqlConnection(DBHelper.ConnectionString);
StringBuilder sb = new StringBuilder();
try
{
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required))
{
mod.ID = AddEntity(mod.ToEntity());
if (mod.ID != -1)
{
#region Add Person
SqlCommand cmd = new SqlCommand();
#region CommandText & Params
sb = new StringBuilder();
sb.Append("INSERT INTO [dbo].[Person]");
sb.Append("([SiteCode]");
sb.Append(",[EntityTypeCode]");
sb.Append(",[PersonId]");
sb.Append(",[ParentId]");
sb.Append(",[ParentRelationId]");
sb.Append(",[MeritalStatusId]");
sb.Append(",[DOB]");
sb.Append(",[Age]");
sb.Append(",[Picture]");
sb.Append(",[Gender]");
sb.Append(",[Signature]");
sb.Append(",[ThumbImpression]");
sb.Append(",[Phone]");
sb.Append(",[NationalId])");
sb.Append("VALUES");
sb.Append("(@SiteCode");
sb.Append(",@EntityTypeCode");
sb.Append(",@PersonId");
sb.Append(",@ParentId");
sb.Append(",@ParentRelationId");
sb.Append(",@MeritalStatusId");
sb.Append(",@DOB");
sb.Append(",@Age");
sb.Append(",@Picture");
sb.Append(",@Gender");
sb.Append(",@Signature");
sb.Append(",@ThumbImpression");
sb.Append(",@Phone");
sb.Append(",@NationalId)");
cmd.Parameters.AddWithValue("@SiteCode", mod.SiteCode);
cmd.Parameters.AddWithValue("@EntityTypeCode", mod.EntityTypeCode);
cmd.Parameters.AddWithValue("@PersonId", mod.ID);
cmd.Parameters.AddWithValue("@ParentId", mod.ParentId);
cmd.Parameters.AddWithValue("@ParentRelationId", mod.ParentRelationId);
cmd.Parameters.AddWithValue("@MeritalStatusID", mod.MeritalStatusId);
cmd.Parameters.AddWithValue("@DOB", mod.DOB);
cmd.Parameters.AddWithValue("@Age", mod.Age);
cmd.Parameters.AddWithValue("@Picture", mod.Picture);
cmd.Parameters.AddWithValue("@Gender", mod.Gender);
cmd.Parameters.AddWithValue("@Signature", mod.Signature);
cmd.Parameters.AddWithValue("@ThumbImpression", mod.ThumbImpression);
cmd.Parameters.AddWithValue("@Phone", mod.Phone);
cmd.Parameters.AddWithValue("@NationalId", mod.NationalId);
#endregion
con.Open();
cmd.CommandText = sb.ToString();
cmd.Connection = con;
cmd.ExecuteNonQuery();
#endregion
trans.Complete();
}
}
}
catch (Exception ex)
{
mod.ID = -1;
}
finally
{
con.Close();
}
return mod.ID;
}
public Person GetPersonById(string siteCode, string entityTypeCode, int id)
{
Person mod = new Person();
SqlConnection con = new SqlConnection(DBHelper.ConnectionString);
try
{
//using (TransactionScope trans = new TransactionScope())
{
EntityDE ety = GetEntityById(siteCode, entityTypeCode, id);
mod = ety.ToPerson();
if (mod != null)
{
StringBuilder sb = new StringBuilder();
SqlCommand cmd = new SqlCommand();
sb.Append("SELECT * FROM [dbo].[Person]");
sb.Append(" WHERE [SiteCode] = @SiteCode");
sb.Append(" AND [EntityTypeCode]=@EntityTypeCode");
sb.Append(" AND [PersonId]=@PersonId");
cmd.Parameters.AddWithValue("@SiteCode", siteCode);
cmd.Parameters.AddWithValue("@EntityTypeCode", entityTypeCode);
cmd.Parameters.AddWithValue("@PersonId", id);
cmd.Connection = con;
cmd.CommandText = sb.ToString();
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
while (rdr.Read())
{
mod.SiteCode = siteCode;
mod.EntityTypeCode = entityTypeCode;
mod.ID = id;
mod.ParentId = rdr["ParentId"] != DBNull.Value ? (int)rdr["ParentId"] : 0;
mod.ParentRelationId = rdr["ParentRelationId"] != DBNull.Value ? (int)rdr["ParentRelationId"] : 0;
mod.MeritalStatusId = rdr["MeritalStatusId"] != DBNull.Value ? (int)rdr["MeritalStatusId"] : 0;
mod.DOB = rdr["DOB"] != DBNull.Value ? (DateTime)rdr["DOB"] : new DateTime(1900, 1, 1);
mod.Age = rdr["Age"] != DBNull.Value ? (int)rdr["Age"] : 0;
mod.Picture = rdr["Picture"] != DBNull.Value ? (string)rdr["Picture"] : string.Empty;
mod.Gender = rdr["Gender"] != DBNull.Value ? (string)rdr["Gender"] : string.Empty;
mod.Signature = rdr["Signature"] != DBNull.Value ? (string)rdr["Signature"] : string.Empty;
mod.ThumbImpression = rdr["ThumbImpression"] != DBNull.Value ? (string)rdr["ThumbImpression"] : string.Empty;
mod.Phone = rdr["Phone"] != DBNull.Value ? (string)rdr["Phone"] : string.Empty;
mod.NationalId = rdr["NationalId"] != DBNull.Value ? (string)rdr["NationalId"] : string.Empty;
}
}
else
mod = null;
}
//trans.Complete();
}
}
catch (Exception ex)
{
mod = null;
}
finally
{
con.Close();
}
return mod;
}
public void Person_CRUD()
{
_bdmSvc = new BDMService();
Person p = new Person();
//p.SiteCode = "QST";
//p.EntityName = "Imran";
//p.EntityTypeCode = "DNR";
//var mod = _bdmSvc.AddPerson(p);
var list = _bdmSvc.GetViewOfAllPersons("QST");
if (list != null && list.Count > 0)
{
PersonView pv = list[0];
//p = pv.ToPerson();
//p.EntityName = "Imran ...";
//p.Signature = "imran@123";
//if (_bdmSvc.ModifyPerson(p))
//{
//}
p = _bdmSvc.GetPersonById(pv.SiteCode, pv.EntityTypeCode, pv.ID);
}
}