动态函数调用输出程序 [英] Dynamic function calling output procedure
问题描述
亲爱的所有人，
我想多次从c＃调用Sql输出程序。所以我尝试获取调用Sql Output过程的动态函数。但我无法尝试。因为我无法知道从params数组输出什么参数方向。如果你感到困惑，抱歉。我想用下面的代码来解释。
//请告诉我它可以将IDataReader更改为DataSet或任何可以接受的东西
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
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);
}
我的Sql程序如下....
My Sql Procedures are as follow....
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
所以我想为任何程序调用该函数，如果我需要返回值如下所示...
ReturnPorcedure（Proc1，vID，vName，vAge ）;
ReturnPorcedure（Proc2，vCardID，vCode，vStartDate，vExperience，vBonus）;
然后我会尝试获取返回对象的价值。
请帮助我。
谢谢大家。
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.
推荐答案
这些链接可能有帮助 -
http://mahedee.blogspot.in/2012/04/retrieve-store-procedures-output.html [ ^ ]
http://www.aspdotnet-suresh.com/2012/07/get-output-parameter-from-stored。 html [ ^ ]
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[^]
而是返回参数个数，返回记录集;）
在您的情况下，记录的数量将始终为相同（一）。
如何：执行存储返回行的过程 [ ^ ]
Proc1
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
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 应类似于 Proc1
谢谢大家！
我已经解决了这个问题。但它在创建sql过程和调用返回函数时有一些规则。然后它使用oledbconnection和oledbcommand而不是sqlconnection和sqlcommand。因为sqlcon或com你将完全知道proc1或proc2的para，就像@ID或@CardID一样。但是oledb不需要。这是我的想法，因为我也不知道c＃。现在我的水平就像初学者一样。我的代码如下。
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.
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
当程序创建时，规则是值para和输出para是分开的。只是不喜欢如下......
When procedure create, the rule is value para and output para are separate as about. Just not like as below...
Alter Procedure Proc2(@f int, @g int output, @h int, @i int, @j int output, @k int output, @l int)
begin
......
end
//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.
这篇关于动态函数调用输出程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
