CODEWITHSUNDEEP
c#sql-server
Dizzle
Dizzle

Reputation: 1016

Inserting multiple rows into MS SQL Server and retrieve all the new table ID's back

Looking at the example given here: https://stackoverflow.com/a/452934

I understand that I will need to iterate through a loop and append value clauses, but what I am missing is how to amend the query to return all the ID's for the newly created records and retrieve them in C#?

For Example my current code can be seen below, I would like to change it to insert multiple rows in one query and retrieve the newly created Id's as a List of integers ideally.

in_new_id = -1;
String query = "INSERT INTO " +   DB_Base.DBTable_Customer_Order_Table + "(" + DB_Base.DBTable_Customer_Order_Table_Customer_ID + "," + DB_Base.DBTable_Customer_Order_Table_ProductId+")";
query += " OUTPUT INSERTED." + DB_Base.DBTable_Customer_Order_Table_ID;
query += " VALUES ( @customerId, @productId);";

using (SqlConnection conn = new SqlConnection(GeneralConfig.DB_STR()))
{
    SqlCommand sql_command = new SqlCommand(query, conn);
    sql_command.Parameters.AddWithValue("@customerId", data_obj.customerId);
    sql_command.Parameters.AddWithValue("@productId", data_obj.productId);
    if (!String.IsNullOrEmpty(query) && sql_command != null && conn != null)
    {
         sql_command.Connection.Open();
         if (sql_command.Connection.State == System.Data.ConnectionState.Open)
         {
             object out_new_id = sql_command.ExecuteScalar();
             if (out_new_id != null)
             {
                 in_new_id = (int)out_new_id;
             }
             sql_command.Connection.Close();
             return ENUM_DB_Status.DB_SUCCESS;
         }
         else
         {
             in_new_id = -1;
             return ENUM_DB_Status.DB_CONNECTION_COULD_NOT_OPEN;
         }
    }
}

return ENUM_DB_Status.DB_FAIL;

Upvotes: 4

Views: 2487

Answers (1)

Zippy
Zippy

Reputation: 1824

Use this: 

List<int> ids = new List<int>();
using (SqlCommand command = new SqlCommand(@"declare @T TABLE(Id int)
                                                                INSERT INTO YourTableName(YourTableColumnNames)
                                                                OUTPUT Inserted.Id into @T VALUES 
                                                                (YourValues1),
                                                                (YourValues2),
                                                                (YourValues3),
                                                                (etc...) select Id from @T ", con))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                int id = int.Parse(reader[0].ToString());
                                ids.Add(id);
                            }
                        }
                    }

Warning!!! This will work only if you're using SQLServer 2008 R2 or higher.
Edit: As Damien said in the comments : "There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will correspond."

Upvotes: 2

Related Questions