Dynamic function calling output procedure

Question

1.00/5 (1 vote)

See more:

Dear All,
I want to call Sql output procedure from c# for many time. So I try to get dynamic function that calling Sql Output procedure. But I can't try it. Because I can't know what parameter direction is output or not from params array. If you confuse, sorry for that. I want to explain with code as bellow.

//Please tell me it can be change IDataReader to DataSet or anything that is ok

C#

public static IDataReader ReturnProcedure(string SPName, params Object[] SPParams)
        {
               //I have already initializt for con
               con.open();
               Sqlcommand cmd = new Sqlcommand();
               cmd.Connection = con;
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = SPName.Trim();

               for (int i = 0; i < SPParams.Length; i++)
               {
                   cmd.Parameters.Add(new SqlParameter());
                   cmd.Parameters[i].Value = SPParams[i];
               }

               cmd.ExecuteNonQuery();

               cmd.Close();

              return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

My Sql Procedures are as follow....

SQL

Alter Procedure Proc1(@ID int, @Name varchar(50), @Age int output)as
begin
   ......
end

Alter Procedure Proc2(@CardID int, @EmpCode varchar(50), @SDate datetime, @Experience int output, @Bonus int output)as
begin
   .....
end

So I want to call that function for any procedure if i need return value like as below ...

ReturnPorcedure("Proc1", vID, vName, vAge);
ReturnPorcedure("Proc2", vCardID, vCode, vStartDate, vExperience, vBonus);

And then I will try to get value from return object.
Please help me.
Thanks you all.

Posted 21-Nov-13 0:22am

Member 10297003

Updated 21-Nov-13 0:26am

ArunRajendra

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2013-11-21T00:31:00

Solution 1

These links might help -
http://mahedee.blogspot.in/2012/04/retrieve-store-procedures-output.html[^]
http://www.aspdotnet-suresh.com/2012/07/get-output-parameter-from-stored.html[^]

Posted 21-Nov-13 0:31am

Abhinav S

Comments

Member 10297003 21-Nov-13 13:34pm

Thanks you Sir! I have already seen your links now. But it exactly know parameter name like as @ID, @CardID are value parameters and @Age, @Bonus are output parameters. When I add cmd.Parameter.Add from SPParams by looping, I can't know exactly which parameter is value para or output para. Because I have no exactly count of parameter when I call ReturnProcedure like that
ReturnPorcedure("Proc1", vID, vName, vAge);
ReturnPorcedure("Proc2", vCardID, vCode, vStartDate, vExperience, vBonus);
How can I get vAge for first calling sql procedure name is Proc1 and vExperience, vBonus for second calling Proc2. How can I catch the value of vAge from Proc1 and vExperience, vBonus from Proc2. Thanks for your kindness. I want to try more and more because the helping of members of codeproject.

Maciej Los · Answer 2 · 2013-11-21T10:39:00

Instead returning the number of parameters, return the set of records ;)
In your case the count of records will be always the same (one).

How to: Execute a Stored Procedure that Returns Rows[^]

Proc1

SQL

DECLARE @table TABLE (ID INT, [Name] VARCHAR(30), Age INT)

INSERT INTO @table (ID, [Name], Age)
SELECT ID, [Name], Age
FROM TableName
WHERE ID =@ID AND [Name]=@Name

SELECT *
FROM @table

Proc2 should be similar to Proc1

Member 10297003 · Answer 3 · 2013-11-21T18:51:00

Thanks you all!
I have solved myself that. But it have a little rule in creating sql procedure and call the return function. And then it use oledbconnection and oledbcommand instead sqlconnection and sqlcommand. Because sqlcon or com is you will be know exactly the para of proc1 or proc2 like that @ID or @CardID. But oledb not need. That's my thinking because I don't know about c# as well. Now my level is just as beginner. My code is as follow.

SQL

Alter Procedure Proc1(@a int, @b int, @c int, @d int output, @e int output)
begin
  ......
end

Alter Procedure Proc2(@f int, @g int, @h int, @i int, @j int output, @k int output, @l int output)
begin
  ......
end

When procedure create, the rule is value para and output para are separate as about. Just not like as below...

SQL

Alter Procedure Proc2(@f int, @g int output, @h int, @i int, @j int output, @k int output, @l int)
begin
  ......
end

C#

//That two are initialize as public.
string Separate="Separator";
private static Object[] Get_SPParams;

//Calling the function ReturnProcedure as below
//that will be separate value para and output para. it will work in ReturnProcedure. So it don't //care procedure are same para or how many output para or value para.

Get_SPParams = ReturnProcedure("Proc1", vID, vName, vPhone, Separate, d, e);
   d = Get_SPParams[4].ToString();
   e = Get_SPParams[5].ToString();

Get_SPParams = ReturnProcedure("Proc2", f, g, h, i, Separate, j, k, l);
   j = Get_SPParams[5].ToString();
   k = Get_SPParams[6].ToString();
   l = Get_SPParams[7].ToString();

public static Object[] ReturnProcedure(string SPName, params Object[] SPParams)
        {
            bool found;
            string GetStr="";

            found = false;
            try
            {
                //OleDBConn has already initialize.
                OleDBCmd = new OleDbCommand();
                OleDBCmd.Connection = OleDBConn;
                OleDBConn.Open();
                OleDBCmd.CommandType = CommandType.StoredProcedure;
                OleDBCmd.CommandText = SPName.Trim();

                for (int i = 0; i < SPParams.Length; i++)
                {
                    GetStr = SPParams[i].ToString();
                    if (GetStr == "Separate")
                    {
                        found = true;
                        i++;
                    }

                    if (found == false)
                    {
                        OleDBCmd.Parameters.Add(new OleDbParameter());
                        OleDBCmd.Parameters[i].Value = SPParams[i];
                    }
                    else
                    {
                        OleDBCmd.Parameters.Add(new OleDbParameter() {Direction = ParameterDirection.Output});
                        OleDBCmd.Parameters[i-1].Value = SPParams[i];
                    }
                }

                OleDBCmd.ExecuteNonQuery();

                for (int i = 0; i < SPParams.Length - 1; i++)
                {
                    if (((OleDBCmd.Parameters[i].Direction == ParameterDirection.InputOutput) || (OleDBCmd.Parameters[i].Direction == ParameterDirection.Output)) && (OleDBCmd.Parameters[i].Value != null))
                    {
                        SPParams[i+1] = OleDBCmd.Parameters[i].Value;
                    }
                }

                OleDBConn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            return SPParams;
        }

If that's not good idea for you, you can advice me. Thanks you all.

Dynamic function calling output procedure

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0