How to retrieve Stored Procedure schema using Linq to SQL?

Question

Basically I am trying to retrieve a list of stored procedure parameters using Linq to SQL? Is there a way to do this?

Answer 1

Perhaps System.Data.SqlClient.SqlCommandBuilder.DeriveParameters() will help.

Answer 2

At design time? Just drag the stored procedure on to the LINQ designer surface.

---

At runtime?

You need sql like this:

SELECT *
FROM syscolumns
WHERE id =
(
  SELECT id
  FROM sysobjects
  WHERE Name = @ProcName
)

Which might be generated by LinqToSql like this:

var params = 
  db.sysobjects
  .Where(o => o.Name == ProcName)
  .SelectMany(o =>
    db.syscolumns
    .Where(c => c.id == o.id)
  )