0

First of all I want to say I'm using Firebird and do not know if this is problem with other database servers.

What I want to do is pass part of command string as parameter.

I have code like this:

using (FbConnection con = new FbConnection(M.Baza.connectionString))
{
    con.Open();
    using (FbCommand cmd = new FbCommand("DELETE FROM STAVKA WHERE VRDOK = @VRDOK AND BRDOK = @BRDOK AND ROBAID IN (@ROBAID)", con))
    {
        cmd.Parameters.AddWithValue("@VRDOK", vrDok);
        cmd.Parameters.AddWithValue("@BRDOK", brDok);
        cmd.Parameters.AddWithValue("@ROBAID", robaId); //robaId is string which looks like this '15, 18, 16, 14, 28'

        cmd.ExecuteNonQuery();

        if (showMessage)
            MessageBox.Show("Stavke uspesno obrisane!");
    }
}

The code above is not working and it drops error

unable to cast object of type 'system.Int32` to type 'system.iconvertible'

But when I write code like this:

using (FbConnection con = new FbConnection(M.Baza.connectionString))
{
    con.Open();
    using (FbCommand cmd = new FbCommand("DELETE FROM STAVKA WHERE VRDOK = @VRDOK AND BRDOK = @BRDOK AND ROBAID IN (" + robaId + ")", con))
    {
        cmd.Parameters.AddWithValue("@VRDOK", vrDok);
        cmd.Parameters.AddWithValue("@BRDOK", brDok);

        cmd.ExecuteNonQuery();

        if (showMessage)
            MessageBox.Show("Stavke uspesno obrisane!");
    }
}

It works.

Why is this happening?

Let's say I have command like this "SELECT " + columns + " FROM TABLE" where columns is type of string which looks like this column1, column2, column3 and it is working. How I can make same code look like this "SELECT @COLUMNS FROM TABLE" and pass that @COLUMNS just like I am passing parameters with cmd.Parameters.AddWithValue("@COLUMNS", columns);

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Aleksa Ristic
  • 2,394
  • 3
  • 23
  • 54
  • 1
    no, you can not pas MULTIPLE values as ONE parameter. Parameters are scalar, not vector. If your values are integer only - you may put them into query text directly. `IN` clause does not have one string value inside - it has a number (array, vector, list. you name it) of some scalar parameters (integer, float, date, anything). So if you would want to pass them as params, you have to make a separate parameter for every value within `IN`-list – Arioch 'The Jun 20 '18 at 09:10
  • Here are some alternative techniques you can use instead of mere "String splicing" (putting numbers directly into query text) or auto-generating the query with parameters like `@ROBAID_1` and `@ROBAID_2` and `@ROBAID_3` and so on. – Arioch 'The Jun 20 '18 at 09:19
  • As to the second part of your question (really, you should ask only on thing per question, not multiple things!), no you cannot parameterize the column list. – Mark Rotteveel Jun 20 '18 at 09:58
  • Okay.... Totally out of point but thanks for time... – Aleksa Ristic Jun 20 '18 at 10:02
  • `no you cannot parameterize the column list` @MarkRotteveel well, then there is `EXECUTE STATEMENT` and `EXECUTE BLOCK` :-D Though it is "dirty ugly hack" to abuse them for that. – Arioch 'The Jun 20 '18 at 10:19
  • That being said, there is a bug in the code. `cmd.ExecuteNonQuery(); if (showMessage) MessageBox.Show("Stavke uspesno obrisane!");` - here the program cheats on the user telling him things ("table Stavke was trimmed successfully") that did not happen yet (the transaction is not commited and maybe never would be). – Arioch 'The Jun 20 '18 at 10:22
  • @Arioch'The There is no point to use those, as the OP could simply already generate the required column list when building his query in C#. – Mark Rotteveel Jun 20 '18 at 10:23
  • I agree about "no practical point" but still that was not quite "impossible". Just made a sidenote. – Arioch 'The Jun 20 '18 at 10:26
  • w.r.t. `How can i implement part of text to command like i am implementing parameter` you only asked with details about one very specific case of parametrizing - parametrizing the values in the `IN`-list. If you want to "parametrise" any random part of SQL query - then you can use FB's `EXECUTE STATEMENT` command to achieve it. But it would be exactly the same string operation "+ sign" just executed in the Firebird ( there it would be "|| sign" ) not in C# and in a VERY indirect (error-prone and boilerplate-heavy) way. – Arioch 'The Jun 20 '18 at 10:32
  • Yea I want to "parametrize" any random part of SQL query but I am not sure how should I build my query inside c# code with `EXECUTE STATEMENT` – Aleksa Ristic Jun 20 '18 at 11:06
  • What Arioch'The tries to say is that you shouldn't parameterize parts of your SQL query. You need to dynamically build those parts in your C# code, only parameterize **values**. Just be aware of SQL injection risk if the dynamic parts of your query are user supplied. – Mark Rotteveel Jun 21 '18 at 07:55

0 Answers0