Inserting multiple rows in MS SQL Server and retrieving all new table ID back
Taking a look at the example given here: stackoverflow
I understand that I will need to iterate over the loops and add clauses with append, but what I am missing is how to modify the query to return all IDs for the newly created records and get them in C #?
For example, my current code can be seen below, I would like to modify it to insert multiple lines in a single query and end up with a new ID 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;
+3
source to share
1 answer
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 only work if you are using SQLServer 2008 R2 or higher.
Edit . As Damien said in the comments, "There is no guarantee that the order in which changes are applied to the table and the order in which rows are inserted into the output table or table variable will match."
+1
source to share