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); } } 
Comments
Post a Comment