ADO.Net Connectivity using Stored Procedure and Output parameters

清疚 2022-08-08 06:48 123阅读 0赞

原文：[http://www.codeproject.com/Tips/990005/ADO-Net-Connectivity-using-Stored-Procedure-and-Ou][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;
            } 
        }

[http_www.codeproject.com_Tips_990005_ADO-Net-Connectivity-using-Stored-Procedure-and-Ou]: http://www.codeproject.com/Tips/990005/ADO-Net-Connectivity-using-Stored-Procedure-and-Ou