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));
}
}
}
}
}
}
} |
Ajouter des éléments à une liste par reflection
Répondre avec citation
Partager