CODEWITHSUNDEEP
c#mysqllistviewvisual-studio-2012
Ilkhom Toktiev
Ilkhom Toktiev

Reputation: 13

What is a syntax of transferring data from listView (Visual studio, c#) to another database?

I have data sent from SQL Server in my C# application. Data contains in a listView (in 2 columns: pay_type and pay_amount). Here is code how's it done:

private bool isDataSetValid(DataSet ds)
{
    return (ds != null) && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0;
}

public void button1_Click(object sender, EventArgs e) {
            try
            {
                DataSet ds = this.getDataToTable();
                if (ds != null)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                    this.listView1.Items.Add(new ListViewItem(
                                new String[] {dr[0].ToString(),dr[1].ToString() }))

                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.Print(ex.Message);
            }
        }

And here I'm trying to send this data to another database:

String insertSQL = "INSERT INTO SALES_TABLE(pay_type, pay_amount) 
VALUES (" + listView1.Items.ToString() + "," + listView1.Items.ToString() + ");";

What should I use instead of listView1.Items.ToString() in order to MySQL command work properly? Any kind of help would be greatly appreciated..

Upvotes: 1

Views: 279

Answers (2)

MajkeloDev
MajkeloDev

Reputation: 1661

You can't insert like that. In sql such thing can be achieved by select into clause but in Your case it won't help. Go with a foreach loop like below:

 using (var conn = new SqlConnection())
        {
            foreach (var item in listView1.Items)
            {
                string firstValue = item.SubItem.First();
                string secondValue = item.Subitem.Last();
                String insertSQL = @"INSERT INTO SALES_TABLE(pay_type, pay_amount) 
                                VALUES (@val1 , @val2)";
                SqlCommand cmd = new SqlCommand(insertSQL, conn);
                cmd.Parameters.AddWithValue("@val1",firstValue);
                cmd.Parameters.AddWithValue("@val2", secondValue);
                cmd.ExecuteNonQuery();
            } 
        }

PLEASE NOTE

Never use a string to add parameters to the query. It makes Your application very vulnerable to sql injection attacks.

Upvotes: 0

Matias Cicero
Matias Cicero

Reputation: 26281

listView1.Items is of type ListViewItemCollection, that is, a collection of ListViewItem.

Now, for each ListViewItem in this collection, you want to insert a row in your SALES_TABLE.

The ToString() method on a collection will not print out each element, nor is something you can use in this case.

Let's start by creating our base string:

StringBuilder insertSQL = new StringBuilder("INSERT INTO SALES_TABLE(pay_type, pay_amount) VALUES ");

Then, we have to iterate over our ListViewItem and append each item to our insertSQL string

foreach(ListViewItem item in view.Items)
{
    //I am using LINQ First() and Last() to get the first and the second items, provided you have only 2 elements on the list
    insertSQL.AppendFormat("({0}, {1}),", item.SubItems.First(), item.SubItems.Last());
}

Lastly, we have to remove the last trailing ,:

insertSQL.Remove(insertSql.Length - 1, 1);

Final code:

private string CreateInsertSQL(ListView view) {
   StringBuilder insertSQL = new StringBuilder("INSERT INTO SALES_TABLE(pay_type, pay_amount) VALUES ");
   foreach(ListViewItem item in view.Items)
   {
       insertSQL.AppendFormat("({0}, {1}),", item.SubItems.First(), item.SubItems.Last());
   }
   insertSQL.Remove(insertSQL.Length - 1, 1);
   return insertSQL.ToString();
}

This will generate a string like the following one:

"INSERT INTO SALES_TABLE(pay_type, pay_amount) VALUES (type1, amount1),(type2,amount2),(type3,amount3)"

You may call this method like this:

string insertCommand = CreateInsertSQL(this.listView1);

NOTE: I am using a StringBuilder for performance reasons, but you may use a normal string.

Upvotes: 2

Related Questions