使用输入输出存储过程进行数据插入

Question

我正在使用ASP.net
C＃创建一个Web应用程序。我有一个预订表格，我需要使用存储过程将数据插入表中。该表有几列，其中第二列是计算列。设置存储过程以插入数据并在插入后从第二列中获取值。以下是存储过程的代码：

    Create Procedure sp_InsertCashPooja
@FirstName varchar(100),
@LastName varchar(100),
@TelNo bigint,
@Star char(50),
@Rasi char(50),
@Gothram char(50),
@PDMID int,
@PayMode bit,
@PujaName char(50),
@DonateAmt decimal(19,2),
@RcptNo varchar(25) output

as

Begin

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

    if @PujaName != 'DONATION'
    Begin

        INSERT INTO PoojaDetails (FirstName, LastName, TelNo, Star, Rasi, Gothram, PoojaDietyMasterID, PayMode) values (@FirstName,@LastName,@TelNo,@Star,@Rasi,@Gothram,@PDMID,@PayMode)

    End

    if @PujaName = 'DONATION'
    Begin

        DECLARE @isDonate int = 0;

        INSERT INTO PoojaDetails (FirstName, LastName, TelNo, Star, Rasi, Gothram, PoojaDietyMasterID, PayMode, isDonate, DonateAmount) values (@FirstName,@LastName,@TelNo,@Star,@Rasi,@Gothram,@PDMID,@PayMode, @isDonate, @DonateAmt)

    End

    Select @RcptNo = max(ReceiptNo) from PoojaDetails
    Return @RcptNo
COMMIT TRANSACTION

END TRY

BEGIN CATCH
    IF (@@TRANCOUNT > 0)

    ROLLBACK TRANSACTION

END CATCH

SET NOCOUNT OFF;


End

我想单击一下按钮即可插入数据：我能够找出以下代码…。

 protected void btnSave_Click(object sender, EventArgs e)
        {


            frmFirstName = txtFirstName.Text.Trim().ToUpper();
            frmLastName = txtLastName.Text.Trim().ToUpper();
            frmPhoneNo = Convert.ToInt32(txtPhoneNo.Text.Trim());
            frmNakshatra = Convert.ToString(cmbNakshatra.SelectedItem).Trim();
            frmRasi = Convert.ToString(cmbRasi.SelectedItem).Trim();
            frmGothram = Convert.ToString(cmbGothram.SelectedItem).Trim();
            frmPujaName = Convert.ToString(cmbPujaName.SelectedItem).Trim();
using (SqlConnection connection = new SqlConnection())
        {
            if (frmPayMode == "Cash")
            {
                if (frmPujaName == "DONATION")
                {
                    SqlDataAdapter CashAdapter = new SqlDataAdapter();

                    CashAdapter.InsertCommand = new SqlCommand("sp_InsertCashPooja", connection);
                    CashAdapter.InsertCommand.CommandType = CommandType.StoredProcedure;

请帮助…。我想捕获返回的RcptNo，以后打算调用另一个ASPX页并使用查询字符串传递值。

谢谢

Answer 1

使用简单的SqlCommand调用您的SP

connection.Open();
var cmd = new SqlCommand("sp_InsertCashPooja", connection);
cmd.Parameters.AddWithValue("FirstName", frmFirstName);
// Add all the others parameters in same way
var id = (int)cmd.ExecuteScalar();
connection.Close();