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
我有一个创建则表某种程度上填补了这个表,从表中的数据库,然后选择一切从该表中则丢弃该table.the问题是一个存储过程,我该如何使用选定列从使用的DataContext我老是掉线表IM将存储过程的结果放在该存储过程的类型列表中
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
// Base_RetriveItem是一个来自数据上下文的存储过程,删除表“GetSubcategories”的存储过程的问题是它不能放在一个列表中,其结果类型为db.GetSubcategories(CategoryId)我希望得到结果来自GetSubcategories(CategoryId)的类型列表
List<GetSubcategoriesResult>
but there is no type like this!! How can I get the selected columns from the dropped table?
但没有这样的类型!!如何从删除的表中获取所选列?
3 个解决方案
#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?
你用的是什么语言?
你在使用MSSQL,MySQL,Oracle吗?什么版本?
Linq to SQL,Linq to EF,还是其他ORM解决方案?
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.
该存储过程看起来不像RETURNS值单个结果,而是使用递归和游标来“遍历”结果。非常非常非常脏。
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.
那个SP是真正的问题。我知道你可能无法修复它,但你可能会更好:a)制作你自己的存储过程,或b)使用dataAdapter或其等价物以旧的方式调用存储过程。
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.
原帖:存储过程通常返回DataSet,而不是arraylist(这些是.NET类型)。您的实施也可以使用一些清理。
Change it to something like this, forgive the syntax errs, I do VB all day.
把它改成这样的东西,原谅语法错误,我整天做VB。
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.
注意:ID具有大写字母,并且还观察如何在每个范围中声明变量。您可能想要选择不同的模式,但这是标准的示例。
//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.
或者,您可以在using块中使用IQueryable。
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"
“是一个存储过程从数据上下文中删除表GetSubcategories的存储过程的问题”
This is really confusing. The procedure is a GET which means it GETS data, not DROPS a TABLE. Please clarify this.
这真令人困惑。该过程是GET,这意味着它是GETS数据,而不是DROPS一个TABLE。请澄清一下。
#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.
我不知道这是否是您需要的确切解决方案,但我过去必须做的一件事是“傻瓜”LinqToSQL通过使用非常直接的SQL语句设置代码方面(使得存储过程只做一个非常简单的选择,它为您提供所需的确切数据结构[比如从临时视图中选择])。然后,一旦您将所有代码内容连接起来,您就可以对存储过程进行更改以添加其高级功能。只要您不重新生成相关的LinqToSQL内容,它就应该继续工作。
#3
Here's a helpful MSDN article.
这是一篇有用的MSDN文章。
According to this article, you should be expecting your results as an IEnumerable of GetSubcategoriesResult. However, you're getting an int instead!
根据这篇文章,你应该期待你的结果作为GetSubcategoriesResult的IEnumerable。但是,你得到一个int而不是!
Cannot implicitly convert type 'int' to ...
无法将类型'int'隐式转换为...
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).
您需要找到此方法的生成源:db.GetSubcategories(RootCat);并检查返回类型。您需要转到设计器并开始使用它,直到它为您提供正确的IEnumerable结果而不是行数(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:
步骤1.创建类以保存返回类型:
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:
步骤2.使用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.
ExecuteQuery将采用(单个)结果集并通过与属性名称匹配的列名将其映射到类(MyCustomResult)。
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.
有些人可能会对此手动解决方案感到失望,但LinqToSql对存储过程的支持(以及存储过程的复杂映射方案)相当薄弱,有时必须重写。
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.
就存储过程而言,它似乎是标准的“任意深度树步行”。在SQL中没有一种真正好的方法来实现它,所以在我看来存储过程的实现很好。
#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?
你用的是什么语言?
你在使用MSSQL,MySQL,Oracle吗?什么版本?
Linq to SQL,Linq to EF,还是其他ORM解决方案?
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.
该存储过程看起来不像RETURNS值单个结果,而是使用递归和游标来“遍历”结果。非常非常非常脏。
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.
那个SP是真正的问题。我知道你可能无法修复它,但你可能会更好:a)制作你自己的存储过程,或b)使用dataAdapter或其等价物以旧的方式调用存储过程。
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.
原帖:存储过程通常返回DataSet,而不是arraylist(这些是.NET类型)。您的实施也可以使用一些清理。
Change it to something like this, forgive the syntax errs, I do VB all day.
把它改成这样的东西,原谅语法错误,我整天做VB。
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.
注意:ID具有大写字母,并且还观察如何在每个范围中声明变量。您可能想要选择不同的模式,但这是标准的示例。
//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.
或者,您可以在using块中使用IQueryable。
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"
“是一个存储过程从数据上下文中删除表GetSubcategories的存储过程的问题”
This is really confusing. The procedure is a GET which means it GETS data, not DROPS a TABLE. Please clarify this.
这真令人困惑。该过程是GET,这意味着它是GETS数据,而不是DROPS一个TABLE。请澄清一下。
#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.
我不知道这是否是您需要的确切解决方案,但我过去必须做的一件事是“傻瓜”LinqToSQL通过使用非常直接的SQL语句设置代码方面(使得存储过程只做一个非常简单的选择,它为您提供所需的确切数据结构[比如从临时视图中选择])。然后,一旦您将所有代码内容连接起来,您就可以对存储过程进行更改以添加其高级功能。只要您不重新生成相关的LinqToSQL内容,它就应该继续工作。
#3
Here's a helpful MSDN article.
这是一篇有用的MSDN文章。
According to this article, you should be expecting your results as an IEnumerable of GetSubcategoriesResult. However, you're getting an int instead!
根据这篇文章,你应该期待你的结果作为GetSubcategoriesResult的IEnumerable。但是,你得到一个int而不是!
Cannot implicitly convert type 'int' to ...
无法将类型'int'隐式转换为...
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).
您需要找到此方法的生成源:db.GetSubcategories(RootCat);并检查返回类型。您需要转到设计器并开始使用它,直到它为您提供正确的IEnumerable结果而不是行数(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:
步骤1.创建类以保存返回类型:
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:
步骤2.使用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.
ExecuteQuery将采用(单个)结果集并通过与属性名称匹配的列名将其映射到类(MyCustomResult)。
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.
有些人可能会对此手动解决方案感到失望,但LinqToSql对存储过程的支持(以及存储过程的复杂映射方案)相当薄弱,有时必须重写。
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.
就存储过程而言,它似乎是标准的“任意深度树步行”。在SQL中没有一种真正好的方法来实现它,所以在我看来存储过程的实现很好。