ADO.Net Connectivity using Stored Procedure and Output parameters

原文：http://www.codeproject.com/Tips/990005/ADO-Net-Connectivity-using-Stored-Procedure-and-Ou

Create Database OutputParameterDemo

Use OutputParameterDemo

Create Table Employee(Emp_ID int identity primary key, Name nvarchar(40), Designation nvarchar(100), Salary int)

Select * from Employee

Insert into Employee values ('Ankit','Software Engineer',45000)
Insert into Employee values ('Max','Software Engineer',55000)
Insert into Employee values ('Mac','Software Engineer',9000)
Insert into Employee values ('Jane','Software Engineer',21000)
Insert into Employee values ('Joseph','Software Engineer',10000)
Insert into Employee values ('Jason','Software Engineer',32000)
Insert into Employee values ('Sumit','Software Engineer',85000)

Create Procedure spOutputParameter
@Name nvarchar(40), @Designation nvarchar(100), @Salary int, @Emp_ID int out
as
Begin
    Insert into Employee Values (@Name, @Designation, @Salary)
    Select @Emp_ID = SCOPE_IDENTITY()
End

string CS = ConfigurationManager.ConnectionStrings["DatabaseCS"].ConnectionString;
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spOutputParameter", con);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@Name", txtName.Text);
                cmd.Parameters.AddWithValue("@Designation", ddlDesignation.SelectedValue);
                cmd.Parameters.AddWithValue("@Salary", txtSalary.Text);

                SqlParameter outputPara = new SqlParameter();
                outputPara.ParameterName = "@Emp_ID";
                outputPara.Direction = System.Data.ParameterDirection.Output;
                outputPara.SqlDbType = System.Data.SqlDbType.Int;
                cmd.Parameters.Add(outputPara);

                con.Open();
                cmd.ExecuteNonQuery();

                string RetrievedEmpId = outputPara.Value.ToString();
                lblStatus.Text = "Your Employee Id is : " + RetrievedEmpId;
            }
        }

other test demo:

ALTER PROCEDURE [dbo].[BuilderOrderID]
	  @NEWORDERID bigint output
AS
BEGIN
	INSERT INTO [OrderIder] ([CreateTime])VALUES (getdate())

	Select @NEWORDERID=@@IDENTITY
END

//Microsoft.Practices.EnterpriseLibrary.Data.dll, v2.0.50727

// Microsoft.Practices.EnterpriseLibrary.Data

 protected static Database db;
        public DbBase()
        {
            if (db == null)
                db = DatabaseFactory.CreateDatabase("dbConnectionString");
        }

public class OrderIderContext : DbBase
    {
        public bool Builder(out int newOrderId)
        {
            newOrderId = 0;
            DbCommand dbCommandWrapper = db.GetStoredProcCommand("[BuilderOrderID]");

            db.AddOutParameter(dbCommandWrapper, "@NEWORDERID", DbType.Int32,32);

            int result = db.ExecuteNonQuery(dbCommandWrapper);
            if (result > 0)
            {
                newOrderId = Convert.ToInt32(db.GetParameterValue(dbCommandWrapper, "@NEWORDERID").ToString());
            }
            return result > 0;
        } 
    }