How do you tell ASP .Net dynamic data to use stored procedures for select?

Question

I see you can specify Insert, Update and Delete stored procs, but no straightforward way for SELECT stored procs.

Answer 1

This is doable but not via the visual drag / drop tool. You have to do three things:

1. Create a new method from the datacontext class that will be called to "get" your data

   public partial class DatabaseDataContext {
   [Function(Name = "dbo.Contact_Get")]
   [ResultType(typeof(Contact))]
   [ResultType(typeof(int))]
   public IMultipleResults GetContacts([Parameter(Name = "PageIndex", DbType = "Int")] System.Nullable<int> pageIndex, [Parameter(Name = "PageSize", DbType = "Int")] System.Nullable<int> pageSize, [Parameter(Name = "Sort", DbType = "NVarChar(10)")] string sort, [Parameter(Name = "ContactTypeId", DbType = "Int")] System.Nullable<int> contactTypeId) {
       IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), pageIndex, pageSize, sort, contactTypeId);
       return ((IMultipleResults)(result.ReturnValue));
   }
   

   }

2. Create a new page template (List.aspx for example) for the particular table you want in the CustomPages folder to control the selecting from.

3. Control the gridview's selecting mechanism.

   protected void GridDataSource_Selecting(object sender, LinqDataSourceSelectEventArgs e) {
   DatabaseDataContext db = new DatabaseDataContext();
   IMultipleResults results = db.GetContacts(e.Arguments.StartRowIndex, e.Arguments.MaximumRows, e.Arguments.SortExpression, (int?)e.WhereParameters["ContactTypeId"]);
   e.Result = results.GetResult<Contact>().ToList();
   e.Arguments.TotalRowCount = results.GetResult<int>().Single<int>();
   

   }

Check out the Dynamic Data SP sample on the codeplex site for DD that shows you how to do this:

http://aspnet.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=14473