I want to use a flexible funtion to paste a class (List of class) into and MS Access database using OLEDB and reflection. I have this function which is working all right:
public static void appenddatatotableRef<T>(string connectionstring, string tablename, IEnumerable<T> values)
{
var properties = typeof(T).GetFields(BindingFlags.Public | BindingFlags.Instance).OrderBy(x => x.Name).ToList();
//setting up the command text for insert into
string columnam = "[" + properties[0].Name + "]";
string valstring = "@" + properties[0].Name;
for (int i = 1; i < properties.Count; i++)
{
columnam = columnam + ", [" + properties[i].Name + "]";
valstring = valstring + ", @" + properties[i].Name;
}
string commandtext = "INSERT INTO [" + tablename + "](" + columnam + ")" + "VALUES(" + valstring + ")";
using (var myconn = new OleDbConnection(connectionstring))
{
myconn.Open();
using (var cmd = new OleDbCommand())
{
foreach (var item in values)
{
cmd.CommandText = commandtext;
cmd.Parameters.Clear();
for (int i = 0; i < properties.Count; i++)
{
cmd.Parameters.AddWithValue("@" + properties[i].Name, properties[i].GetValue(item) ?? "");
}
cmd.Connection = myconn;
cmd.ExecuteNonQuery();
}
}
myconn.Close();
}
}
Now the problem is that sometimes the tables in the MS Access DB might not have a column for each class member or the names might have a misspelling. Instead of this failing, I would like to insert the data that matches so I changed the function to this:
public static void newappenddatatotableRef<T>(string connectionstring, string tablename, IEnumerable<T> values)
{
using (var myconn = new OleDbConnection(connectionstring))
{
myconn.Open();
var tableheaders = new List<string>();
var schemaTable = myconn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tablename, null });
for (int i = 0; i < schemaTable.Rows.Count; i++)
tableheaders.Add(schemaTable.Rows[i].ItemArray[3].ToString());
var properties = typeof(T).GetFields(BindingFlags.Public | BindingFlags.Instance).Where(x => tableheaders.Contains(x.Name)).OrderBy(x => x.Name).ToList();
string columnam = "[" + properties[0].Name + "]";
string valstring = "@" + properties[0].Name;
for (int i = 1; i < properties.Count; i++)
{
columnam = columnam + ", [" + properties[i].Name + "]";
valstring = valstring + ", @" + properties[i].Name;
}
string commandtext = "INSERT INTO [" + tablename + "](" + columnam + ")" + "VALUES(" + valstring + ")";
using (var cmd = new OleDbCommand())
{
foreach (var item in values)
{
cmd.CommandText = commandtext;
cmd.Parameters.Clear();
for (int i = 0; i < properties.Count; i++)
{
cmd.Parameters.AddWithValue("@" + properties[i].Name, properties[i].GetValue(item) ?? "");
}
cmd.Connection = myconn;
cmd.ExecuteNonQuery();
}
}
myconn.Close();
}
}
This does not throw and error but also doesnt insert the data. It matches all the columns correctly and steps all the way through but somehow doesnt work in the end. Any pointers would be great.
Aucun commentaire:
Enregistrer un commentaire