title: Insert class values into SQL database using C#
body: <p>I am working on a C# project that is going to be used to read in values from an excel spreadsheet, clean the values to make sure the data is correct, then insert said values into a database table. That being said I am also fairly new to C# (this is my first "big" project"), so with the help of a coworker we have two out of the three requirements coded. </p>
  
  <p>With that background out of the way, I am having trouble calling the class that has the values stored so I can insert them into the required table. Here is the code that I have so far:</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.ComponentModel;
  using System.Data;
  using System.Drawing;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  using System.Windows.Forms;
  using Microsoft.Office.Interop.Excel;
  using System.Text.RegularExpressions;
  
  namespace ReadExcel
  {
    public partial class Form1 : Form
    {
      public Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application() { DisplayAlerts = false, Visible = false };
  
      public List&lt;Attorney&gt; listOfAttys = Helpers.getAttorneys();
      public List&lt;LegalTransactionRec&gt; listofTransactions = Helpers.insertRecords();
  
      public Form1()
      {
          InitializeComponent();
      }
  
      private void Form1_Load(object sender, EventArgs e)
      {
          setAttyList();
      }
  
  
      private void setAttyList()
      {
          foreach (var item in listOfAttys.Select(x =&gt; x.Caption))
              cmbAtty.Items.Add(item);
  
  
      }
  
  
      public void btnRun_Click(object sender, EventArgs e)
      {
          Workbook workbook = excelApp.Workbooks.Open(txtbxFilename.Text); 
  
          try
          {
  
              Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1);
              Microsoft.Office.Interop.Excel.Range xlRange = worksheet.UsedRange;
  
              int rowCount = xlRange.Rows.Count;
              int colCount = xlRange.Columns.Count;
  
              List&lt;LegalTransactionRec&gt; lstTran = new List&lt;LegalTransactionRec&gt;();
  
  
                  for (int i = 1; i &lt;= rowCount; i++)
                  {
                      for (int j = 1; j &lt;= colCount; j++)
                    {
                         lstTran.Add(new LegalTransactionRec()
                          {
  
                              AccountNumber = CleanString(xlRange.Cells[i, 1].Value2.ToString()),
                              CostAmount = TryToParse(CleanAmount(xlRange.Cells[i, 3].Value2.ToString())),
                              SSN = CleanString(xlRange.Cells[i, 6].Value2.ToString()),
                              TransactionDate = CheckDate(xlRange.Cells[i, 2].Value2.ToDate()),
                              Description = xlRange.Cells[i, 8].Value2.ToString(),
                              TransactionCode = CheckNull(xlRange.Cells[i, 2].Value2.ToInt())
                          });
                    }
                  }
  
              if (validateHeader(worksheet))
              {
  
  
  
  
              }
          }
          catch (Exception ex)
          { 
          }
  
  
          excelApp.Quit();
      }
  
      private void btnLoad_Click(object sender, EventArgs e)
      {
          txtbxFilename.Text = null;
  
          System.IO.Stream myStream = null;
          OpenFileDialog openFileDialog1 = new OpenFileDialog();
  
          openFileDialog1.InitialDirectory = "C:\\";
          openFileDialog1.Filter = "Excel files (*.xlsx)|*.xlsx";
          openFileDialog1.FilterIndex = 2;
          openFileDialog1.RestoreDirectory = true;
  
          if (openFileDialog1.ShowDialog() == DialogResult.OK)
          {
              try
              {
                  if ((myStream = openFileDialog1.OpenFile()) != null)
                  {
                      txtbxFilename.Text = openFileDialog1.FileName;
                      myStream.Close();
                      myStream.Dispose();
                  }
              }
              catch (Exception ex)
              {
                  MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
              }
          }
      }
  
  
      private bool validateHeader(Microsoft.Office.Interop.Excel.Worksheet Worksheet)
      {
          if (Worksheet == null)
              return false;
  
          bool isValid = false;
  
          //get header row
          //check all cell values
  
          return isValid;
      }
  
      public int CheckNull(int intVal)
      {
          int cleanValue;
          if (intVal == null)
          {
              throw new System.ArgumentException("Value cannot be null", "original");
          }
          else
          {
              cleanValue = intVal;
          }
  
          return cleanValue;
      }
  
      public string CleanString(String strVal)
      {
          string cleanValue;
          if (strVal == null)
          {
              throw new System.ArgumentException("Value cannot be null", "original");
          }
          else
          {
              cleanValue = Regex.Replace(strVal," ", "").Replace("$","").Replace("-","");
          }
  
          return cleanValue;
      }
  
      public string CleanAmount(String amt)
      {
          string cleanAmt;
          if (amt == null)
          {
              throw new System.ArgumentException("Value cannot be null", "original");
          }
          else
          {
              cleanAmt = Regex.Replace(amt, "(", "-").Replace(")", "").Replace("$","").Replace("[^0-9]+", "").Replace(" ","");
          }
          return cleanAmt;
      }
  
      public static void TryToParse(string strAmt)
      {
          decimal decAmt;
  
          bool result = Decimal.TryParse(strAmt, out decAmt);
  
      }
  
      public DateTime CheckDate(DateTime tranDate)
      {
          DateTime date;
  
          if (tranDate == null)
          {
              throw new System.ArgumentException("Value cannot be null", "original");
          }
          else
          {
              date = tranDate;
          }
  
          return date;
      }
  
  }
  
  public class Attorney
  {
      public string AttorneyID { get; set; }
      public int OrganizationID { get; set; }
      public string AttorneyName { get; set; }
      public string Caption { get; set; }
  }
  
  public class LegalTransactionRec
  {
      public string AccountNumber { get; set; }
      public string CostAmount { get; set; }
      public string SSN { get; set; }
      public int BatchID { get; set; }
      public Attorney Attorney { get; set; }
      public DateTime TransactionDate { get; set; }
      public string Description { get; set; }
      public int TransactionCode { get; set; }
  }
  
  
  public class ReviewOutput
  {
  }
  
  public class ApprovedOutput
  { 
  }
  
  }
  </code></pre>
  
  <p>Here is the other part:</p>
  
  <p>EDIT: I added some code to before the insert statement begins, I figured I might be able to use DataRows like my coworker used when he created the <code>select statement</code>. My problem now is just figuring out how to point to my class that is holding the values I need to insert.</p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Data;
  using System.Linq;
  using System.Text;
  using System.Threading.Tasks;
  
  namespace ReadExcel
  {
    class Helpers
    {
  
      public static List&lt;Attorney&gt; getAttorneys()
      {
          List&lt;Attorney&gt; lst = new List&lt;Attorney&gt;();
  
          StringBuilder sql = new StringBuilder();
          sql.AppendLine("SELECT ");
          sql.AppendLine("  V.ATTORNEY_ID, V.ORGANIZATION_ID, A.NAME, ( '(' || V.ATTORNEY_ID || ') - ' || a.name ) as caption");
          sql.AppendLine("FROM ");
          sql.AppendLine("  VENDOR_PORTAL.organization_attys V, ATLAS.RLGLENTY A ");
          sql.AppendLine("WHERE ");
          sql.AppendLine("  V.ATTORNEY_ID = A.ID_CODE (+) ");
          sql.AppendLine("  and V.organization_id != 122 ");
          sql.AppendLine("  and V.organization_id is not null ");
          sql.AppendLine("ORDER BY A.NAME ASC ");
          using (DataTable dt = DataManager.GetData(sql))
          {
              foreach (DataRow dr in dt.Rows)
              {
                  try
                  {
                      lst.Add(new Attorney()
                      {
                          AttorneyName = dr["name"].Equals(DBNull.Value) ? "" : dr["name"].ToString(),
                          OrganizationID = int.Parse(dr["organization_id"].ToString()),
                          AttorneyID = dr["attorney_id"].Equals(DBNull.Value) ? "" : dr["attorney_id"].ToString(),
                          Caption = dr["caption"].Equals(DBNull.Value) ? "" : dr["caption"].ToString()
                      });
                  }
                  catch (Exception ex)
                  { }
              }
          }
  
          return lst;
      }
  
      public static List&lt;LegalTransactionRec&gt; insertRecords()
      {
          StringBuilder sql = new StringBuilder();
          using (DataTable dt = DataManager.GetData(sql))
          {
              foreach (DataRow dr in dt.Rows)
              {
                  try
                  {
                      sql.AppendLine("INSERT INTO PCOSCETTE.LEGAL_TRANSCATIONS (BATCH_ID, ACCOUNT, ATTORNEY_ID, ORG_ID, TRANSACTION_DATE, DATE_INSERTED, TRANSACTION_CODE, AMOUNT, DESCRIPTION, DEBTOR_SSN");
                      sql.AppendLine("VALUES ( (select max(batch_id) from pcoscette.legal_transaction_batch_info),");
                  }
                  catch (Exception ex)
                  { }
              }
          }
      }
  
    }
  }
  </code></pre>
  
  <p>Any point in the right direction would be a life saver, thanks. Also if I have forgotten anything in my original post please let me know and I will add it. </p>