Last post Apr 30, 2012 07:44 PM by Loganix77
Apr 30, 2012 12:02 PM|Anand Reddy|LINK
I have a web page with some controls
1) text boxes
2) labels and dropdownlists
Totally I have 4 stored procedures (sp) to use.
a) First sp is used to fetch data to fill in textboxes ( this data come from properties after sp has executed in DAO layer)
b) Second sp is used to fill all the 20 dropdowns (returns DataTable using which all dropdowns are binded)
c) Third sp is used to display 20 labels text dynamically (each label is located in front of one dropdown) (returns DataTable using which we bind data to all labels)
d) Fourth sp is used to select the dropdown selected item
The scenario how I am calling all the stored procedures is..
In the page_Load -> I am calling First sp, if the datatable which it returns has count >0 i'm sending a boolean value true to code behind.
If the boolean value is true, I am calling second and third sp and filling dropdowns and labels text,
finally I am checking the condition which item should be selected in the drop down list..
Every time I am calling a new function to execute stored procedure.
Is this the correct scenario what iam doing??
If not then please tell me a better way to call...
Apr 30, 2012 02:02 PM|praseTech|LINK
Are you planning to call multiple sp from one another? If no then use transaction scope at the code level to ensure that the code work in transaction. It should complete enitrely or nothing.
Apr 30, 2012 02:12 PM|2177|LINK
Create a new wrapper proc. This proc can call all your 4 procs based on some conditions you may want to apply. So the proc will return DataSet and in code you can do whatever you want to with this DataSet.
This way all you need is just call the wrapper proc from the code.
Apr 30, 2012 02:13 PM|Loganix77|LINK
If your SP's very fast to populate you may want to consider only making one pass at the DB. You could add all your parameters at once if possible and load all of your result sets into a DataSet on your web server. You can then check your dataset results
and execute your conditional codes based on the values in your datatables inside your dataset. Your dataset would then act like a temporary database housing the temporary tables you created with one pass on your DB.
I personally prefer to limit the number of times I need to hit the database on a page. By using a dataset with several datatables populated by your SP you would only have to hit the DB once to pull all your data.
So your SP would Fill your Dataset, in the order your result sets are created.
So Query1 in the SP would be DataSet.DataTable(0)
Query2 to would be DataSet.DataTable(1) and so on
There isn't really anything "wrong" with the way your doing it now, but by retreiving all of your records in one pass on the Database if possible, you limit the number of connections being made to the DB which is normally a good thing :)
Apr 30, 2012 03:28 PM|Anand Reddy|LINK
Can you send me sample code. So that I can call multiple sp as transaction..
Apr 30, 2012 07:22 PM|Loganix77|LINK
Ok for example.
Here I have an SP that pulls to recordsets back for our customer service advisors. We want to see detailed sales information and we want to see summary information for their budgets:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[usp_CSA_Budget]
Declare @command as varchar(8000)
Declare @tblSales as Table (CSA nvarchar(10),Br nvarchar(5),CustNo nvarchar(50), CustName nvarchar(200),MTDParts Float, MTDService Float,
MTDTotal Float, YTDParts Float, YTDService Float, YTDTotal Float)
set @command = 'Select Statment Here'
Insert Into @tblSales
Select * From @tblSales
Select CSA, Sum(MTDTotal) as TotalMTD, (AnnualBudget/12) as MTDBudget, (Sum(MTDTotal) - (AnnualBudget/12)) as MTDVariance,
Sum(YTDTotal) as TotalYTD, ((AnnualBudget/12) * @vMonth) as YTDBudget, (Sum(YTDTotal) - ((AnnualBudget/12) * @vMonth)) as YTDVariance,AnnualBudget
From @tblSales as a Inner Join CSASalesmen on a.CSA = CSASalesmen.CSANo
Group By CSA, (AnnualBudget/12), ((AnnualBudget/12) * @vMonth), AnnualBudget
Now when I call that SP from my code I can populate two gridviews, one with details and the other with summary. You can databind any to any valid control (DLs, GVs, etc.) for this example I bind to two different gridviews. But you could apply this same logic to your scenario.
Dim cnn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyString").ConnectionString)
Dim sqlAdp As New Data.SqlClient.SqlDataAdapter
Dim cmd As New Data.SqlClient.SqlCommand
Dim ds As New Data.DataSet
cmd.CommandTimeout = 600
cmd.Connection = cnn
cmd.CommandText = "usp_CSA_Budget"
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Parameters.AddWithValue("vMonth", DatePart(DateInterval.Month, mDate1))
sqlAdp.SelectCommand = cmd
BudgetSummary.DataSource = ds.Tables(1)
BudgetDetails.DataSource = ds.Tables(0)
Apr 30, 2012 07:29 PM|Loganix77|LINK
In my SQL example you'll see that I'm declaring my SQL statement as an nvarchar variable. My webserver communicates with our business system via a linked server connection. Because of this I have to use OpenQuery() to query our business system. SPs are
not very friendly with the OpenQuery process but it will execute the command if it's stored as a variable and then issued the exec(command) statement.
Assuming you're not using linked servers you don't need to store the command in a string and execute it, you can just simply place your query in your SP. You'll see that all I'm doing is issuing two different select statements which returns two different
recordsets. Then I fill a dataset with the SP and datatable(0) is the first record set returned and dataset(1) is the second returned and then you just databind. I've filled two gridviews, with two different select statements, but I've only called the connection
to my DB once.
Apr 30, 2012 07:37 PM|Loganix77|LINK
On a side note, talking about only making one call to the database. You'll see in my SQL SP that I'm storing my intial query into a temporary table. What I'm doing here is telling my SP to collect all the relevant information I need in one query. I use
that data to create a temporary table variable in my SP. Then I issue queries against that temporary Table to populate my recordsets. So my Page makes a single call to my database for the information it wants for the report, and my database makes only a
single call to the remote database as well.
I just feel it's always best to keep the number of connections made to a minimum :)
Apr 30, 2012 07:44 PM|Loganix77|LINK
Here's a sample of using transact code to run multiple SPs. The SPs in this code are performing update commands on my database so I've got the transact code setup to rollback any transactions if a part of the process fails. It also catches the error and
stores it so I can keep track of any failures. This is probably a little more than what you're needing for what you're trying to accomplish:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Will B
-- Create date: 01/30/2009
-- Description: Transact Proc To Update Rental Profit Report
ALTER PROCEDURE [dbo].[usp_RentalProfitUpdate]
Exec usp_RFProfitUpdate1 @DateNum1,@DateShort2,@mrun,@yrun
Exec usp_RFProfitUPdate2 @DateNum1,@mrun,@yrun
Exec usp_RFProfitUPdate7 @DateNum1,@mrun,@yrun
Exec usp_RFProfitUpdate3 @DateShort1,@DateShort2,@mrun,@yrun
Exec usp_RFProfitUpdate4 @mrun,@yrun
Exec usp_RFProfitUpdate5 @PeriodYear,@mrun,@yrun
Exec usp_RFProfitUpdate6 @mrun,@yrun
If @@Trancount > 0 Rollback
Insert Into RentalProfitabilityTblErr(DateOfError,ErrorNum,ErrorProc)