Problem with the return type of a stored procedure

Question

i have a stored procedure that creates a table then fills this table somehow from tables of the database then selects everything from this table then drops this table.the problem is,how can i use the selected columns from that dropped table im using DataContext i always put the result of the the stored procedure in a list of the type of that stored procedure

ex:

MyDataContext db=new MyDataContext();

public List<Base_RetriveItemResulte> RetriveItem(int ItemId)

{ 

    List<Base_RetriveItemResulte> ItemList=db.Base_RetriveItem(ItemId).ToList<Base_RetriveItemResulte>(); 

    return ItemList;

}

//Base_RetriveItem is a stored procedure from the data context the problem with the stored procedure that drops the table" GetSubcategories " is that it cant be put in a list with its result type db.GetSubcategories(CategoryId) i was expecting to put the result from GetSubcategories(CategoryId) in a list of type

List<GetSubcategoriesResult>

but there is no type like this!! How can I get the selected columns from the dropped table?

Answer 1

EDIT: Please read the comments left at the end of your question. If you won't participate like everyone else does it gets confusing and frustrating, and those who would willingly help you will just ignore your request.

Please supply some information in your post. If we dont have clear information, we are guessing wildly and making ASSumptions.

• What language are you using?
• Are you using MSSQL, MySQL, Oracle? What version?
• Linq to SQL, Linq to EF, or another ORM solution?

That stored procedure does not look like it RETURNS a value single result, but rather uses recursion and cursors to "walk" through a result. Very, Very, Very dirty.

That SP is the real problem here. I understand that you may not be able to fix it, but you may be better off by either a) making your own stored procedure, or b) calling the stored procedure the good old fashioned way with a dataAdapter or its equivalent.

As another point, you would be wise to look at the example of code that I gave to you and understand that it is going to be less prone to bugs and other problems. There are several key differences in our approaches that you should take the time to understand.

Original post: Stored procedures typically return DataSets, not an arraylist (Those are .NET types) . Your implementation could use some cleaning up too.

Change it to something like this, forgive the syntax errs, I do VB all day.

Note: ID has both letters capitalized, and also observe how the variables are declared in each scope. You may want to choose a different pattern, but this is an example of a standard.

//retrieves a list of strings related to the itemID
public List<String> RetriveItem(int itemID)
{

  // declare our return value
  List _itemList = new List<String>; // int, whatever type of list

  // create a data context with the using statement (so it will be cleaned up when
  // the operation is completed.
  using (MyDataContext _db=new MyDataContext()) 
  {

     // get the data from the stored procedure
     DataSet _ds = _db.Base_RetriveItem(itemID);

     // go through each row in the first table (you may have more tables to look in
     foreach DataRow _dr in _ds.Tables(0)
     {
        // adds the first column value in the row to the return list.
        _itemList.Add(Convert.ToString(_dr(0)); //convert to whatever type your list is.
     }
  }

return _itemList;

}

Alternately you could use the IQueryable inside the using block.

 var listOfItems = from i in _db._db.Base_RetriveItem(itemID) select i;
 foreach i in listOfItems 
 {
   _itemList.Add(i); //convert to whatever type your list 
 }

Its kind of difficult to provide without more detail and feedback.

"is a stored procedure from the data context the problem with the stored procedure that drops the table GetSubcategories"

This is really confusing. The procedure is a GET which means it GETS data, not DROPS a TABLE. Please clarify this.

Answer 2

I don't know if this is the exact solution you need, but one thing I've had to do in the past is "fool" LinqToSQL by setting up the code side of things with a very straight-forward SQL statement (make the stored procedure do nothing more than a very simple select that gives you the exact data structure that you need [like selecting from a temp view]). Then once you've got all the code stuff wired up, you can go make changes to your stored procedure to add its advanced functionality. As long as you don't regenerate the associated LinqToSQL stuff then it should continue to work.

Answer 3

Here's a helpful MSDN article.

According to this article, you should be expecting your results as an IEnumerable of GetSubcategoriesResult. However, you're getting an int instead!

Cannot implicitly convert type 'int' to ...

You need to find the generated source for this method: db.GetSubcategories(RootCat); and inspect the return type. You need to go to the designer and kick it around until it gives you a proper IEnumerable result instead of a count of rows (the int).

---

If the designer does not cooperate, you can fall back to a manual execution method:

Step 1. Create the class to hold the return type:

public class MyCustomResult
{
  public int CategoryId{get;set;}
  public int ParentCategoryId{get;set;}
  public int IsActive{get;set;}
}

Step 2. Call the stored proc using ExecuteQuery:

public List<MyCustomResult> GetResult(int RootCat)
{
  List<MyCustomResult> result =
    db.ExecuteQuery<MyCustomResult>("EXEC _BASE_GetSubcategories {0}", RootCat);
  return result;
}

ExecuteQuery will take the (single) resultset and map it to the class (MyCustomResult) by column names matching to property names.

Some people may be disappointed by this manual solution, but LinqToSql's support for stored procedures (and complex mapping scenarios with stored procedures) is fairly weak and sometimes has to be overridden.

---

As far as the stored procedure goes, it appears to be a standard "arbitrary depth tree walk". There isn't a really good way to implement this in SQL, and so that stored proc's implementation is fine, in my opinion.