CODEWITHSUNDEEP
c#sqlmethods
SANTI SANTOSH MAHAPATRA
SANTI SANTOSH MAHAPATRA

Reputation: 25

I want to pass a parameter in C# method where it will accept for SQL query

public int ColumnCountinFailedQueue(long QueueNo)
{
      string query = "select count(QueueNo) 
                      from NS_FailedQueue 
                      where queueid = @QueueNo";

I am not getting QueueNo, is this incorrect.

Upvotes: 0

Views: 309

Answers (5)

Harshil Shukla
Harshil Shukla

Reputation: 137

it should be like this:

public int ColumnCountinFailedQueue(long QueueNo) 
{
    string query = "select count(QueueNo) from NS_FailedQueue where queueid = "+QueueNo.ToString();
}

Upvotes: 0

JamieSee
JamieSee

Reputation: 13030

I see that you're attempting to do a parameterized query here. You're missing some key elements from your question. If you're using raw ADO.NET, a parameterized query looks like this:

public int ColumnCountinFailedQueue(long QueueNo)
{
    string query = "select count(QueueNo)
                    from NS_FailedQueue
                    where queueid = @QueueNo"; 

    int queueCount = 0;

    using (SqlConnection connection = new SqlConnection("connectionString"))
    using (SqlCommand getQueueCountCommand = new SqlCommand(query, connection))
    {
        getQueueCountCommand.Parameters.AddWithValue("@QueueNo", QueueNo);

        connection.Open();
        queueCount = (int)getQueueCountCommand.ExecuteScalar();
    }

    return queueCount;
}

Also, since you're only getting one row with one column, you can use ExecuteScalar to get your result.

It should be noted that the other answers that have you concatenate the parameter to the end of the query as a string will work, and in this specific case are not SQL injection vulnerable because the long on your method parameter forces it to be a number. The problem lies in that if this parameter is ever changed to a string instead, it will be vulnerable to SQL injection.

Upvotes: 0

ígor
ígor

Reputation: 1164

I hate concat string with +, so it's my solution

 string   query = string.Format(@"select count(QueueNo) from NS_FailedQueue where queueid={0}", QueueNo);

But i think is better use sqlparameter

Upvotes: 0

Freelancer
Freelancer

Reputation: 9074

string query = "select count(QueueNo) "
             + "from NS_FailedQueue "
             + "where queueid = " + QueueNo;

From my point of view queueid can be integer... if it is not..

queueid like QueueNo.tostring()

Upvotes: 0

burning_LEGION
burning_LEGION

Reputation: 13460

string query = "select count(QueueNo) "
             + "from NS_FailedQueue "
             + "where queueid = " + QueueNo.ToString();

Upvotes: 2

Related Questions