inserting with linq

Question

hi

i am trying to insert a load of data into a table with linq the data arrives in a nameValueCollection with the key as the column name and the value as the value to be inserted

i need to convert all the values to their correct datatype but cant think of a good way to do this and then insert i can iterate over the columns in the LINQ'ed table

        TransactionDataContext db = new TransactionDataContext();

        var columns = db.Mapping.MappingSource
                .GetModel(typeof(TransactionDataContext))
                .GetMetaType(typeof(Transaction))
                .DataMembers;

        Type t;
        string typeName, colName;

        Transaction trans = new Transaction();

         for(int i = 0;i<columns.Count();i++)
        {
            if(columns[i].Name.In(nvcRequest.Keys)){
                colName = columnNames[i].Name;
                t =  columnNames[i].Type;
                typeName = t.Name.ToString().ToLower();

               switch(typeName){
               case "int":
            //convert value to int and add it into the new transaction
            //but i cant do t[columns[i]] = newly typed value unfortunately..  - what can i do?
            break;
            case "datetime":
            //convert to datetime and add into the appropriate field in the new transaction
            break;
            }
        }

etc.. ... ..

db.SubmitChanges();

the In function is :

    public static bool In(this object o, IEnumerable c){

        foreach(object i in c){
            if(i.Equals(o))
                return true;
        }
        return false;
    }

any ideas? maybe i should just build up a string query myself? i hope not :(

any help much appreciated

nat

Answer 1

Take a look at Convert.ChangeType

Edit:

Based on your comment below, if you have a Transaction object, and a Dictionary<string, object> with the values and you want to set the properties of the object with the values from the dictionary, you could do

foreach (PropertyInfo myPropertyInfo in myTransactionObject.GetType().GetProperties())
{
    myPropertyInfo.SetValue(myTransactionObject,
        Convert.ChangeType(aPropertyValue, myPropertyInfo.PropertyType), 
        null);
}

Answer 2

hi vedran

i would still have to do trans.float_column = convert.changetype(value, typeof(float));

for each of the 45 fields

would really like to be able to loop around the NVC, as there isnt always data in the NVC for every column (the ones that arent always there are nullable in the DB) to avoid having write lines to check if there is a value, convert it and then finish with the trans.colname = converted value 45 times :(

hence the need to do trans[string columnname] = convert.changetype(...)

can i do that