none
Return a single integer value from an sql stored procedure to C# code

    Question

  • USE [Trial]
    GO
    /****** Object:  StoredProcedure [dbo].[Invoice_Insert]    Script Date: 02/27/2014 10:51:53 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: Debra
    -- Create date: February 25, 2014
    -- Description: Work order insert with table valued parameters
    -- =============================================
    ALTER PROCEDURE [dbo].[Invoice_Insert]
    -- Add the parameters for the stored procedure here
    @InvoiceType int, --work order = 0, invoice = 1, return = 2, credit = 2
    @InvoiceARH int,
    @Customer int,
    @InvType nvarchar(3),
    @Subtotal decimal(10,2),
    @DriverID int, 
    @Status nvarchar(3), 
    @Shipped bit,
    @By nvarchar(10),
    @TempWOID nvarchar(15),
    @ARDTable AS ARD_UDTT READONLY
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        -- Insert statements for procedure here
        DECLARE @ARHID int
        DECLARE @Table TABLE(ID int)
        INSERT INTO ARH(INVOICE, CUSTOMER, INVTYPE, DATE, TAXES,SUBTOTAL, DRIVERID, [OPEN], PRINTED,ADJUST,PAID,STATUS,SHIPPED,[BY],TEMPWOID)
        OUTPUT INSERTED.ARHID INTO @Table
        VALUES(@InvoiceARH, @Customer,@InvType, CONVERT(Date, GETDATE()), 0.00, @Subtotal,@DriverID, 'TRUE', 'FALSE', 0.00, 0.00, @Status, @Shipped, @By, @TempWOID)

        SELECT @ARHID = ID FROM @Table  

    INSERT INTO ARD SELECT Invoice, Item, [Desc], Desc2, Qty, Price, @ARHID FROM @ARDTable

    --IF(@InvoiceType = 0)--DO NOTHING 
    IF(@InvoiceType = 1)--SUBTRACT
    BEGIN
    UPDATE a
    SET ONHAND = ONHAND-b.Qty 
    , STAMPED = CASE WHEN b.[Type] = 'CIG' THEN STAMPED-b.Qty ELSE 0 END 
    , LASTDATE = CAST(GETDATE() AS DATE)
    FROM Inv a
    INNER JOIN @ARDTable b ON a.Item = b.Item
    END
    ELSE IF (@InvoiceType = 2)--ADD
    BEGIN
    UPDATE a
    SET ONHAND = ONHAND+b.Qty 
    , STAMPED = CASE WHEN b.[Type] = 'CIG' THEN STAMPED+b.Qty ELSE 0 END 
    , LASTDATE = CAST(GETDATE() AS DATE)
    FROM Inv a
    INNER JOIN @ARDTable b ON a.Item = b.Item
    END
    END

    I would like the output of ARHID to also show in C# since I will need the ID later on in my code is that possible?

    Below is my C# code:

    private void InvoiceInsertWithTableValuedParameters()
            {
                string tempWOID = "";
                if (UserControl1.InvoiceType.ToLower() == "wo")
                {
                    tempWOID = GetTemporaryWOID();
                    Lists.InvoiceNum = -1;
                }
                else
                {
                    DataTable dtARNum = new DataTable();
                    Data.GetData("ARNewInvoiceNum_Select", dtARNum);
                    foreach (DataRow row in dtARNum.Rows)
                    {
                        Lists.InvoiceNum = Convert.ToInt32(row["INVOICE"]);
                    }
                }
                dtARD.Clear();
                foreach (DataRow row in dtProductsForInvoice.Rows)
                {
                    DataRow newRow = dtARD.NewRow();
                    newRow["Invoice"] = Lists.InvoiceNum;
                    newRow["Item"] = row["Item"];
                    newRow["Desc"] = row["Description"];
                    newRow["Desc2"] = row["Desc2"];
                    newRow["Qty"] = row["Qty"];
                    newRow["Price"] = row["Price"];
                    newRow["Type"] = row["Type"];
                    dtARD.Rows.Add(newRow);
                }

                Successful.Clear();
                SqlDataAdapter dataAdapter = new SqlDataAdapter();
                SqlConnection conn = new SqlConnection("Server=localhost;Database=MandTobPsuedo;Integrated Security=true");
                conn.Open();
                try
                {
                    SqlCommand insertCommand = new SqlCommand("Invoice_Insert", conn);
                    insertCommand.CommandType = CommandType.StoredProcedure;
                    dataAdapter.UpdateCommand = insertCommand;
                    int invoiceType = 0;
                    switch (UserControl1.InvoiceType)
                    {
                        case "wo":
                            invoiceType = 0;
                            break;
                        case "inv":
                            invoiceType = 1;
                            break;
                        case "ret":
                        case "cre":
                            invoiceType = 2;
                            break;
                    }
                    insertCommand.Parameters.AddWithValue("@InvoiceType", invoiceType);
                    insertCommand.Parameters.AddWithValue("@InvoiceARH", Lists.InvoiceNum);
                    insertCommand.Parameters.AddWithValue("@Customer", Lists.currentCustomer);
                    string invType = "";
                    switch (UserControl1.InvoiceType)
                    {
                        case "inv":
                        case "wo":
                            invType = "REG";
                            break;
                        case "ret":
                            invType = "RET";
                            break;
                        case "cre":
                            invType = "REF";
                            break;
                    }
                    insertCommand.Parameters.AddWithValue("@InvType", invType);
                    insertCommand.Parameters.AddWithValue("@Subtotal", discounts + sumTotal);
                    insertCommand.Parameters.AddWithValue("@DriverID", cmbDrivers.SelectedValue == null ? -1 : cmbDrivers.SelectedValue);
                    insertCommand.Parameters.AddWithValue("@Status", UserControl1.InvoiceType);
                    bool shipped = false;
                    switch (ckbxShipped.CheckState)
                    {
                        case CheckState.Checked:
                            shipped = true;
                            break;
                        case CheckState.Unchecked:
                            shipped = false;
                            break;
                    }
                    insertCommand.Parameters.AddWithValue("@Shipped", shipped);
                    insertCommand.Parameters.AddWithValue("@By", Login.LoginName);
                    insertCommand.Parameters.Add("@TempWOID", System.Data.SqlDbType.NVarChar, 15);
                    if (tempWOID == "")
                    {
                        insertCommand.Parameters["@TempWOID"].Value = DBNull.Value;
                    }
                    else
                    {
                        insertCommand.Parameters["@TempWOID"].Value = tempWOID;
                    }
                    insertCommand.Parameters.AddWithValue("@ARDTable", dtARD);
                    insertCommand.ExecuteNonQuery();
                    Successful.Add(true);

                    if (UserControl1.InvoiceType == "wo")
                    {
                        string[] parameterArray = { "@TempWOID|" + tempWOID + "|0" };
                        Data.Command("TempWOID_Update", parameterArray);
                        string[] split = { };
                        split = tempWOID.Split(new char[] { '~' });
                        lblInvoiceNum.Text = split[0] + split[1];
                    }
                    else
                    {
                        lblInvoiceNum.Text = Lists.InvoiceNum.ToString();
                    }
                    Lists.newInvoice = false;
                }
                catch
                {
                    Successful.Add(false);
                }
            }


    Debra has a question

    Thursday, February 27, 2014 3:59 PM

Answers

  • The simplest way is to return a one-column, one-row resultset from the stored procedure.

    To do this, simply add:

    SELECT @ARHID ARHID;

    At the end of the stored procedure, and use ExecuteScalar() instead of ExecuteNonQuery.

    ExecuteScalar will run the command, and return you the first column of the first row of the first resultset as a scalar value.

    You can also change the signature of the stored proc to have an output parameter and bind an additional parameter in code.  This is slightly better-performing, but it more work.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 27, 2014 4:15 PM
  • insertCommand.Parameters.Add("@ARHID", SqlDbType.Int);
    insertCommand.Parameters["@ARHID"].Direction = ParameterDirection.Output
    insertCommand.ExecuteNonQuery();
    int arhid = (Int) insertCommands.Parameters["@ARHID"].Value;

    Disclaimer: this is a T-SQL forum and not a C# forum. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 27, 2014 11:03 PM

All replies

  • You can use an OUTPUT parameter to get the value and retrieve it (output parameter) from your application code.
    Thursday, February 27, 2014 4:12 PM
  • The simplest way is to return a one-column, one-row resultset from the stored procedure.

    To do this, simply add:

    SELECT @ARHID ARHID;

    At the end of the stored procedure, and use ExecuteScalar() instead of ExecuteNonQuery.

    ExecuteScalar will run the command, and return you the first column of the first row of the first resultset as a scalar value.

    You can also change the signature of the stored proc to have an output parameter and bind an additional parameter in code.  This is slightly better-performing, but it more work.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Thursday, February 27, 2014 4:15 PM
  • How would I be able to do it the second way?

    Debra has a question

    Thursday, February 27, 2014 4:17 PM
  • insertCommand.Parameters.Add("@ARHID", SqlDbType.Int);
    insertCommand.Parameters["@ARHID"].Direction = ParameterDirection.Output
    insertCommand.ExecuteNonQuery();
    int arhid = (Int) insertCommands.Parameters["@ARHID"].Value;

    Disclaimer: this is a T-SQL forum and not a C# forum. :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 27, 2014 11:03 PM