Here's my situation, I have to create a common function for inserting values into a MySQL db. So far I have the approach below. This code works fine when there is no composite class within another class. If we have navigational properties in the entities then this code breaks, i'm stuck here.
( ORM - we can't use it, since its clients requirement)
public string create(object entity){
var sql = new OdbcCommand();
var d = new StringDictionary();
var dd = new StringDictionary();
try
{
var objectType = entity.GetType();
var properties = objectType.GetProperties();
var tableName = objectType.GetCustomAttributes(false).Select(x => (x as TableAttribute).Name).FirstOrDefault();
foreach (var prop in properties)
{
if (prop.DeclaringType != typeof (BasicRequest)) // BasicRequest is base class
{
if (
prop.GetCustomAttributes(false).Where(y=>y is ColumnAttribute).Select(x => (x as ColumnAttribute)).FirstOrDefault().IsPrimaryKey)
{
continue;
}
if (prop.PropertyType.IsGenericType) // here comes the problem
{
// the problem is if a class has List of its child
objects then how to insert them since here parent
is not yet inserted.
}
if (prop.GetCustomAttributes(false).Where(y => y is ColumnAttribute).Select(x => (x as ColumnAttribute)).FirstOrDefault().IsParent)
{
// same problem here also but this time this object is
parent object of the entity
/*parentObject = prop.GetValue(entity, null);
CreateNew(parentObject);
*/
continue;
}
d[prop.GetCustomAttributes(false).Where(y => y is ColumnAttribute).Select(x => (x as ColumnAttribute).Name).FirstOrDefault()] = DBUtil.FixSQLString(Convert.ToString(prop.GetValue(entity, null)));
}
else
{
continue;
}
}
Connection coxn = ConnectionPool.GetInstance().GetCoxn(Constants.MyName,ConnectionPool.WriteServer,"db_name");
sql.Connection = coxn.odbcConnection;
sql.CommandType = CommandType.Text;
sql.CommandText = DBUtil.CreateInsert(tableName, d);
int affected = sql.ExecuteNonQuery();
if (affected != 1)
{
ErrorMessage = "Insert Failed Unexpectedly";
return "0";
}
return "1";
}
catch (Exception ex)
{
ErrorMessage = ex.Message;
return "0";
}
}
Lets take an entity (this kinda design is not in my hand, this classes are already present I cannot change this entities)
[Table(Name="person")]
public class Person
{
[Column(Name="id",IsPrimaryKey=true)] // Column is my custom attribute
public int Id{get;set;}
[Column(Name="name",IsPrimaryKey=true)]
public string Name{get;set;}
// this is where i'm not able get it how do i insert
// child class with the function i wrote above
[Column(IsChild=true)]
public List<Contact> contact{get;set;} // Navigation property
//Same problem with this property also
// how do i do insert for parent
[Column(IsParent=true)]
public User user{get;set;}
}
// i'll call that insert method as
Common c = new Common();
c.Create(Person p); // p will be passed as argument from controller
