Pass table from DataSet as parameter

Question

I'm really new with C# and ASP.Net, I can say that i'm learning over the road. What i need to do is run an stored procedure and create an Excel file with the response. This is my code so far of the controller:

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ConfigurationSettings = System.Configuration.ConfigurationManager;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace NewSalesReport.Controllers
{    
public class NewSalesReportController : Controller
{

    class Program
    {
        static void Main()
        {
            string inline = ConfigurationSettings.AppSettings["ConectionString"];
            using (SqlConnection toConect = new SqlConnection(inline))
            {
                using (SqlCommand cmd = new SqlCommand("DaReport", toConect))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
                    cmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));

                    using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        adp.Fill(ds);
                        /*if (ds != null)
                        {
                            //Console.Write("It is not Empty");
                        } */


                    }

                }


            }

        }

        private void SetDataExcel(object result)
        {
            GridView grid = new GridView();
            grid.DataSource = result;
            StringWriter strwritter = new StringWriter();
            HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);

            grid.RenderControl(htmltextwrtter);

            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=reportExcel.xls");
            Response.ContentType = "application/ms-excel";

            Response.Output.Write(strwritter.ToString());
            Response.Flush();
            Response.End();
            strwritter.Dispose();
            htmltextwrtter.Dispose();
        }

        public ActionResult GetAprovedForPay(DateTime? fi = null, DateTime? ff = null)
        {
            var result = _repo.GetAprovedForPay(fi, ff);
            SetDataExcel(result);
            return null;
        }

    }


}

}

Someone is helping me with a few advices, and he told me that i need to pass the first table as a parameter: ds.Tables[0], but he didn't explain how.

Also, when i run the compiler shows an error which says that i need a

reference object for the field, method or property Controller.Response.

Someone can help me, please with this two issues? As i said, i'm new with this hole thing of .Net.

Thanx in advance

Answer 1

An object reference is required for the non-static field, method or property error occurred because you're calling Controller.Response from a method which has return type of void instead of ActionResult.

First, remove inner class Program and rename Main() method to other name (also remove static if necessary). Sounds like the code includes Main() method previously used in console/WinForms application, and you should change it to avoid confusion.

Second, remove Response usage and use FileResult to return file for download as in example below:

public FileResult SetDataExcel(object obj)
{
    GridView grid = new GridView();
    grid.AutoGeneratedColumns = true; // automatically generate all columns from data source
    grid.DataSource = result;
    StringWriter strwritter = new StringWriter();
    HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);

    grid.RenderControl(htmltextwrtter);

    string path = Server.MapPath("~/path/to/filename.xls");

    using (var sw = new StreamWriter(path))
    {
        sw.Write(strwriter.ToString());
    }

    var savedFile = new FileStream(path, FileMode.Open);

    return File(savedFile, "application/ms-excel");
}

Finally, you can use redirection to call SetDataExcel which returns the file:

public ActionResult GetAprovedForPay(DateTime? fi = null, DateTime? ff = null)
{
    var result = _repo.GetAprovedForPay(fi, ff);
    return RedirectToAction("SetDataExcel", new { obj = result });   
}

Note: Regarding data binding for GridView, if AutoGeneratedColumns set to true then you can use ds.Tables[0] as data source (see the reference here):

string inline = ConfigurationSettings.AppSettings["ConectionString"];
using (SqlConnection toConect = new SqlConnection(inline))
{
    using (SqlCommand cmd = new SqlCommand("DaReport", toConect))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));
        cmd.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));

        using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
        {
            DataSet ds = new DataSet();
            adp.Fill(ds);

            grid.DataSource = ds.Tables[0];
            grid.DataBind();
        }
    }
}