"Steven Hughes" <shughes@noemail.nospam> wrote in message
news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@microsoft.com...
>I have a C# stored procedure that I use to run a query, do some processing
>on
> the results and send the results back to the caller via
> SqlPipe.SendResultsRow().  It looks something like this:
>
> string myConditions =
> MyFunctionToBuildTheConditionsFromCallerProvidedData();
> SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
> myConditions, contextConnection);
>
> dataRecord = MyFunctionToBuildTheDataRecord();
> sqlPipe.SendResultsStart(dataRecord);
>
> SqlDataReader reader = cmd.ExecuteReader();
> while (reader.Read())
> {
>    // Do some processing
>
>    if (someInternalConditonForThisRecordIsMet)
>    {
>        sqlPipe.SendResultsRow(dataRecord);
>    }
> }
>
> sqlPipe.SendResultsEnd();
> }
>
> Not every record from the original dataset is returned.  The caller is
> using
> SqlCommand.ExecuteReader() to retrieve the set of records from the stored
> procedure.
>
>
> Here's the problem...
> The caller is a web page that allows the user to specify search criteria.
> If the user specifies search criteria that is too broad, the query will
> return tens of thousands of records.  To prevent this, I want my C#
> procedure
> to stop after some number of maximum records and return an error.  So that
> looks something like this:
>
>    if (someInternalConditonForThisRecordIsMet)
>    {
>        if (++recordCounter <= maximumRecordsToReturn)
>        {
>            sqlPipe.SendResultsRow(dataRecord);
>        }
>        else
>            // Bail out and report an error to the caller
>    }
>
>
> I've tried throwing an exception in the 'else' but Sql Server seems to
> absorb it.
> The caller receives exactly the maximum number of records but doesn't
> catch
> an exception.
>        else
>            throw new ApplicationException("Too many records matched search
> criteria.");
>
> I could set the function to return an integer indicating if there were too
> many results, but I could not find a property/method of the SqlDataReader
> that retrieved the value returned from the procedure.
>        else
>            break;  // break out of while (reader.Read())
>
>        // this line is at the ned of the procedure
>        return recordCounter > maximumRecordsToReturn ? 1 : 0;
>
>
> I could set an output parameter on the stored procedure, but again, I
> could
> not find a property/method of the SqlDataReader class that could retrieve
> the
> output parameter.
>
>
> How could/should I return this error condition back to the caller?
>
> --
>
> Steven Hughes - MCSD

"Arnie Rowland" wrote:

> Try closing the datareader and then getting the return value. The return
> value is only available after the operation finishes. While the datareader
> is open, the operation is not complete. (I know, that seems odd - but who
> are we to question...)
>
> dr.Close();
> int i=(int)cmd.Parameters["@retval"].Value;
>
>
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Steven Hughes" <shughes@noemail.nospam> wrote in message
> news:6C5FAECA-1795-480E-92F2-9AB5A5B126E4@microsoft.com...
> >I have a C# stored procedure that I use to run a query, do some processing
> >on
> > the results and send the results back to the caller via
> > SqlPipe.SendResultsRow().  It looks something like this:
> >
> > string myConditions =
> > MyFunctionToBuildTheConditionsFromCallerProvidedData();
> > SqlCommand cmd = new SqlCommand("SELECT * FROM myTable WHERE" +
> > myConditions, contextConnection);
> >
> > dataRecord = MyFunctionToBuildTheDataRecord();
> > sqlPipe.SendResultsStart(dataRecord);
> >
> > SqlDataReader reader = cmd.ExecuteReader();
> > while (reader.Read())
> > {
> >    // Do some processing
> >
> >    if (someInternalConditonForThisRecordIsMet)
> >    {
> >        sqlPipe.SendResultsRow(dataRecord);
> >    }
> > }
> >
> > sqlPipe.SendResultsEnd();
> > }
> >
> > Not every record from the original dataset is returned.  The caller is
> > using
> > SqlCommand.ExecuteReader() to retrieve the set of records from the stored
> > procedure.
> >
> >
> > Here's the problem...
> > The caller is a web page that allows the user to specify search criteria.
> > If the user specifies search criteria that is too broad, the query will
> > return tens of thousands of records.  To prevent this, I want my C#
> > procedure
> > to stop after some number of maximum records and return an error.  So that
> > looks something like this:
> >
> >    if (someInternalConditonForThisRecordIsMet)
> >    {
> >        if (++recordCounter <= maximumRecordsToReturn)
> >        {
> >            sqlPipe.SendResultsRow(dataRecord);
> >        }
> >        else
> >            // Bail out and report an error to the caller
> >    }
> >
> >
> > I've tried throwing an exception in the 'else' but Sql Server seems to
> > absorb it.
> > The caller receives exactly the maximum number of records but doesn't
> > catch
> > an exception.
> >        else
> >            throw new ApplicationException("Too many records matched search
> > criteria.");
> >
> > I could set the function to return an integer indicating if there were too
> > many results, but I could not find a property/method of the SqlDataReader
> > that retrieved the value returned from the procedure.
> >        else
> >            break;  // break out of while (reader.Read())
> >
> >        // this line is at the ned of the procedure
> >        return recordCounter > maximumRecordsToReturn ? 1 : 0;
> >
> >
> > I could set an output parameter on the stored procedure, but again, I
> > could
> > not find a property/method of the SqlDataReader class that could retrieve
> > the
> > output parameter.
> >
> >
> > How could/should I return this error condition back to the caller?
> >
> > --
> >
> > Steven Hughes - MCSD
>
>
>

"Charles Wang[MSFT]" wrote:

> Hi,
> Thanks for your post!
>
> From your description, I understand that:
> You were using C# to develop the CLR SQL Stored Procedure;
> you found when a heavy load query from Web degraded the performance
> seriously.
> If I have misunderstood, please to let me know.
>
> You may write a common stored procedure to get any query result row count
> for appropriate decision by your application.
> Also you may write a pagination stored procedure to get a specified count
> of records once for appropriate display.
> Here is a sample just for reference:
> CREATE Procedure proc_getquerybypage
> (
>    @PageSize int,   -- record count of every page 
>    @PageNumber int, -- current page number
>    @QuerySql varchar(1000),--partial query string ,like '* From TABLENAME
> order by ID desc'
>    @KeyField varchar(500) 
> )
> AS
> Begin
>  Declare @SqlTable AS varchar(1000)
>  Declare @SqlText AS Varchar(1000)
>
>  Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+'
> '+@QuerySql
>  Set @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '
>         +'('+@SqlTable+') As TembTbA '
>         +'Where '+@KeyField+' Not In (Select Top
> '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
>         +'('+@SqlTable+') AS TempTbB)'
>  Exec(@SqlText)
> End
> GO
>
> You may refer to this article for "CLR Stored Procedure":
> http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
>
> Please note that this is a C#.NET development issue, when you meet such
> issue next time,  I recommend you post it to
> microsoft.public.dotnet.languages.csharp for wider audience and more
> professional solution than here.
>
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
>
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> =====================================================
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
>
> This and other support options are available here:
>
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
>  https://partner.microsoft.com/US/technicalsupport/supportoverview/
>
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

> Unfortunately, the situation is not that simple.  Not every row that
> is selected in the stored procedures query is returned to the caller.
> Complex logic which includes pulling data from other tables for each
> record in question must be performed to determine if the record can be
> sent back to the caller.  This logic must run for all records in the
> table before a count of how many records will be returned is known.
>
> "Charles Wang[MSFT]" wrote:
>
>> Hi,
>> Thanks for your post!
>> From your description, I understand that:
>> You were using C# to develop the CLR SQL Stored Procedure;
>> you found when a heavy load query from Web degraded the performance
>> seriously.
>> If I have misunderstood, please to let me know.
>> You may write a common stored procedure to get any query result row
>> countYour
>> for appropriate decision by your application.
>> Also you may write a pagination stored procedure to get a specified
>> count
>> of records once for appropriate display.
>> Here is a sample just for reference:
>> CREATE Procedure proc_getquerybypage
>> (
>> @PageSize int,   -- record count of every page
>> @PageNumber int, -- current page number
>> @QuerySql varchar(1000),--partial query string ,like '* From
>> TABLENAME
>> order by ID desc'
>> @KeyField varchar(500)
>> )
>> AS
>> Begin
>> Declare @SqlTable AS varchar(1000)
>> Declare @SqlText AS Varchar(1000)
>> Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS
>> varchar(30))+' '+@QuerySql Set @SqlText='Select Top '+Cast(@PageSize
>> AS varchar(30))+' * From ' +'('+@SqlTable+') As TembTbA ' +'Where
>> '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS
>> varchar(30))+' '+@KeyField+' From ' +'('+@SqlTable+') AS TempTbB)'
>> Exec(@SqlText) End GO
>>
>> You may refer to this article for "CLR Stored Procedure":
>> http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
>>
>> Please note that this is a C#.NET development issue, when you meet
>> such issue next time,  I recommend you post it to
>> microsoft.public.dotnet.languages.csharp for wider audience and more
>> professional solution than here.
>>
>> If you have any other concerns, please feel free to let me know. It's
>> my pleasure to be of assistance.
>>
>> +++++++++++++++++++++++++++
>> Charles Wang
>> Microsoft Online Partner Support
>> +++++++++++++++++++++++++++
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>>
>> =====================================================
>> Business-Critical Phone Support (BCPS) provides you with technical
>> phone
>> support at no charge during critical LAN outages or "business down"
>> situations. This benefit is available 24 hours a day, 7 days a week
>> to all
>> Microsoft technology partners in the United States and Canada.
>> This and other support options are available here:
>>
>> BCPS:
>> https://partner.microsoft.com/US/technicalsupport/supportoverview/400
>> 10469
>> Others:
>> https://partner.microsoft.com/US/technicalsupport/supportoverview/
>> If you are outside the United States, please visit our International
>> Support page:
>> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
>> =====================================================
>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>

