Databases :: Accessing Stored Procedure With Try Catch Finally Block
Apr 2, 2010
I wrote a stored procedure call with the format:
bool isValid=true;
using (OracleConnection myConn = new OracleConnection(connectionString))
{
OracleCommand myCmd = new OracleCommand("sproc", myConn);
myCmd.CommandType = CommandType.StoredProcedure;
myCmd.Parameters.AddWithValue("p1", a1);[code].....
Second format just doesn't feel right but I am not that experienced. I am curious if there is any downfall to that other than exception not being type specific.
I am creating asp.net web apps in .net 3.5 and I wanted to know when to use and when not to use Try Catch Finally blocks? In particular, a majority of my try catch's are wrapped around executing stored procs and populating textfields or gridviews? Would you use Try Catch EVERYTIME when you execute a stored proc and populated a data display control?
I have a stored procedure that creates a dynamic query. Sometimes the query is not built because there are no elements in the dynamic query. When the aspx page loads the SqlDataSource throws an error that the query is malformed. How can I catch this error, and handle it with a default message, before it returns the error to the aspx page?
i have a stored procedure and i want to return a value from that procedure and returned value i want to store in a variable Stored Procedure is create procedure simpleproc(OUT pcount int)begin set @pcount=(select count(*) from testtable); end
Now i call this proc from C# code
string query="CALL simpleproc(@count)"; MySqlCommand commond = new MySqlCommand(); MySqlParameter returnValue = new MySqlParameter("@count", DbType.Int32); returnValue.Direction = ParameterDirection.ReturnValue; commond.Parameters.Add(returnValue); commond.CommandText = query; commond.Connection = con; // I already set the connection in con and opened it commond.ExecuteNonQuery(); int count = Int32.Parse(command.Parameters["@count"].Value.ToString());
What should i have to change to get return value in count
var a=conn.getData2();//getting the value from a global class conn.cs OracleConnection con = conn.s(); OracleCommand oc = new OracleCommand("logedin(I need to pass the value here)", con);
logedin is strored procedure :-
create procedure logedin(user_id in int) as begin update user_details set status='logedin' where userid=user_id; end;
i would lke to pass the value into the stored proc that was retrieved by the variable into the database.
I'm not so familiar with stored procedures but I'm interesting in moving most of my web selects to stored procedures. I'm working with .net based website and all my data access is something like this:
[Code]....
But now I want to improve some process and make tunings. For that I want to use stored procedures, so instead of the "SELECT ...." sentence I'll put stored procedure name. My problem is how to return a table from one.
I am using PL/SQL Developer.I have a below query select distinct
DATA_SRC_CD as Source, FILE_NM as File_Name, PATH as File_Path from PKG_SFI_REPORTS.DATALOAD2SUMMARYETLRPRT("FACTS","CM_DDL_PARTY_2011_01_12_08_24_09.DAT") In the above Query PKG_SFI_REPORTS => Package, DATALOAD2SUMMARYETLRPRT=> Procedure. "FACTS" and "CM_DDL_PARTY_2011_01_12_08_24_09.DAT" => Parameters Now I need to know the Data Types of "DATA_SRC_CD" and "FILE_NM" and "PATH". How can I Know?
I have a parent window parent.aspx and a popup window popup.aspx. parent.aspx
has a confirm button which on click open popup.aspx. This popup.aspx holds a filled application form like leave application form of employees and a email sending button. That button on click send emails to the specified email addresses with attachment. The popup.aspx has a javascript function sendResponse() which call another javascript function display() of parent.aspx after sending the email. The display() function display a message by Label control regarding the email sending i.e., whether or not this email sending operation is successful. The sendResponse() function after calling display() close the popup.aspx window. I have successfully did all the aforesaid jobs.
Now the scenario is that I have a database which is holding various leave information like casual leave, medical leave etc of employees. After sending email successfully I have to update this database.It will be better to tell in this context that the email sending portion of my cs script is :
[Code]....
this info string is passed to the sendResponse() javascript function as : [Code]....
And the sendResponse() function is : [Code]....
Now the problem is, in which portion or when I do update the database. Should I update the database in try-catch block of email sending or from sendResponse() function?I have tried to call C# function UpdateDatabase() from sendRespose() as shown here. But this static method is creating too many problems like the popup.aspx is not closing automatically, if I try to call another C# non-static method from the static UpdateDatabase() method to close the window I have to call it by creating a new instance of that page, which is eventually not closing the popup window. Can I call stored procedure from javascript function sendResponse() and pass parameters to it? If so how can I call it?
I'm programming a webpage in ASP.NET which displays a list of students not listed as participants in something, and upon clicking a student name shows you a brief summary of their details, so the user can ensure they're selecting the right person.
My code currently correctly obtains their ID, adds it as a parameter to my stored procedure and executes the procedure;
[code]....
However, when I try to access the results I get the following error:
System.NullReferenceException: Object reference not set to an instance of an object.
Upon debugging, there don't seem to be any results returned... but when running just the stored procedure in SQL Server with the same data, it does return a single row, as expected.
How do I access this result so I can bind it to my fields?
(I am working in ASP.NET 3.5 in Visual Studio 2008, with SQL Server 2008.)
I'm programming a webpage in ASP.NET which displays a list of students not listed as participants in something, and upon clicking a student name shows you a brief summary of their details, so the user can ensure they're selecting the right person. Each student is identified by a unique ID.
My code currently correctly obtains their ID, adds it as a parameter to my stored procedure and executes the procedure;
[Code]....
However, when I try to access the results, with say dv.Table.Rows[0], I get the following error:
[Code]....
Upon debugging, there don't seem to be any results returned... when running just the stored procedure in SQL Server with the same data, it does return a single row, as expected.
I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008.
I've installed the asp.net schema on my hosted database, and have created several users from the ASP.NET web administration interface, as well as tested that the login works. Running the application locally with the remote connection string nets the same results. However - I'm able to run my scripts and generate my tables and stored procedures without errors - but when the site is run I get the following error on all of my .aspx pages that try to access a stored procedure:
Server Error in '/' Application. Incorrect syntax near 'LoadProfileData'. Description: An unhandled exception occurred during the execution of the current web request. review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'LoadProfileData'.
Source Error:
Line 62: adapter.SelectCommand.Parameters.Add("@ProfessionalName", SqlDbType.VarChar).Value = professionalName; Line 63: DataSet profile = new DataSet(); Line 64: adapter.Fill(profile, "Profile"); Line 65: return profile; Line 66: }
Is this a possible Sql 2005 vs 2008 issue? I'm hoping someone else has seen this issue in a similar scenario and can point me in the right direction. The server is running asp.net 2.0, 3.0 and 3.5 and IIS 7.0.
Stored Procedure:
Drop procedure CreateDefaultProfile GO Create procedure CreateDefaultProfile(@UserName varchar(256)) as declare @ProfessionalID uniqueidentifier
Here is the code copied from [URL] In asp.net code behind, I use try-catch try to catch any error but never catch it. In SQL database, if I rename Employees to Employeesx or change column DepartmentID to DepartmentIDx, record will not be deleted (it is right) without any error (it is wrong, suppose catch an error).
CREATE PROCEDURE DeleteDepartment ( @DepartmentID int ) AS BEGIN TRANSACTION DELETE FROM Employees WHERE DepartmentID = @DepartmentID IF @@ERROR <> 0 BEGIN ROLLBACK RAISERROR ('Error', 16, 1) RETURN END DELETE FROM Departments WHERE DepartmentID = @DepartmentID IF @@ERROR <> 0 BEGIN ROLLBACK RAISERROR ('Error', 16, 1) RETURN END OMMIT
i want to send mails internally and externally for eg. i want to send mail to my boss in the company and also to the customer which doesnt belong to our domain.
[URL]
and also i want to attach an excel file which is stored in my computer. so how am i going to access that file through the same procedure and then attach the file and send it to both internal as well as external email ids.
I'm still trying to deploy this site, but with every problem I solve, another arises. Anyways - I've set up the database at my hosting to allow remote connections, and it is running Sql Server 2005. On my development machine, I am working with Sql Server 2008.
I've installed the asp.net schema on my hosted database, and have created several users from the ASP.NET web administration interface, as well as tested that the login works. Running the application locally with the remote connection string nets the same results. However - I'm able to run my scripts and generate my tables and stored procedures without errors - but when the site is run I get the following error on all of my .aspx pages that try to access a stored procedure:
Server Error in '/' Application. Incorrect syntax near 'LoadProfileData'. Description: An unhandled exception occurred during the execution of the current web request. review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'LoadProfileData'.
Source Error:
Line 62: adapter.SelectCommand.Parameters.Add("@ProfessionalName", SqlDbType.VarChar).Value = professionalName; Line 63: DataSet profile = new DataSet(); Line 64: adapter.Fill(profile, "Profile"); Line 65: return profile; Line 66: }
Is this a possible Sql 2005 vs 2008 issue? I'm hoping someone else has seen this issue in a similar scenario and can point me in the right direction. The server is running asp.net 2.0, 3.0 and 3.5 and IIS 7.0.
I have tried this on two different controller methods now, and both times, even if the linq2sql doesn't allow the data to be saved, the code never jumps into the catch block.
I've watched the noun object in the middle of the trace, and the isvalid property is false, but the modelstate isvalid is true. Either way, the code never jumps into the catch block.I'm pulling my hair out about this. I feel like it will be something really silly.
The code all works similar to nerd dinner.
NounRepository nounRepository = new NounRepository(); Noun noun = new Noun(); try[code]...
I'd rather not have to add code in this manner though, as it seems like an unnecessary duplication.
I have to call the function, when exception occured. Is it possible to call the function in Catch block (Try-Catch)? I have tried it but the function did not get invoked. What i have to do for it? Shall we unable to call the functions in Catch block? I have shown my code below.
Try
Catch ex As Exception ErrorHandlers.LogErrorDetails(ex, Session("UserId").ToString, "") Throw ex End Try.
I knew, We can invoke this function in Finally block but i do not need as per my requirements.
Should I write it for each method or only in event handlers or ????
What happens when an exception occurrs? What is the stack for exception?
If I use it at entry point (event handlers) then how do I get actually at which line error occurred?
Suppose an exception was thrown from Data Access Layer How do I get it in presentation layer that exactly in which class method at which line why that error occurred?
Is it true that excessive use of try catch makes application slow?
I am being told that adding a try catch block adds major performance cost in the order of 1000 times slower than without, in the example of a for loop of a million. Is this true?
Isn't it best to use try catch block as much as possible?
Initially, I have tried to use stored procedure. But I changed my mind and preferred to call sql query in codebase with command text. However, it stills tries to find initially-called stored procedure (which is neither called or exists).I think that it is related caching. But I tried it with different browsers it did not work.What might be the reason?
