none
WPF, LINQ TO SQL & Dynamic sql RRS feed

  • Question

  • I am working on a WPF Project using Linq to SQL
    Now I have created a stored procedure which run a dynamic SQL to return a data set the usp is
    create PROCEDURE [dbo].[Usp_StudyDataExtract]
    @StudyID Int
    AS

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(MAX)

    Select @SQL =DataExractSelectSQL From StudyConfig Where StudyId=@StudyID
    SET @SQL = @SQL +'
    FROM PATIENT PT
    INNER JOIN TrackedSample TS ON PT.Patientid = TS.PatientId
    LEFT JOIN QualityReview QR ON TS.TrackedSampleId = QR.TrackedSampleId
    LEFT JOIN HER3Result Rt ON Rt.QRID = QR.QRid
    INNER JOIN TimePoint TP ON  TP.TimePointId = TS.TimePointid
    INNER JOIN SampleType ST ON  ST.SampleTypeId =TS.SampleTypeId
    INNER JOIN CompanySite CSite ON   CSite.SiteId =TS.SiteId
    INNER JOIN CompanyStudy CStudy  ON  CStudy.StudyId =CSite.StudyID
    INNER JOIN CompanyRegion CRegion ON  CRegion.StudyId =CStudy.StudyId
    AND CRegion.RegionId = CSite.RegionId INNER JOIN Company ON
     Company.CompanyId = CRegion.CompanyId AND Company.CompanyId = CStudy.CompanyId
    AND Company.CompanyId = CSite.CompanyID
    where Cstudy.Studyid =' + CONVERT (VARCHAR(4),@StudyID)

    EXEC (@SQL)

    Drag the Usp to my dbml it created a  signature as follow
    [global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.Usp_StudyDataExtract")]
      public int Usp_StudyDataExtract([global::System.Data.Linq.Mapping.ParameterAttribute(Name="StudyID", DbType="Int")] System.Nullable<int> studyID)
      {
       IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), studyID);
       return ((int)(result.ReturnValue));
      }

    So this say that the usp return a int value but it return a dataset

    So how do I set the public int Usp_StudyDataExtract to return a list and how do set the list type  ?
    All I am doing is I wanted to save what is returning from Usp_StudyDataExtract to an excel file
    So what is the best solution for this.
    There are many example on the web but  could not find a one which run a dynamic sql.
    Appreciate all your help and assistance 

    • Moved by Annabella Luo Monday, May 7, 2012 2:58 AM (From:Windows Presentation Foundation (WPF))
    Thursday, May 3, 2012 1:30 PM

All replies

  • Hi Rushdy,

    According to your description, I understand your issue is a data one, so I'm moving your thread to linq to sql forum, so that you can get a better help.

    Thank you for your understanding and support.

    Have a nice day.


    Annabella Luo[MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 7, 2012 2:57 AM