Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL-Server
i want to check record in DB, if that record is present in DB then just update , if not present then insert thst ,, plz suggest me through c# code
Posted 10-May-13 18:28pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi
 
For checking record form db you can use SlqDataReader if you in SQL Server platform, and for update you can use ExecuteNonQuery method in SqlCommand object for example:
 

public class Class1
    {
        public void Execute()
        {
            SqlConnection readerconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            readerconn .Open();
 
            SqlCommand cmd = new SqlCommand("SELECT ID , Date FROM Table1", readerconn );
            SqlDataReader reader = cmd.ExecuteReader();
 
            SqlConnection writerconn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            writerconn .Open();
 
            SqlCommand writerCommand = new SqlCommand("", writerconn );
 
            while (reader.Read()) // here we are checking record is existing or no
            {
                int ID = reader.GetInt32(0);
                DateTime Date = reader.GetDateTime(1);
                 //here we want to update record ;)
                writerCommand.CommandText = "UPDATE Table1 SET Date = '" + EventDate.AddDays(1).ToString() + "' WHERE ID = " + ID.ToString();
                writerCommand.ExecuteNonQuery();
            }
        }
    }
 
If you want get more information about Update,Delete,Insert and Reading record with ADO.NET please refer here:
Simple ADO.NET Database Read, Insert, Update and Delete using C#.[^]
 
Best Regards.
  Permalink  
v4
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

There are multiple ways to do that. Here are I am specifying two.
 
First Approach:
 
1) First you check the database using select query if the target record is already present in the database or not.
 
2) If the record is present then execute update query. Otherwise, execute create query.
 
Here, the main drawback is to execute two queries for the such operation - one for select, another for insert/update. If your database is not in the same server where business logic is running, two sql round trips are to be performed. From the performance point of view this is not a good approach.
 

Second Approach:
 
1) Add suitable unique/primary key constraint in the table where you are planing to update/insert. This will restrict from inserting multiple rows with same information in these columns.
 
2) Write two queries one after another. First update query, then insert query for the same table. When the command will be executed, update query would run first, then insert query.
 
3) Now two possibilities:
 
a) Record is already present in the database : In this case, 1st query (update) would execute and 2nd query (insert) will throw exception since record is already there. This is a specific exception like 'unique/primary key violation'. So, you may easily capture that and ignore because intended update on the existing record is done.
 
b) Record is not present in the database : In this case, 1st query (update) won't affect anything since record is not present in the database. On the other hand, 2nd query would be executed and new row would be added.
 
So, we are solving the same issue using one set of queries (one round trip). Try to convince yourself that if we change the order of these two queries, this wont work expectedly during update.
 
Let me know if you anyone has worked on some other better method.
 
Cheers,
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 539
1 Maciej Los 300
2 DamithSL 233
3 Sergey Alexandrovich Kryukov 209
4 BillWoodruff 200
0 OriginalGriff 7,168
1 Sergey Alexandrovich Kryukov 6,377
2 DamithSL 5,461
3 Manas Bhardwaj 4,876
4 Maciej Los 4,450


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 11 May 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100