CODEWITHSUNDEEP
c#sql-serverado.net
gentlerainsky
gentlerainsky

Reputation: 256

How to get output from SQL Server Insert Command with more than one row using C# ADO.Net?

I want to receive all output primary key from this insert command using C# ADO.Net.

I run this in SQL Server 2012 Studio and I saw the result table with all of the values, so is it possible to get that table from C#?

INSERT INTO dbo.Suspension 
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
created_by, updated_by) 
OUTPUT INSERTED.pkey VALUES
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2);

What I have tried in C# ADO.NET. But DataTable didn't get any value from the insertedOutput.

SqlCommand cmd = new SqlCommand(insertQuery, this.conn);
var insertedOutput = cmd.ExecuteReader(); 

DataTable dt = new DataTable();
dt.Load(insertedOutput); // something wrong here

Noted that I copied the SQL code from the debugger. It work fine. (not sure where the 'this.' come from but it didn't cause any issues)

In the debugger, there are results from cmd.ExecuteReader() in insertedOutput, but I can't copy those result from dt (a DataTable variable).

Upvotes: 2

Views: 2724

Answers (2)

Satyajit
Satyajit

Reputation: 2210

You can use the value of the identity column in the inserted row and store it in a table then fetch the values from it.

DECLARE @tblIds TABLE (id int)

Insert into dbo.Suspension 
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
created_by, updated_by)
OUTPUT inserted.pkey INTO @tblIds
values
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2)

select * from @tblIds

Here i am assuming pkey is your identity column :)

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062502

Your query looks fine (except for the this.created_by / this.updated_by, which is confusing me, but... if you say it works...)

My initial thought, therefore, is: do you perhaps have an instead-of trigger that incorrectly only processes one row? Although I would expect that to report:

The target table 'dbo.Suspension' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The following 3 ways of reading that sql (or a very similar version to it) all work fine:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    const string insertQuery = @"
INSERT INTO dbo.Suspension
(pallet_position, processing_pallet_pkey, datetime_created, datetime_updated,
[this.created_by], [this.updated_by]) 
OUTPUT INSERTED.pkey VALUES
(1, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(2, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(3, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2), 
(4, 2, '20141013 16:27:25.000', '20141013 16:27:25.000', 2, 2);";

    // via datatable
    DataTable dt = new DataTable();
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    using (var insertedOutput = cmd.ExecuteReader())
    {
        dt.Load(insertedOutput);
    }
    Console.WriteLine(dt.Rows.Count); // 4

    // via manual read
    var list = new List<int>();
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    using (var insertedOutput = cmd.ExecuteReader())
    {
        while(insertedOutput.Read())
        {
            list.Add(insertedOutput.GetInt32(0));
        }
    }
    Console.WriteLine(list.Count); // 4

    // via dapper
    var ids = conn.Query<int>(insertQuery).ToList();
    Console.WriteLine(ids.Count); // 4
}

Upvotes: 3

Related Questions