Linq To Sql 存储过程

  Store Procedure,存储过程。也是被别人写过的东西。我习惯性先看别人都写了点啥,然后才开始想看看自己还要写点啥。那就先谈谈它与udf的区别吧。

  在Linq To Sql进阶系列(四)User Define Function篇 中,我们提到了两者的差别。比如Store Procedure支持多个rowset的,而udf不行。他们还有一些其他的差别。Store Procedure只能返回整型,而udf可以是其他类型,比如char等,除个别类型外,比如imager类型,是不可以做为udf的返回类型的。 Store Procedure支持Out Parameter而udf没有。

1, SingleResultSet
  我们先来看这个sprocs.

CREATE PROCEDURE [dbo].[Customers By City]
    -- Add the parameters for the stored procedure here
    (@param1 NVARCHAR(20))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT CustomerID, ContactName, CompanyName, City from Customers as c where c.City=@param1
END

  其生成的code如下。

        [Function(Name="dbo.[Customers By City]")]
        public ISingleResult<Customers_By_CityResult> Customers_By_City([Parameter(DbType="NVarChar(20)")] string param1)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
            return ((ISingleResult<Customers_By_CityResult>)(result.ReturnValue));
        }

  这里Customers_By_CityResult是这个sprocs的影射类。但你可以在OR Designer里调整。如图,


  选中该函数后,右击属性。就可以使用其他影射类。但是Linq会对返回的rowset做检查,如果发现返回结果和影射不匹配它会报错。而且一旦更改了,当你需要改回去的时候,你只能在Designer中删掉此sprocs,然后重新拖过来。
  调用它很简单,就当作一个函数,但是,这里和普通的linq语句不一样的地方是,它不是延迟加载的。

            DataClasses1DataContext db = new DataClasses1DataContext();
            db.Log = Console.Out;
            var q = db.Customers_By_City("London");

  正因它不是延迟加载的,所以,linq可以对他进行简单的内联操作,比如

            DataClasses1DataContext db = new DataClasses1DataContext();
            db.Log = Console.Out;

            var q = from c in db.Customers_By_City("London")
                    orderby c.City
                    select c;

  注意的时,这里是Linq To Object而不是Linq To Sql。


2, MultipleResultSets
看下面的例子

CREATE PROCEDURE [dbo].[Get Customer And Orders](@CustomerID nchar(5))
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT * FROM Customers AS c WHERE c.CustomerID = @CustomerID  
    SELECT * FROM Orders AS o WHERE o.CustomerID = @CustomerID
END

  使用OR designer对其影射,其dbml为

  <Function Name="dbo.[Get Customer And Orders]" Method="Get_Customer_And_Orders">
    <Parameter Name="CustomerID" Parameter="customerID" Type="System.String" DbType="NChar(5)" />
    <ElementType Name="Get_Customer_And_OrdersResult">
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" CanBeNull="false" />
      <Column Name="CompanyName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
      <Column Name="ContactName" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="ContactTitle" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
      <Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
      <Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
      <Column Name="Fax" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
    </ElementType>
  </Function>

用sqlmetal对它做影射,生成dbml为

<Function Name="dbo.Get Customer And Orders" Method="GetCustomerAndOrders">
    <Parameter Name="CustomerID" Parameter="customerID" Type="System.String" DbType="NChar(5)" />
    <ElementType Name="GetCustomerAndOrdersResult1">
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
      <Column Name="CompanyName" Type="System.String" DbType="NVarChar(40)" CanBeNull="true" />
      <Column Name="ContactName" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="ContactTitle" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
      <Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
      <Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
      <Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
      <Column Name="Fax" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
    </ElementType>
    <ElementType Name="GetCustomerAndOrdersResult2">
      <Column Name="OrderID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
      <Column Name="EmployeeID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="OrderDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="RequiredDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="ShippedDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
      <Column Name="ShipVia" Type="System.Int32" DbType="Int" CanBeNull="true" />
      <Column Name="Freight" Type="System.Decimal" DbType="Money" CanBeNull="true" />
      <Column Name="ShipName" Type="System.String" DbType="NVarChar(40)" CanBeNull="true" />
      <Column Name="ShipAddress" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
      <Column Name="ShipCity" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="ShipRegion" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
      <Column Name="ShipPostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
      <Column Name="ShipCountry" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
    </ElementType>
  </Function>

  仔细比较他们的区别哦。“好像名字不一样呢”。晕倒。看主要的。第一个只有一个ElementType子项,而第二个有2个。这个地方其实可以说是OR designer的一个bug。要想修改这个bug,需要更改一个设计,而推动更改设计,比较麻烦。但并不是不能改。如果你认为这个真的很需要,而且对你 很重要,你更喜欢用or designer的话,我建议你去下面的社区发帖子。

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1
  要求更改此处的问题。因为,我已经无力推动他们修复该bug,ms更乐意听来自客户的声音,说不定你会成功的哦,还有奖励的哦。
  这个sprocs准确的影射代码为

        [Function(Name="dbo.Get Customer And Orders")]
        [ResultType(typeof(GetCustomerAndOrdersResult1))]
        [ResultType(typeof(GetCustomerAndOrdersResult2))]
        public IMultipleResults GetCustomerAndOrders([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID);
            return ((IMultipleResults)(result.ReturnValue));
        }

  对于MultipleResultSets的sprocs,大家更关注如何取其返回结果。其实很简单,按顺序,一个rowset,一个rowset的取。这个顺序和你sprocs里的顺序是一样的。

