c# - Build UpdateCommand for DataAdapter with some parsing applied to the SourceColumn? -

at first, i'll show standard way build custom updatecommand dataadapter (used commonly when select has many tables involved , can't use commandbuilder build commands automatically dataadapter).

here code dataadapter:

sqldataadapter myadapter = new sqldataadapter("select id, name mytable", myconnection); 

here code building standard updatecommand myadapter. example above involves 1 single table, however, that's simple , demonstrative purpose.

myadapter.updatecommand = new sqlcommand("update mytable set id=@id,name=@name id=@id2", myconnection); myadapter.updatecommand.parameters.add("id", sqldbtype.varchar).sourcecolumn = "id"; myadapter.updatecommand.parameters.add("name", sqldbtype.varchar).sourcecolumn = "name"; myadapter.updatecommand.parameters.add("id2", sqldbtype.varchar, 20, "id").sourceversion = datarowversion.original; 

the problem don't want bind the parameter sourcecolumn exactly. want change value sourcecolumn little before values passed corresponding parameter. example, id column contains values 1,2,3,4,... append string (e.g: 000, 001, a, b, c, ...) before , pass custom sourcecolumn parameter. code above, passed values sourcecolumn id has: 1,2,3,4... , want example: c1,c2,c3,c4,...

that's kind of parsing value using middle layer, know if possible. parsing value of sourcecolumn outside ok there case have parse right when building updatecommand adapter. here case:

i have datagridview has column of gender, column of type datagridviewcomboboxcolumn 2 items: male , female. underlying datasource should have corresponding gender column , values should "male" , "female", unmatched values (not contained in items list of comboboxcolumn) won't make comboboxcell display value correctly. i've tried cellformating , cellpainting didn't work. cellpainting complex , cellformating didn't work @ all. in fact, i'm not using standard .net datagridview, i'm using custom datagridview (also inherits standard datagridview). i've done datagridview didn't work. solution prepare underlying values include "male" , "female". designed gender column boolean/bit column. here select command:

sqldataadapter myadapter = new sqldataadapter("select id, name, case when gender = 1 'male' else 'female' end gender mytable", myconnection); 

the gender column type of varchar or text , not boolean/bit did before. gender column in original table type of bit. how can build updatecommand select command above? solution include hidden gender column , use column sourcecolumn build updatecommand, visible gender displaying. little more work do. if kind of custom sourcecolumn, better use.

your highly appreciated!

i don't know if helps, think heading in wrong direction.

let datagridview display how see , edit data. regarding 'gender' column, should able setup combobox column follows:

consider following scenario: data table has column 'gender' holds 0 (unknown), 1(male), 2(female), 3(other).

here sample code define lookup, creates sample data , populates grid data. gender displayed in english stored number.

    class employee     {         public string name { get; set; }         public int gender { get; set; }     }     class genderlookup     {         public string display { get; set; }         public int value { get; set; }     }      private void setupgrid()     {      genderlookup[] _lookups = new[]         {             new genderlookup { value = 0, display = "unknown" },              new genderlookup { value = 1, display = "male" },             new genderlookup { value = 2, display = "female" },              new genderlookup { value = 3, display = "other" }         };      employee[] _employees = new[]         {             new employee() { gender = 1, name = "james t. kirk" },              new employee() { gender = 2, name = "lt. uhura" },             new employee() { gender = 3, name = "data" }         };         gridview.autogeneratecolumns = false;         gridview.datasource = _employees;         gridview.columns.add(             new datagridviewtextboxcolumn()                  {                      datapropertyname = "name"                  }             );          gridview.columns.add(             new datagridviewcomboboxcolumn()                 {                     datasource = _lookups,                     displaymember = "display",                     valuemember = "value",                     datapropertyname = "gender"                 }             );     } 

so there no need 'parse' (or better: translate) gender display name manually. datagridview creates 'view' of underlying data, might hard read (as gender information in case).