Does my Query allows me to extend SELECT statment

Question

In my SOAP service I have currently search by ID(A_KONTO) and right now I want to modify this query that I can search also by City or Address or Name(whatever)

A_KONTO     NAME        ADDRESS         CITY
123456      Test123     West Cost       New York
321456      Nunde       East Cost       Washington
123666      Huhned      Schwaube Street     Berlin
123456      Test123     West Cost       New York
321456      Nunde       East Cost       New York
123666      Huhned      Schwaube Street     New York

In this case if I search by City it needs to return a list of all city in my db as XML format.

[WebMethod]
        public string GetAkontasById(string Id)
        {
            OracleConnection conn = new OracleConnection("DATA SOURCE=test-1:1521/test;USER ID=testuser;PASSWORD=test123");
            OracleDataAdapter dr = new OracleDataAdapter("Select * from AKONTAS where A_KONTO='" + Id + "'", conn);
            DataSet ds = new DataSet();
            ds.Tables.Add("AKONTAS");
            dr.Fill(ds, "AKONTAS");
            DataTable tt = ds.Tables[0];
            return ds.GetXml();
        }

So if I for example enter New York it needs to return XML of all data where is New York

Something Like this

<string xmlns="http://tempuri.org/">
<NewDataSet><AKONTAS> 
<A_KONTO>123456</A_KONTO> 
<NAME>Test123   </NAME> 
<ADDRESS>Test123</ADDRESS> 
<CITY>New York</CITY> 
</AKONTAS> </NewDataSet>

<NewDataSet><AKONTAS> 
<A_KONTO>547896</A_KONTO> 
<NAME>sadadsa   </NAME> 
<ADDRESS>babab </ADDRESS> 
<CITY>New York</CITY> 
</AKONTAS> </NewDataSet>

<NewDataSet><AKONTAS> 
<A_KONTO>313365</A_KONTO> 
<NAME>teea</NAME> 
<ADDRESS>hahhd</ADDRESS> 
<CITY>New York</CITY> 
</AKONTAS> </NewDataSet>

<NewDataSet><AKONTAS> 
<A_KONTO>541223</A_KONTO> 
<NAME>dasasd</NAME> 
<ADDRESS>hsahdkjah</ADDRESS> 
<CITY>New York</CITY> 
</AKONTAS> </NewDataSet>
</string>

Yo have a method named `GetAkontas*ById*` And you want to extend it... You should consider renaming it, why dont you create a new method named `SearchAkontas(string searchTerm)`?. Your method names should always do what they say. If the method name says it gets something with an specific Id, it should not do different things. — Cleptus, Sep 27 '19 at 06:59
Yes you are right. I have method named GetAkontas*ById* and I want to extend it to make that can search by City or Name or Address whatever. I can rename this method like GetAkontas since table name is Akontas. I also thought in that way but my Team Lead wants to make in one function, extend this query Since I try everthing and I always get empty xml file — , Sep 27 '19 at 07:03
Just checked your actual SQL... You have a serious problem there. Check [Bobby tables](https://xkcd.com/327/) — Cleptus, Sep 27 '19 at 07:03
HAAHAHAH, I checked but I always get empty XML OracleDataAdapter dr = new OracleDataAdapter("Select * from AKONTAS where MJESTO='" + Mjesto + "'", conn); — , Sep 27 '19 at 07:06
You should use bind parameters instead of concatenated strings. — Wernfried Domscheit, Sep 27 '19 at 07:07

score 0 · Accepted Answer · answered Sep 27 '19 at 07:10

0

You can use LIKE condition if you are searching for a particular text.

Select * from AKONTAS where A_KONTO LIKE '%"+Id+"%' OR NAME LIKE '%"+name+"%' OR ADDRESS LIKE '%"+address+"%' OR CITY LIKE '%"+city+"%'

answered Sep 27 '19 at 07:10

Vincent Dante

166
6

I would always suggest a parametrized query, one that does not allow SQL Injection attacks – Cleptus Sep 27 '19 at 07:12
Check this [related answer](https://stackoverflow.com/a/11048965/2265446) – Cleptus Sep 27 '19 at 07:16
@bradbury9 Interesting staff, but doesnt work for me either I put something like this OracleCommand oraCommand = new OracleCommand("SELECT MJESTO FROM AKONTAS WHERE MJESTO = :Mjesto",conn); oraCommand.Parameters.Add(new OracleParameter("Mjesto", oraCommand)); – Sep 27 '19 at 07:54
You have no column named "MJESTO" right? You said your columns where "A_KONTO,NAME,ADDRESS,CITY" – Cleptus Sep 27 '19 at 08:25
Yes, I have column in db MJESTO – Sep 27 '19 at 09:20
I suppose this return method need to return `List` since in my `db` I have a couple of field like `New York` – Sep 27 '19 at 09:22

Does my Query allows me to extend SELECT statment

1 Answers1