0

this is my code in saving listview items into the mysql database in c#... how can i skip a listview row if it has no data inserted on it? Im kind of lost as to where I am going to insert the If Statement .. Please anyone?

for (int cnt = 0; cnt <= lv1.Items.Count - 1; cnt++)
{
    string query = "insert into results(sid,c_id)values('" + _studid + "','" + lv1.Items[cnt].SubItems[2].Text + "')";

    conn.Open();
    MySqlCommand cmd = new MySqlCommand(query, conn);
    cmd.ExecuteNonQuery();
    conn.Close();


}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Justin Aizengard
  • 92
  • 1
  • 12

3 Answers3

2

Extract all the code to build the MySqlCommand from the loop and initialize two parameters, the first one has always the same value, while the second one varies. So, inside the loop check for the empty values in the listview subitem and if not empty, set the value for the second parameter. Now you could execute it and the subsequent loops need to change only the second parameter's value.

string query = "insert into results(sid,c_id)values(@id, @cid))";
MySqlCommand cmd = new MySqlCommand(query, conn);
cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = _stuid;
cmd.Parameters.Add("@cid", MySqlDbType.Int32);

conn.Open();

for (int cnt = 0; cnt < lv1.Items.Count; cnt++)
{
    string subItem = lv1.Items[cnt].SubItems[2].Text;
    if(!string.IsNullOrWhiteSpace(subItem))
    { 
        cmd.Parameters["@cid"].Value = Convert.ToInt32(subItem);
        cmd.ExecuteNonQuery();
    }

}
conn.Close();

In this way you open the connection just one time and prepare the command before executing the loop. The command uses a parameterized query and thus it is safe from Sql Injection.
Look also at how the sql string, without the quotes and the string concatenation, is more readable.

Final notes:
I am assuming the the c_id field is a numeric field.
I have changed the for...loop to a simpler form.
This kind of code leads to using a transaction to keep everything atomic (meaning, if one insert fails everything should be rolled back without any change to the database). For more info look at this question

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Yes, instead of concatenating the values in the string (Sql Injection, parsing problems with strings containing single quotes, parsing date formats, decimal separators and so on) you place a placeholder for your value, prepare a parameter with the type, size, precision required and let the database code to figure out how to correctly use all that information – Steve Sep 03 '14 at 15:53
0

You can simply check if the value of your item is empty

I recommand you read about parameterized sql querys http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

if(lv1.Items[cnt].SubItems[2].Text== string.Empty)
{
  continue;
}
phadaphunk
  • 12,785
  • 15
  • 73
  • 107
0

This code skips insert into database if there is no data to add, query uses now parameters: You can open and close connection outside the loop, it definitely improves performane

conn.Open();
for (int cnt = 0; cnt <= lv1.Items.Count - 1; cnt++)
{
     if(lv1.Items[cnt].SubItems[2].Text=="")
         continue;
MySqlCommand m = new MySqlCommand(readCommand);
m.Parameters.Add(new MySqlParameter("@id", _studid));
m.Parameters.Add(new MySqlParameter("@val", lv1.Items[cnt].SubItems[2].Text));

    string query = "insert into results(sid,c_id)values(@id, @val)";

    MySqlCommand cmd = new MySqlCommand(query, conn);
    cmd.ExecuteNonQuery();
}
conn.Close();
cinek
  • 1,888
  • 2
  • 13
  • 14