C# - Using A String Passed Through A Parameter In String

Question

I want to achieve this:

public void SQLInfo(string column)
{
    SqlConnection Connect = new SqlConnection(
        "Server=server;Database=db;User ID=user;Password=pass;");
    Connect.Open();        
    SqlCommand com = new SqlCommand(
        "select distinct [column] from [dbo].[ServerAttributes]");
    SqlDataReader reader = com.ExecuteReader();
}

I failed to find documentation on this. Is there any specific method to use a string passed through a parameter within a string? For example, in Powershell you could use the '$' to denote a variable within a string. "This is the $server you're working on."

You most likely really don't want to do this since it's a perfect way to introduce Sql Injection if column can be provided via user input (and it probably can). — David L
– David L, Commented Jul 21, 2016 at 17:17
Possible duplicate of C# SqlCommand - cannot use parameters for column names, how to resolve? — Pierre-Alain Vigeant
– Pierre-Alain Vigeant, Commented Jul 21, 2016 at 17:19
@DavidL I don't want it to be provided via user input because I'm trying to automate this process. I'm only using a handful of columns that will remain fixed unless changes are required. — Chasen Bettinger
– Chasen Bettinger, Commented Jul 21, 2016 at 17:20
If SQLInfo is called in a loop, use a StringBuilder ... to credit @DavidL ... if anyone inputs "1;delete *" for your column input, you're in for some serious crying time. — Paurian
– Paurian, Commented Jul 21, 2016 at 17:20

Rahul · Accepted Answer · 2016-07-21 17:18:22Z

1

Yes you can using string.Format() like

public void SQLInfo(string column)
        {
  string query = string.Format("select distinct {0} from [dbo].[ServerAttributes]", column);
  SqlCommand com = new SqlCommand(query);
  SqlDataReader reader = com.ExecuteReader();

answered Jul 21, 2016 at 17:18

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shachaf.Gortler · Accepted Answer · 2016-07-21 17:30:53Z

0

When using C# lower than 6 use string formatter

string command = string.Format("SELECT DISTINCT[{0}] FROM[dbo].[ServerAttributes]", column);

For C#6 , you have string literal operator $ it is translated to the same logic as in previous version , but makes the code more readble.

string command = $"SELECT DISTINCT[{column}] FROM[dbo].[ServerAttributes];

answered Jul 21, 2016 at 17:30

Shachaf.Gortler

5,76514 gold badges50 silver badges74 bronze badges

Comments

konkked · Accepted Answer · 2016-07-21 17:32:51Z

You can use string interpolation if you are using C# 6

public void SQLInfo(string column)
{
    SqlConnection Connect = new SqlConnection(
        "Server=server;Database=db;User ID=user;Password=pass;");
    Connect.Open();        
    SqlCommand com = new SqlCommand(
        $"select distinct [{column}] from [dbo].[ServerAttributes]");
    SqlDataReader reader = com.ExecuteReader();
}

Otherwise use string.Format

public void SQLInfo(string column)
{
    SqlConnection Connect = new SqlConnection(
        "Server=server;Database=db;User ID=user;Password=pass;");
    Connect.Open();        
    SqlCommand com = new SqlCommand(
        string.Format("select distinct [{0}] from [dbo].[ServerAttributes]",column));
    SqlDataReader reader = com.ExecuteReader();
}

Note If you're going to do something like this would suggest at least checking to make sure the string isn't introducing the '[' character

column = column.Remove("]");

Collectives™ on Stack Overflow

C# - Using A String Passed Through A Parameter In String

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related