1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126
|
public class Root
{
private string _connectionString;
public Root()
{
string bdd = ConfigurationManager.AppSettings["BDD"];
_connectionString = ConfigurationManager.ConnectionStrings[bdd].ConnectionString;
}
private string buildClause(KeyValuePair<string, Object> kvp)
{
PropertyInfo prop = this.GetType().GetProperty(kvp.Key);
string clause = "";
string typeName = this.GetType().GetProperty(kvp.Key).PropertyType.Name;
switch (typeName)
{
case "String":
clause = kvp.Key + " = '" + Convert.ToString(kvp.Value) + "'";
break;
case "Boolean":
clause = kvp.Key + " = " + (Convert.ToBoolean(kvp.Value) ? "true" : "false");
break;
case "DateTime":
clause = kvp.Key + " = '" + Convert.ToDateTime(kvp.Value).ToString("yyyy-MM-dd") + "'";
break;
default:
throw new UnknownTypeException("Unknown type : " + typeName);
}
return clause;
}
private object getValue(MySqlDataReader reader, Type type, int col)
{
switch (type.Name)
{
case "String":
return reader.GetString(col);
case "DateTime":
return reader.GetDateTime(col);
case "Boolean":
return reader.GetBoolean(col);
default:
throw new UnknownTypeException("Unknown type : " + type.Name);
}
}
public void loadFromDB(string tableName, Dictionary<string, Object> keys)
{
// Build SQL request from parameters
string sql = "select * from " + tableName;
bool first = true;
foreach (KeyValuePair<string, Object> kvp in keys)
{
sql += (first ? " where " : " and ") + buildClause(kvp);
first = false;
}
// Execute sql request
MySqlConnection myConnection = new MySqlConnection(_connectionString);
MySqlCommand myCommand = myCommand = new MySqlCommand(sql, myConnection);
myConnection.Open();
MySqlDataReader reader = myCommand.ExecuteReader();
// Parse result
if (reader.HasRows)
{
// Here we know there is at least one row
// First get the objects property
PropertyInfo[] props = this.GetType().GetProperties();
// The dictionnary will store the corresponding column number in the reader result
Dictionary<PropertyInfo, int> columns = new Dictionary<PropertyInfo, int>();
// Parse the properties to get the corresponding column number
foreach (PropertyInfo prop in props)
{
try
{
// Store the pair in the dictionnary
columns.Add(prop, reader.GetOrdinal(prop.Name));
}
catch (IndexOutOfRangeException e)
{
// current property does exist as column is this table
// ignore this error
}
}
// For each result row
while (reader.Read())
{
// Parse the properties to fetch them from reader data
foreach (KeyValuePair<PropertyInfo, int> column in columns)
{
// get the property type
Type type = column.Key.PropertyType;
// Check if it is a List
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List<>))
{
// Get the list item type
Type itemType = type.GetGenericArguments()[0];
// Get the list to add the value
var col = column.Key.GetValue(this, null) as IList;
if (col != null) // --> col is always null
col.Add(getValue(reader, itemType, column.Value));
else
throw new InvalidOperationException("Not a list");
}
else
{
// If not just fetch the property or the corresponding field
if (column.Key.CanWrite)
column.Key.SetValue(this, getValue(reader, type, column.Value), null);
else
this.GetType().GetField("_" + column.Key.Name, BindingFlags.NonPublic | BindingFlags.Instance).SetValue(this, getValue(reader, type, column.Value));
}
}
}
}
}
}
} |
Partager