Using C# SqlCommand to Query SQL Table-CodePudding

I have a class SqlGetGroupCutRates which has a

public bool UpdateDefaultTarget(string param1)

method. In this method, I am using the SqlClient.SqlCommand class.

public bool UpdateDefaultTarget(string g1)
{
    string myworkCenterCode = Form1.globalWorkCenter;

    try
    {
        string connString = @"Data Source ="   server   "; Initial Catalog ="   redGreenDB   ";Integrated Security=SSPI";
        // WHERE CUT_RATE_GROUP = @CUT_RATE_GROUP... WHAT IS @CUT_RATE_GROUP????

        string myCommandString = "SELECT TOP 1 Default_Cut_Rate_Per_Hour FROM "   groupCutRateTable   " WHERE "  
                "Cut_Rate_Group= @Cut_Rate_Group ORDER BY Record_Date DESC";

        // SQL connection using the connString taking us to the redGreenDB
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();

            // Get the specific default cut rate from groupCutRateTable ("dbo.ABR_Tape_Cvt_Group_Cut_Rates") where Cut_Rate_Group (column)
            using (SqlCommand cmd = new SqlCommand(myCommandString, conn))
            {
                cmd.Parameters.AddWithValue("Cut_Rate_Group", g1);                       

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        defaultFoundInQuery = true;

                        while (reader.Read())
                        {
                            int ordTarget = reader.GetOrdinal("Default_Cut_Rate_Per_Hour");

                            // Handle potential null database values:
                            if (!reader.IsDBNull(ordTarget))
                            {
                                int ord1 = reader.GetOrdinal("Default_Cut_Rate_Per_Hour");
                                default_Cut_Rate_Per_Hour = reader.GetDouble(ord1);
                            }
                            else
                            { 
                                default_Cut_Rate_Per_Hour = 0; 
                            }
                        }
                    }
                    //  else if no data rows found.
                    else
                    {
                        default_Cut_Rate_Per_Hour = 0;
                    }

                    conn.Close();
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Problem getting Group Cut Rates from database:  \n"   ex);
    }

    return defaultFoundInQuery;
}

In my myCommandString variable, you will notice it is set to the value

string myCommandString = "SELECT TOP 1 Default_Cut_Rate_Per_Hour FROM "   groupCutRateTable   " WHERE "  
                    "Cut_Rate_Group= @Cut_Rate_Group ORDER BY Record_Date DESC";

I am querying the top 1/first Default_Cut_Rate_Per_Hour column value from the groupCutRateTable WHERE the Cut_Rate_Group column value is @Cut_Rate_Group.

My question is... in this query... what is meant by the @Cut_Rate_Group part of the query? Is this just going to return the first Cut_Rate_Group column value? Essentially making this a "static" query that will always return the same value? Or is the syntax of @Cut_Rate_Group seen as dynamic? Meaning @Cut_Rate_Group is assigned a value somewhere in my code?

Apologies, I am very new to this. If you have any docs I could read further into this, I would also appreciate that so I can better understand any answer I may get. Thanks for taking the time to read this.

I am expecting that this syntax would make @Cut_Rate_Group dynamic in the sense that it is almost viewed as a variable that is assigned a value.

CodePudding user response：

@Cut_Rate_Group is a sql paramter (and is dynamic like a variable). Parameterization of sql commands is to safe guard from sql injections.

It's value is added here

 cmd.Parameters.AddWithValue("Cut_Rate_Group", g1)

CodePudding user response：

The statement

cmd.Parameters.AddWithValue("Cut_Rate_Group", g1)

creates the parameter @Cut_Rate_Group that is referred to in the select statement. Since its value comes from the parameter g1, it will be "dynamic" in that whatever value is passed in g1 will become the value of the parameter @Cut_Rate_Group used in the select statement.

The statement above could have been written

cmd.Parameters.AddWithValue("@Cut_Rate_Group", g1)

If you call UpdateDefaultTarget with the same value of g1, and no records have been deleted from the table, it will return the same record if no new records have a record date less than or equal to that original record.

However, not knowing what you are trying to accomplish, this may not be what you actual want to happen.