title: how to cast string data type to decimal and int from data table to sql table
body: <p>I am inserting values to SQL table from a data table and i am getting "converting data type errors". My source file is fixed width and i am using substring to get these values.</p>
  
  <pre><code>Source:
  
      Amount       Name       Id            Date
      -33,167.74   NHP       1,503         05/09/2017
  </code></pre>
  
  <p>This is my code to insert SQL table using data table</p>
  
  <pre><code>private DataTable InsertDataTable()
  {
      DataTable datatable = new DataTable();
      datatable.Columns.Add("Id", typeof(int));
      datatable.Columns.Add("amount", typeof(decimal));
      datatable.Columns.Add("name", typeof(string));
      datatable.Columns.Add("date", typeof(datetime));
  
      //--inserting datatable
  
      foreach (string row in columns)
      {
          datarow["Id"] = Convert.ToInt32(row.Substring(51, 12).Trim().Replace(",", ""));
          datarow["Amount"] = Convert.ToDecimal(row.Substring(0, 9).Trim());
          datarow["date"] = Convert.ToDateTime(row.Substring(12, 18).Trim());
          datarow["name"] = Convert.ToDecimal(row.Substring(20, 35).Trim());
          datatable.Rows.Add(datarow);
      }
  }
  
  private void InsertSqlTable()
  {
      DataTable finaldatatable = InsertDataTable();
      //sql connection--------------------
      using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
      {
          //Set the database table name
          sqlBulkCopy.DestinationTableName = "[dbo].[table]";
          sqlBulkCopy.ColumnMappings.Add("id", "id");
          sqlBulkCopy.ColumnMappings.Add("amount", "amount");
          sqlBulkCopy.ColumnMappings.Add("name", "name");
          sqlBulkCopy.ColumnMappings.Add("date", "date");
      }
  }
  </code></pre>
  
  <p>My target table has below columns and data types</p>
  
  <pre><code>id int
  name string
  amount money
  date  datetime
  </code></pre>
  
  <p>I am getting conversion errors for all the data types except string. also my Id is 1,503 and I want 1503. 
  Can anyone of you help me converting to correct datatype and also eliminating comma from ID ?
  any help is appreciated!</p>