Mapping values from a sql database to a .csv file.

See more:

C#

MS-Excel

I have an application that takes files in one format, .xls or .xlsx and converts them into .csv files. The initial file has a policy number that is a bit different than the one my company uses. So I need to query a sql database and get the policy number that matches the original in the .xls file but in my companies format so I can write that to the .csv file.

When I try loading my SqlDataReader(reader) that is getting my actual policy number from the sql database into my datatable (dt1), it does not appear to have anything in it and subsequently does not write to the file.

Can someone take a look at my code and point me in the right direction. Thank you!

if (file.Exists)
                {
                    //The connection string to the excel file
                    string connstr = @"Provider=Microsoft.Ace.Oledb.12.0;Data Source=C:\travelersTEST\travelersTEST.xlsx;" + "Extended Properties=" + "\"" + "Excel 12.0;" + "\""; //C:\inetpub\temp\travelersTEST.xlsx
                    //The connection to that file
                    OleDbConnection conn = new OleDbConnection(connstr);
                    //The query
                    string strSQL = "SELECT [Policy Number], [Comm Due], [Effective Date], [Comm Rate], [Transaction Premium] FROM [Sheet1$]";
                    //The command 
                    OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                    DataTable dt = new DataTable();
                    conn.Open();
                    string date1 = DateTime.Now.ToString("yyyy-MM-dd");
                    string date2 = DateTime.Now.ToString("hh-mm-ss");
                    try
                    {
                        OleDbDataReader dr1 = cmd.ExecuteReader();
                        StreamWriter sw = new StreamWriter(@"C:\travelersTEST\Travelers - " + date1 + " @ " + date2 + ".csv");
                        if (dr1.HasRows)
                        {
                            dt.Load(dr1);
                            dt.AcceptChanges();
                        }


----------------------------The code I need help with is below-----------------------------------------------------------

                        // Now write all the rows.
                        sw.Write("POLICY NUMBER, Commission Amount, POLICY Effective Date, COMMISSION RATE, Premium");
                        sw.Write(sw.NewLine);
                        SqlConnection myConn = new SqlConnection(@"Data Source=lrstest-sql1;Initial Catalog=VelocityReports;Integrated Security=SSPI");
                        myConn.Open();
                        SqlCommand myCommand = myConn.CreateCommand();
                        
                        foreach (DataRow dr in dt.Rows)
                        {
                            if (dr[0].ToString() != "")
                            {
                                string polNumber = dr[0].ToString();
                                int index = polNumber.IndexOf(" ");
                                if (index > 0)
                                {
                                    polNumber = polNumber.Substring(0, index);
                                }
                                myCommand.CommandText = "Select polPolicyNumber From tblPolicies Where polPayee = 543 and polPolicyNumber like " +
                                    "'IOUB" + polNumber + "'and polEffectiveDate =" + dr[2].ToString() + " Order by polEffectiveDate desc";
                                myCommand.CommandType = CommandType.Text;
                                SqlDataReader reader = myCommand.ExecuteReader();
                                DataTable dt1 = new DataTable();
                                dt1.Load(reader);
                                foreach (DataRow dr2 in dt1.Rows)
                                {
                                    sw.Write(dr2[0].ToString() + ",");
                                }
                                sw.Write(dr[1].ToString() + ",");
                                sw.Write(dr[2].ToString() + ",");
                                sw.Write(dr[3].ToString() + ",");
                                sw.Write(dr[4].ToString());
                                sw.Write(sw.NewLine);
                                reader.Close();
                            }
                         }
                        
                        myConn.Close();
                        sw.Close();