存在查询througyh c＃ [英] is exists query througyh c#

问题描述

我想检查DB中的记录，如果该记录存​​在于DB然后只是更新，如果不存在则插入thst，plz建议我通过c＃code

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

推荐答案

您好


如果你在SQL Server平台上，你可以使用 SlqDataReader 来检查记录表格db，并且你可以更新在 SqlCommand 对象中使用ExecuteNonQuery方法，例如：

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();
            }
        }
    }

如果你想获得更多关于更新，删除，插入的信息和ADO.NET阅读记录请参考：

使用C＃读取，插入，更新和删除简单的ADO.NET数据库。 [ ^ ]



最好的问候。

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.

有多种方法可以做到这一点。这是我指定两个。



第一种方法：



1）首先你使用数据库检查数据库如果目标记录已存在于数据库中，则选择查询。



2）如果记录存在，则执行更新查询。否则，执行create query。



这里，主要的缺点是对这样的操作执行两个查询 - 一个用于选择，另一个用于插入/更新。如果您的数据库不在运行业务逻辑的同一服务器中，则将执行两次sql往返。从性能的角度来看，这不是一个好方法。





第二种方法：



1）在您要规划更新/插入的表中添加合适的唯一/主键约束。这将限制在这些列中插入具有相同信息的多行。



2）一个接一个地写两个查询。首先更新查询，然后插入同一个表的查询。当执行命令时，首先运行更新查询，然后插入查询。



3）现在有两种可能性：



a）数据库中已经存在记录：在这种情况下，第一个查询（更新）将执行，第二个查询（插入）将抛出异常，因为记录已存在。这是一个特殊的例外，例如''unique / primary key violation''。因此，您可以轻松捕获并忽略，因为已完成对现有记录的预期更新。



b）数据库中不存在记录：在这种情况下，第一个查询（更新）不会影响任何事情，因为数据库中没有记录。另一方面，将执行第二个查询并添加新行。



因此，我们使用一组查询解决相同问题（一次往返） ）。试着说服自己，如果我们改变这两个查询的顺序，这在更新期间不会有效。



如果你有其他人更好地工作，请告诉我方法。



干杯，

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,

这篇关于存在查询througyh c＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！