CODEWITHSUNDEEP
c#asp.netwcf
Lakshmitha
Lakshmitha

Reputation: 705

How to get the Id generated by the table while inserting data into the table?

This is the function which i wrote in DataAccess Layer, I am using WCF concepts also.. here i've to insert some values into RequisitionTable and i've to pass that Id returned from that table into another table..

In database table i specified RequisitionID column as primarykey and auto increment by 1. in storedProcedure i declared RequisitionID as @RequisitionID int OUT

public RequisitionDTO createRequisition(RequisitionDTO requisitionDTO)
{

Logging.logDebug("RequisitionDA: createRequisition initiated");
DataTable requisitionDataTable = new DataTable();
try
{
  Database dataBase = CommonDB.getApplicationDatabase();
  //Insert and Store Id into variable 'i'  
  int i= dataBase.ExecuteNonQuery("SP_requisitionInsert",
     requisitionDTO.FirstName, requisitionDTO.MiddleName,    
     requisitionDTO.LastName,requisitionDTO.Address);
  int RequisitionID = Convert.ToInt16(i);

}
catch(exception e)
{
  //
}

Upvotes: 1

Views: 783

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I'm guessing this is the Enterprise Library Data Access Application Block.

I can't find Database.ExecuteNonQuery documentation online, but from my offline help:

Executes the storedProcedureName using the given parameterValues and returns the number of rows affected

(Italics in original, bold added by me)

I think you need to use one of the other overloads where you explicitly provide a command object, to which you've attached explicit parameter objects, including a parameter @RequisitionID set to output.

That's assuming your SP is correctly assigning SCOPE_IDENTITY() to this parameter before returning, as others have suggested.

I've also found an article entitled Executing a Command and Accessing Output Parameters, which was written as part of the online docs for Enterprise Library, but you'll notice that unlike other parts of MSDN, it doesn't link to documentation for the individual methods, nor is there a "reference" part of the Enterprise Library - it's almost like they don't want people to find their documentation.

Upvotes: 2

Alireza Maddah
Alireza Maddah

Reputation: 5885

If database engine is SQL SERVER, then SCOPE_IDENTITY returns the last identity value inserted into the table:

Upvotes: 2

Aravind
Aravind

Reputation: 4163

If you are using sql server 2005 or 2008 use SCOPE_IDENTITY() in your stored procedure

Upvotes: 0

Related Questions