How to write SQL command in c#

Question

I have a project about searching database with given statements from a Windows form but I couldn't write the correct command for searching SQL. My code:

int? value;
if (this.textBox2.Text == "" || this.textBox2.Text == null)
    value = null;
else
    value = Convert.ToInt32(this.textBox2.Text);
    SqlCommand command = new SqlCommand(
        @"SELECT * 
          FROM Billing 
            INNER JOIN Payment ON Payment.payment_id = Billing.billing_type 
            INNER JOIN Customer ON Customer.customer_id = Billing.billing_customer
          WHERE  billing_id like '%" + textBox5.Text.ToString() + "%' 
            AND billing_type like '%" + comboBox2.SelectedValue + "%'
            AND Billing.billing_cost > " + value + "", connection);
    SqlDataReader reader = command.ExecuteReader();

The problem is that when I enter 3000 to textBox2 (I controlled that it is reading 3000) searching is not working and display nothing. Also there is no exception thrown.

Did I write the command or not?

Hard to tell without SQL Data. This looks suspicious: "Payment.payment_id = Billing.billing_type". Also watch out for sql insertions. — Carra, Apr 05 '16 at 11:06
@KAYA you're sure theres data when Billing.billing_cost is >3000? — President Camacho, Apr 05 '16 at 11:08
Yeap, I am sure , I looked the data value from converting, but there is no error in "Payment.payment_id = Billing.billing_type" because I can display datas with this code. — KAYA, Apr 05 '16 at 11:15
What you're doing are joins with filters so if you get no records when running the query and there's no error, the most obvious cause would be that no record complies the condition. Take into account that inner join requires matching on both left and right tables. — derloopkat, Apr 05 '16 at 11:21
payment_id implies that what is the billing type, and others just get the data which customer has this billing , there is no relation with searching — KAYA, Apr 05 '16 at 11:24
@KAYA did you put a breakpoint, get the query string and try running you query with these parameters in Management Studio? This will tell you if there's a problem with the query itself or it's something with .Net. — derloopkat, Apr 05 '16 at 11:29
KAYA - go and read about "SQL Injection". Do it now. It's that important! — Martin Milan, Apr 05 '16 at 12:42
I will do, @derloopkat. And also I will read SQL Injection , thanks :/ — KAYA, Apr 05 '16 at 12:48
better to use mysqli, it's more secure, injects without showing the structure of data to would be hackers. — GeorgeWL, Apr 05 '16 at 14:12
@TheGeorgeL How do you propose using `mysqli` with SQL Server? — Lasse V. Karlsen, Apr 06 '16 at 07:30
@Kaya That can be due to your inner join some side of your inner join is null — Ghassen, Apr 06 '16 at 09:41
@lasse-v-karlsen oops, i said a stupid and thought they said mysql instead. — GeorgeWL, Apr 06 '16 at 14:29

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

Try using parameters instead :

string query =  @"SELECT * 
          FROM Billing 
            INNER JOIN Payment ON Payment.payment_id = Billing.billing_type 
            INNER JOIN Customer ON Customer.customer_id = Billing.billing_customer
          WHERE  billing_id like '%" + textBox5.Text.ToString() + "%' 
            AND billing_type like '%" + comboBox2.SelectedValue + "%'
            AND Billing.billing_cost > @value", connection);

SqlCommand command = new SqlCommand(query,connection);

command.Parameters.Add("@value",SqlDbType.Int);
command.Parameters["@value"].Value = Convert.ToInt32(this.textBox2.Text);
SqlDataReader reader = command.ExecuteReader();

Taken from What are good ways to prevent SQL injection?

edited May 23 '17 at 11:50

Community

1
1

answered Apr 06 '16 at 07:26

Mihai-Daniel Virna

984
9
19

thank you but it warns me that @ID is not found and still not working – KAYA Apr 06 '16 at 09:34
@KAYA sorry, corrected my answer, should've been `@value` – Mihai-Daniel Virna Apr 06 '16 at 09:35
it warns again .. "Additional information: The parameterized query '(@value int)SELECT * FROM Billing INNER JOIN Payment ON Payme' expects the parameter '@value', which was not supplied" – KAYA Apr 06 '16 at 09:47
@KAYA That usually happens when the supplied parameter is null. – Mihai-Daniel Virna Apr 06 '16 at 09:55

Jishnu KM · Answer 2 · 2016-04-06T05:09:57.410

-1

try this

"select * From Billing INNER JOIN Payment ON Payment.payment_id = Billing.billing_type 
INNER JOIN Customer ON Customer.customer_id = Billing.billing_customer
where billing_id like '%'+'" + textBox5.Text + "'+'%' 
    and billing_type like '%'+'" + comboBox2.SelectedValue + "'+'%' 
    and Billing.billing_cost > " + value

Use char ' on both before and after the symbol %
Tips. Never convert a data to same datatype. MSDN says it will reduce the speed speed of execution upto 7 times than exact coding. mentioning about your use of textBox5.Text.ToString(). Its wrong because its already a string.

edited Apr 06 '16 at 05:09

answered Apr 05 '16 at 11:39

Jishnu KM

234
1
8

@KAYA I have edited. Please retry. I got answer - [Click here to see a snap of your answer](https://drive.google.com/open?id=0B7qBAOThvb7zUV9vX002MkMzV0k) – Jishnu KM Apr 06 '16 at 05:20
downvote reason: this post ignores the biggest problem of the OP code, leaving the sql code still vulnerable to Sql injection attacks. – Zohar Peled Apr 06 '16 at 06:11
1

@ZoharPeled feel sad i Know this is not a gud way as a developer. But just read KAYAs comments on his Qst. **I will read SQL Injection** as a reply to ones Qst **go and read about "SQL Injection". Do it now. It's that important!**. I think he is a biggner and try to develop a very small application. and i just correct his Code. Thts all – Jishnu KM Apr 06 '16 at 07:06
I tried from sql and write again correctly by using your solution but not working :/ @JishnuKM – KAYA Apr 06 '16 at 09:51

How to write SQL command in c#

2 Answers2