带(output)函数的存储过程,asp.net中的执行
存储过程创建
CREATE proc P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC(@Cust_NO varchar(50),
@Address varchar(100) output,
@Passward varchar(100) output,
@smtpDomain varchar(100) output,
@ErrMessage varchar(100) output
)
as
begin
SELECT --GRP_Code,
--GRP_Desc,
@Address=Address,
@Passward=Passward,
@smtpDomain=smtpDomain
from SMTP_Address_Enable left join Base_Customer_Info
on SMTP_Address_Enable.Cust_NO=Base_Customer_Info.Customer_NO
left join SMTP_SendAddress
on Base_Customer_Info.CUST_GRP_CODE=SMTP_SendAddress.GRP_Code
where SMTP_Address_Enable.Cust_NO=@Cust_NO
if(@Address='')
begin
SELECT --GRP_Code,
--GRP_Desc,
@Address=Address,
@Passward=Passward,
@smtpDomain=smtpDomain
from SMTP_SendAddress
where GRP_Code='other'
end
if(@Address='')
begin
set @ErrMessage='无邮箱'
end
end
GO
类代码:
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using NDO;
namespace SMTPBusiLogic
{
public class P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC
{
private string _Address;
private string _Passward;
private string _smtpDomain;
private string _ErrMessage;
public string Address
{
get { return _Address; }
set { _Address = value; }
}
public string Passward
{
get { return _Passward; }
set { _Passward = value; }
}
public string smtpDomain
{
get { return _smtpDomain; }
set { _smtpDomain = value; }
}
public string ErrMessage
{
get { return _ErrMessage; }
set { _ErrMessage = value; }
}
public static P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC Smtp_Addr_By_GRP_Code_Get_FOR_PIC(string cust_no)
{
P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC Smtp_Addr_FOR_PIC_class = new P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC();
try
{
string ConnString = "";
ConnString = ConfigurationManager.ConnectionStrings["ConnectLocal"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(ConnString);
sqlConn.Open();
string sql = "P_Smtp_Addr_By_GRP_Code_Get_FOR_PIC";
SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandTimeout = 9999999;
SqlParameter par_sendaddress = new SqlParameter("@Cust_NO", System.Data.SqlDbType.VarChar, 100);
par_sendaddress.Direction = ParameterDirection.Input;
par_sendaddress.Value = cust_no;
sqlCmd.Parameters.Add(par_sendaddress);
sqlCmd.Parameters.Add("@Address", System.Data.SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add("@Passward", System.Data.SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add("@smtpDomain", System.Data.SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
sqlCmd.Parameters.Add("@ErrMessage", System.Data.SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
sqlCmd.ExecuteNonQuery();
Smtp_Addr_FOR_PIC_class.Address = sqlCmd.Parameters["@Address"].Value.ToString();
Smtp_Addr_FOR_PIC_class.Passward = sqlCmd.Parameters["@Passward"].Value.ToString();
Smtp_Addr_FOR_PIC_class.smtpDomain = sqlCmd.Parameters["@smtpDomain"].Value.ToString();
Smtp_Addr_FOR_PIC_class.ErrMessage = sqlCmd.Parameters["@ErrMessage"].Value.ToString();
sqlConn.Close();
}
catch (System.Exception ex)
{
throw new System.Exception(ex.StackTrace);
}
return Smtp_Addr_FOR_PIC_class;
}
}
}
前台界面:
P_SMTP_SendAddress_Get_Send_For_STOCK class_SendAddress = P_SMTP_SendAddress_Get_Send_For_STOCK.Class_SMTP_SendAddress_Get_Send_For_STOCK(cust_no);
if (class_SendAddress.ErrMessage != "")
{
MessageBox.Show(cust_no + " 没有设置发送邮件!", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}