如何从asp.net c#page发送参数到存储过程并用resultat来解决它? [英] how to send parameter from asp.net c# page to stored procedure and retieve it with the resultat?
问题描述
这里是存储过程代码:
ALTER程序[dbo]。[LtoffrEmpPst]
@offPst nvarchar(60),
@ sec nvarchar(60)输出,
@vil nvarchar(60)输出,
@cntr nvarchar(60)输出,
@dat datetime输出
as
开始
SET NOCOUNT ON;
select @ sec = us.Nm_societ,@ vil = vil.Nom_ville,@ cntr = cn.typContra,@ dat = emp.Dat_creat
来自OffrEmplwa emp,typ_Uzrs us,sectr_Activit sec,fonkcion fnk,Vlle vil,Expriens ex
,TypContra cn,Rolle rl,其中emp.Id_Uzr = us.Id_user和emp.Id_veil = vil.Id_vll
和emp.Id_Contra = cn.IdContra和us.Id_roole = rl.Id_rol >
和us.Id_roole = 1和emp.NmPost类似'%@ offPst%'
group by emp.Id_OffrEmp,us.Nm_societ,emp.NmPost,vil.Nom_ville
,cn.typContra,emp.Dat_creat
结束
这里是DAL.cs的代码
公共DataTable offemplistBypst(DTO dUser)
{
DataTable dt = null;
使用(SqlConnection cnx = Conection.GetDbConnection())
{
using(SqlCommand cmd = new SqlCommand (LtoffrEmpPst,cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(@ offPst ,SqlDbType.NVarChar,60).Value = dUser.NmPst;
cnx.Open();
cmd.ExecuteNonQuery();
dt = new DataTable();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
dr。关闭();
dr.Dispose();
返回dt;
}
}
}
plz,任何人都可以告诉我如何使这项工作
因为它们是输出参数你需要添加它们到cmd.Parameters集合。例如,请参见 http://stackoverflow.com / questions / 12174399 / access-sql-server-stored-procedure-output-parameter-in-c-sharp [ ^ ]
或者,最好在我看来,你不使用输出参数,而只是运行一个普通的Select语句,然后你可以访问使用dr [fieldName]
<选择的字段blockquote>尝试以下代码:
public DataTable offemplistBypst(DTO dUser )
{
DataTable dt = null ;
使用(SqlConnection cnx = Conection.GetDbConnection())
{
使用(SqlCommand cmd = new SqlCommand( LtoffrEmpPst ,cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( @ offPst,SqlDbType.NVarChar, 60 )。Value = dUser.NmPst;
SqlParameter parm2 = new SqlParameter( @ sec,SqlDbType.VarChar);
parm2.Size = 60 ;
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3 = new SqlParameter( @ vil,SqlDbType.VarChar);
parm2.Size = 60 ;
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm3);
SqlParameter parm4 = new SqlParameter( @ cntr,SqlDbType.VarChar);
parm2.Size = 60 ;
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm4);
SqlParameter parm5 = new SqlParameter( @ dat,SqlDbType.DateTime);
parm2.Size = 60 ;
parm2.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parm5);
cnx.Open();
cmd.ExecuteNonQuery();
dt = new DataTable();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
dr.Close();
dr.Dispose();
return dt;
}
}
}
您可以使用cmd.Parameter.AddWithValue(@ your_param,value)方法代替。
here is stored procedure code :
ALTER procedure [dbo].[LtoffrEmpPst]
@offPst nvarchar(60),
@sec nvarchar(60) output,
@vil nvarchar(60) output,
@cntr nvarchar(60) output,
@dat datetime output
as
begin
SET NOCOUNT ON;
select @sec=us.Nm_societ,@vil=vil.Nom_ville,@cntr=cn.typContra,@dat=emp.Dat_creat
from OffrEmplwa emp,typ_Uzrs us,sectr_Activit sec,fonkcion fnk ,Vlle vil,Expriens ex
,TypContra cn,Rolle rl where emp.Id_Uzr=us.Id_user and emp.Id_veil=vil.Id_vll
and emp.Id_Contra=cn.IdContra and us.Id_roole=rl.Id_rol
and us.Id_roole=1 and emp.NmPost like '%@offPst%'
group by emp.Id_OffrEmp,us.Nm_societ,emp.NmPost,vil.Nom_ville
,cn.typContra,emp.Dat_creat
end
here is the code of DAL.cs
public DataTable offemplistBypst(DTO dUser)
{
DataTable dt = null;
using (SqlConnection cnx = Conection.GetDbConnection())
{
using (SqlCommand cmd = new SqlCommand("LtoffrEmpPst", cnx))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@offPst", SqlDbType.NVarChar,60).Value = dUser.NmPst;
cnx.Open();
cmd.ExecuteNonQuery();
dt = new DataTable();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
dr.Close();
dr.Dispose();
return dt;
}
}
}
plz, can anybody tell me how to make this work
Since they are output parameters you need to add them to the cmd.Parameters collection. For example, see here http://stackoverflow.com/questions/12174399/accessing-sql-server-stored-procedure-output-parameter-in-c-sharp[^]
Alternatively, and preferably in my opinion, you don't use output parameters but instead just run a normal Select statement and then you can access the fields that are selected by using dr["fieldName"]
Try following code:
public DataTable offemplistBypst(DTO dUser) { DataTable dt = null; using (SqlConnection cnx = Conection.GetDbConnection()) { using (SqlCommand cmd = new SqlCommand("LtoffrEmpPst", cnx)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@offPst", SqlDbType.NVarChar,60).Value = dUser.NmPst; SqlParameter parm2 = new SqlParameter("@sec", SqlDbType.VarChar); parm2.Size = 60; parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm2); SqlParameter parm3 = new SqlParameter("@vil", SqlDbType.VarChar); parm2.Size = 60; parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm3); SqlParameter parm4 = new SqlParameter("@cntr", SqlDbType.VarChar); parm2.Size = 60; parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm4); SqlParameter parm5 = new SqlParameter("@dat", SqlDbType.DateTime); parm2.Size = 60; parm2.Direction = ParameterDirection.Output; cmd.Parameters.Add(parm5); cnx.Open(); cmd.ExecuteNonQuery(); dt = new DataTable(); SqlDataReader dr = cmd.ExecuteReader(); dt.Load(dr); dr.Close(); dr.Dispose(); return dt; } } }
You can use cmd.Parameter.AddWithValue("@your_param",value) method instead.
这篇关于如何从asp.net c#page发送参数到存储过程并用resultat来解决它?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!