Direct SQL search from an MVC application

Question

I am trying to implement the below functions:

Execute an SQL stored procedure; which gets the names of all the tables in the database. This stored procedure is currently displayed in MVC application as a dropdownlist. I want the client to be able to query a selected table from the list for example if the "Organization" table is selected from the list, and the user enters Id = 5 and submits, this should generate a comma-separated row, Write it in a CSV file (Name of CSV file should be the table name) After completing the export, create a ZIP file which includes this CSV file.

I am really stuck please help. Really appreciate it.

Thank you.

So far I have the stored procedure part done:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

Need to narrow down this question. As it is it sounds like you're asking three different questions, how to call a stored procedures, how to write a csv file, how to write a zip file. Start with focusing on the next step, how to call a stored procedure. This might help: http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application — AaronLS, May 02 '14 at 21:59

score 0 · Accepted Answer · edited May 23 '17 at 10:26

There are too many questions to answer so i'll give you some pointers..Ill just assume youve got the stored procedure working although you may want to look into this a stored procedure to dynamically execute a query, so sneaky

A CSV writer library to output your file as a download. I should mention the output of csvwriter is a comma delimited list. So when you execute your stored procedure want to iterate over the returned rows along these lines...

public ActionResult DownloadCsv()
    {
        using (var memStream = new MemoryStream())
        {
            using (var streamWriter = new StreamWriter(memStream))
            {
                var writer = new CsvWriter(streamWriter);

                using (var conn = new SqlConnection(CONNECTION_STRING))
                {
                    var command = new SqlCommand("YourStoredProcedure", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@id", id);
                    conn.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                Console.WriteLine(reader.GetValue(i));
                                writer.WriteField(reader.GetValue(i));
                            }
                            writer.NextRecord();//you must also do this ALWAYS otherwise your .WriteFields wont be output for the current line
                        }

                    }
                }

                streamWriter.Flush();//note: you must flush


                return File(memStream.ToArray(), "text/csv", "My 1337 download name.csv");
            }
        }

    }

Did some research and here it is with zipping (without all the sql goo), you'll need this apparently uber popular library

public ActionResult DownloadCsv()
    {
        using (MemoryStream zippedDownloadStream = new MemoryStream())
        {
            using (ZipFile zip = new ZipFile())
            {
                using (var writerMemoryStream = new MemoryStream())
                {
                    using (var streamWriter = new StreamWriter(writerMemoryStream))
                    {
                        var writer = new CsvWriter(streamWriter);

                        //your writing

                        streamWriter.Flush();
                    }
                    zip.AddEntry("awesome file name.csv", writerMemoryStream.ToArray());

                    zippedDownloadStream.Flush();
                    zip.Save(zippedDownloadStream);
                    return File(zippedDownloadStream.ToArray(), "application/zip", "zippedup.zip");
                }
            }
        }
    }

Thank you, I will try your pointers, the Zip file needs to be downloaded, but since that's the final step it's ok, as long as I get the other steps first. I'll keep you posted thanks again cheers — Hinda, May 05 '14 at 16:56

Direct SQL search from an MVC application

1 Answers1