is exists query througyh c#

Question

1.00/5 (1 vote)

See more:

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 17:28pm

Member 9993510

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aydin Homay · Answer 1 · 2013-05-10T17:53:00

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:

C#

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.

debkumar@codeproject · Answer 2 · 2013-05-11T00:57:00

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,

is exists query througyh c#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0