ASP.NET中存储过程的简单使用

1.存储过程的创建

--创建一个存储过程来获取一个邮件的发件人,收件人和日期
create proc proc_ShowDetails
(@id varchar(256),@sender nvarchar(256) output,@receiver nvarchar(256) output,@date datetime output)
as

begin transaction tran_Detail

select @sender=m_senderEmail,@date=m_date from tab_mail where m_ID=@id

select @receiver=r_receiverEmail from tab_receiver where rm_ID=@id

commit transaction tran_Detail

2.方法
    /// <summary>
    /// 获得邮件的发件人，收件人，主题，好友名字的方法
    /// </summary>
    /// <param name="id">邮件的id</param>
    protected void getDetails(string id)
    {
        using (SqlConnection conn = new SqlConnection("server=.;database=GMail;uid=sa;pwd=111"))
        {
            using (SqlCommand cmd = new SqlCommand("proc_xyzShowDetails", conn))
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@id", SqlDbType.VarChar, 256);
                cmd.Parameters.Add("@sender", SqlDbType.NVarChar, 256);
                cmd.Parameters.Add("@receiver", SqlDbType.NVarChar, 256);
                cmd.Parameters.Add("@date", SqlDbType.DateTime);
                cmd.Parameters.Add("@friendName", SqlDbType.NVarChar, 30);

                cmd.Parameters["@id"].Value = id;
                cmd.Parameters["@sender"].Direction = ParameterDirection.Output;
                cmd.Parameters["@receiver"].Direction = ParameterDirection.Output;
                cmd.Parameters["@date"].Direction = ParameterDirection.Output;
                cmd.Parameters["@friendName"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                string friend = cmd.Parameters["@friendName"].Value.ToString();
                string friendName;
                if (friend == null || friend == string.Empty)
                {
                    friendName = cmd.Parameters["@sender"].Value.ToString();
                }
                else
                {
                    friendName = " < " + friend + " >  " + cmd.Parameters["@sender"].Value.ToString();
                }

                this.lb_sender.Text = friendName;
                this.lb_receiver.Text = cmd.Parameters["@receiver"].Value.ToString();
                this.lb_date.Text = cmd.Parameters["@date"].Value.ToString();

                 }
        }

    }