Working with composite primary key in c#

Question

I have a sql table temp.it has 4 columns, 3 of which are primary keys(composite). While doing a insert to a table I need to check if the composite PK exists already in table. If yes I need to update the row else I need to insert a new row to temp table. Can I proceed this way. I don't know hoe to check for PK in table. Kindly guide me. Below is the Insert

  string constr = ConfigurationManager.ConnectionStrings["constr"].ToString();
            using (OdbcConnection con = new OdbcConnection(constr))
            {
                try
                {
                            string query = "Insert into temp_table(Name,DeptName,Alias,City) values(name,dept,alias,city)";
                            con.Open();
                            OdbcCommand cmd = new OdbcCommand(query, con);
                            cmd.ExecuteNonQuery();

              }

here name, dept and city are composite primary key.

This has nothing to do with C# or composite keys. You are asking how to write an INSERT statement. Add a WHERE clause. — Panagiotis Kanavos, May 24 '17 at 09:32
Although SQL Server takes care for Primary Key violation, so it will not allow you to insert duplicate values, you should use a merge statement to handle the upsert logic — PacoDePaco, May 24 '17 at 09:32
what @PawełKucharski means is add where not exists (select 1 from from temp_table WHERE name= and dept= and city= ). — Mark, May 24 '17 at 09:38
you could just do the update with proper where clause and if @@rowcount = 0 then do an insert — GuidoG, May 24 '17 at 09:52
Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — Ian Ringrose, May 24 '17 at 10:05

score 1 · Answer 1 · answered May 24 '17 at 09:37

1

your solution

if not exists (Select * from temp_table where Name=@name and DeptName=@dept and City=@city)
begin
     Insert into temp_table
     (Name,DeptName,Alias,City) 
     values(@name,@dept,@alias,@city)
end
else
begin
     update temp_table set Alias=@alias where Name=@name and DeptName=@dept and City=@city
end

answered May 24 '17 at 09:37

Ravi

1,157
1
9
19

1

It's best to first do the update, and then test if the @@rowcount equals zero. If it's zero (no update was done) then do an insert. – bastos.sergio May 24 '17 at 10:32
if the row is not there how can you first update and if you are updating a row then rowcount is always be greater than zero – Ravi May 24 '17 at 10:35
See [here](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) – bastos.sergio May 24 '17 at 10:37
brother its same, checking same condition in if clause. it will always give same result. You just have to argue with me thats why questing my answer. Please tell me the error in my code. – Ravi May 24 '17 at 10:40
It's not the same... Your answer is doing a lookup (if not exists), then inserting or updating based on that... What I'm saying is that the lookup is not necessary... – bastos.sergio May 24 '17 at 10:45
Your sql works for small data tables, but on big data tables (with a million plus rows) that small lookup can turn expensive... – bastos.sergio May 24 '17 at 10:47

score 1 · Accepted Answer · answered May 24 '17 at 10:40

Try to update first, if the record does not exists than the update will fail and then you can do an insert.
This is more efficient because each time the update succeeds then only one statement will be called.

 update temp_table 
 set    Alias = @alias 
 where  Name = @name 
 and    DeptName = @dept

 if @@rowcount = 0 then
 begin
     insert into temp_table (Name, DeptName, Alias, City) 
     values (@name, @dept, @alias, @city)
 end

score 0 · Answer 3 · answered May 24 '17 at 11:50

0

The Merge Command combine check, insert and update into one command.

Syntax is here: https://msdn.microsoft.com/en-us/library/bb510625.aspx

answered May 24 '17 at 11:50

coding Bott

4,287
1
27
44

Working with composite primary key in c#

3 Answers3