How to store multiple checked Checkbox values in DB using ASP.NET MVC

Question

I am developing a project using ASP MVC and stored procedures (SQL Server), I want to store checked checkbox items in database. I've tried to add a List<string> type in model in order to access these value and then store them in the database.

The problem that the relational databases are designed specifically to store one value per row/column combination. In order to store more than one value, I must serialize my list into a single value for storage, then deserialize it upon retrieval.

That's my view markup:

               <h6>Items</h6>
                    <ul>
                        <li>
                            <label class="anim">
                                <input type="checkbox" class="checkbox" value="Soups" name="Items">
                                <span>Soups</span>
                            </label>
                        </li>
                        <li>
                            <label class="anim">
                                <input type="checkbox" class="checkbox" value="Burger" name="Items" >
                                <span>Burger</span>
                            </label>
                        </li>
                        <li>
                            <label class="anim">
                                <input type="checkbox" class="checkbox" value="Drinks" name="Items">
                                <span>Drinks</span>
                            </label>
                        </li>
                        <li>
                            <label class="anim">
                                <input type="checkbox" class="checkbox" value="Desserts" name="Items">
                                <span>Desserts</span>
                            </label>
                        </li>
                    </ul>

Method AddBestellung:

try
{
    using (SqlConnection con = new SqlConnection(Connection()))
    {
        using (SqlCommand cmd = new SqlCommand("AddNewBestellung", con))
        {
            foreach(var item in bs.Items)
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Items", item);
            }

            // Another saved parameters...

            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();

            if (i >= 1)
                return true;
            else
                return false;
        }
    }
}
catch(Exception ex)
{
    string e = ex.Message;
    return false;
}

private static string Connection()
{
    return ConfigurationManager.ConnectionStrings["deliverycon"].ToString();
}

Model:

public List<string> Items { get; set; }

you could try using a Flags Enumeration msdn.microsoft.com/en-us/library/… — Rich Bryant
– Rich Bryant, Commented Oct 17, 2017 at 12:03
Actually, since you're using SQL Server, you could also store your array of checkbox values as XML. — Rich Bryant
– Rich Bryant, Commented Oct 17, 2017 at 12:05
@RichBryant thanks but can you explain more how it will be as XML — Exact
– Exact, Commented Oct 17, 2017 at 12:08

Thomas · Accepted Answer · 2017-10-17 22:11:38Z

1

You shouldn't serialize/deserialize. This will be painful to use afterward.

Imagine you need to retrieve objects that have items 1 and 5 checked. If it's serialized as a string it's less efficient and easy that to store it as follow.

Let's say you have a "Person" table and you want to check the list of "Consoles" they own.

You'll have table person :

id int primary key
Name varchar not null

and the table console

id int primary_key
name varchar not null

and the table to store the console owned :

owned_console

id int primary key
person_id int (foreign key => person(id))
console_id int (foreign key => console(id))

In your code, you will insert a record per checkbox checked.

Person:

(1)Thomas
(2)Pierre

Console:

(1)NES
(2)MegaDrive
(3)NeoGeo

owned_console:

(1)(1)
(1)(2)
(1)(3)
(2)(1)
(2)(1)

And then you can do things like :

SELECT * 
FROM   person p
INNER  JOIN owned_console oc
ON     p.id = oc.person_id
WHERE  oc.console_id IN (3,1);

edited Oct 17, 2017 at 22:11

answered Oct 17, 2017 at 12:16

Thomas

1,4131 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Exact Over a year ago

thanks but it is a lil complicated with creating new table, I want only only to store more than one in a column,

Rich Bryant Over a year ago

I disagree, it's very simple. See other answer. Next I'll serialize to XML so you can see how easy that is, too.

marc_s Over a year ago

@Exact: this IS the proper, relational way of doing things. You should never ever store multiple values into a single cell - just say NO! to comma-separated values in a cell - you'll be looking at a maintenance nightmare down the road! Do it properly - from the beginning !

marc_s Over a year ago

@Thomas: Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (25 years ago!) and its use is discouraged

marc_s Over a year ago

@Exact: the proper relational way to store multiple values is a separate table (with one row for each value) which handles your "multiple values" for a single entity

|

Rich Bryant · Accepted Answer · 2017-10-18 08:56:21Z

