ADO.Net Connectivity using Stored Procedure and Output parameters

来源：互联网发布：中国城市化进程数据编辑：程序博客网时间：2024/06/08 09:08

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

Create Database OutputParameterDemoUse OutputParameterDemoCreate Table Employee(Emp_ID int identity primary key, Name nvarchar(40), Designation nvarchar(100), Salary int)Select * from EmployeeInsert 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 outasBegin    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 outputASBEGININSERT INTO [OrderIder] ([CreateTime])VALUES (getdate())Select @NEWORDERID=@@IDENTITYEND

//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;        }     }

0 0