c# - Retrieve/insert data using SQLiteDataAdapter -

i'm creating small application using windows forms , sqlite. after reading tutorials implemented method data retrieval:

public datatable getdatatable(ref sqlitedataadapter adapter, string sql)         {             datatable dt = new datatable();              // connect database.             using (sqliteconnection connection = new sqliteconnection(connectionstring))             // create database adapter using specified query             using (adapter = new sqlitedataadapter(sql, connection))             // create command builder generate sql update, insert , delete commands             using (sqlitecommandbuilder command = new sqlitecommandbuilder(adapter))             {                 // populate datatable return, using database adapter                                 adapter.fill(dt);             }             return dt;         } 

(as getdatatable doesn't take sqlitedataadapter parameter)

i have 3 classes, let's call them ui, link , database. ui nothing displaying data , raising events upon user interaction. link creates database , sqlitedataadapter, retrieves data table through method mentioned above, , binds data grid view on ui. user cannot alter table through data grid view, should through text boxes. (does make binding table dgv obosolete?)

what's best way user input text boxes database, using adapter? or should use datareader , insert method instead of adapter?

as of know, ui exposes controls through get-methods. there better solution?

private void initialize() {     // subscribe userinterface events     userinterface.datagridviewselectionchanged += new eventhandler(userinterface_datagridviewselectionchanged);     userinterface.newclicked += new eventhandler(userinterface_newclicked);     userinterface.saveclicked += new eventhandler(userinterface_saveclicked);      // datagridview userinterface , bind database     bindingsource = new bindingsource();     bindingsource.datasource = database.getdatatable(ref adapter, "select * sometable");     userinterface.getdatagridview().datasource = bindingsource; }    void userinterface_datagridviewselectionchanged(object sender, eventargs e) {     if (userinterface.getdatagridview().selectedrows.count != 0)     {         datagridviewrow row = userinterface.getdatagridview().selectedrows[0];         userinterface.getidtextbox().text = row.cells["primarykey].value.tostring();         userinterface.getotheridtextbox().text = row.cells["foreignkey"].value.tostring();          datatable dt = database.getdatatable("select * sometable foreignkey=" + row.cells["foreignkey"].value);         userinterface.getlastnametextbox().text = dt.rows[0]["lastname"].tostring();         userinterface.getfirstnametextbox().text = dt.rows[0]["firstname"].tostring();         userinterface.getcompanytextbox().text = dt.rows[0]["company"].tostring();     }             }  void userinterface_newclicked(object sender, eventargs e) {     // text boxes , clear them     // let ui take care of itself?                      }  void userinterface_saveclicked(object sender, eventargs e) {         // text/data text boxes , insert (or update if editing table) database         // adapter.update(...)? } 

cheers!

insert, update , delete operations working of dbcommand. need different method takes sql string , collection of sqliteparameter use insert.

i try write pseudocode insert operation

public class myhelperclass {     public static int insertcommand(string sql, sqliteparameter[] parameters)     {         int result = 0;         using (sqliteconnection connection = new sqliteconnection(connectionstring))         using (sqlitecommand cmd = new sqlitecommand(sql, connection))         {             cmd.parameters.addrange(parameters);             result = cmd.executenonquery();         }           return result;     } } 

now have build parameter array pass method , should done ui code

string sqlcommand = "insert table1 (firstname, lastname) values (@fname, @lname)"; sqliteparameter[] p = new sqliteparameter[2]; p[0] = new sqliteparameter("@fname", textbox1.text); p[1] = new sqliteparameter("@lname", textbox2.text); int rowadded = myhelperclass,insertcommand(sql, p); 

the operation update , delete command similar. suggest add version of getdatatable accepts parameter array instead of building sql commands string concatenation. repetead innumerable times here string concatenation leads errors and, worst of all, weak code exposed sql injection.