c# - How to bind an ObservableCollection to an sql table? -

i want bind observablecollection sql table, thought of adding event handlers collection , keeping track of event types handle sql delete , insert operations such as

someobservablecollection.collectionchanged += (s, e) =>                 {                     if (e.action == system.collections.specialized.notifycollectionchangedaction.add)                     {                         //generate sql insert command e.newitems using reflection                     }                     if (e.action == system.collections.specialized.notifycollectionchangedaction.remove)                     {                        //generate sql delete command e.olditems                     }                 }; 

and updates have add event handlers selected object in viewmodel

my question on right track , has been done before or have scratch?

here solution

first crated 2 simple attributes

sqlcolumn holds sql column name , if primarykey , column type

[attributeusage(attributetargets.property,inherited=false)]     public class sqlcolumn : attribute     {         public sqlcolumn(string columnname, type valuetype, bool primarykey)         {             _columnname = columnname;             _valuetype = valuetype;             _primarykey = primarykey;         }          private string _columnname;         private type _valuetype;         private bool _primarykey;          public string columnname         {                         {                 return this._columnname;             }             set             {                 _columnname = value;             }         }          public type valuetype         {                         {                 return this._valuetype;             }             set             {                 _valuetype = value;             }         }          public bool primarykey         {                         {                 return this._primarykey;             }             set             {                 _primarykey = value;             }         }      } 

sqltable holds sql table name

 [attributeusage(attributetargets.class,allowmultiple=false,inherited=false)]     public class sqltable : attribute     {          public sqltable(string tablename)         {             this._tablename = tablename;         }          protected string _tablename;         public string tablename         {                         {                 return this._tablename;             }         }         } 

and sql command generator use create relevant sql command in collection changed event handlers , property changed event handlers

 public static class sqlgenerator {     public static sqlcommand generateselectcommand<t>()     {         string tablename = gettablename<t>();         propertyinfo[] props = getpropertyinfos<t>();         //stringbuilder sbwhere = new stringbuilder(" ");         stringbuilder sbcolumns = new stringbuilder(" ");         var cmd = new sqlcommand();          foreach (var prop in props)         {             sbcolumns.append(prop.name);         }           cmd.commandtext =             "select " +             sbcolumns.tostring().trimend(',') +             " " + tablename;          return cmd;     }       public static sqlcommand generateinsertcommand<t>(object obj)     {         string tablename = gettablename<t>();         propertyinfo[] props = getpropertyinfos<t>();         var cmd = new sqlcommand();         stringbuilder sbcolumns = new stringbuilder(" ");         stringbuilder sbvalues = new stringbuilder(" ");          foreach(var prop in props)         {             var sqlcolumnattr = (sqlcolumn)prop.getcustomattribute(typeof(sqlcolumn), false);             var colvalue = prop.getvalue(obj);             if(!sqlcolumnattr.primarykey && colvalue!=null)             {                 sbcolumns.appendformat("{0},",sqlcolumnattr.columnname);                 sbvalues.appendformat("@{0},", sqlcolumnattr.columnname);                 var param = new sqlparameter("@" + sqlcolumnattr.columnname, colvalue);                 param.dbtype = (dbtype)enum.parse(typeof(dbtype), sqlcolumnattr.valuetype.name);                 cmd.parameters.add(param);             }          }          cmd.commandtext = "insert " + tablename +"("+ sbcolumns.tostring().trimend(',') +") values(" + sbvalues.tostring().trimend(',') + ");select scope_identity();";          return cmd;     }       public static sqlcommand generateupdatecommand<t>(object obj,ienumerable<string> propertynamesthatchanged)     {         string tablename = gettablename<t>();         propertyinfo[] props = getpropertyinfos<t>().where(pinfo=>!propertynamesthatchanged.contains(pinfo.name)).toarray();         var cmd = new sqlcommand();         stringbuilder sbvalues = new stringbuilder(" ");         stringbuilder sbwhere = new stringbuilder(" ");         foreach (var prop in props)         {             var sqlcolumnattr = (sqlcolumn)prop.getcustomattribute(typeof(sqlcolumn), false);             var colvalue = prop.getvalue(obj);             if (!sqlcolumnattr.primarykey && colvalue != null)             {                 sbvalues.appendformat("{0}=@{0},", sqlcolumnattr.columnname);                 var param = new sqlparameter("@" + sqlcolumnattr.columnname, colvalue);                 param.dbtype = (dbtype)enum.parse(typeof(dbtype), sqlcolumnattr.valuetype.name);                 cmd.parameters.add(param);             }             else if(sqlcolumnattr.primarykey)             {                 sbwhere.appendformat("{0}=@{0}", sqlcolumnattr.columnname);                 var param = new sqlparameter("@" + sqlcolumnattr.columnname, colvalue);                 param.dbtype = (dbtype)enum.parse(typeof(dbtype), sqlcolumnattr.valuetype.name);                 cmd.parameters.add(param);             }          }          cmd.commandtext = "update " + tablename +  sbvalues.tostring().trimend(',') + sbwhere.tostring();          return cmd;     }      public static sqlcommand generatedeletecommand<t>(object obj)     {         string tablename = gettablename<t>();         propertyinfo[] props = getpropertyinfos<t>();         stringbuilder sbwhere = new stringbuilder(" ");         var cmd = new sqlcommand();          foreach (var prop in props)         {             var sqlcolumnattr = (sqlcolumn)prop.getcustomattribute(typeof(sqlcolumn), false);             if(sqlcolumnattr.primarykey)             {                 var colvalue = prop.getvalue(obj);                 sbwhere.appendformat("{0}=@{0}", sqlcolumnattr.columnname);                 var param = new sqlparameter("@" + sqlcolumnattr.columnname, colvalue);                 param.dbtype = (dbtype)enum.parse(typeof(dbtype), sqlcolumnattr.valuetype.name);                 cmd.parameters.add(param);             }          }          cmd.commandtext = "delete " + tablename + sbwhere.tostring();          return cmd;     }         public static string gettablename<t>()     {         return typeof(t).getattributevalue((sqltable sqltable)=> sqltable.tablename);     }      public static propertyinfo[] getpropertyinfos<t>()     {         return typeof(t).getproperties().where(prop => attribute.isdefined(prop, typeof(sqlcolumn))).toarray();     }          public static tvalue getattributevalue<tattribute, tvalue>(     type type,     func<tattribute, tvalue> valueselector)     tattribute : attribute     {         var att = type.getcustomattributes(             typeof(tattribute), true         ).firstordefault() tattribute;         if (att != null)         {             return valueselector(att);         }         return default(tvalue);     }  }