How can I use a SQL value in a formula

Question

I have a C# winform app, that contains a formula. The formula works ok but depending on the id from a SQL table the numbers in the formula can change.

How can I accomplish this?

The code I have is:

private void button1_Click(object sender, EventArgs e) 
{
    decimal ufa1;
    decimal aav1; 
    decimal uft1;

    ufa1 = uft1 * aav1 * VALUE FROM SQL;  
}

I have a previous query that gets the value I want but I can't get it integrated in the formula.

The code to get the id is:

con.Open();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select prumos from dbo.modelos where id = '"+id+"'";
SqlDataReader dr = cmd.ExecuteReader();

new code: Because of dependencies i had to change the select from id to a column named "prumos" with int values just like id but it wont run.

    con.Open();
    using (SqlCommand cmd = new SqlCommand("select prumos from dbo.modelos where prumos = @prumos", con))
    {
        cmd.Parameters.Add(new SqlParameter("prumos", prumos));
        ValueFromDB = decimal.Parse(cmd.ExecuteScalar().ToString());
    }

any solutions?

https://msdn.microsoft.com/es-es/library/haa3afyz(v=vs.110).aspx — Juan Carlos Oropeza, Nov 17 '16 at 14:38
Use parameter to avoid sql injection. http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements — Juan Carlos Oropeza, Nov 17 '16 at 14:39

score 3 · Answer 1 · edited Nov 17 '16 at 15:12

3

Instead of using DataReader, just Read Execute the command as scalar:

You can do something like this:

public int GetValue(int id)
{
 ....
    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select top 1 prumos from dbo.modelos where id = '"+id+"'";
    int value = int.Parse(cmd.ExecuteScalar().ToString());
    return value;
}

and then:

private void button1_Click(object sender, EventArgs e) 
{
    decimal ufa1;
    decimal aav1; 
    decimal uft1;

    ufa1 = uft1 * aav1 * GetValue(1);  
}

Note that if you need to send the Id to the GetValue() function, you can send it as a parameter.

edited Nov 17 '16 at 15:12

CDspace

2,639
18
30
36

answered Nov 17 '16 at 14:37

Ashkan Mobayen Khiabani

33,575
33
102
171

Good tip,But my guess is `id` is a `PK` so probably `top 1` isnt necesary – Juan Carlos Oropeza Nov 17 '16 at 14:40
yep I guess so, but as I'm not sure, precaution seems wise ;) – Ashkan Mobayen Khiabani Nov 17 '16 at 14:42
thanks for the reply, i tried it but after this how can i indicate the value in the formula? – MarcIT Nov 17 '16 at 14:49
Now get the error `The object reference was not defined as an instance of an object.` in the line `int value = int.Parse(cmd.ExecuteScalar().ToString());` – MarcIT Nov 17 '16 at 15:14
Well an item with that id might not exist. you can deal with it like: `obj val = cmd.ExecuteScalar(); if(val!=null) return int.Parse(val.ToString()); else return 0; //a default value if id doesn't exist` – Ashkan Mobayen Khiabani Nov 17 '16 at 15:34

Yanga · Accepted Answer · 2016-11-17T15:28:07.270

0

private void button1_Click(object sender, EventArgs e) 
{
    decimal ufa1;
    decimal aav1; 
    decimal uft1;

    con.Open();
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select prumos from dbo.modelos where id = '"+id+"'";
    //SqlDataReader dr = cmd.ExecuteReader();
    //decimal ValueFromDB = (decimal)cmd.ExecuteScalar();
    decimal ValueFromDB = Decimal.Parse(cmd.ExecuteScalar().ToString());


    ufa1 = uft1 * aav1 * ValueFromDB ;  
}

Or even better:

    private void button1_Click(object sender, EventArgs e)
    {
        decimal ufa1;
        decimal aav1;
        decimal uft1;
        decimal ValueFromDB;

        using (SqlCommand cmd = new SqlCommand("select prumos from dbo.modelos where id = @ID", con))
        {
            cmd.Parameters.Add(new SqlParameter("ID", id));
            ValueFromDB = Decimal.Parse(cmd.ExecuteScalar().ToString());

        }
        ufa1 = uft1 * aav1 * ValueFromDB;
    }

edited Nov 17 '16 at 15:28

answered Nov 17 '16 at 14:39

Yanga

2,885
1
29
32

Thanks for the help, i tried it but get an error in line: `decimal ValueFromDB = (decimal)cmd.ExecuteScalar();` `The specified conversion is invalid` – MarcIT Nov 17 '16 at 14:51
Hum ok try with double ValueFromDB = Double.Parse(cmd.ExecuteScalar().ToString()); then. I ve just edited my post – Yanga Nov 17 '16 at 14:56
`operator '*' cannot be applied to operands of type 'decimal' and 'double'` – MarcIT Nov 17 '16 at 15:18
Hum ok look like math operations can only occur on variables of the same type, i updated my anwser and operate a Decimal.Parse instead of Double.Parse – Yanga Nov 17 '16 at 15:29
quick follow up question. because of dependecies i had to change the target column from id to "prumos", witch contains int values to, just like id. but when i try to run the app i get an error. I updated my question for easyer comprehension – MarcIT Nov 17 '16 at 16:12

How can I use a SQL value in a formula

2 Answers2