I have to insert some data periodically in my SQL Server database. But the feeds where I read the data repeats some data that was inserted before. When I use Linq-to-SQL to insert into the DB either some data is duplicated, or a primary key violation exception is raised, depending on the primary key.
How to insert the data without duplications and without exceptions? I don't want to avoid the exception with a try-catch, because once the exception is raised the rest of the data isn't inserted.
update I also found my own solution: I wrote a duplicated entries deletion stored procedure, which is run right after the InsertAllOnSubmit + SubmitChanges
If you want to add data to your SQL table, then you can use the INSERT statement. Here is the basic syntax for adding rows to your SQL table: INSERT INTO table_name (column1, column2, column3,etc) VALUES (value1, value2, value3, etc); The second line of code is where you will add the values for the rows.
To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.
You can update rows in a database by modifying member values of the objects associated with the LINQ to SQL Table<TEntity> collection and then submitting the changes to the database. LINQ to SQL translates your changes into the appropriate SQL UPDATE commands.
When using LINQ to SQL, you insert new records by calling the InsertOnSubmit () method. After calling the InsertOnSubmit () method, you must call SubmitChanges () to make the insertion happen.
We recommend In this final section, we’ll examine how you can use LINQ to SQL to modify database data. In this section, you’ll build a page that you can use to insert new records into the Products database table. When using LINQ to SQL, you insert new records by calling the InsertOnSubmit () method.
Following are the syntax of insert or update or delete operations using in LINQ to SQL. Following is the syntax of using LINQ to Insert operation query in c#, vb.net to insert data in database. emp.EmpName = txtname.Text emp.Location = txtlocation.Text
Use the following procedure to insert a row into the database: Create a new object that includes the column data to be submitted. Add the new object to the LINQ to SQL Table collection associated with the target table in the database. Submit the change to the database.
All you have to do is create a new instance of your class and then call InsertOnSumbit() on the table:
var foo = new MyFoo { Name = "foo1" };
var dc = new MyDataContext();
dc.Foos.InsertOnSubmit(foo);
dc.SubmitChanges();
The other thing you need to be sure of is how you're incrementing your ID column. In general, I always make sure to use the IDENTITY(1,1) setting on my ID columns. This is declared on your LINQ entity's id column like so:
[Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true)]
public Int32 Id { get; set; }
To avoid duplicates, what you really need is what we call in my shop an "append" functionality. IMHO, this is most easily accomplished with a stored procedure - we even have a template we use for it:
USE [<Database_Name, sysobject, Database_Name>]
GO
CREATE PROCEDURE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>__append]
(
@id INT OUTPUT,
@<Key_Param, sysobject, Key_Param> <Key_Param_Type, sysobject, VARCHAR(50)>
)
AS
BEGIN
SELECT @id = [id] FROM [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] (NOLOCK) WHERE [<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>
IF @id IS NULL
BEGIN
INSERT INTO [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s] ([<Key_Param, sysobject, Key_Param>])
OUTPUT INSERTED.[id] INTO @inserted_ids
VALUES (@<Key_Param, sysobject, Key_Param>)
SELECT TOP 1 @id = [id] FROM @inserted_ids;
END
ELSE
BEGIN
UPDATE [<Schema, sysobject, dbo>].[<Table_Name, sysobject, Table_Name>s]
SET
[<Key_Param, sysobject, Key_Param>] = @<Key_Param, sysobject, Key_Param>
WHERE [id] = @id
END
END
GO
It is possible to do it in linq though, just query for a list of existing IDs (or whatever column you're keying off of):
var dc = new MyDataContext();
var existingFoos = dc.Foos.ToList();
var newFoos = new List<Foo>();
foreach(var bar in whateverYoureIterating) {
// logic to add to newFoos
}
var foosToInsert = newFoos.Where(newFoo => !existingFoos.Any(existingFoo => newFoo.Id == existingFoo.Id));
dc.Foos.InsertAllOnSubmit(foosToInsert);
dc.SubmitChanges();
// use the next line if you plan on re-using existingFoos. If that's the case I'd wrap dc.SubmitChanges() in a try-catch as well.
existingFoos.AddRange(foosToInsert);
Unfortunately, there's no way around it as Linq to SQL does not check the database before it performs the insert. The only way to do this is to query the database first to determine if the duplicate record exists and then add the record if it does not.
Ideally Linq to SQL would support the Ignore Duplicate Keys property on a SQL column. But unfortunately it does not at the moment.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With