return output Inserted.ColName value into a parameter
The question is how to retrieve an OUTPUT value from an INSERT statement into C# code.
The reason this was important is because performance was poor when using a uniqueidentifier as a primary key that had the guid value passed in from C#. There were a lot of index page splits occuring. Performance dramatically increased by usingNEWSEQUENTIALID, which required the code changes shown below.
Since this was originally posted, my coworker found the answer, which both of us validated in testing.
Here is the table and the insert statement:
CREATE TABLE Employee(
EmployeeIDuniqueidentifier NOT NULLDEFAULT NEWSEQUENTIALID(),
EmployeeNamenchar(10) NOT NULL
)
insert Employee (EmployeeName)
output Inserted.EmployeeID
values ('Ima Person')
Since the guid was no longer being passed into SQL Server, one line had to be added to receive the guid from SQL Server 2005.
SqlParameter parm = cmd.CreateParameter();
parm.SqlDbType = SqlDbType.UniqueIdentifier;
parm.ParameterName = "EmployeeID";
parm.Direction = ParameterDirection.Output;// added code
cmd.Parameters.Insert(0, parm);cmd.ExecuteNonQuery() ;// this had to be modified
After these simple changes, the guid from SQL Server could be retrieved and passed to the insert statement for the child tables.cmd.ExecuteReader();// modified code
parm.value = rdr.GetGuid(0);// added code
Hi John,
You would to use an STORED PROCEDURE to insert records in your table.
CREATE PROCEDURE EmployeeInsert
@name NCHAR(10),
@id UNIQUEIDENTIFIER OUT
AS
INSERT INTO employee( EmployeeName )
OUTPUT Inserted.EmployeeID INTO @id
VALUES ( @name )
GO
Good Coding!
Javier Luna
http://guydotnetxmlwebservices.blogspot.com/
Apparently you misread my June 30, 2005 post. Perhaps you missed the point of "Since this was originally posted, my coworker found the answer, which both of us validated in testing." I made the original post a month or so prior to the June 30, 2005 post you read and did not receive a response. On June 30, 2005, I updated the original post adding the answer and also code fragments showing how to code a solution. At this point the post was complete with both the question and the answer.
On August 28, 2006, you said "You would to use an STORED PROCEDURE to insert records in your table." That is not correct. A stored procedure is not necessary as the code fragments in my June 30, 2005 post show. Below you can find a complete, working console application (not code fragments) that clearly reiterate a stored procedure is not necessary. Before running the code, use the CREATE TABLE from my June 30, 2005 post. A stored procedure can be used if desired.
using
System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace OUTPUT
{
class Program
{
static void Main(string[] args)
{
SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
con.ConnectionString = "server=(local);Integrated Security=SSPI;database=AdventureWorks";
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert Employee (EmployeeName) output Inserted.EmployeeID values ('Ima Person')";
cmd.Connection = con;
SqlParameter parm = cmd.CreateParameter();
parm.SqlDbType = SqlDbType.UniqueIdentifier;
parm.ParameterName = "EmployeeID";
parm.Direction = ParameterDirection.Output;
cmd.Parameters.Insert(0, parm);
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
parm.Value = rdr.GetGuid(0);
Console.WriteLine("guid value is " + parm.Value.ToString());
con.Close();
Console.ReadLine();
}
}
} 
