CODEWITHSUNDEEP
c#sqlarrays
fishmong3r
fishmong3r

Reputation: 1434

How to query from SQL comparing with array members?

So I have an array containing computer names called hostnames[].

Contains: compname1, compname2, compname3 etc.

Actually it gets it's members from another SQL query.

I have a data table and I need to query all rows where hostname column has any of the computer names in my array.

something like:

select * from table where hostname in hostnames[]

How should I proceed to achieve my goal?

EDIT: I was thinking on the below:

string temp = "'" + hostnames[0] + "'";
for(int i=1; i<hostnames[].Lenght; i++)
{
temp = temp + ",'" + hostnames[i] + "'";
}
string query = "SELECT * FROM table WHERE hostname IN (" + temp + ")";

Upvotes: 1

Views: 5276

Answers (2)

GarethD
GarethD

Reputation: 69769

The best way to use parameters for an IN clause is to use Table-valued parameters, in your case you will need a type as a table with one nvarchar column. I've used a generic name so the type can be reused without confusion:

CREATE TYPE dbo.StringList AS TABLE (value NVARCHAR(MAX));

Then it is simply a case of adding your values to a DataTable and passing this as a parameter to your select command:

var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("Value", typeof(string)));

for (int i = 0; i < hostnames.Length; i++)
{
    var dr = dataTable.NewRow();
    dr[0] = "";
    dataTable.Rows.Add(dr);
}
using (var connection = new SqlConnection("connectionString"))
using (var command = new SqlCommand("SELECT * FROM Table WHERE HostName IN (SELECT Value FROM @StringList)", connection))
{
    SqlParameter stringListParameter = new SqlParameter("@StringList", SqlDbType.Structured);
    stringListParameter.Value = dataTable;
    stringListParameter.TypeName = "dbo.StringList";
    command.Parameters.Add(stringListParameter);

    // OPEN CONNECTION EXECUTE COMMAND ETC

}

Upvotes: 2

Osa E
Osa E

Reputation: 1781

There are 4 ways to achieve what you want. You choose what works best for you.

  1. Use IN as in your code
  2. Break down to OR. A IN (1, 2, 3) => A=1 OR A=2 OR A=3
  3. Use Table Valued Parameters
  4. User the sql query passed earlier. ex: "SELECT * FROM table WHERE hostname IN (Select hostname from tableusedEarlier)"

Upvotes: 1

Related Questions