SQL Server : query export to CSV

Question

I want to export the results of this query to CSV file but when I put it on server it doesn't work.

When I run this locally, it is fine, but as soon as I upload it to the server, it doesn't do anything and crashes the application. Can someone help me please?

protected void btnExport_Click(object sender, EventArgs e)
{
    string sCon = ConfigurationManager.ConnectionStrings["PayrollPlusConnectionString"].ConnectionString;

    using (SqlConnection con = new SqlConnection(sCon))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT ManualAdjustments.PTLName, ManualAdjustments.PTLStaffID, ManualAdjustments.CampusOccurred, ManualAdjustments.DeptOccurred, ManualAdjustments.DateOccurred, ManualAdjustments.CourseDetails, ManualAdjustments.Duration, ManualAdjustments.ValueOfDuration, ManualAdjustments.Grade, ManualAdjustments.CostCentre, ManualAdjustments.Comment, ManualAdjustments.Reason, ManualAdjustments.RatePerHour, ManualAdjustments.Pay, ManualAdjustments.PayPeriod FROM ManualAdjustments INNER JOIN PayrollPeriod ON ManualAdjustments.PayPeriod = PayrollPeriod.PayrollPeriod ORDER BY ManualAdjustments.PTLName"))
        {
                SqlDataAdapter sda = new SqlDataAdapter();

                try
                {
                    cmd.Connection = con;
                    con.Open();
                    sda.SelectCommand = cmd;

                    DataTable dt = new DataTable();
                    sda.Fill(dt);

                    if (dt.Rows.Count > 0)
                    {
                        string path = Server.MapPath("exportedfiles\\");

                        if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                        {
                            Directory.CreateDirectory(path);
                        }

                        File.Delete(path + "ManualAdjustments.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.

                        // ADD A WORKBOOK USING THE EXCEL APPLICATION.
                        Excel.Application xlAppToExport = new Excel.Application();
                        xlAppToExport.Workbooks.Add("");

                        // ADD A WORKSHEET.
                        Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
                        xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];

                        // ROW ID FROM WHERE THE DATA STARTS SHOWING.
                        int iRowCnt = 2;

                        //// SHOW THE HEADER.
                        //xlWorkSheetToExport.Cells[1, 1] = "Manual Adjustments";

                        //Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
                        //range.EntireRow.Font.Name = "Calibri";
                        //range.EntireRow.Font.Bold = true;
                        //range.EntireRow.Font.Size = 20;

                        //xlWorkSheetToExport.Range["A1:D1"].MergeCells = true;       // MERGE CELLS OF THE HEADER.

                        // SHOW COLUMNS ON THE TOP.
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "PTL Name";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "PTL Staff ID";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "Campus";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Department";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 5] = "Date";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 6] = "Course Details";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 7] = "Duration";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 8] = "Value of Duration";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 9] = "Grade";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 10] = "Cost Centre";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 11] = "Reason";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 12] = "Comment";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 13] = "Rate Per Hour";
                        xlWorkSheetToExport.Cells[iRowCnt - 1, 14] = "Pay";


                        int i;
                        for (i = 0; i <= dt.Rows.Count - 1; i++)
                        {
                            xlWorkSheetToExport.Cells[iRowCnt, 1] = dt.Rows[i].Field<string>("PTLName");
                            xlWorkSheetToExport.Cells[iRowCnt, 2] = dt.Rows[i].Field<string>("PTLStaffID");
                            xlWorkSheetToExport.Cells[iRowCnt, 3] = dt.Rows[i].Field<string>("CampusOccurred");
                            xlWorkSheetToExport.Cells[iRowCnt, 4] = dt.Rows[i].Field<string>("DeptOccurred");
                            xlWorkSheetToExport.Cells[iRowCnt, 5] = dt.Rows[i].Field<string>("DateOccurred");
                            xlWorkSheetToExport.Cells[iRowCnt, 6] = dt.Rows[i].Field<string>("CourseDetails");
                            xlWorkSheetToExport.Cells[iRowCnt, 7] = dt.Rows[i].Field<string>("Duration");
                            xlWorkSheetToExport.Cells[iRowCnt, 8] = dt.Rows[i].Field<decimal>("ValueOfDuration");
                            xlWorkSheetToExport.Cells[iRowCnt, 9] = dt.Rows[i].Field<int>("Grade");
                            xlWorkSheetToExport.Cells[iRowCnt, 10] = dt.Rows[i].Field<string>("CostCentre");
                            xlWorkSheetToExport.Cells[iRowCnt, 11] = dt.Rows[i].Field<string>("Reason");
                            xlWorkSheetToExport.Cells[iRowCnt, 12] = dt.Rows[i].Field<string>("Comment");
                            xlWorkSheetToExport.Cells[iRowCnt, 13] = dt.Rows[i].Field<decimal>("RatePerHour");
                            xlWorkSheetToExport.Cells[iRowCnt, 14] = dt.Rows[i].Field<decimal>("Pay");

                            iRowCnt = iRowCnt + 1;
                        }

                        // FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
                        //Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
                        //range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);

                        // SAVE THE FILE IN A FOLDER.
                        xlWorkSheetToExport.SaveAs(path + "ManualAdjustments.xlsx");

                        // CLEAR.
                        xlAppToExport.Workbooks.Close();
                        xlAppToExport.Quit();
                        xlAppToExport = null;
                        xlWorkSheetToExport = null;

                    }
                }
                catch (Exception ex)
                {
                    lblConfirm.Text = ex.Message.ToString();
                    lblConfirm.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
                }
                finally
                {
                    sda.Dispose();
                    sda = null;


                    //download file

                    try
                    {
                        string sPath = Server.MapPath("exportedfiles\\");

                        Response.AppendHeader("Content-Disposition", "attachment; filename=ManualAdjustments.xlsx");
                        Response.TransmitFile(sPath + "ManualAdjustments.xlsx");
                        Response.End();
                    }
                    catch (Exception ex) { }
                }
            }
    }
}

