Return Value when using CLR stored procedure. (Edit: For use in SQL side paging)
I have a stored procedure that I put together using the CLR. The basic idea is the have a query that can accept any number of values. I havent included the portion of the code that handles smalldatetime searches and I havent yet finished the logic to handle sql data type constraints but I have been working on paging using SQL's Row_Number(). The code below works but I havent been able to come up with a way to find out either how many pages the result set has or how many total rows in the result set. I thought there might be a way to use a procedure's Return Value but I can't figure out how that can be done when using CLR based procedures.
Any ideas on where to start would be appreciated.
public static void DynamicSearch(int rtype, string dfields, int pageSize, int pageIndex, string sortBy, int sortDirection) { //Search parameters received from .aspx in format fieldName|fieldType|searchValue //Multiple search parameter segments are delimited with ^ String[] searchParams = dfields.Split('^'); SqlPipe p; //create the query and sqlcommand string cmdstr = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " + sortBy + " " + sortDirection.ToString().Replace("0", "ASC").Replace("1", "DESC") + ") AS Row, * FROM myFiles WHERE "; SqlCommand sCmd = new SqlCommand(); //build in the parameters to the query foreach (string param in searchParams) { //parse out the params and add to query String[] paramParts = param.Split('|'); string fieldName = paramParts[0].ToString(); string fieldType = paramParts[1].ToString(); string searchValue = paramParts[2].ToString(); searchValue = "%" + searchValue + "%"; sCmd.Parameters.AddWithValue("@" + fieldName, searchValue); cmdstr += "(" + fieldName + " LIKE @" + fieldName + ") AND "; } //paging logic int startRow = pageIndex * pageSize + 1; int endRow = startRow + (pageSize - 1); cmdstr = cmdstr + ") AS Rownumbers WHERE Row BETWEEN " + startRow + " AND " + endRow + ";"; //Tie off querystring cmdstr = cmdstr.Replace(" AND ) AS Rownumbers", ") AS Rownumbers"); //Apply querystring and run command sCmd.CommandText = cmdstr; p = SqlContext.Pipe; p.ExecuteAndSend(sCmd); }
|
0 |
|
svelasquez123
2/16/2009 8:36:16 PM
asp.net.sql-datasource
29906 articles. 
0 followers.
5 Replies
1542 Views
Are you sure you needed CLR and dynamic SQL to handle many parameters?
For similar requirement we were using SQLWhereBuilder from Code Project.
http://www.codeproject.com/KB/custom-controls/SqlWhereBuilder.aspx
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
|
|
0 |
|
Naom
2/17/2009 1:56:10 AM
Naom,
Your solution is definately more polished and I'll take a look to see if I can use it or something like it instead of a CLR solution. I'm wondering though, when paging through records is it faster to pass the whole result set to asp.net and page through it using some sort of asp.net paging solution or would it be faster to just use SQL to page the data and pass only one page of results at a time?
I guess the real problem I am having is that using ROW_NUMBER() to page on the SQL side, regardless of whether I use CLR or your solution, I would have to run the SELECT statement twice. I tried using @@ROWNUMBER like:
SELECT @@ROWCOUNT AS totalHits;
but I only get the number of rows that were returned for that page so when my page size is 10 totalHits is returned as 10 unless I run a second select using the same WHERE values in a second SELECT and then @@ROWCOUNT that resultset.
...if that makes sense.
Thanks for your input.
-Sam
|
|
0 |
|
svelasquez123
2/17/2009 4:41:42 AM
Please see http://www.code-magazine.com/Article.aspx?quickid=0703041
Sorry, I am very busy right now for the longer answer, I'll answer from home tonight.
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
|
|
0 |
|
Naom
2/17/2009 4:27:05 PM
Naom:
Please see http://www.code-magazine.com/Article.aspx?quickid=0703041
Sorry, I am very busy right now for the longer answer, I'll answer from home tonight.
Mr. Goff's use of CTEs looks like it will give me what I need to make it work. I tested it with something like:
CREATE PROCEDURE [dbo].[testGetMyFiles] AS BEGIN WITH SearchResults AS (SELECT *, ROW_NUMBER() OVER (ORDER BY LastName ASC) AS ResultsRow FROM myFiles WHERE FirstName LIKE '%an%') SELECT * FROM (SELECT SearchResults.*, (SELECT COUNT(*) FROM SearchResults) AS RecCount FROM SearchResults)SearchRes WHERE ResultsRow BETWEEN 10 and 20 END
Using this gives me the rows that I was looking for and two additional rows ResultsRow and RecCount which have the assigned row number and the total number of matches for the parameters supplied respectively. I can now use these values in these columns and come up with some paging logic.
If anyone else finds this thread, I ran across another post (http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx) that uses the Common Table Expressions that Mr. Goff's example uses. Both examples are using very large result sets so I'll head down this direction.
Thanks a lot for the direction Naom.
|
|
0 |
|
svelasquez123
2/17/2009 8:36:27 PM
My pleasure. Don't forget to mark the responses, though :)
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
|
|
0 |
|
Naom
2/17/2009 8:38:34 PM
Similar Artilces:
How do I use the return value from a SQL stored procedure?
I want to be able to use the returned number to set a Label accordingly.Something like this:if the return value == 1 then Label1.Text = "Correct" else Label1.Text = "Wrong" What is the correct syntax for this? I couldn't find this answer. If you've just got one return parameter, I usually stick with ExecuteScalar. This will execute a stored procedure and return an object, which is taken from the first row and first column of whatever the stored procedure returns.So at the end of the stored procedure, after doing what you need to do, add a select stat...
Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource? <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...
How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...
Do i have to use sql cache dependency if i am using stored procedure?
Hi guyz! i just have 1 question: Do i have to use sql cache dependency if i am using stored procedure?ez no need***Hope this helps you***thank you,-nm reddy Ah ok.. One more thing, is it okay to enable all my table for sql cache dependency?ez anyone is welcome to replyez hello?ez Is there solution with my problem?ez Ez416:is it okay to enable all my table for sql cache dependency? Yes you can...but not recommended.Each table you enable is an extra bit of overhead to the Sqlserver...because it has to maintain triggers, procedures on those tables for monitoring.You should e...
How to Retrive the store procedure return value in Ado.Net by using Dot Net?
Hi i am thiru. i try to create the small search engine. so that i have wrote the store procedure for it. thats given below.Create procedure FindtestThree @Name varchar(25), @Location varchar(25), @EmpId varchar(25) as select * from TbInfo where Name like + '%' + @Name + '%' select * from TbInfo where Location like + '%' + @Location + '%' select * from hi where ComName like + '%' + @EmpId + '%' Exec FindtestThree 'aa','a','d' ----------------------------------------- The above procedure return t...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
How do I use a stored procedure to join two sql tables and use the fields for a listview
I am trying to create a listview combining fields of two tables with a common record. I have a stored procedure with one parameter (@loginID). I want to use this paramater to look for a common record in two tables, and join the date of the fields of the two selected records. I know this isn't right, but hopefully someone can modify it for me.Create PROCEDURE [dbo].[tblLoginSelectCommandWithParam] [ @LoginID integer ] AS Select * from tblLogins Select * from tblLoginDetail Join tblLoginDetail Where @LoginID = LoginID SELECT tblLogins.*, t...
Using SQL Server Stored Procedure for implementing Custom Paging
Hi, All of us would have implemented Paging in our applications.Paging is particularly useful if you have lots of records to be displayed on a page and you can't get them displayed in one stretch. Say we have 1000 records to be displayed in a page. In this scenario, we cannot show up all the records in a single stretch in the page. Hence we need to implement Paging functionality whereby users can see a set of records and then click on a Button/Link to view the next set of records. This <a href="http://aspnet_harish.blogspot.com/2005_03_01_aspnet_harish_archive.html">Article</a>...
Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...
saving .net DateTime to sql database using Stored Procedure...
Hello, I've read many posts on this, but not found any real answers. I have a web method that accepts a DateTime parameter, then writes this value back to a SQL database table. Sql data type is "datetime" and the column accepts nulls. When the web service is called I get an error, "SqlTypeException: SqlDateTime overflow" or another error of similiar results.When I removed the database call and just write to a label or variable on the page, the web method works fine, but when it saves to the database, the error is thrown. Any ideas on how to handle...
Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...
Capture Return Value from Stored Procedure, Use Same in Code Behind Page
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted. Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varcha...
Capturing SQL Return codes from Stored Procedure when using Gridview or Detailsview
I have an application that does some editing or inserting of data. I use GRIDVIEW or DETAILSVIEW to edit the data. I use stored procedures to to the updates. My stored procedures returns different integer values depending on what happens. I send back 0 if sucessful, general database errors = -1 and I send back a 9 if the data I'm attempting to insert already exists. When I write the code to call the stored procedures in the code behind I can easily catch the values. The problem I have right now is I don't know how to catch these errors when using t...
sql stored procedure
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD...
I want to be able to use the returned number to set a Label accordingly.Something like this:if the return value == 1 then Label1.Text = "Correct" else Label1.Text = "Wrong" What is the correct syntax for this? I couldn't find this answer. If you've just got one return parameter, I usually stick with ExecuteScalar. This will execute a stored procedure and return an object, which is taken from the first row and first column of whatever the stored procedure returns.So at the end of the stored procedure, after doing what you need to do, add a select stat...
Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource? <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...
How to transfer a GUID created using vb.net into a SQL database using a stored procedure
I am able to create a guid using: Public Function GetGUID() As String ' Returns a new GUID Return System.Guid.NewGuid.ToString End Function however when I try to add this to a parameter using the following: Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = GetGUID() I get an error, I have also tried this: Dim uidQuoteDetail As String = GetGUID() Dim myuid = New System.Guid(uidQuoteDetail) Me.cmdSpAddOptions.Parameters("@QuoteDetailID").Value = myuid but get the error "Object must implement IConvertible" A...
Do i have to use sql cache dependency if i am using stored procedure?
Hi guyz! i just have 1 question: Do i have to use sql cache dependency if i am using stored procedure?ez no need***Hope this helps you***thank you,-nm reddy Ah ok.. One more thing, is it okay to enable all my table for sql cache dependency?ez anyone is welcome to replyez hello?ez Is there solution with my problem?ez Ez416:is it okay to enable all my table for sql cache dependency? Yes you can...but not recommended.Each table you enable is an extra bit of overhead to the Sqlserver...because it has to maintain triggers, procedures on those tables for monitoring.You should e...
How to Retrive the store procedure return value in Ado.Net by using Dot Net?
Hi i am thiru. i try to create the small search engine. so that i have wrote the store procedure for it. thats given below.Create procedure FindtestThree @Name varchar(25), @Location varchar(25), @EmpId varchar(25) as select * from TbInfo where Name like + '%' + @Name + '%' select * from TbInfo where Location like + '%' + @Location + '%' select * from hi where ComName like + '%' + @EmpId + '%' Exec FindtestThree 'aa','a','d' ----------------------------------------- The above procedure return t...
sql count using stored procedure withing stored procedure
I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing for each user name in a temporary table (already done) total = result from execute totaling stored procedure Can you help with this Thanks It would be easier if you can change the stored procedure into a function. Once you do that, the total can be calculated easily with something like thisSelect Sum(dbo.CalculateHours(User...
How do I use a stored procedure to join two sql tables and use the fields for a listview
I am trying to create a listview combining fields of two tables with a common record. I have a stored procedure with one parameter (@loginID). I want to use this paramater to look for a common record in two tables, and join the date of the fields of the two selected records. I know this isn't right, but hopefully someone can modify it for me.Create PROCEDURE [dbo].[tblLoginSelectCommandWithParam] [ @LoginID integer ] AS Select * from tblLogins Select * from tblLoginDetail Join tblLoginDetail Where @LoginID = LoginID SELECT tblLogins.*, t...
Using SQL Server Stored Procedure for implementing Custom Paging
Hi, All of us would have implemented Paging in our applications.Paging is particularly useful if you have lots of records to be displayed on a page and you can't get them displayed in one stretch. Say we have 1000 records to be displayed in a page. In this scenario, we cannot show up all the records in a single stretch in the page. Hence we need to implement Paging functionality whereby users can see a set of records and then click on a Button/Link to view the next set of records. This <a href="http://aspnet_harish.blogspot.com/2005_03_01_aspnet_harish_archive.html">Article</a>...
Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...
saving .net DateTime to sql database using Stored Procedure...
Hello, I've read many posts on this, but not found any real answers. I have a web method that accepts a DateTime parameter, then writes this value back to a SQL database table. Sql data type is "datetime" and the column accepts nulls. When the web service is called I get an error, "SqlTypeException: SqlDateTime overflow" or another error of similiar results.When I removed the database call and just write to a label or variable on the page, the web method works fine, but when it saves to the database, the error is thrown. Any ideas on how to handle...
Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...
Capture Return Value from Stored Procedure, Use Same in Code Behind Page
My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@myERROR" then display in the label I have that a duplicate entry was attempted. Stored ProcedureCREATE PROCEDURE dbo.usp_InsertNew @IDNumber nvarchar(25), @ID nvarchar(50), @LName varchar(50), @FName varcha...
Capturing SQL Return codes from Stored Procedure when using Gridview or Detailsview
I have an application that does some editing or inserting of data. I use GRIDVIEW or DETAILSVIEW to edit the data. I use stored procedures to to the updates. My stored procedures returns different integer values depending on what happens. I send back 0 if sucessful, general database errors = -1 and I send back a 9 if the data I'm attempting to insert already exists. When I write the code to call the stored procedures in the code behind I can easily catch the values. The problem I have right now is I don't know how to catch these errors when using t...
sql stored procedure
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD...
Web resources about - Return Value when using CLR stored procedure. (Edit: For use in SQL side paging) - asp.net.sql-datasource
Parliamentary procedure - Wikipedia, the free encyclopedia... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Rushed cosmetic procedures a 'recipe for disaster'​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?
What is some of the worst behavior you've witnesses by attorneys?
NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher ReportThe NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...
We need to reform the culture of law enforcement, not just the proceduresWe need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as tubal ligation would violate its religious freedom
Hospital in California argued that the procedure known as tubal ligation would violate its religious freedom
TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...
Resources last updated: 1/18/2016 12:43:41 AM