How to get Data from sql server back to checkBoxList..!!

Question

I have a checkBoxList that is binding to a sql column that we can select multiple selection to save those selection to a single row column

Seprited with , like this

| column A |

| , Hello world1 ,|
| , Hello world2 ,|
| , Hello world3 ,|
| , Hello world4 ,|
|__________________|

The question is ..
How to get those data back to the
checkBoxList and make the checkBoxList
Items check thet is retrieve from database

Thank you so much an advance

Answer 1

The following code provides the parts needed to load a CheckedListBox from a SQL-Server database table and how to get at the checked items. A tad rough in that you can tailored it to your needs were rough might mean to some open.

Container

using System;  
  
public class Product  
{  
    public int ProductID { get; set; }  
    public string ProductName { get; set; }  
    public int? SupplierID { get; set; }  
    public int? CategoryID { get; set; }  
    public string QuantityPerUnit { get; set; }  
    public decimal? UnitPrice { get; set; }  
    public short? UnitsInStock { get; set; }  
    public short? UnitsOnOrder { get; set; }  
    public short? ReorderLevel { get; set; }  
    public bool Discontinued { get; set; }  
    public DateTime? DiscontinuedDate { get; set; }  
    public string Items => $"{ProductID}, {ProductName},{Discontinued}";  
    public override string ToString()  
    {  
        return ProductName;  
    }  
  
}  

Data class

using System;  
using System.Collections.Generic;  
using System.Data.SqlClient;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
  
namespace CheckListBoxProducts.Classes  
{  
    public class SqlServerOperations  
    {  
        public static string DefaultCatalog = "NorthWind2020";  
        public static string DatabaseServer = ".\\SQLEXPRESS";  
  
        public static List<Product> ProductsByCategoryIdentifier(int pCategoryIdentifier)  
        {  
            List<Product> productList = new List<Product>();  
  
            var selectStatement =   
                @"SELECT   
                    ProductID,   
                    ProductName,   
                    SupplierID,   
                    QuantityPerUnit,   
                    UnitPrice,   
                    UnitsInStock,   
                    UnitsOnOrder,   
                    ReorderLevel,   
                    Discontinued   
                FROM dbo.Products   
                WHERE CategoryID = @Identifier";  
  
            using (var cn = new SqlConnection { ConnectionString = $"Data Source={DatabaseServer};Initial Catalog={DefaultCatalog};Integrated Security=True" })  
            {  
                using (var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement })  
                {  
  
                    cmd.Parameters.AddWithValue("@Identifier", pCategoryIdentifier);  
                      
                    cn.Open();  
  
                    var reader = cmd.ExecuteReader();  
  
                    while (reader.Read())  
                    {  
                        productList.Add(new Product()  
                        {  
                            ProductID = reader.GetInt32(0),  
                            ProductName = reader.GetString(1),  
                            Discontinued = reader.GetBoolean(8)  
                        });  
                    }  
                }  
  
            }  
  
            return productList;  
        }  
    }  
}  

Form code

using System;  
using System.Collections.Generic;  
using System.ComponentModel;  
using System.Data;  
using System.Drawing;  
using System.Linq;  
using System.Text;  
using System.Threading.Tasks;  
using System.Windows.Forms;  
using CheckListBoxProducts.Classes;  
  
namespace CheckListBoxProducts  
{  
    public partial class Form1 : Form  
    {  
        private List<Product> _products = new List<Product>();  
        public Form1()  
        {  
            InitializeComponent();  
              
            Shown += OnShown;  
        }  
  
        private void OnShown(object sender, EventArgs e)  
        {  
            _products = SqlServerOperations.ProductsByCategoryIdentifier(1);  
  
            ProductCheckedListBox.DataSource = _products;  
        }  
  
        private void GetCheckedButton_Click(object sender, EventArgs e)  
        {  
            if (ProductCheckedListBox.CheckedItems.Count <= 0) return;  
            List<Product> list = new List<Product>();  
            for (int index = 0; index < ProductCheckedListBox.Items.Count - 1; index++)  
            {  
                if (ProductCheckedListBox.GetItemChecked(index))  
                {  
                    list.Add(_products[index]);  
                }  
            }  
  
            var items = string.Join("\n",list.Select(product => product.Items));  
  
            MessageBox.Show(items);  
        }  
    }  
}