0

Flags Enumeration -

[Flags]
public enum ItemStorage
{
    Soups = 1,
    Burger = 2,
    Drinks = 4,
    Dessert = 8,
    Cheese = 16
}

I added the Cheese just to emphasise the binary nature of the Flags enum.

And here's your other code -

public class Stuff
{
    private List<string> Items;
    private static string Connection()
    {
        return ConfigurationManager.ConnectionStrings["deliverycon"].ToString();
    }

    public bool DoStuff()
    {
        try
        {
            using (SqlConnection con = new SqlConnection(Connection()))
            {
                using (SqlCommand cmd = new SqlCommand("AddNewBestellung", con))
                {

                    cmd.Parameters.AddWithValue("@Items", (int)ConstuctEnumValueFromList(Items));
                    //other saved parameters...

                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    con.Close();

                    if (i >= 1)
                        return true;
                    else
                        return false;
                }
            }
        }
        catch (Exception ex)
        {
            string e = ex.Message;
            return false;
        }
    }

    private ItemStorage ConstuctEnumValueFromList(List<string> list)
    {
        var result = new ItemStorage();
        if (list.Any())
        {
            var separatedList = string.join(",", list)
            bool success = Enum.TryParse<ItemStorage>(separatedList, out result)
        }
        return result;
    }
}

In this case, you can simply store the Items field as a single integer and cast it back to ItemStorage when retrieving like so -

int i = 1 + 2 + 4 + 8;
var items = (ItemStorage)i;
Console.WriteLine(items.ToString());

This should give you "Soups, Burger, Drinks, Desserts";

edited Oct 18, 2017 at 8:56

answered Oct 17, 2017 at 12:47

Rich Bryant

91710 silver badges29 bronze badges

9 Comments

Exact Over a year ago

Thanks, i have to write the Flags enumeration in model ?

Rich Bryant Over a year ago

Or simply an integer value. Either is fine.

Exact Over a year ago

Ok I will try and let you know

Exact Over a year ago

Following your suggestion, when I check more than 1 value, only the first checked one is stored in DB

Rich Bryant Over a year ago

That's interesting. Try putting a breakpoint in the ConstuctEnumValueFromList(List<string> list) method and stepping through.

|

Rich Bryant · Accepted Answer · 2017-10-17 13:28:14Z

XML Serialization

First, make sure you're familiar with this document. This method requires that you store your Items collection to an XML column in SQL Server. It also has the advantage that you can query against that column, provided you're not afraid of SQLXML and XPath. Many developers are.

Once you have that, everything looks very similar (if a little simpler).

public class Stuff
{
    private List<string> Items;
    private static string Connection()
    {
        return ConfigurationManager.ConnectionStrings["deliverycon"].ToString();
    }

    public bool DoStuff()
    {
        try
        {
            using (SqlConnection con = new SqlConnection(Connection()))
            {
                using (SqlCommand cmd = new SqlCommand("AddNewBestellung", con))
                {

                    cmd.Parameters.AddWithValue("@Items", ConstructXmlFromList(Items));
                    //other saved parameters...

                    con.Open();
                    int i = cmd.ExecuteNonQuery();
                    con.Close();

                    if (i >= 1)
                        return true;
                    else
                        return false;
                }
            }
        }
        catch (Exception ex)
        {
            string e = ex.Message;
            return false;
        }
    }

    public static T FromXML<T>(string xml)
    {
        using (var stringReader = new StringReader(xml))
        {
            var serializer = new XmlSerializer(typeof(T));
            return (T)serializer.Deserialize(stringReader);
        }
    }

    public string ToXML<T>(T obj)
    {
        using (var stringWriter = new StringWriter(new StringBuilder()))
        {
            var xmlSerializer = new XmlSerializer(typeof(T));
            xmlSerializer.Serialize(stringWriter, obj);
            return stringWriter.ToString();
        }
    }

    private string ConstructXmlFromList(List<string> list)
    {
        return ToXML(list);
    }
}

And again, it's easy to rehydrate the XML fragment into the List<string> that you started with. You'd simply use

var myList = FromXML<List<string>>(fieldValue);

Collectives™ on Stack Overflow

How to store multiple checked Checkbox values in DB using ASP.NET MVC

3 Answers 3

9 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related