如何使用c#将多个复选框列表值插入到数据库中

i use this to select one checkbox to isselected column how i convert this to multi checkboxlist to single column i use many ways more than 3 days without success

我用它来选择一个复选框到isselected列我如何将它转换成多重复选框列表到单列我用了很多方法，超过三天没有成功

 private void BindCheckBoxList()
    {
        DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection(GetConnectionString());
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM boby";
            SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

            sqlDa.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                CheckBoxList1.RepeatColumns = 4; // set the number of columns in the CheckBoxList
                CheckBoxList1.DataSource = dt;
                CheckBoxList1.DataTextField = "Name"; // the items to be displayed in the list items
                CheckBoxList1.DataValueField = "Name"; // the id of the items displayed
                CheckBoxList1.DataBind();

                //Setting the Selected Items in the ChecBoxList based from the value in the database
                //to do this, lets iterate to each items in the list
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString()))
                    {
                        CheckBoxList1.Items[i].Selected = Convert.ToBoolean(dt.Rows[i]["IsSelected"]);
                    }
                }
            }

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        { 
            connection.Close();
        }
    }

    private void Update(string name, bool isSelected)
    {
        SqlConnection connection = new SqlConnection(GetConnectionString());
        SqlCommand cmd;
        string sqlStatement = string.Empty;
        try
        {
            connection.Open();
            sqlStatement = "UPDATE handymen SET IsSelected = @IsSelected WHERE Name = @BizName";
            cmd = new SqlCommand(sqlStatement, connection);
            cmd.Parameters.AddWithValue("@Name", name);
            cmd.Parameters.AddWithValue("@IsSelected", isSelected);
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Insert/Update error";
            msg += ex.Message;
            throw new Exception(msg);

        }
        finally
        {
            connection.Close();
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindCheckBoxList();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string str = string.Empty;

        for (int i = 0; i < CheckBoxList1.Items.Count; i++)
        {
            if (CheckBoxList1.Items[i].Selected)
            {
                str = CheckBoxList1.Items[i].Text;
                Update(str, CheckBoxList1.Items[i].Selected);
            }
        }
        //ReBind the List to retain the selected items on postbacks

        BindCheckBoxList();
    }

4 个解决方案

#1

Well, if you're going to do that, the best way would be to combine them into a bitwise composite, perhaps like this:

如果你要这样做，最好的方法是把它们组合成位复合函数，也许像这样:

int value = 0;      //or short or long, depending on the number of bits
int bitDegree = 1;  //or short or long, depending on the number of bits

for (int i = 0; i < dt.Rows.Count; i++)
{
    if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString())
        && Convert.ToBoolean(dt.Rows[i]["IsSelected"]))
    {
        value += bitDegree;
    }

    bitDegree *= 2;
}

If you can, however, it would be best practice to use multiple Bit columns in your database instead of combining them.

但是，如果可以，最好的做法是在数据库中使用多个位列，而不是组合它们。

#2

Personally I wouldn't do it that way, its bad design. I'd create another table with all the values in there rather than trying to stuff them all into one column.

就我个人而言，我不会那样做，这是糟糕的设计。我将创建另一个包含所有值的表，而不是尝试将它们全部塞到一个列中。

If you HAVE to though you could convert them to ints and separate them with a comma?

如果你必须这么做你可以把它们转换成ints然后用逗号把它们分开?

eg. 1,0,0,1,1 etc

如。1,0,0,1,1)等

#3

Have you considered using a bit mask? Below is an explanation using selected categories as an example (not sure if categories will work in your situation but it illustrates the concept):

您考虑过使用位掩码吗?下面是一个以选定类别为例的解释(不确定类别是否适用于您的情况，但它说明了概念):

Begin by assigning a single binary digit to each value-

首先为每个值分配一个二进制数字—

cat1  cat2  cat3 cat4
----  ----  ---- ----
1     2     4    8

Next you will add an integer column to your main table, we will call it options. When the number is converted to binary each digit will represent whether categories 1, 2, 3 or 4 are set. Example:

接下来，您将向主表添加一个整数列，我们将它称为options。当数字转换为二进制时，每一个数字将表示类别1、2、3或4是否设置。

5 = 0101 in binary = cat1 is set, cat2 is not set, cat3 is set, cat4 is not set

id | name         | options
---------------------------
1  | name1        | 5
2  | name2        | 2
3  | name3        | 7
4  | name4        | 6

We can now use bitwise operations against the options column to determine what options are allowed. Examples:

我们现在可以对options列使用位操作来确定允许哪些选项。例子:

To get all records that have category 2 set when we don't care about the other categories, perform the following operation:

当我们不关心其他类别时，要获取所有具有第2类的记录，执行以下操作:

2 & options = 2

2 &选项= 2

This would return records 2,3 and 4.

这将返回记录2、3和4。

To get all records that have cat2 and cat3 set we would perform the following bitwise operation:

要获取所有有cat2和cat3集的记录，我们将执行以下位操作:

6 & options = 6

6 &选项= 6

This would return records 3 and 4

这将返回记录3和4

To get all records that have category 1 and 3 set we would perform the following bitwise operation:

要获得第1类和第3类的所有记录，我们将执行以下的位操作:

5 & options = 5

5和选项= 5

This would return records 1 and 3.

这将返回记录1和3。

ONLY category 3 set:

只有3级设置:

4 | Options = 4

|选项= 4

Category 3 NOT set:

3级没有设置:

4 & options = 0

4 &选项= 0

This is probably a hard concept to grasp so please let me know if you have any questions. It seems to me that it might be the simplest way to accomplish what you are trying to do once you can grasp the concept.

这可能是一个很难理解的概念，如果你有任何问题，请告诉我。在我看来，一旦你掌握了这个概念，这可能是完成你想做的事情的最简单的方法。

#4

Try this

试试这个

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("Data Source=ANSA-PC\\SQLEXPRESS;Initial Catalog=pms;Integrated Security=True");
    String str = "";

    for (int i = 0; i <=CheckBoxList1.Items.Count-1; i++)
    {

        if (CheckBoxList1.Items[i].Selected)
        {

            if (str == "")
            {
                str = CheckBoxList1.Items[i].Text;
            }
            else
            {
                str += "," + CheckBoxList1.Items[i].Text;

            }

        }
    }
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into aa(a)values('" +str + "')", con);
    cmd.ExecuteNonQuery();
    ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('ansari:u also got it')</script>");
}

#1