i'm creating game in unity, allows users enter sql commands.
lets have table called terminals in sql database columns id, name, type , hacked.
if user enters select command, e.g select id, name terminals;
linelist gets added number of data fields returned, e.g. id , name only.
my question is, how assign properties in terminals class correct values returned sql query?
here's code:
public class sqlconnect : monobehaviour { private void query(string sqlcommand) { using (dbcon = new sqlconnection(connectionstring)) { using (dbcmd = dbcon.createcommand()) { dbcmd.commandtext = sqlcommand; dbcon.open(); using (reader = dbcmd.executereader()) { var readlist = new list<list<object>>(); while (reader.read()) { var linelist = new list<object>(); (int = 0; < reader.fieldcount; i++) { linelist.add(reader.getvalue(i)); // reads entries in row } readlist.add(linelist); } } } } } } public static class sqldynamicdata { public static list<terminals> terminallist; public class terminals { public int id { get; set; } public string name { get; set; } public string type { get; set; } public bool hacked { get; set; } public terminals(int id, string name, string type, bool hacked) { id = id; name = name; type = type; hacked = hacked; } } }
here's have tried:
using (reader = dbcmd.executereader()) { var readlist = new list<list<object>>(); while (reader.read()) { var linelist = new list<object>(); (int = 0; < reader.fieldcount; i++) { linelist.add(reader.getvalue(i)); // reads entries in row } readlist.add(linelist); } foreach (var item in readlist) { sqldynamicdata.terminallist.add(new sqldynamicdata.terminals(convert.toint32(item[0]), item[1].tostring(), item[2].tostring(), convert.toboolean(item[3]))); } }
the problem is, if data returned id , name, item[2] , item[3] throw exceptions. also, name might returned first , not id if user selects name , nothing else, in case converting item[0] int isn't correct
how do this? need because want update in game objects based on sql data.
if want know columns present in datareader created query use getschematable method returns table description of columns. in context interested in name of columns, create simple list<string>
names of columns. opens way use list<terminals>
instead of complex list<list<object>>
of course, need method leave query method generic possible in such way, if pass incomplete query text class, possible use same code.
simple way reach goal separate usual stuff opens connection, create command, execute , loops on records task of reading content of reader. achieved if pass query method action delegate receives datareader , list of columns present in datareader.
so, query method changes
private void query(string sqlcommand, action<sqldatareader, list<string> loader) { using (dbcon = new sqlconnection(connectionstring)) using (dbcmd = dbcon.createcommand()) { dbcmd.commandtext = sqlcommand; dbcon.open(); datatable dt = reader.getschematable(); list<string> columns = dt.asenumerable().select(x => x.field<string>("columnname")).tolist(); using (reader = dbcmd.executereader()) { while (reader.read()) if(loader != null) loader(reader, columns); } } }
as can see, query method doesn't know how retrieve fields query, leaves task special method received second parameter. method wants 2 parameters, datareader , list of column names. them creates terminals instance, extracts columns values (checking if expected columns present) , add terminals instance global list of terminals.
then, whoever calls query method should provide method loads data, method this
private void loadterminalsdata(sqldatareader reader, list<string> cols) { terminals t = new terminals(); if(cols.indexof("id") != -1) t.id = reader.getint32(reader.getordinal("id")); if(cols.indexof("name") != -1) t.name = reader.getstring(reader.getordinal("name")); if(cols.indexof("type") != -1) t.type = reader.getstring(reader.getordinal("type")); if(cols.indexof("hacked") != -1) t.hacked = reader.getboolean(reader.getordinal("hacked")); listofterminals.add(t); }
call passing query method this
....query("select id, name terminals", loadterminaldata);
Comments
Post a Comment