First item only Save in the database

Question

I have a List of News and in here i tried to save/update to the database.News List comes as a LIST and here i have foreach the news list and trying to do the operation.But the issue is Always 1 item goes to Inserted section and save it to the database and all other items it doesn't exists in the database as goes to Update Query section

MyCode

 public string SaveNewsDetails(List<TempNews> listTempNews, int RowCount)
    {

        int value;
        string NewsStatus = string.Empty;


        StringBuilder sqlBuilder = new StringBuilder();

        DataAccess db = DataAccess.Create("Conn");
        try
        {
            using (var trn = db.BeginTransaction())
            {
                foreach (var item in listTempNews)
                {

                    IEnumerable<string> newsList = null;

                    sqlBuilder.Append("SELECT NewsCode ");
                    sqlBuilder.Append("FROM T_NEWS ");
                    sqlBuilder.Append("WHERE NewsCode = '" + item.N_Code + "'");

                    newsList = trn.Query<string>(sqlBuilder.ToString());


                    if (newsList == null || newsList.Count() == 0)
                    {
                        // Insert to table Query goes here
                    }
                    else
                    {
                       // Update Query Goes here
                    }
                    trn.Execute(sqlBuilder.ToString());

                }

                trn.Complete();
            }

        }
        catch (Exception Ex)
        {

        }
        return NewsStatus;

    }

Do you want to INSERT all data into the Table, or do you want to UPDATE all items in the DB if they exist? — Georg.Duees, Jun 20 '18 at 09:04
In here i have foreach item by item.If item exists in the db it needs to edit & if item doesn't exists in the db needs to insert.Currently First item of the List goes to Insert section — TechGuy, Jun 20 '18 at 09:05
Have a look at the following Thread, its kind of a Inser or Update Query: https://stackoverflow.com/a/27593831/4990965 [SQL Update multiple records in one query](https://stackoverflow.com/questions/20255138/sql-update-multiple-records-in-one-query) — Georg.Duees, Jun 20 '18 at 09:08
You should read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection pronto. — mjwills, Jun 20 '18 at 09:11
@bommelding actually not.Lets say `listTempNews` has 5 items.1st three items needs to be inserted,Because it already doesn't exists in the database and other 2 items already exists in the database and it needs to update..My insert and update query working fine.But the thing is Only first item inserted other all the items goes to Update section. — TechGuy, Jun 20 '18 at 09:39
If N_Code is set in database to a primary key you will only get one row for each N_Code. You need to check the return value from trn.Execute() which indicates the number of rows in database changed. If you do a Update and the item is not in the database you will get a zero and then you have to do Insert. Or you can do a Insert first and if you get a zero then do an Update. Insert gives zero if the items already exists and Update gives zero if the data is not in the database. — jdweng, Jun 20 '18 at 09:56

First item only Save in the database

MyCode

0 Answers0