none
Inserting objects with IDENTITY using Stored Procedure RRS feed

  • Question

  • I've got the following table: Product { ID int IDENTITY, Name varchar(255) }

     

    using Linq2SQL I wrote the code below to quickly test Linq2SQL. This worked really fine. Next thing I tried is specifing a stored procedure for the insert (also listed below). Which also worked, in the database, but sadly the inserted ID has not been written back onto the object, which further lead to not updating the product and also not deleting it.

    Which changes do I have to make, so that the ID is written back to the object, as it is not using STP?

     

    (PS: It's kind of confusing using ADO.NET EF and then Linq2sql. Its SubmitChanges() against SaveChanges() its not Add() or AddToEntity() its InsertOnSubmit() ... thats kind of annoying ..)

     

    Code Snippet

    class Program

    {

    static void Main(string[] args)

    {

    TestClass test = new TestClass();

    Stopwatch sw = new Stopwatch();

    Console.WriteLine("Insert Product");

    Product myProduct = test.InsertProduct("MyProduct");

    Console.WriteLine("Product saved");

    Console.WriteLine(myProduct.ID);

    Console.WriteLine(myProduct.AvgPrice);

    Console.WriteLine("Update Product");

    myProduct.Name = "New Product Name";

    test.UpdateProduct(myProduct);

    Console.WriteLine("Product updated");

    Console.WriteLine(myProduct.AvgPrice);

    Console.WriteLine("Delete Product");

    test.DeleteProduct(myProduct);

    Console.WriteLine("Deleted product");

    Console.Read();

    }

     

    static DataClasses1DataContext _Context = new DataClasses1DataContext();

    public class TestClass

    {

    public Product InsertProduct(string name)

    {

    Product p = CreateProduct(name);

    SaveProduct(p);

    return p;

    }

    private Product CreateProduct(string name)

    {

    Product p = new Product();

    p.Name = name;

    return p;

    }

    private Product SaveProduct(Product p)

    {

    _Context.Product.InsertOnSubmit(p);

    _Context.SubmitChanges();

    return p;

    }

    public void DeleteProduct(Product myProduct)

    {

    _Context.Product.DeleteOnSubmit(myProduct);

    _Context.SubmitChanges();

    }

    public void UpdateProduct(Product myProduct)

    {

    _Context.SubmitChanges();

    }

    }

    }

     

     

     

    Code Snippet

    ALTER PROCEDURE [dbo].[dao_DAOProduct_insert]

    @Name as varchar(255)

    AS

    BEGIN

    INSERT INTO dbo.[Product]( [Name] )

    VALUES ( @Name )

    SELECT * FROM Product WHERE ID = SCOPE_IDENTITY()

    END

     

     

    Saturday, November 1, 2008 1:18 PM

Answers

  • LINQ to SQL expects the SP used for insert to return the generated values via output parameters.  When you edit the mapping for the SP (in the designer) you can specify mapping out parameters back to object properties.

     

    Saturday, November 1, 2008 6:18 PM
    Moderator

All replies

  • LINQ to SQL expects the SP used for insert to return the generated values via output parameters.  When you edit the mapping for the SP (in the designer) you can specify mapping out parameters back to object properties.

     

    Saturday, November 1, 2008 6:18 PM
    Moderator
  • Ok Thanks for that.

     

    After changing the stored procedure I also had to update the procedure in my solution. How do I do this without removing and readding the stp to the model?

    In the context Menu of the EF-Model there's an option called "Update Model from Database" - Is there anything similar in Linq2Sql?

    Sunday, November 2, 2008 1:14 PM