Tried a different approach but still not working, i think its to do with trying to save to local file on PC. Is there any way around this?

protected void btnExportMA_Click(object sender, EventArgs e) { string strDelimiter = ","; string cs = ConfigurationManager.ConnectionStrings["PayrollPlusConnectionString"].ConnectionString;

        StringBuilder sb = new StringBuilder();

        using (SqlConnection con = new SqlConnection(cs))
        {
            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT ManualAdjustments.PTLName, ManualAdjustments.PTLStaffID, ManualAdjustments.CampusOccurred, ManualAdjustments.DeptOccurred, ManualAdjustments.DateOccurred, ManualAdjustments.CourseDetails, ManualAdjustments.Duration, ManualAdjustments.ValueOfDuration, ManualAdjustments.Grade, ManualAdjustments.CostCentre, ManualAdjustments.Comment, ManualAdjustments.Reason, ManualAdjustments.RatePerHour, ManualAdjustments.Pay, ManualAdjustments.PayPeriod FROM ManualAdjustments INNER JOIN PayrollPeriod ON ManualAdjustments.PayPeriod = PayrollPeriod.PayrollPeriod ORDER BY ManualAdjustments.PTLName", con);
            DataSet ds = new DataSet();
            da.Fill(ds);

            ds.Tables[0].TableName = "Manual Adjustments";

            foreach (DataRow MADR in ds.Tables["Manual Adjustments"].Rows)
            {
                string PTLName = Convert.ToString(MADR["PTLName"]);
                sb.Append(PTLName.ToString() + strDelimiter);

                sb.Append(MADR["PTLStaffID"].ToString() + strDelimiter);

                sb.Append("\r\n");

                //add all other columns here (no delimiter in last column)


            }
        }

        StreamWriter file = new StreamWriter(@"H:\ManualAdjustments.txt");
        file.WriteLine(sb.ToString());
        file.Close();

    }

Is there write permission to the directory in which you are writing the csv on the server? — Alfie Goodacre, Apr 06 '16 at 09:30
To export a datatable to csv, you can try the following method which does not require Excel to be installed: http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv/4959869#4959869 — vc 74, Apr 06 '16 at 09:31
@AlfieGoodacre Yes there is cause i upload a spreadsheet to store data in database and it saves it on the server. — Alexandria, Apr 06 '16 at 10:04
@Alexandria Not only would you need to install office on the server which is not advised, but automation is basically not thread safe and you'll run into trouble if several users execute your code at the same time — vc 74, Apr 06 '16 at 10:15

score 0 · Answer 1 · answered Apr 06 '16 at 11:27

 using (ExcelPackage excelPack = new ExcelPackage())
            {
               // Add ExcelWorksheet  ExcelWorksheet excelWorksheet = excelPack.Workbook.Worksheets.Add("NAME");
               // formatting ExcelWorksheet here... 
                string sPath = Server.MapPath("exportedfiles\\");
                string FileName =sPath + "ManualAdjustments.xlsx";
                FileInfo fiCustom = new FileInfo(FileName);
                excelPack.SaveAs(fiCustom);
                System.Diagnostics.Process.Start(FileName, "cmd");
            }

Try this...

SQL Server : query export to CSV

1 Answers1