"Dan Sullivan" wrote:

> Your stored proc can just return the result code you wish, return a non-zero
> value to indicate that something did not go as expected. You can also use
> RAISERROR to raise a waring or error to the client. Here is a simple example
> for an sp called LimitedSP.  colors is a table with some three rows of colors
> colors in it. Using it looks like:
>
> DECLARE @resultCode int
> exec @resultCode = LimitedSP
> SELECT @resultCode
>
> and returns
>
> color
> --------
> red
> green
>
> -----------
> 1 
>
> result code of sp was 1, which by convention in this sp means not all rows
> were returnd.
>
>
> Here is the sp itself.
>
> public partial class StoredProcedures
> {
>     [Microsoft.SqlServer.Server.SqlProcedure]
>     // make sp return an Int32, this will the result code from SP
>     public static Int32 LimitedSP()
>     {
>         using (SqlConnection conn = new SqlConnection("context connection=true"))
>         using (SqlCommand cmd = new SqlCommand("SELECT color FROM colors", conn))
>         {
>             SqlPipe pipe = SqlContext.Pipe;
>             conn.Open();
>             SqlMetaData[] md = new SqlMetaData[1];
>             md[0] = new SqlMetaData("color", SqlDbType.NVarChar, SqlMetaData.Max);
>             SqlDataRecord rec = new SqlDataRecord(md);
>             pipe.SendResultsStart(rec);
>             using (SqlDataReader rdr = cmd.ExecuteReader())
>             {
>                 Int32 limit = 2; // don't return more than four rows
>                 Int32 index = 1;
>                 while (rdr.Read())
>                 {
>                     string color = rdr.GetSqlString(0).Value;
>                     rec.SetSqlString(0, color);
>                     if (index > limit)
>                     {
>                         pipe.SendResultsEnd();
>                         rdr.Close();
>                         // use higher level if you want this to be more than a warning
>                         cmd.CommandText = "RAISERROR('too many rows', 10, 1)";
>                         cmd.ExecuteNonQuery();
>                         return 1; // indicates not all rows returned
>                     }
>                     pipe.SendResultsRow(rec);
>                     index++;
>                 }
>                 pipe.SendResultsEnd();
>                 return 0; // indicates all rows were returned
>             }
>         }
>     }
> };
>
>
> Dan
>
>
> > Unfortunately, the situation is not that simple.  Not every row that
> > is selected in the stored procedures query is returned to the caller.
> > Complex logic which includes pulling data from other tables for each
> > record in question must be performed to determine if the record can be
> > sent back to the caller.  This logic must run for all records in the
> > table before a count of how many records will be returned is known.
> >
> > "Charles Wang[MSFT]" wrote:
> >
> >> Hi,
> >> Thanks for your post!
> >> From your description, I understand that:
> >> You were using C# to develop the CLR SQL Stored Procedure;
> >> you found when a heavy load query from Web degraded the performance
> >> seriously.
> >> If I have misunderstood, please to let me know.
> >> You may write a common stored procedure to get any query result row
> >> countYour
> >> for appropriate decision by your application.
> >> Also you may write a pagination stored procedure to get a specified
> >> count
> >> of records once for appropriate display.
> >> Here is a sample just for reference:
> >> CREATE Procedure proc_getquerybypage
> >> (
> >> @PageSize int,   -- record count of every page
> >> @PageNumber int, -- current page number
> >> @QuerySql varchar(1000),--partial query string ,like '* From
> >> TABLENAME
> >> order by ID desc'
> >> @KeyField varchar(500)
> >> )
> >> AS
> >> Begin
> >> Declare @SqlTable AS varchar(1000)
> >> Declare @SqlText AS Varchar(1000)
> >> Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS
> >> varchar(30))+' '+@QuerySql Set @SqlText='Select Top '+Cast(@PageSize
> >> AS varchar(30))+' * From ' +'('+@SqlTable+') As TembTbA ' +'Where
> >> '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS
> >> varchar(30))+' '+@KeyField+' From ' +'('+@SqlTable+') AS TempTbB)'
> >> Exec(@SqlText) End GO
> >>
> >> You may refer to this article for "CLR Stored Procedure":
> >> http://msdn2.microsoft.com/zh-cn/library/ms131094.aspx
> >>
> >> Please note that this is a C#.NET development issue, when you meet
> >> such issue next time,  I recommend you post it to
> >> microsoft.public.dotnet.languages.csharp for wider audience and more
> >> professional solution than here.
> >>
> >> If you have any other concerns, please feel free to let me know. It's
> >> my pleasure to be of assistance.
> >>
> >> +++++++++++++++++++++++++++
> >> Charles Wang
> >> Microsoft Online Partner Support
> >> +++++++++++++++++++++++++++
> >> When responding to posts, please "Reply to Group" via your newsreader
> >> so that others may learn and benefit from your issue.
> >>
> >> =====================================================
> >> Business-Critical Phone Support (BCPS) provides you with technical
> >> phone
> >> support at no charge during critical LAN outages or "business down"
> >> situations. This benefit is available 24 hours a day, 7 days a week
> >> to all
> >> Microsoft technology partners in the United States and Canada.
> >> This and other support options are available here:
> >>
> >> BCPS:
> >> https://partner.microsoft.com/US/technicalsupport/supportoverview/400
> >> 10469
> >> Others:
> >> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> >> If you are outside the United States, please visit our International
> >> Support page:
> >> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> >> =====================================================
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
>
>
>