CODEWITHSUNDEEP
c#
Chelsea
Chelsea

Reputation: 751

Concatenating this SQL query

I have this sql query

SqlCommand cmd = new SqlCommand("select distinct fld from client", con);

can i set the column name using variable as

string str = "fld";
SqlCommand cmd = new SqlCommand("select distinct + str  + from client", con);

Upvotes: 0

Views: 3314

Answers (2)

Prabu
Prabu

Reputation: 4197

It is good practice to use SQLCommand Parameters here, as described here on msdn. This is to prevent SQL Injection.

For example:

 string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics. 
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

However, for the columns selected you must still use dynamic sql, as described by @marc_s in this answer.

As @marc_s describes his solution:

var sqlCommandStatement = String.Format("select distinct {0} from client", "fld");

and then use the sp_executesql stored proc in SQL Server to execute that SQL command (and specify the other parameters as needed).

Upvotes: 4

N K
N K

Reputation: 3327

string str = "fld";
SqlCommand cmd = new SqlCommand(string.Format("select distinct {0} from client", str), con);

Upvotes: 4

Related Questions