SQL multiple-value parameter in C#-CodePudding

This is what I have:

 select *
 from AuditQuestionnaires
 where Companyid = @companyid 
   and RiskTypeID != @RiskIdList[x];

Why can't I add a parameter like this:

command2.Parameters.Add(new SqlParameter("RiskIdList[x]", RiskIdList[x]));

I'm using SQL Server, and this is the script I'm using in .cs file

CodePudding user response：

Firstly, since you are using SQL Server, you cannot have a parameter named @RiskTypeId[x]. It is simply not a valid name.

Secondly, when passsing parameters from c# the parameter name has to include the initial "@".

Thirdly, as a general rule it is much better to construct the SQLParameter including the DataType. This means that there is no guesswork required, and guesses occasionally go wrong.

Fourthly, it seems to me that you want to iterate through an array. There is nothing to stop you doing this in c#. You would simply create a SqlParameter like this

command2.Parameters.Add(new SqlParameter { ParameterName = "@RiskTypeId", DBType = DBType.Int32, Value = RiskIDList[x] });

However I suspect that what you are really trying to do is to send a list to SQL Server. To achieve this, you need to use a User-Defined Table Type. In your case the following is sufficient:

CREATE TYPE [dbo].[IdList] AS TABLE ([Id] int);

Then you can define a parameter to your query as:

@RiskTypeIdList IdList READONLY

Finally you have to put the contents of your array into a DataTable. Because this is something I do frequently, I have a Class for this job:

public class TDataTable<int> : DataTable
{
    public TDataTable(IEnumerable<T> ids) : base()
    {
        Columns.Add("Id", typeof(T));
        var added = new HashSet<T>();
        foreach (T id in ids)
        {
            //ensure unique values
            if (added.Add(id))
            {
                Rows.Add(id);
            }
        }
    }
    public TDataTable() : base()
    {
        Columns.Add("Id", typeof(T));
    }
}

You can then pass the parameter like this:

command2.Parameters.Add(new SqlParameter { ParameterName = "@RiskTypeIdList", SqlDBType = SqlDBType.Structured, Value = new TDataTable<int>(RiskIDList) });

Within your SQL you can then do what Thorsten suggested:

WHERE RiskTypeId NOT IN (SELECT Id FROM @RiskTypeIdList)

CodePudding user response：

@Thorsten Kettner, its a valid SQL query, we can != in sql query.

command2.Parameters.Add(new SqlParameter("RiskIdList[x]", RiskIdList[x]));

We cannot pass "RiskIdList[x]" as SQL Parameter, we need to pass parameter like this "RiskIdList", RiskIdList[x] means that you are passing argument in run time, but query or store procedure has not dynamic parameter in where clause .