.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

c# code to create an array of parameters for oracle stored procedure

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :Windows Application


Working on a c# project that has oracle as backend.  I have problem creating a function in my codebehind page to create an array of paramenters for oracle stored procedure. for example i have the follwoing code for ms sql server...

SqlParameter[] parm = 


                         DataCon.createSqlParameter ("@id", DataCon.DBNullIfBlank(txtid.Text.ToString()), SqlDbType.Char,9 ) ,

                         DataCon.createSqlParameter ("@FName", DataCon.DBNullIfBlank(txtFName.Text.ToString()), SqlDbType.VarChar,14),

                         DataCon.createSqlParameter ("@MI", DataCon.DBNullIfBlank(txtMI.Text.ToString()), SqlDbType.Char,1),


 For the same in oracle I am using the followin

View Complete Post

More Related Resource Links

Trying to run a stored procedure from vb code with oracle data provider.



Here is my SP:

create or replace
 Open p_getuserssignon_recordset1 for
 SELECT Distinct(Userid), UserPassword, SecurityLevel, ActiveStatus
WHERE substr(UserId,1,2) <> vUid
Order By UserId;

I would like to run this SP from code and fill a gridview with the result. 

I am not sure how to go about this, as I have found several different examples, other than the one I  think I need.

I am using the oracle data provider and I have an input parameter (vUid, which will equal "zz").

First question. When filling a gridview with a result set from a stored procedure should the recordset OUT be defined as a REFCURSOR (like i did above)? 


Do you have example code as to how to execute the SP and fill a gridview?  I keep trying different variations of code i've found on the internet without any success other than getting more confused.

(I am using VS 2005, VB).

Thank you.



Passing an array from C# code-behind to SQL Server stored procedure



 Via the button1_ click event I retrieve all files in a directory (Example Follows)

    protected void Button1_Click(object sender, EventArgs e)
        DirectoryInfo di = new DirectoryInfo(@"C:\Test");

        foreach (FileInfo filename in di.GetFiles())

I now need to check whether this filename exists as a field in a SQL Server table.  For example, if the directory retrieves a file named ExampleFile.txt which does not exist in the (Table = TableFileStore;s Field = FileRetrievals) then I want to insert the filename. 

How do I construct a stored procedure to pass the filename(s) into a stored procedure as a parameter?  Do I need to also create an array of filenames and pass the array into my stored procedure?  Does the stored procedure belong within the foreach loop?    

How best to achieve the above goal?


Oracle stored procedure using parameters


Hi, am having a problem sending parameters to the stored procedure, I'm not sure if i'm sending Oracle the parameters in the right way.

USING:oracle 11g x64, C#, ASP.net 4.0, ODP.NET


The header of the stored procedure on Oracle server is:


?PROCEDURE create_new_user(p_log_in_name IN VARCHAR2, p_password_text IN VARCHAR2, isSuccessful OUT NUMBER)


The code the use the stored procedure in C# asp.net:

       OracleConnection connect = new OracleConnection("User Id=xpcal;Password=password123;Data Source=ora11g;");
        OracleCommand command_create_user = new OracleCommand();

        command_create_user.Connection = connect;

        command_create_user.CommandText = "xpcal.xpcal_users.create_new_user";
        command_create_user.CommandType = CommandType.StoredProcedure;

        OracleParameter p_log_in_name1 = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbox_user.Text;

        OracleParameter p_password = new OracleParameter();
        p_log_in_name1.OracleDbType = OracleDbType.Varchar2;
        p_log_in_name1.Direction = ParameterDirection.Input;
        p_log_in_name1.Value = tbo

Create stored procedure from asp.net



we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.



Using the BDC API how to catch the returned code when executing a stored procedure?

using BDC API, how can i catch the return code a stored procedure returns?

I tried Entity.Execute to execute a generic method. However, it returns an empty Entity table.

I know you can do it by setting up an output variable. I prefer returning an integer for tracking errors.

Any ideas?



TSQL: Passing array/list/set to stored procedure (MS SQL Server)

