Is it Possible to call a Stored Procedure using LINQ in LINQPad?

Question

In visual studio you have the nice designer that encapsulates a stored proc with a nifty little method. I totally love LINQPad and use it on a daily basis at work (haven't had a need to open up SQL Studio for my job since I've been using it!) and would like to call stored procs while using it.

I'm afraid I know the answer to my own question but I'm hoping that maybe there's a feature I'm missing or perhaps someone has some black magic they can lend me to make this happen. Btw, I'm using LINQPad 4 if that makes a difference.

Edit
See my answer below.

Pleochroism

Stored Procedure support in LINQPad has improved further in version 4.28 (www.linqpad.net/beta.aspx): it now supports optional parameters, lets you access the return value + output parameter values, and lets you call sys procs (such as sys.sp_who2) from any database. Go to Help | What's New for more info. – Erse

Thanks for the heads up Joe :) – Pleochroism

Glad I could help. You might want to take your "answer" section of your question and post it as an actual answer. If you did, I would upvote it, since it adds useful information. – Defeasible

@Daniel, noted and added my answer below. – Pleochroism

A small example here using LINQPAD. hodentekmsss.blogspot.in/2015/01/… – Appendectomy

Answer 1

At least in my copy of LINQPad, stored procedures show up in the database treeview and can be invoked directly.

Here's a screenshot:

Defeasible

I'll have to try those method names out and see if they'll execute if the language is set to C# Statements or C# Program. Thanks Daniel! – Pleochroism

Answer 2

Summing up some of the other answers as well as adding a bit of additional information:

Connect to your data source using the Default (LINQ to SQL) driver. Make sure that the check box Include Stored Procedures and Functions is checked.

Stored procedures and functions are now available as .NET functions (e.g. C#) in queries using the connection. The parameters required by the function reflects the parameters required by the stored procedure or database function.

The value returned by the function is a ReturnDataSet which is a LINQPad type deriving from DataSet. Unfortunately it is not so easy to perform LINQ queries on data sets but LINQPad provides the extension method AsDynamic() that will take the first table of the returned data set (normally there is only one table) and convert the rows to IEnumerable<Object> where the objects in the collection are dynamic allowing you to access the column values as properties. E.g. if your stored procedure returns columns Id and Name you can use LINQ:

SomeStoredProc().AsDynamic().Where(row => row.Id == 123 && row.Name == "Foo")

Unfortunately you will not have intellisense because the row objects are dynamic.

Adorn

I am using 5.36 with Oracle and so far have not been able to get a stored procedure called. The closest I got was using ADO.NET (OracleConnection, OracleCommand, etc) but I get an error: ORA-24372: invalid object for describe when I try to execute the SP. – Woodcut

Answer 3

My Answer (With Daniel's assistance, thanks.)

Daniel helped me realize that stored procedures can be called if you target a database with the drop down list in the query window; then in the query window call the stored proc by its name and append parentheses to the end to call it as a function.

The main difference between the current version (I'm using 4.26.2 as of this date) is that LINQ in VS returns custom data types to match data objects returned from the stored procedure and LINQPad returns a DataSet. So by selecting "C# Statement(s)" you can successfully run this as a query:

DataSet fooResults = foo_stored_proc(myParam);

Thanks for everyone's help!

Pleochroism

Answer 4

Example: Say I have a Stored Procedure called PersonMatchNoDOBRequired and it expects me to pass in a First and Last Name

var b = PersonMatchNoDOBRequired("John", "Smith").AsDynamic();
b.Dump();

If I want further filtering ( in my case I have an advanced algorithm in sql that scores the first and last name , first name can return "Jodi" , but say I really want to filter it further then it is like this:

var b = PersonMatchNoDOBRequired("John", "Smith").AsDynamic().Where(x => x.FirstName == "John" && x.LastName == "Smith");   

Other answers are partially right - I didn't see anything passing in the parameters ( if needed).

Shifrah

Answer 5

Using the example that @jlafay used you can grab the rows from the table and reference the column by name.

DataSet spItem = sp_StoredProcedure(parameters);
var rows = spItem.Tables[0].Rows;
foreach (DataRow row in rows)
{
   // Do something
   Console.WriteLine($"{row["MyColumn"]}, {row["LastName"]}");
} 

Efren

Answer 6

I am using 4.51.03 version and connecting to SQL Server 2012 Express edition. After connecting to AdventureWorks2012 database I can see all the stored procedures. Right clicking a stored procedure and choosing StoredProceedureName(...) drop-down, you display the stored procedure in the query window. You need to insert the parameters inside the parenthesis containg the ellipsis and run the query.

Examples are shown in the following two posts:

http://hodentekmsss.blogspot.com/2015/01/learn-querying-sql-server-2012-using.html

http://hodentekmsss.blogspot.com/2015/01/learn-querying-sql-server-2012-using_25.html

Appendectomy

Answer 7

You could just save a query that uses C# with standard ADO.NET objects (SqlConnection, SqlCommand, etc) and Dump() the results.

I realize it's not using LINQ, but it has served me well.

Histochemistry

I've done the same as well but trying to use more LINQ if possible :) This question is something that has stuck in the back of my mind for a little while now and tends to surface. So I thought today was as good as any to ask it. – Pleochroism