VB.NET Executing a store procedure - Edit

Question

I am exporting database information into an excel file in a VB.NET MVC project.

I have the excel part done and I am able to export a hard code table into excel. But I need to fill that table with fields from my database. I have a stored procedure that will get me all the information I need but I'm having trouble getting it to run and be able to let me go through and place the fields I need in the order I need them in.

This is how I'm making my excel sheet if that helps.

Dim blah = New DataTable("testing")
blah.Columns.Add("Col1", Type.GetType("System.String"))
blah.Columns.Add("Col2", Type.GetType("System.String"))

'Below will be switched out for table data, this is where the try for the SQL will end up

blah.Rows.Add(1, "hello")
blah.Rows.Add(2, "hello")
blah.Rows.Add(3, "hello")
blah.Rows.Add(4, "hello")

Dim grid = New GridView()
grid.DataSource = blah
grid.DataBind()

Response.ClearContent()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment; filename=Test-Report.xls")
Response.ContentType = "application/ms-excel"
Response.Charset = ""

Dim sw = New StringWriter()
Dim htw = New HtmlTextWriter(sw)

grid.RenderControl(htw)

Response.Output.Write(sw.ToString())
Response.Flush()
Response.End()

EDIT---- I am having an issue with figuring out the syntax and values to use to run a stored procedure that has a return value and being able to iterate through it and take the row values. I've done a ton of google searches and most have stored procedures that don't have a return value or queries with one value being returned and I am returning a lot of fields.

The stored procedure I'm running looks like this:

SELECT
    i.insurer_name
    ,cs.customer_name
    ,cs.customer_address
    ,cs.claim_number
    ,cs.work_type
    ,anu.FirstName
    ,anu.LastName
    ,l.location_name
    ,cs.completed_date
    ,q.question_text
    ,a.answer_text
FROM insurer i
inner join completed_survey cs on cs.insurer_id = i.insurer_id
inner join AspNetUsers anu on anu.Id = cs.user_id
inner join location l on l.location_id = cs.location_id
inner join answer a on a.complete_id = cs.completed_id
inner join question q on q.question_id = a.question_id
ORDER BY i.insurer_name, cs.survey_score desc

EDIT 2 ---- I'm not entirely sure how to answer what DB connection I'm using. I have a connection string in the web config and I'm using entity framework so most of my code is like

Private db As New SurveyDBModel
db.table_name.ToList()
db.table_name.Add(new_row)

Where is your try of executing the SP? Besides, posible duplicate of [Execute Stored Procedure](https://stackoverflow.com/questions/5991877/execute-a-sql-stored-procedure-and-process-the-results) — D Ie, Aug 16 '17 at 13:08
_but I'm having trouble getting it to run_ - Please be specific about the problem you are having. "I'm having trouble" is not a valid problem statement. — Chris Dunaway, Aug 16 '17 at 13:12
what DB connection library are you using? Please show the code you're using to connect to the DB and execute the procedure. If you're using ADO.NET you can normally just export the result directly to a DataSet or DataTable in a couple of lines. Also what RDBMS is it? SQL Server? — ADyson, Aug 16 '17 at 13:22
"I'm not entirely sure how to answer what DB connection I'm using"...well then you said "Entity Framework", which is a valid answer to the question. EF is an ORM library for connecting to databases. You still didn't say which RDBMS it is, but thankfully EF abstracts things sufficiently to largely make it irrelevant. — ADyson, Aug 16 '17 at 13:46
You should be able to call a (SQL Server) stored procedure something like: `using (var db = new YourEntitiesObject()) { object[] paramList = new object[1]; paramList[0] = new SqlParameter("someParameter", someVariable); return db.Database.SqlQuery("[YourSchemaName].[YourProcedureName] @someParameter", paramList).ToList(); }` which will serialise the results directly to the type you specify. — ADyson, Aug 16 '17 at 13:50

score 0 · Answer 1 · answered Aug 16 '17 at 13:30

As for your sample query, I'd use a View for a simple data manipulation like that...

If you want to modify a data in DB, then a stored procedure comes handy and is easily executed from within SQL query...

EXEC dbo.MyStoredProc @MyKeyNo

It does not return data this way, the data are modified in the DB.

score 0 · Accepted Answer · answered Aug 16 '17 at 15:34

Thanks to @ADyson, although the code you provided had a little more stuff to it, the base of it worked for me.

One of my main problems was I didn't actually know I had to make a Model for the excel sheet to be able to get my fields to export. Also apparently my stored procedure didn't get transferred over so I need to do raw SQL.

Here's the basis of what ended up working:

Dim blah = New DataTable("insurer")
blah.Columns.Add("Insurer Name", Type.GetType("System.String"))
blah.Columns.Add("Col2", Type.GetType("System.String"))

Using db As New SurveyDBModel()

Dim rsInsurer = db.Database.SqlQuery(Of report)("SELECT
i.insurer_name ,cs.customer_name ,cs.customer_address ,cs.claim_number ,cs.work_type ,anu.FirstName ,anu.LastName ,l.location_name ,cs.completed_date ,q.question_text ,a.answer_text
FROM insurer i
inner join completed_survey cs on cs.insurer_id = i.insurer_id
inner join AspNetUsers anu on anu.Id = cs.user_id
inner join location l on l.location_id = cs.location_id
inner join answer a on a.complete_id = cs.completed_id
inner join question q on q.question_id = a.question_id
ORDER BY i.insurer_name, cs.survey_score desc").ToList()

For Each item In rsInsurer
    blah.Rows.Add(item.insurer_name, "Bye")
 Next

 End Using

Thank you to all the people who tried to help me understand what the hey I was doing!

VB.NET Executing a store procedure - Edit

2 Answers2