Writing data to SQL and dumping it into Excel

Question

My C# application is significantly slower than I would like. The program accesses an Excel sheet and loops through each row on a sheet / does the following:

1. Collects variables from that row
2. Creates an SQL query based off those variables
3. Executes that query
4. then a reader goes out and puts it in its proper column on that same row.

*Note, each row has 6 different SQL queries/ final numbers that are calculated and input into the sheet, the code below is just showing the first 2 for brevity's sake. The sheet has around 300 rows, so the program is executing 300 * 6= 1,800 SQL queries each time its run. For each one of those 1,800 numbers, the program is accessing the sheet and inputting it into the sheet.

Instead of doing the excelWorksheet.get_Range and inputting the number into the Excel sheet for each number, one at a time, would it be faster to store each number into an array and then go back later and mass dump all of the numbers into the sheet, once all of the queries have been executed and the array is full?

using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Data Source=WRDXPVSEPIRPT00;DATABASE=Epicor905;Workstation ID=SMEBPPL204TN;Trusted_Connection=true";

            try
            {
                //initiate the connection
                conn.Open();
            }
            catch(SqlException ex)
            {

                throw new ApplicationException(string.Format("You're not connected to the database, please close the program, re-connect to the network, and try again."), ex);

            }

            progressBar1.Value = 60;

            statusLabel.Text = "Retrieving account balances from database...";
            /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            //iterate through each row and pull information
            for (int i = 2; i < lastUsedRow + 1; i++)
            {
                //string iString = Convert.ToString(i);
                statusLabel.Text = "Retrieving balances for " + i + " of " + lastUsedRow + " accounts...";



                //declare excel sheet range variables
                var CompanyVar = excelWorksheet.get_Range("A" + i, "A" + i).Text;
                var FiscalYear = excelWorksheet.get_Range("B" + i, "B" + i).Text;

                var FiscalPeriod = excelWorksheet.get_Range("C" + i, "C" + i).Text;
                var segValue1 = excelWorksheet.get_Range("F" + i, "F" + i).Text;
                var segValue2 = excelWorksheet.get_Range("G" + i, "G" + i).Text;
                var segValue3 = excelWorksheet.get_Range("H" + i, "H" + i).Text;

                int FiscalYearHelper = Convert.ToInt32(FiscalYear);
                var FiscalYearPYY = FiscalYearHelper - 1;



                //begin filling in CY YTD column                   

                string SQLCode = "SELECT SUM(DebitAmount-CreditAmount) as BalanceAmt FROM GLJrnDtl WITH (NOLOCK) WHERE" +
                             " FiscalPeriod between 1 AND " + FiscalPeriod + "AND Company =" + "'" + CompanyVar + "'" +
                             "AND FiscalYear =" + "'" + FiscalYear + "'" + "AND SegValue1 LIKE " + "'" + segValue1 + "'" +
                             "AND SegValue2 LIKE " + "'" + segValue2 + "'" + "AND SegValue3 LIKE " + "'" + segValue3 + "'";

                SqlCommand command = new SqlCommand(SQLCode, conn);


                // Create new SqlDataReader object and read data from the command.
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {


                        string cyAct = reader["BalanceAmt"].ToString();
                        if (cyAct == "")
                        {
                            goto CYM;
                            //cyAct = "0.00";
                        }
                        var cyAct1 = (float)Convert.ToDouble(cyAct);
                        int one = Convert.ToInt32(excelWorksheet.get_Range("E" + i, "E" + i).Text);
                        double cyAct2 = (cyAct1 * one);
                        string cyAct3 = cyAct2.ToString("0.##");
                        excelWorksheet.get_Range("I" + i, "I" + i).Value = cyAct3;
                    }
                }
                //end filling in column


                //begin filling in CY Month column                   
                CYM:
                string SQLCodeMonth = "SELECT SUM(DebitAmount-CreditAmount) as BalanceAmt FROM GLJrnDtl WITH (NOLOCK) WHERE" +
                             " FiscalPeriod = " + FiscalPeriod + "AND Company =" + "'" + CompanyVar + "'" +
                             "AND FiscalYear =" + "'" + FiscalYear + "'" + "AND SegValue1 LIKE " + "'" + segValue1 + "'" +
                             "AND SegValue2 LIKE " + "'" + segValue2 + "'" + "AND SegValue3 LIKE " + "'" + segValue3 + "'";

                SqlCommand commandMonth = new SqlCommand(SQLCodeMonth, conn);


                // Create new SqlDataReader object and read data from the command.
                using (SqlDataReader reader = commandMonth.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string cyAct = reader["BalanceAmt"].ToString();
                        if (cyAct == "")
                        {
                            goto APY;
                            //cyAct = "0.00";
                        }
                        var cyAct1 = (float)Convert.ToDouble(cyAct);
                        int one = Convert.ToInt32(excelWorksheet.get_Range("E" + i, "E" + i).Text);
                        double cyAct2 = (cyAct1 * one);
                        string cyAct3 = cyAct2.ToString("0.##");
                        excelWorksheet.get_Range("J" + i, "J" + i).Value = cyAct3;
                    }
                }
                //end filling in column


                //begin filling in Act PY month column    

Answer 1

THIS IS NOT A FULL ANSWER, there is not enough space to write this as a comment, do not mark this as an answer its a comment.

Please try to use objects (instances of classes or structs) it will make your programming more efficient and easy to maintain.
for example:

     private void UseObjects()
     {
         List<ExcelObjects> ListOfvarsForQuery = new List<ExcelObjects>();

         for (int i = 2; i < lastUsedRow + 1; i++)
         {
             ExcelObjects obj = new ExcelObjects();

             obj.CompanyVar = ws.Cells[i, 1];
             obj.FiscalYear = ws.Cells[i, 2];
             obj.FiscalPeriod = ws.Cells[i, 3];
             obj.segValue1 = ws.Cells[i, 4];
             obj.segValue2 = ws.Cells[i, 5];
             obj.segValue3 = ws.Cells[i, 6];
             ListOfvarsForQuery.Add(obj);
         }

         string SQLCode = // use the list of ExcelObjects and write a better query.


     }
 }

 struct ExcelObjects
 {
     public string CompanyVar;
     public string FiscalYear;
     public string FiscalPeriod;
     public string segValue1;
     public string segValue2;
     public string segValue3;
 }

Answer 2

Instead of looping through each row and running a SQL query for each row, I found that it is faster to outer join the table I speak of in my question with another SQL table that has the account balances on it. I'm doing all of it within memory because I've been coding for 3 weeks and I don't have time to learn how to create a temp table in SQL server yet.