CODEWITHSUNDEEP
c#sqlsql-updatesql-insert
dumbel
dumbel

Reputation: 205

C# database update

I'm stuck on a little problem concerning database.

Once a month I get a XML file with customer information (Name, address, city,etc.). My primary key is a customer number which is provided in the XML file.

I have no trouble inserting the information in the database;

var cmd = new SqlCommand("insert into [customer_info] 
   (customer_nr, firstname, lastname, address_1, address_2, address_3.......)");
//some code
cmd.ExecuteNonQuery();

Now, I would like to update my table or just fill it with new information. How can I achieve this?

I've tried using TableAdapter but it does not work.

And I'm only permitted to add one XML because I can only have one customer_nr as primary key.

So basically how do I update or fill my table with new information?

Thanks.

Upvotes: 0

Views: 585

Answers (3)

trickdev
trickdev

Reputation: 599

If I understand your question correctly - if the customer already exists you want to update their information, and if they don't already exist you want to insert a new row.

I have a lot of problems with hard-coded SQL commands in your code, so I would firstly be very tempted to refactor what you have done. However, to achieve what you want, you will need to execute a SELECT on the primary key, if it returns any results you should execute an UPDATE else you should execute an INSERT.

It would be best to do this in something like a Stored Procedure - you can pass the information to the stored procedure at then it can make a decision on whether to UPDATE or INSERT - this would also reduce the overhead of making several calls for your code to the database (A stored procedure would be much quicker)

Upvotes: 2

VinayC
VinayC

Reputation: 49165

AdaTheDev has indeed given the good suggestion.

But in case, you must insert/update from .NET code then you can

  1. Create a stored procedure that will handle insert/update i.e. instead of using a direct insert query as command text, you make a call to stored proc. The SP will check if row exists or not and then update (or insert).
  2. User TableAdapter - but this would be tedious. First you have to setup both insert & update commands. Then you have to query the database to get the existing customer numbers and then update the corresponding rows in the datatable making the Rowstate as Updated. I would rather not go this way.

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

One way would be to bulk insert the data into a new staging table in the database (you could use SqlBulkCopy for this for optimal insert speed). Once it's in there, you could then index the customer_nr field and then run 2 statements:

-- UPDATE existing customers
UPDATE ci
SET ci.firstname = s.firstname,
    ci.lastname = s.lastname, 
    ... etc
FROM StagingTable s
    INNER JOIN Customer_Info ci ON s.customer_nr = ci.customer_nr

-- INSERT new customers
INSERT Customer_Info (customer_nr, firstname, lastname, ....)
SELECT s.customer_nr, s.firstname, s.lastname, ....
FROM StagingTable s
    LEFT JOIN Customer_Info ci ON s.customer_nr = ci.customer_nr
WHERE ci.customer_nr IS NULL

Finally, drop your staging table.

Alternatively, instead of the 2 statements, you could just use the MERGE statement if you are using SQL Server 2008 or later, which allows you to do INSERTs and UPDATEs via a single statement.

Upvotes: 3

Related Questions