none
Linq to SQL insert if not already in database RRS feed

  • Question

  •  

    Hi,

     

    Bit of a noob I'm afraid.

     

    I'm using Linq to SQL and I'm sure this should be very simple but I can't quite solve it.

     

    How do I insert unique items into my database?

    In other words...

    How do I check the textbox1.text against my database list and only insert if the item does not already exist - using Linq syntax?

     

    Thanks

     

    Code Snippet

    // Select

    var query = from m in db.Catering_FoodCategories

    select new

    {

    m.CategoryName

    };

     

    // Insert

     

    CateringDataContext db = new CateringDataContext();

    Catering_FoodCategory category = new Catering_FoodCategory();

    category.CategoryName = TextBox1.Text;

    db.Catering_FoodCategories.InsertOnSubmit(category);

    db.SubmitChanges();

     

     

    Wednesday, July 9, 2008 10:17 AM

Answers

  • FYI, another way to do this would be the following:

     

    Code Snippet

    private bool DoesDataExist(string DataEntry)

    {

      using (CateringDataContext db = new CateringDataContext())

      {

        return (db.Catering_FoodCategories.Any(o => o.CategoryName.Contains(TextBox1.Text)));

      }

    }

     

     

    It should generate the same SQL as your code, but it's just a bit more concise.

     

    Also, note the using statement, which ensures the data context is properly disposed.

     

    -Larry

     

    Friday, July 11, 2008 3:13 AM

All replies

  • You'll have to query the database for items with the specific value of the attribute (CategoryName, I assume) using a 'Where' clause, and add the item only if the query returns no results.

     

    Thanks,

    --Samir

     

    Wednesday, July 9, 2008 8:18 PM
  • Samir - thanks I think that's the approach I took.

     

    Code Snippet

    private bool DoesDataExist(string DataEntry)

    {

       CateringDataContext db = new CateringDataContext();

       var query = from food in db.Catering_FoodCategories

                   where food.CategoryName.Contains(TextBox1.Text)

                   select food;

    // return false if the item already exists

       if (query.Any())

          return false;

       else

          return true;

    }

     

    protected void Button1_Click(object sender, EventArgs e)

    {

    if (DoesDataExist(TextBox1.Text)

    {

    CateringDataContext db = new CateringDataContext();

    Catering_FoodCategory category = new Catering_FoodCategory();

    category.CategoryName = TextBox1.Text;

     

    db.Catering_FoodCategories.InsertOnSubmit(category);

    db.SubmitChanges();

    }

    else

    Response.Write("Sorry the Item already exists");

    }

     

     

     

    Thursday, July 10, 2008 8:34 AM
  • FYI, another way to do this would be the following:

     

    Code Snippet

    private bool DoesDataExist(string DataEntry)

    {

      using (CateringDataContext db = new CateringDataContext())

      {

        return (db.Catering_FoodCategories.Any(o => o.CategoryName.Contains(TextBox1.Text)));

      }

    }

     

     

    It should generate the same SQL as your code, but it's just a bit more concise.

     

    Also, note the using statement, which ensures the data context is properly disposed.

     

    -Larry

     

    Friday, July 11, 2008 3:13 AM
  • Folks, take note of the question .. and this keyword in the opening thread: UNIQUE.

     

    Currently these are all scenario's where a race condition (http://en.wikipedia.org/wiki/Race_condition) could occur.

     

    As such, how about trying this.

     

    1) Create a stored procedure that inserts a item if the item does not exist. Output is the scope_identity OR the existing ID (sample below)

    2) Drag/drop the stored procedure to the DBML designer.

    3) Update your class in the DBML designer to CONFIGURE BEHAVIOUR on the class, and for an INSERT scenario make it use the stored proc you just made.

    4) When you save the instance always do an INSERT or check to see when if the current instance has no ID (then insert) else update.

     

    here's some sample sql that i use for inserting unique items (and avoiding race conditions)

     

    pseudo code:-

     

    Code Snippet

    ALTER PROCEDURE dbo.CreateUniqueCountry

    (

    @CountryId INTEGER OUTPUT,

    @Name NVARCHAR(50),

    @Timestamp TIMESTAMP OUTPUT

    )

    AS

        INSERT INTO Countries ([Name])

        SELECT @Name

        WHERE NOT EXISTS

            (SELECT [Name]

            FROM Countries

            WHERE [Name] = @Name)

     

        SET @CountryId = SCOPE_IDENTITY()

     

        IF @CountryId IS NULL

            SELECT @CountryId = CountryId, @Timestamp = [Timestamp]

            FROM Countries

            WHERE [Name] = @Name

        ELSE

            SELECT @CountryId = CountryId, @Timestamp = [Timestamp]

            FROM Countries

            WHERE CountryId = @CountryId

     

        -- Required for Linq DataContex (NOT TO SURE IF I NEED TO DO THIS .. MATT WARREN .. WHAT DO U THINK?)

        RETURN @CountryId

     

     

     

    Friday, July 11, 2008 3:24 AM
  • I'm sure this is good stuff but i'm not too bothered about race conditions - i might use this later so thank you for very much for the input.

    Monday, July 14, 2008 8:23 AM
  • Hiya Guys,

    How could I use this code to query against multiple text boxes.

    eg: If I have a Build of matrials (BOM) say 10 text boxes.

    I want to check that data in each text box does not already exist in DB.

    textbox1, textbox2

    If It alreay exists in DB then error.

     

    Do I need to use the chunk of code for each textbox ?

     

    Ray..

     

    Monday, July 28, 2008 11:43 AM
  • Dude - that question has nothing to do with this topic. This topic was about inserting if not existing. You're just asking about if an item exists in the db  ---  I'd suggest you post a new topic.

    Monday, July 28, 2008 1:24 PM
  • A better method for this (that is a good bit less typing!) is just to try the update and check the @ROWCOUNT to see if the update was made. If the update did not occur, then add a new record.

    Also, the above won't stop race conditions unless it's run inside the appropriate isolation level (the time in which a race condition can occur though, is reduced from the previous posts that don't use an SP).

    Wednesday, June 16, 2010 3:03 AM
  • nice code.
    Friday, October 21, 2011 7:52 PM