asp.net - SQL Parameters won't apply to command -
im trying use sql parameters avoid sql injection in query parameters don't seem apply command.
public static ilist<call> getcallsfilter(string startdate, string enddate,list<string>contextname, list<string>valuename, list<string>typename) { using (sqlconnection connection = new sqlconnection(configurationmanager.connectionstrings["bamconnectionstring"].connectionstring)) { connection.open(); startdate += " 00:00:00"; enddate += " 23:59:59"; using (sqlcommand cmd = new sqlcommand("", connection)) { cmd.commandtext = "select dbo.calls.callid, dbo.connections.connectionname,dbo.calls.connectionid, dbo.calls.parentid, dbo.calls.starttime, dbo.calls.endtime, replace(dbo.calls.querytime, ',', '.') querytijd, dbo.calls.template, dbo.calls.profilecall, dbo.calls.objectcall, dbo.calls.method, dbo.calls.error, dbo.calls.category, dbo.calls.uur, dbo.calls.dayofmonth, dbo.repositorys.repositoryname,dbo.calls.resultlink, replace(max(querytime) on (partition datepart(yyyy, dbo.calls.starttime), datepart(m, dbo.calls.starttime), dbo.calls.dayofmonth, dbo.calls.uur, datepart(minute, dbo.calls.starttime)), ',', '.') maxquerytime dbo.calls inner join dbo.connections on dbo.calls.connectionid = dbo.connections.connectionid inner join dbo.repositorys on dbo.connections.repositoryid = dbo.repositorys.repositoryid starttime between '" + startdate + "' , '" + enddate + "'"; if (contextname != null && valuename != null) { (int = 0; < contextname.count; i++) { if (contextname[i].tostring() != "filter") { if (typename[i].tostring() == "like") { cmd.commandtext += " , exists (select * dbo.context callid = dbo.calls.callid , name=@name , value @value)"; cmd.parameters.add(new sqlparameter("name", contextname[i].tostring())); cmd.parameters.add(new sqlparameter("value", valuename[i].tostring())); } else if (typename[i].tostring() == "=") { cmd.commandtext += " , exists (select * dbo.context callid = dbo.calls.callid , name='" + regex.escape(contextname[i].tostring()) + "' , value = '" + regex.escape(valuename[i].tostring()) + "')"; } else if (typename[i].tostring() == "not like") { cmd.commandtext += " , exists (select * dbo.context callid = dbo.calls.callid , name='" + regex.escape(contextname[i].tostring()) + "' , value not '%" + regex.escape(valuename[i].tostring()) + "%')"; } } } } using (sqldataadapter adapter = new sqldataadapter(cmd)) { calldata = new list<call>(); datatable table = new datatable(); adapter.fill(table); foreach (datarow rij in table.rows) { calldata.add(new call() { callid = int64.parse(rij[0].tostring()), connectionname = rij[1].tostring(), connectionid = rij[2].tostring(), parentid = rij[3].tostring(), starttime = ((datetime)rij[4]).tostring("d/mm/yyyy hh:mm:ss.fff"), endtime = ((datetime)rij[5]).tostring("d/mm/yyyy hh:mm:ss.fff"), querytime = rij[6].tostring(), template = rij[7].tostring(), profile = rij[8].tostring(), object = rij[9].tostring(), method = rij[10].tostring(), error = rij[11].tostring(), category = rij[12].tostring(), uur = rij[13].tostring(), dayofmonth = rij[14].tostring(), repository = rij[15].tostring(), datum = rij[4].tostring(), resultlink = rij[16].tostring(), maxquerytime = rij[17].tostring() }); } } } } return calldata; } im trying out in if function won't work atm.
public static ilist<call> getcallsfilter(string startdate, string enddate,list<string>contextname, list<string>valuename, list<string>typename) { using (sqlconnection connection = new sqlconnection(configurationmanager.connectionstrings["bamconnectionstring"].connectionstring)) { connection.open(); startdate += " 00:00:00"; enddate += " 23:59:59"; using (sqlcommand cmd = new sqlcommand("", connection)) { cmd.commandtext = "select "; cmd.commandtext += " dbo.calls.callid, "; cmd.commandtext += " dbo.connections.connectionname, "; cmd.commandtext += " dbo.calls.connectionid, "; cmd.commandtext += " dbo.calls.parentid, "; cmd.commandtext += " dbo.calls.starttime, "; cmd.commandtext += " dbo.calls.endtime, "; cmd.commandtext += " replace(dbo.calls.querytime, ',', '.') "; cmd.commandtext += " "; cmd.commandtext += " querytijd, "; cmd.commandtext += " dbo.calls.template, "; cmd.commandtext += " dbo.calls.profilecall, "; cmd.commandtext += " dbo.calls.objectcall, "; cmd.commandtext += " dbo.calls.method, "; cmd.commandtext += " dbo.calls.error, "; cmd.commandtext += " dbo.calls.category, "; cmd.commandtext += " dbo.calls.uur, "; cmd.commandtext += " dbo.calls.dayofmonth, "; cmd.commandtext += " dbo.repositorys.repositoryname, "; cmd.commandtext += " dbo.calls.resultlink, "; cmd.commandtext += " replace(max(querytime) on (partition datepart(yyyy, dbo.calls.starttime), "; cmd.commandtext += " datepart(m, dbo.calls.starttime), "; cmd.commandtext += " dbo.calls.dayofmonth, "; cmd.commandtext += " dbo.calls.uur, "; cmd.commandtext += " datepart(minute, dbo.calls.starttime)), ',', '.') "; cmd.commandtext += " maxquerytime dbo.calls "; cmd.commandtext += " inner join dbo.connections on "; cmd.commandtext += " dbo.calls.connectionid = dbo.connections.connectionid "; cmd.commandtext += " inner join dbo.repositorys on dbo.connections.repositoryid = dbo.repositorys.repositoryid "; cmd.commandtext += " starttime between @stardate , @enddate "; if (contextname != null && valuename != null) { (int = 0; < contextname.count; i++) { if (contextname[i].tostring() != "filter") { cmd.commandtext += " , exists "; cmd.commandtext += "(select * dbo.context "; cmd.commandtext += " callid = dbo.calls.callid , name=@name , value "; cmd.commandtext += typename[i].tostring(); cmd.commandtext += " @value)"; cmd.parameters.addwithvalue("@context", contextname[i].tostring()); cmd.parameters.addwithvalue("@value", valuename[i].tostring()); } } } cmd.parameters.addwithvalue("@startdate", startdate); cmd.parameters.addwithvalue("@enddate", enddate); using (sqldataadapter adapter = new sqldataadapter(cmd)) { calldata = new list<call>(); datatable table = new datatable(); adapter.fill(table); foreach (datarow rij in table.rows) { calldata.add(new call() { callid = int64.parse(rij[0].tostring()), connectionname = rij[1].tostring(), connectionid = rij[2].tostring(), parentid = rij[3].tostring(), starttime = ((datetime)rij[4]).tostring("d/mm/yyyy hh:mm:ss.fff"), endtime = ((datetime)rij[5]).tostring("d/mm/yyyy hh:mm:ss.fff"), querytime = rij[6].tostring(), template = rij[7].tostring(), profile = rij[8].tostring(), object = rij[9].tostring(), method = rij[10].tostring(), error = rij[11].tostring(), category = rij[12].tostring(), uur = rij[13].tostring(), dayofmonth = rij[14].tostring(), repository = rij[15].tostring(), datum = rij[4].tostring(), resultlink = rij[16].tostring(), maxquerytime = rij[17].tostring() }); } } } } return calldata; }
Comments
Post a Comment