如何在C＃数据绑定中将值列表作为参数传递 [英] How to pass a list of values as parameter in c# databinding

问题描述

数据绑定

private DataSet BindGridView(List<int> userids)
   { DataSet ds = new DataSet();
     string MysqlStatement = "SELECT OrganisationID, OrganisationName FROM tbl_organisation WHERE OrganisationID=@OrganisationID";
    MySqlParameter[] param = new MySqlParameter[1];     
     foreach (var OrgID in userids)
    {
        param[0] = new MySqlParameter("@OrganisationID", MySqlDbType.Int32);
        param[0].Value = OrgID;
        ds = server.ExecuteQuery(CommandType.Text, MysqlStatement, param);
        ds.Merge(ds);
    }
    Grid_Organisationtable.DataSource = ds;
    Grid_Organisationtable.Columns[0].Visible = false;
    Grid_Organisationtable.DataBind();
    return ds;
}

我将值列表传递给数据绑定方法。我传递了带有OrganistionID的列表。我应该如何进行数据绑定。它返回一个空的数据集。我正在传递带有60,61,62之类的值的列表。我遇到的错误是

I pass a list of values to the databinding method. I pass a list with organistionID. how should I do the databinding. It is returning an empty dataset. I am passing the list with values like 60,61,62. The error I am getting is

无法转换类型为'System.Collections.Generic.List`1 [System.Int32]'的对象键入 System.IConvertible。

Unable to cast object of type 'System.Collections.Generic.List`1[System.Int32]' to type 'System.IConvertible'.

推荐答案

您不能在列表中传递id以这种方式进入SQL查询。您的 SELECT 语句原样希望传递的参数是 WHERE 子句使用的单个值。

如果要坚持使用此查询，则将需要遍历列表并一次传递每个值，并进行单独的数据库调用，例如：

You can't pass in a list of id's into an SQL query in this way. Your SELECT statement as it stands is expecting the parameter passed in to be a single value to be used by the WHERE clause.
If you were to stick with this query you will need to loop through your list and pass in each value one at a time and make a seperate database call, for example:

string MysqlStatement = "SELECT OrganisationID, OrganisationName FROM tbl_organisation WHERE OrganisationID=@OrganisationID";
foreach(var id in userids)
{

    MySqlParameter[] param = new MySqlParameter[1];     
    param[0] = new MySqlParameter("@OrganisationID", MySqlDbType.Int32);
    param[0].Value = id;      
    ds = server.ExecuteQuery(CommandType.Text, MysqlStatement, param);
    ... add result to another list which is used to databind to your grid
}

一个更好的选择是使用 WHERE ... IN ... 子句并一次性传递所有ID，因为这只会触发一个数据库调用并且不需要传递参数：

A better alternative would be to use a WHERE...IN... clause and pass all your id's in one go as this will only fire one database call and there is not need to pass in parameters:

string MysqlStatement = string.Format("SELECT OrganisationID, OrganisationName FROM tbl_organisation WHERE OrganisationID IN ({0})", String.Join(",", userIds));
ds = server.ExecuteQuery(CommandType.Text, MysqlStatement, null);

您的第三个选择是编写一个存储过程，该存储过程接受列表的xml字符串表示形式并使用此形式查询数据库，尽管这要复杂一些。这种方式可以让您坚持使用参数。

Your third option would to write a stored procedure that accepted an xml string representation of your list and used this to query the database although this is somewhat more complicated. This way allows you to stick to using a parameter.

注意：通常不建议将动态数据作为参数传递，这是非常糟糕的做法，因为这可能会使您对Sql开放注入攻击，但是在这种情况下，由于数据来自传递到您方法中的List参数，因此您可以肯定地确定它不会弄乱数据库。

Note: it is usually very bad practice to not pass in dynamic data as parameters as this can leave you open to Sql injection attacks but in this case as the data is coming from a List parameter passed into your method you can be fairly sure that it isn't going to mess with your database.

这篇关于如何在C＃数据绑定中将值列表作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！