Working with parameterized stored procedure in ASP.NET

So, my SQL Server admin has given me access to a stored procedure with 5 parameters that returns 15 columns of data.

However, if I want to filter on the columns that are not parametrized, I have just a few options. Creating a DataSet does not help, since the query to 开发者_如何学Cthe database can only be the stored proc and it's parameters, not Select statements.

• I can create an XML file and query that using Linq-to-XML
• I can create some temporary tables in another database and query those

What am I missing?

---

some ideas...

1) Can you ask your dba for a new stored procedure that filters (using additional parameters) on columns you need to filter on?

2) Can you write your own stored procedures? If so, you could, for example declare a table variable, insert into it using an exec on the stored procedure your dba wrote, and then select from it using any filters you like.

3) re: your two options -- those will work -- you can pull all of the data into a datatable in asp.net, or an xml file, but that's moving and exposing data you know at design time you won't need, so not an ideal solution.

4) Can you directly query the table(s) yourself?

EDIT

You can bring (all) of the data into a datatable (asp.net), and then filter it there. For example (VB):

Dim myFilter as String
myFilter = "SomeField = SomeValue"

Dim myRows() as datarow
myRows = myDataSet.Tables(i).Select(myFilter)

For each myRow as datarow in myRows
   myNewDataTable.ImportRow(myRow)
Next

It's not ideal, but considering the limitations...

---

Creating a DataSet does not help, since the query to the database can only be the stored proc and it's parameters, not Select statements.

A DataSet can be filled using a stored procedure. When you filled up your DataSet then you filter the records using whatever data access technology you know/like.

marc_s is right, get a new DBA. If he doesn't want to create a stored procedure or add parameters to the existing procedure then he probably won't mind your application getting 1,700 records every time you call that stored procedure when all you really need is a subset of those records.

---

You can bring the data back into a DataSet and then use a DataView object to apply a filter. In short, this will call the stored procedure, gather all of the rows into the DataSet, and then the DataView will let you enumerate the data skipping over rows that don't match the filter.

Here's an example in C#: C# DataView Usage

---

If the number of records you get back is not huge, you could do this:

Create a POCO class to represent the data record coming back from the stored proc:

public class MyRecord
{
  Field1Name { get; set;}
  ...
  Field10Name { get; set;}
} 

Populate a List<MyRecord> with the results coming back from your stored proc:

List<MyRecord> mylist = new List<MyRecord>();
foreach(record in collectionOfRecordsFromStoredProc)
{
  mylist.Add(new MyRecord {  
        Field1Name = "", /* retrieve your value from record here */
        ...
        Field10Name = "" /* retrieve your value from record here */
  });
}

Then you can query those results using the standard Linq to Objects:

List<MyRecord> filteredRecords = mylist.Where(x => x.Field10Name.Contains("Smith")).ToList();

This essentially will do everything in memory without an intermediaray place to persist the data. But this will not scale well if you are expecting to receive very large numbers or records back from this stored proc.

NOTE: code above is untested so will probably need tweaking