How to write: insert into table ALL except X, if statement

Second question of all time on this community! I am a noob and my weakness are if statements within or amoungst loops and other if statements.

So here is my scenario. This method inserts anything into a database, but I want to validate something. Instead of adding anything into the database, I do not want anything entered in that begins with "LIFT", I want the method to skip over that line and proceed to the next one. Is there a way I can program this into this method? Or do I need to write a new method? Thanks a bunch!

      public bool BatchInsert(string table, string[] values)
{
    string statement = "INSERT INTO " + table + " VALUES(";
    for (var i = 0; i < values.Length - 1; i++)
    {
        if(values[i].Contains("'")){
            values[i] = values[i].Replace("'", "''");
        }
        statement += "'"+values[i]+"', ";
    }
    statement += "'" + values[values.Length - 1] + "');";

    SqlCommand comm = new SqlCommand(statement, connectionPCICUSTOM);
    try
    {
        comm.Connection.Open();
        comm.ExecuteNonQuery();
    }
    catch (Exception e)
    {
        开发者_开发问答KaplanFTP.errorMsg = "Database error: " + e.Message;
    }
    finally
    {
        comm.Connection.Close();
    }

    return true;

}

---

A couple hints. Don't += string types as it slows down performance. I also prefer foreach loops as the code is cleaner and easier to read/less likely to mess up the index. Also make using of the using statement to ensure proper disposal.

Assuming you have a reference to System.Linq you can use the following. I didn't test it but it should work:

public bool BatchInsert(string table, IEnumerable<string> values)
    {
        var sql = new StringBuilder();
        sql.Append("INSERT INTO " + table + " VALUES(");

        var newValues = values.Where(x => !x.StartsWith("LIFT")).Select(x => string.Format("'{0}'", x.Replace("'", "''")));
        sql.Append(string.Join("","", newValues.ToArray()));
        sql.Append(")");

        using (var comm = new SqlCommand(statement, connectionPCICUSTOM))
        {
            try
            {
                comm.Connection.Open();
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                KaplanFTP.errorMsg = "Database error: " + e.Message;
            }
            finally
            {
                comm.Connection.Close();
            }
        }
        return true;
    }

---

If your goal is to iterate through your collection of 'values', leaving values beginning with 'lift' and their corresponding columns untouched, you may have to revise the way your INSERT Query is constructed. You will add columns as needed, instead of assuming that each value will be accounted for. Basically, you will need to use the form:

INSERT INTO tablename (col1, col2...) VALUES (val1, val2...)

For example:

string statement = "INSERT INTO tablename ";
string columns = "(";
string values = "(";
for (var i = 0; i < values.Length - 1; i++)
{
     //if values doesn't contain lift, add it to the statement
     if(!values[i].contains("LIFT")){
          //columnName is a collection of your db column names
          columns += "'"+columnName[i]+"'";
          values += "'"+values[i]+"'";
     }
 }
 columns += ")";
 values += ")";
 statement += columns +" VALUES " + values;

Like some of the comments have stated, this approach opens you up to SQL injections. Use with caution.

EDIT : Sorry, I missed where you said 'starts with 'LIFT'. Revise the .contains() line to the following:

if(!values[i].StartsWith("LIFT")){