CODEWITHSUNDEEP
c#sql-serverwinforms
MarcIT
MarcIT

Reputation: 39

How can I use a SQL value in a formula

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?

Upvotes: 1

Views: 106

Answers (2)

Yanga
Yanga

Reputation: 3012

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;
    }

Upvotes: 0

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34180

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.

Upvotes: 3

Related Questions