IMultipleResults result = db.GetCustomerAndOrders("SEVES");
IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();
IEnumerable<OrdersResultSet> orders = result.GetResult<OrdersResultSet>();


  如果,你很贪心,再加一行的话,

IEnumerable<CustomerResultSet> customer = result.GetResult<CustomerResultSet>();


  报错咯,越界了。没有那么多,你问要也不给。

3,OutParameters
  似乎没有什么好讲的,很简单,当作ref 的函数参数输出的。其也只是在生成的函数里加了这么一句

outParameter =  ((System.Nullable<int>)(result.GetParameterValue(1)));

   调用result.GetParameterValue方法,大家要记住这个哦。

4,Return Value
  呀,把return value丢那里了。的确,Linq曾舍弃过return value.后来在qa的坚持下,dev们决定保留了它。但是,需要你自己去更改code,才能获得。我们可以从下面这个sprocs上获得灵感。

CREATE PROCEDURE [dbo].[CustOrderTotal] 
@CustomerID nchar(5),
@TotalSales money OUTPUT
AS
SELECT @TotalSales = SUM(OD.UNITPRICE*(1-OD.DISCOUNT) * OD.QUANTITY)
FROM ORDERS O, "ORDER DETAILS" OD
where O.CUSTOMERID = @CustomerID AND O.ORDERID = OD.ORDERID

其影射的code为

        [Function(Name="dbo.CustOrderTotal")]
        public int CustOrderTotal([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID, [Parameter(Name="TotalSales", DbType="Money")] ref System.Nullable<decimal> totalSales)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), customerID, totalSales);
            totalSales = ((System.Nullable<decimal>)(result.GetParameterValue(1)));
            return ((int)(result.ReturnValue));
        }

  因为该sprocs并没有rowset返回,其最后只剩返回值了。其是将result.RenturnValue强制转化为int型,以得到sprocs 的整形返回值。那对于SingleResultSet和MultipleResultSets的,该如何获取它的返回值呢?那只有自己动手,修改code 了。就是自己强制转换,而后通过out 参数输出。比如用第一个sprocs

        [Function(Name = "dbo.[Customers By City]")]
        public ISingleResult<Customers_By_CityResult1> Customers_By_City([Parameter(DbType = "NVarChar(20)")] string param1)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1);
            return ((ISingleResult<Customers_By_CityResult1>)(result.ReturnValue));
        }
        public ISingleResult<Customers_By_CityResult1> Customers_By_City2(string para, out int returnValue)
        {
            ISingleResult<Customers_By_CityResult1> result = this.Customers_By_City(para);
            returnValue = (int)result.ReturnValue;
            return ((ISingleResult<Customers_By_CityResult1>)result);
        }

测试一下

            DataClasses1DataContext db = new DataClasses1DataContext();
            db.Log 
= Console.Out;

            
int returnValue = -1;
            var q 
= db.Customers_By_City2("London",out returnValue);
            Console.WriteLine(returnValue);

  也可以使用result.GetParameterValue方法获取返回值。只是linq会检查影射函数的参数个数。这个使用起来比较麻烦。可以这么使 用,比如,有一个sprocs,有2个参数,其中有一个out的参数。这个out的参数,可以不做任何操作。在影射后,修改其code。这两个参数的位标 是从0开始,依次递增。其code本来为outParameter =  ((System.Nullable<int>)(result.GetParameterValue(1))); 将其索引修改位为2,就是return value了。再大了又该抛了。

来源 http://www.cnblogs.com/126/archive/2007/08/16/851138.html

评论(0)网络
阅读(58)喜欢(0)不喜欢(0)asp.net-linq