0

i have a list of String Data i would Like to Store in my MySQL database Table in Column "Categories".

Is there a way to store it at a go because its a long list.` 

  public Class PickerView{
  List<string> CategoriesPicker = new List<string>();
  public Button SaveItemsButton = new Button();
  public  PickerView()
    {

        CategoriesPicker.Items.Add("Hotels & Travel");
        CategoriesPicker.Items.Add("Restaurant");
        CategoriesPicker.Items.Add("Wholesalers");
        CategoriesPicker.Items.Add("Automotives");
        CategoriesPicker.Items.Add("Pets");
        CategoriesPicker.Items.Add("Musical Instruments Services");
        CategoriesPicker.Items.Add("Specialty Food");
        CategoriesPicker.Items.Add("Food");
        CategoriesPicker.Items.Add("Boutique");
        CategoriesPicker.Items.Add("Home & Gardens");
        CategoriesPicker.Items.Add("Shopping");
        CategoriesPicker.Items.Add("Education");
        CategoriesPicker.Items.Add("Books,Mags,Music & Video");
        CategoriesPicker.Items.Add("Fashion");
        CategoriesPicker.Items.Add("Event Planning & Services");
        CategoriesPicker.Items.Add("Arts & Craft");
        CategoriesPicker.Items.Add("Local Services");
        CategoriesPicker.Items.Add("NightLife(Bars..)");

        SaveItemsButton.Clicked += SavedItemsButton_Clicked
    }
    private void SavedItemsButton_Clicked(object sender, System.EventArgs e)
    {
         string sqlstring = "server=; port= ; user id =;Password= ;Database=test;";
        MySqlConnection conn = new MySqlConnection(sqlstring);
        try
        {
            conn.Open();
        }
        catch (MySqlException ex)
        {
            throw ex;
        }
        string Query = "INSERT INTO test.maintable (Categories)values('" +(**//I DONT KNOW WHAT TO WRITE HERE TO SAVE ALL AT ONCE**) + "');";
        MySqlCommand cmd = new MySqlCommand(Query, conn);
        cmd.ExecuteReader();
        conn.Close();
    }
 }`

How do i save the list of items in CategoriesPicker in database when SaveItemsButton is clicked.

Improve this question
8
  • What do you mean by "store it at a go"? What have you tried and what isn't working? Commented Feb 6, 2019 at 17:08
  • 1
    One possible option would be the MySqlBulkLoader Commented Feb 6, 2019 at 17:11
  • @David as in store everything at once, not one at a time Commented Feb 6, 2019 at 17:19
  • @loisbotwe: In that case the aforementioned bulk loader class is probably the best bet. Another option might be to create a bunch of insert statements and then execute them all in a batch, potentially as one big SQL command separated by semicolons if the driver supports that. Commented Feb 6, 2019 at 17:46
  • @stuartd My data is not in a file, its in a list in c#. Commented Feb 6, 2019 at 17:54

3 Answers 3

Reset to default
1

Simple use the mysql insert into statement.

insert into tbl1 values (1, 'Name1', 1, null), (2, 'Name2', 2, null), (3, 'Name3', 1, null);

(3, 'Name3', 1, null) is of course the structure of tbl1 This will work in any language you use or even in comand line

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

1 Comment

are you saying there is no way to store it at once ,except individually.
0

MySQL (and MariaDB) don't have list-valued column types since that goes against first normal form in database design.

What you do instead is what @nbk suggested and, using whatever database framework you chose, insert multiple rows. Here's a sample table definition for MariaDB/MySQL if you don't have one already:

Microsoft's current recommended way of interacting with a database is Entity Framework Core. The documentation there can help you with connecting to a database, creating a table, adding rows to a table and saving all of that to the database.

Hope that helps!

Improve this answer

Comments

0

I added using blocks to your code to ensure that your database objects are closed and disposed even if there is an error.

Set the query text only once, it stays the same for each iteration of the loop. Likewise the parameter is added outside the loop. Only the value of the parameter is changed inside the loop. We use .ExecuteNonQuery (not reader) for Insert, Update or Delete. .ExecuteReader is used for returning data.

    private List<string> CategoriesPicker = new List<string>();
    //add all the items to the list
    private void SavedItemsButton_Clicked(object sender, System.EventArgs e)
    {
        string sqlstring = "server=; port= ; user id =;Password= ;Database=test;";
        using (MySqlConnection conn = new MySqlConnection(sqlstring))
        {
            string Query = "INSERT INTO test.maintable (Categories)values(@Category);";
            using (MySqlCommand cmd = new MySqlCommand(Query, conn))
            {
                cmd.Parameters.Add("@Category", MySqlDbType.VarChar);
                try
                {
                    conn.Open();
                }
                catch (MySqlException ex)
                {
                    throw ex;
                }
                foreach (String item in CategoriesPicker)
                {
                    cmd.Parameters["@Category"].Value = item;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.