Save / get / compare CheckboxList values from database
I'm not sure if what I want is possible, but so far I've had no luck ...
I am using Visual Studio, asp.net and SQL. field in table in varchar (max) type ...
What I want to do is store the CheckBoxList values in the database and get them to compare in a SQL command to filter multiple values from a table in the database.
It's easier to understand when I give an example:
So I have this on the HTML side of the code.
<asp:CheckBoxList ID="CheckBoxList1" runat="server" onselectedindexchanged="CheckBoxList1_SelectedIndexChanged">
<asp:ListItem Value="1">Sales</asp:ListItem>
<asp:ListItem Value="2">Administration</asp:ListItem>
<asp:ListItem Value="3">Help</asp:ListItem>
</asp:CheckBoxList>
This is in the code to save data
protected void CheckBoxList1_SelectedIndexChanged(object sender, EventArgs e)
{
TextBox20.Text = "";
string s = null;
for (int i = 0; i < CheckBoxList1.Items.Count; i++)
{
if (CheckBoxList1.Items[i].Selected)
s += CheckBoxList1.Items[i].Value + ",";
TextBox20.Text = s;
}
}
Now when someone selects as two options like Sales
and Administration
, I want the values 1,2 to be stored in the database in a single column.
Then, extract the values using the show SQL lines that have Sales or Administration / Sales and Administration in the column I want.
I'm not sure if I'm going to do this the right way. But any help would be appreciated ... I read that creating a table with all the values I need is the right way .. but I don't know how it works.
source to share
Firstly, I would recommend using the StringBuilder class to perform string concatenation within a loop, as it is much more efficient -
For routines that perform extensive string manipulation (for example, applications that change the string multiple times in a loop), changing the string repeatedly can result in significant performance degradation. An alternative is to use StringBuilder, which is a mutable string class.
MSDN StringBuilder Class Documentation
I agree with the comment above made by HABO, storing comma separated values in one column is almost always a bad idea. You should probably create a User class. Give properties of the User class like "name" and "roles".
Regarding the database structure for this data, I again agree with HABO, it is recommended to use two tables. User and UserRoles. In the User table, you should store data such as Id, Username, Age, etc., which you can then store in the UserRoles UserRoles UserId store (an identifier from the user table), so a user can be assigned multiple roles.
If you want more details feel free to ask and I will try to provide an example of a schematic etc.
source to share