I have a C# stored procedure that I use to run a query, do some processing o
n
the results and send the results back to the caller via
SqlPipe.SendResultsRow(). It looks something like this:
string myConditions = MyFunctionToBuildTheConditionsFromCaller
ProvidedData()
;
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# procedur
e
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 th
e
output parameter.
How could/should I return this error condition back to the caller?
Steven Hughes - MCSDTry 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 =
> MyFunctionToBuildTheConditionsFromCaller
ProvidedData();
> 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|||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 w
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/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.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 th
e
caller. This logic must run for all records in the table before a count of
how many records will be returned is known.
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 w
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/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||That works!! Thank you.
Too bad I have to read in the resultset before being able to retrieve the
output parameter value though.
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...
>
>|||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 color
s
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:
>|||Interesting... so I use RAISEERROR like I would from a standard stored
procedure then.
Ok. Thank you.
Steven Hughes - MCSD
"Dan Sullivan" wrote:
> Your stored proc can just return the result code you wish, return a non-ze
ro
> 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 examp
le
> for an sp called LimitedSP. colors is a table with some three rows of col
ors
> 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
>
>
>|||Hi,
I'm glad to see you got the answer you want.
Thanks for using Microsoft Newsgroup.
If you have any other concerns, please don't hesitate to let us know.
Enjoy your day!
+++++++++++++++++++++++++++
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 w
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/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
)