I am looking to create a database row-to-object mapper for a production server.
My goal is to fill at a single location what are the database column name, and into which of this object's field to store a single row returned from a database query.
I am planning to later add functionalities to automatically fetch table name and connection string. The purpose of doing this is to centralize all database-related stuff (naming of columns and query string follows automatically, add protections on returned results, error logging, etc.)
I read a lot about generic set accessors thought I am currently unable to synthesize a solution from that.
Here is a (non-working) skeleton of what I am trying to achieve. All comments are welcomed, about optimizations and better practices, too.
Utilisation :
/* get MediaInfo filled regarding CDbColumnFind criterias */
CEventReportingMediaInfo MediaInfo = DbQuery(1, new CDbColumnFind() { Name = "uid", Value = "2" });
Here is the Db-Mappable base class
public class DbMappableClass
{
/********************************************/
/* required fields for database information */
/********************************************/
public string TableName;
public IList TableColumns;
/********************************************/
/* generic object filling helper */
/********************************************/
public void ReadObject(DataRow FromRow)
{
foreach (CDbColumn Column in TableColumns)
{
TryParseGeneric(FromRow[Column.Name].ToString(), Column.Field);
}
}
/********************************************/
/* conversion helper */
/********************************************/
public static bool TryParseGeneric(string src, T value)
{
TypeConverter converter = TypeDescriptor.GetConverter(typeof(T));
try
{
value = (T)converter.ConvertFromString(src);
return true;
}
catch
{
value = default(T);
CSysLog.WriteEntry(SysLogLevel.WARNING, "failed to convert src ({0}) to type ({1}), use default value ({2})", src.ToString(), typeof(T), value);
return false;
}
}
/********************************************/
/* Db column type */
/********************************************/
public class CDbColumn
{
private readonly string name;
private readonly SqlDbType sqltype;
private readonly Type ctype;
public CDbColumn(string Name, SqlDbType SqlType, object Field)
{
this.name = Name;
this.sqltype = SqlType;
this.Field = Field;
this.ctype = Field.GetType();
}
public string Name { get { return name; } }
public SqlDbType SqlType { get { return sqltype; } }
public object Field { get; set; }
public Type CType { get { return ctype; } }
}
/********************************************/
/* Db Query type criterias */
/********************************************/
public class CDbColumnFind
{
public string Name;
public object Value;
}
/********************************************/
/* Generic Db Query */
/********************************************/
public static TReturnType DbQuery(int ExpectedCount, params CDbColumnFind[] Args) where TReturnType : DbMappableClass, new()
{
/* query database, obtain results in RowResults ... */
TReturnType ReturnResult = new TReturnType();
DataRow RowResults = TableResult.Rows[0];
ReturnResult.ReadObject(RowResults);
}
}
Now is the custom, centralized declaration of a db-mappable derived object
/********************************************/
/* Event Reporting Media Table : Database */
/********************************************/
public class CEventReportingMediaInfo : DbMappableClass
{
/**********************************/
/** Member Declaration ***/
/**********************************/
public int Uid { get; set; }
public MediaType Type { get; set; }
public string IpAddr { get; set; }
public Int32 Port { get; set; }
public MediaStatus Status { get; set; }
public int Delay { get; set; }
/**********************************/
/** Database Informations ***/
/**********************************/
public CEventReportingMediaInfo()
{
TableName = "event_reporting_media_tbl";
TableColumns = new ReadOnlyCollection (new[] {
new CDbColumn ("uid" , SqlDbType.Int, Uid),
new CDbColumn ("media_type" , SqlDbType.TinyInt, Type),
new CDbColumn ("media_ip" , SqlDbType.VarChar, IpAddr),
new CDbColumn ("media_port" , SqlDbType.Int, Port),
new CDbColumn ("media_status" , SqlDbType.TinyInt, Status),
new CDbColumn ("media_delay" , SqlDbType.Int, Delay)
});
}
public enum MediaType
{
INVALID = 0,
SIP_GATEWAY = 1,
MODEM = 2
}
public enum MediaStatus
{
OFFLINE = 0,
ONLINE = 1
}
}
How can I achieve to have specified fields (Uid, Type, IpAddr, etc) generically filled by ReadObject()? I need an equivalent to a pointer to members... I read about reflection too but I am not sure about efficiency and do not understand 100% how to implement it.
Note: I would prefer to not use full ORM solutions - my understanding is that it is not a stragegic long-term solution (less flexible for future implementations, support, etc - easier but not really enterprise-grade). I would like to keep control of the db mapping layer while reducing glitches occurence in future development on the production servers.
What do you guys think about this implementation, and what would be the best way to achieve my goals?
Thanks for your time, and with my best regards,
Morpheus
