Advice about insert into SQLCE

Question

i am inserting about 1943 records by these function into SQLCE.This is my insert function.Parameters come from StringReader(string comes from webservice).This function executes 1943 times and takes about 20 seconds.I dropped table's indexes,what can i do to improve it?I create just 1 time mycomm and sqlceresultset.

Public Function Insert_Function(ByVal f_Line() As String, ByRef myComm As SqlCeCommand, ByRef rs As SqlCeResultSet) As String
    Try
        Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
        rec.SetInt32(0, IIf(f_Line(1) = "", DBNull.Value, f_Line(1)))
        rec.SetInt32(1, IIf(f_Line(2) = "", DBNull.Value, f_Line(2)))
        rec.SetInt32(2, IIf(f_Line(3) = "", DBNull.Value, f_Line(3)))
        rec.SetInt32(3, IIf(f_Line(4) = "", DBNull.Value, f_Line(4)))
        rec.SetValue(4, IIf(f_Line5(5) = "", DBNull.Value, f_Line(5)))
        rs.Insert(rec)
        rec = Nothing
    Catch ex As Exception
        strerr_col = ex.Message
    End Try
    Return strerr_col
End Function

Answer 1

I think you get exception(s) when you are trying to set DBNull.Value calling SetInt32. I get managed to insert ten thousand records in under one second using this code :

      using (var cn = new SqlCeConnection(ConnectionString))
  {
   using (var cmd = new SqlCeCommand())
   {
    cn.Open();
    cmd.Connection = cn;
    cmd.CommandText = "TableName";
    cmd.CommandType = CommandType.TableDirect;

    using (var rs = cmd.ExecuteResultSet(ResultSetOptions.None))
    {
     var record = rs.CreateRecord();

     using (var reader = new StringReader(input))
     {
      string line;
      while ((line = reader.ReadLine()) != null)
      {
       var index = 0;
       var values = line.Split(new[] { ',' });

       record.SetValue(index, values[index++] == string.Empty ? (object)DBNull.Value : values[index - 1]);
       record.SetValue(index, values[index++] == string.Empty ? (object)DBNull.Value : values[index - 1]);
       record.SetValue(index, values[index++] == string.Empty ? (object)DBNull.Value : values[index - 1]);
       record.SetValue(index, values[index++] == string.Empty ? (object)DBNull.Value : values[index - 1]);
       record.SetValue(index, values[index++] == string.Empty ? (object)DBNull.Value : values[index - 1]);
       rs.Insert(record);
      }
     }
    }
   }
  }