如何通过值列表在C＃中的数据绑定参数 [英] How to pass a list of values as parameter in c# databinding

问题描述

数据绑定

 私有数据集BindGridView（列表＆LT; INT＆GT;用户ID）
   {的DataSet DS =新的DataSet（）;
     字符串MysqlStatement =SELECT OrganisationID，OrganisationName FROM tbl_organisation WHERE OrganisationID = @ OrganisationID;
    了MySQLParameter [] =参数新了MySQLParameter [1];
     的foreach（在用户ID VAR ORGID）
    {
        参数[0] =新了MySQLParameter（@ OrganisationID，MySqlDbType.Int32）;
        参数[0] .value的= ORGID;
        DS = server.ExecuteQuery（CommandType.Text，MysqlStatement，参数）;
        ds.Merge（DS）;
    }
    Grid_Organisationtable.DataSource = DS;
    Grid_Organisationtable.Columns [0] =。可见假的;
    Grid_Organisationtable.DataBind（）;
    返回DS;
}
 

我传递值的列表，数据绑定方法。我通过与organistionID列表。我应该怎么办的数据绑定。它返回一个空的数据集。我传递的名单像60,61,62值。我得到的错误是

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

解决方案

您不能在ID的列表传递到这样一个SQL查询。因为它代表你的 SELECT 语句是希望传递的是由 WHERE 子句中使用单个值参数。结果
如果你坚持使用这个查询，你将需要遍历列表，并在同一时间在每个值为1传递，使一个单独的数据库调用，例如：

 字符串MysqlStatement =SELECT OrganisationID，OrganisationName FROM tbl_organisation WHERE OrganisationID = @ OrganisationID;
的foreach（在用户ID VAR ID）
{    了MySQLParameter [] =参数新了MySQLParameter [1];
    参数[0] =新了MySQLParameter（@ OrganisationID，MySqlDbType.Int32）;
    参数[0] .value的= ID;
    DS = server.ExecuteQuery（CommandType.Text，MysqlStatement，参数）;
    ......添加结果用来数据绑定到您的网格中的另一个列表
}
 

有一个更好的替代方法是使用 where ...在... 条款，并通过所有的ID一气呵成，因为这只会火一个数据库调用和有没有需要的参数传递：

 字符串MysqlStatement =的String.Format（SELECT OrganisationID，OrganisationName FROM tbl_organisation WHERE OrganisationID IN（{0}）的string.join（，，用户id））;
DS = server.ExecuteQuery（CommandType.Text，MysqlStatement，NULL）;
 

您第三个选择是编写接受XML字符串重新列表的presentation，并用它来查询数据库虽然这是较为复杂的存储过程。这种方式可以让你坚持使用参数。

请注意：这通常是非常不好的做法，不是在动态数据传递的，因为这可以让你打开到SQL注入攻击，但在这种情况下，由于数据从传递到你的方法列表参数来参数可以是相当确保它不会乱用您的数据库。

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;
}

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

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

解决方案

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
}

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);

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.

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屋！