i need insert values several tables first have retrieve university id table college , insert faculty name table faculty , generated sql server id. after of have insert both ids other table.
problem have close readers , after can't retrieve ids them variable should saved null. here code. how correctly?
sorry new c# , sql server.
// reading data combobox try { sqldatareader myreader = null; sqlcommand mycommand = new sqlcommand("select * colege", myconnection); myreader = mycommand.executereader(); while (myreader.read()) { combobox1.items.add(myreader["name"].tostring()); // console.writeline(myreader["column2"].tostring()); } } catch (exception ex) { console.writeline(ex.tostring()); } myconnection.close(); private void button1_click(object sender, eventargs e) { string item = combobox1.text.tostring(); // messagebox.show(item); sqlconnection myconnection = new sqlconnection("user id=bogdan_db;" + "password=1234;server=localhost;" + "trusted_connection=yes;" + "database=cafedrascience; " + "connection timeout=30"); try { myconnection.open(); } catch (exception e) { console.writeline(e.tostring()); } // reading data combobox string colegeid = null; try { sqldatareader myreader = null; sqlcommand mycommand = new sqlcommand("select * colege name like'" + item + "'", myconnection); myreader = mycommand.executereader(); while (myreader.read()) { colegeid = myreader["id"].tostring(); // console.writeline(myreader["column2"].tostring()); } myreader.close(); } catch (exception ex) { messagebox.show(ex.tostring()); } string facultyid = null; try { sqldatareader myreader1 = null; sqlcommand mycommand = new sqlcommand("select * depart name like'" + textbox1.text + "'", myconnection); myreader1 = mycommand.executereader(); while (myreader1.read()) { facultyid = myreader1["id"].tostring(); } myreader1.close(); } catch (exception ex) { messagebox.show(ex.tostring()); } sqlcommand mycommand1 = new sqlcommand("insert coledge_faculty (coledge_id, faculty_id) " + "values ('"+colegeid+"''"+facultyid+"')", myconnection); mycommand1.executenonquery(); // messagebox.show(colegeid); // messagebox.show(facultyid); myconnection.close(); }
the number 1 thing can stress code should using parameterised queries, beyond obvious risks of sql injection, protects against malformed sql, data truncation through conversion, , allows use cached execution plans.
the next thing point out should not using select * in production code, e.g.
sqlcommand mycommand = new sqlcommand("select * colege name like'" + item + "'", myconnection); myreader = mycommand.executereader(); while (myreader.read()) { colegeid = myreader["id"].tostring(); // console.writeline(myreader["column2"].tostring()); } why bother retrieving columns of colege database, sending them on network if care column id?
finally diagnosis of problem not correct:
problem have close readers , after it, can't retreave ids them, variable should saved null
if assign string variable colegeid value have retieved data reader, not null after have closed reader, retain value assigned. reason variable null because reader returns no rows never assign value.
now, rant over, answer question. massively on complicating issue, not need retrieve values application tier insert them table, can in single query in database:
insert coledge_faculty (coledge_id, faculty_id) select c.id, d.id depart d cross join colege c d.name = @depart , c.name = @colege; then case of calling sql c#:
string item = combobox1.text.tostring(); string connectionstring = "user id=bogdan_db; password=1234;server=localhost; trusted_connection=yes; database=cafedrascience; connection timeout=30"; string sql = @"insert coledge_faculty (coledge_id, faculty_id) select c.id, d.id depart d cross join colege c d.name = @depart , c.name = @colege;"; using (var connection = new sqlconnection(connectionstring)) using (var command = new sqlcommand(sql, connection)) { command.parameters.add("@colege", sqldbtype.varchar, 50).value = item; command.parameters.add("@depart", sqldbtype.varchar, 50).value = textbox1.text; connection.open(); command.executenonquery(); } it idea use using blocks objects implement idisposable, ensure resources freed when done them (don't confuse not being able reuse connection, .net has connection pooling in background reuse connections you, shouldn't keep sqlconnection object open available in case need use again).
on unrelated note, think liberal try/catch blocks, or @ least not dealing exception properly, using 1 example:
try { myconnection.open(); } catch (exception e) { console.writeline(e.tostring()); } if myconnection.open() throw error, still carry on method. carry on until here:
sqlcommand mycommand = new sqlcommand("select * colege name like'" + item + "'", myconnection); myreader = mycommand.executereader(); where exception, along lines of command requiring open , available sql connection, go exception.
catch (exception ex) { messagebox.show(ex.tostring()); } again don't exit method, carry on, , same error later when try , use connection again. again method carries on , use closed connection third time try , insert 2 variables never assigned because exceptions thrown database. fine, use try catch blocks, meaningful exception , exit method.
Comments
Post a Comment