Passing array/list/set to stored procedure is fairly common task when you are working with Databases. You can meet this when you want to filter some collection. Other case - it can be an import into database from extern sources. I will consider few solutions: creation of sql-query at server code, put set of parameters to sql stored procedure's parameter with next variants: parameters separated by comma, bulk insert, and at last table-valued parameters (it is most interesting approach, which we can use from MS SQL Server 2008). Ok, let's suppose that we have list of items and we need to filter this items by categories ("TV", "TV game device", "DVD-player") and by firms ("Firm 1", "Firm2", "Firm 3). It will look at database like this So we need a query which will return us list of items from database. Also we need opportunity to filter these items by categories or by firms. We will filter them by identifiers. Ok, we know the mission. How we will solve it? Most easy way, used by junior developers - it is creating SQL-instruction with C# code, it can be like this List<int> categories = new List<int>() { 1, 2, 3 };   StringBuilder sbSql = new StringBuilder(); sbSql.Append( @" select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName from Item i inner join Firm f on i.FirmId =

can a Workflow access a stored procedure and pass the parameters from the list data to the stored pr

The reason that I would like to consider this functionailty is because my table architecture is complicated and I do not want to modify my master table to accept all of this data where some of the data should be normalized into sub tables.  Has anyone see evidence of the stored-procedure parm approach?  Is this best accomplished through VS 2010 or can I do it through SPD? Thanks

SQL 2010 SP1 does not fill in Stored Procedure Parameters

I am executing a Stored Procedure in Server Management Studio.  The Procedure gives the expected results but it does not fill in the parameters?  I have stop and restarted the server to no avail. Please help? Thanks. STORED PROCEDURE USE   [char68003f] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SelectDonorXiD] ( @DonorID varchar(50) , @ContactTitle varchar(7)=null OUTPUT, @ContactFirstName varchar(40)=null OUTPUT, @ContactLastName varchar(40)=null OUTPUT, @ContactAddress1 varchar(40)=null OUTPUT, @ContactAddress2 varchar(40)=null OUTPUT, @ContactCity varchar(30)=null OUTPUT, @ContactStateCode varchar(2)=null OUTPUT, @ContactZipCode varchar(10)=null OUTPUT, @ContactPhone varchar(30)=null OUTPUT, @ContactEmail varchar(80)=null OUTPUT, @BillingTitle varchar(7)=null OUTPUT, @BillingFirstName varchar(40)=null OUTPUT, @BillingLastName varchar(40)=null OUTPUT, @BillingAddress1 varchar(40)=null OUTPUT, @BillingAddress2 varchar(40)=null OUTPUT, @BillingCity varchar(30)=null OUTPUT, @BillingStateCode varchar(2)=null OUTPUT, @BillingZipCode varchar(10)=null OUTPUT, @BillingPhone varchar(30)=null OUTPUT, @BillingEmail varchar(80)=null OUTPUT ) AS Select ContactTitle, ContactFirstName, ContactLastName, ContactAddress1, ContactAddress2, ContactCity, ContactStateCode, ContactZipCode, ContactPhone, ContactEmail, BillingTitle, Bil

Need to create a stored procedure on a db if that db exists..

IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

Capture Stored Procedure Parameters in Trace

Hi, I want to capture each execution of all stored procedures and the parameters supplied, I'm going to do this via a trace on RPC:starting, capturing TextData, Database and starttime. I'll need to parse the TextData column to get the information that I want. I assume I also need to capture nested stored procedure calls , i.e. SP: starting as well. Does this sound correct and is this the best way of extracting this information ?Sean

want to create common Stored procedure

Hello, I have more than 20 tables, for this i want to create a common Stored procedure which fetches data by id Column So I tried like this,but it doesnt work ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID] -- Add the parameters for the stored procedure here @TableName nvarchar(100), @ColumnName nvarchar(100), @ColumnVal nvarchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @ExecQuery nvarchar(100) SET @ExecQuery =@TableName -- Insert statements for procedure here select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal exec (@ExecQuery) END please help me out Regards,

SqlDataSource, FormView, and Update with stored procedure in code behind

Hello, Using a FormView with a SqlDataSource, I'm attempting to Update data by calling a stored proc in code behind. I was having trouble getting parameters using Update Parameters in the SqlDataSource, but found a working solution by coding the parameters. The problem now is I'm getting an "Updating is not supported by data source 'XYZ' unless UpdateCommand is specified'. I saw some previous posts on the forums, but didn't find them very enlightening. 

Combine Common Code in Stored Procedure

I have the following stored procedure: ALTER PROCEDURE dbo.tc_TopicsGetTopics ( @StartRow int, @MaxRows int, @LocationID int, @GroupID int ) AS BEGIN SET NOCOUNT ON -- Return topics IF @GroupID <> 0 BEGIN WITH TopTemp AS (SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY TopCreated DESC) AS RowID, TopID, TopTitle, TopCreated FROM Topics WHERE TopGroupID = @GroupID) SELECT TopID, TopTitle, TopCreated FROM TopTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID END ELSE BEGIN WITH TopTemp AS (SELECT TOP (@StartRow + @MaxRows) ROW_NUMBER() OVER (ORDER BY TopCreated DESC) AS RowID, TopID, TopTitle, TopCreated FROM Topics WHERE TopLocationID = @LocationID) SELECT TopID, TopTitle, TopCreated FROM TopTemp WHERE RowID BETWEEN @StartRow + 1 AND (@StartRow + @MaxRows) ORDER BY RowID END RETURN END Two questions: 1. Is there a simple way to move the two identical SELECT statements out of the IF statement so it would only need to appear once in the procedure? I assume I could create a temporary table. Is that the same as using WITH? 2. Does anyone see much reason to do what I'm asking? Would it affect storage or efficiency? Thanks!  Jonathan Wood • SoftCircuits • Developer Blog

How to create a second independent transaction inside a CLR stored procedure?

I use the context connection for the "normal" work in the CLR procedure.But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.What is the best way to do this inside a CLR procedure?Thanx a lot

Error when trying to create a stored procedure

Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

can I create a job to call a stored procedure?




Can I create a job to call a stored procedure everyday? If yes, how to do it?



how to create stored procedure in sql server ???


here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....

if this exected i should get succecss msg also and boolen is true...


flase and not exectuted msg...


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend