Database only updating first row with diffrent value C#

Question

        string connetionString = null;
        SqlConnection connection;
        SqlCommand command;
        SqlDataAdapter adpter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        XmlReader xmlFile;
        string sql = null;

        int ID = 0;
        string Name = null, Text = null, Screenname = null;


        connetionString = "myconnection";

        connection = new SqlConnection(connetionString);

        xmlFile = XmlReader.Create("my.XML", new XmlReaderSettings());
        ds.ReadXml(xmlFile);
        int i = 0;
        connection.Open();
        for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
        {
            ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
            Text = ds.Tables[0].Rows[i].ItemArray[1].ToString().Replace("'", "''");
            Name = ds.Tables[0].Rows[i].ItemArray[2].ToString().Replace("'", "''");
            Screenname = ds.Tables[0].Rows[i].ItemArray[3].ToString().Replace("'", "''");

            //sql = "insert into nicktest values(" + ID + ",'" + Text + "'," + Name + "," + Screenname + "," + DateTime.Now.ToString() + ")";
            sql = "If Exists(Select * from niktest2 Where Id  = ID) " +
                                        " BEGIN " +
                                        " update niktest2 set Name  = '" + Text + "' , Screenname = '" + Name + "', Profimg= '" + Screenname + "', InsertDateTime= '" + DateTime.Now.ToString() + "' where Id=ID" +
                                        " END " +
                                        " ELSE " +
                                        " BEGIN " +
                                        " insert into niktest2(Id,Name,Screenname,Profimg,InsertDateTime) values('" + ID + "','" + Text + "','" + Name + "','" + Screenname + "' ,'" + DateTime.Now.ToString() + "')" +
                                        " END ";
            command = new SqlCommand(sql, connection);
            adpter.InsertCommand = command;
            adpter.InsertCommand.ExecuteNonQuery();
        }
    }

after running this code only first row gets updated even my xml file is having more data. i Want to insert all data into database with assign id to it in xml file. Please help..

SQL Injection warning: a couple of edits to your XML and your database is mine (or gone)…. *Please* use parameters in your SQL commands. — Richard, Jul 24 '14 at 08:04
What DBMS are you using - SQL Server 2005, SQL Server 2008, Oracle, Postgresql etc? — GarethD, Jul 24 '14 at 08:06
[Read and absorb](http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev) — Mwigs, Jul 24 '14 at 08:23

GarethD · Accepted Answer · 2014-07-24T08:31:55.253

As soon as you have inserted one row, this condition will be true:

If Exists(Select * from niktest2 Where Id  = ID)

So you will perform the update, rather than the insert, so you will only ever get one row in the database.

Since you are using SQL Server 2008 I would adopt a completely different approach, using Parameterised queries, MERGE, and table valued parameters.

The first step would be to create your table valued parameter (I have had to guess at your type:

CREATE TYPE dbo.nicktestTableType AS TABLE
(   
    Id INT,
    Name VARCHAR(255),
    Screenname VARCHAR(255),
    Profimg VARCHAR(255)
);

Then you can write your MERGE statement to upsert to the database:

MERGE nicktest WITH (HOLDLOCK) AS t
USING @NickTestType AS s
    ON s.ID = t.ID
WHEN MATCHED THEN 
    UPDATE
    SET Name = s.Name,
        Screenname = s.Screenname,
        Profimg = s.Profimg,
        InsertDateTime = GETDATE()
WHEN NOT MATCHED THEN 
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (s.Id, s.Name, s.Screenname, s.Profimg, GETDATE());

Then you can pass your datatable to the query as a parameter:

using (var command = new SqlCommand(sql, connection))
{
    var parameter = new SqlParameter("@NickTestType", SqlDbType.Structured);
    parameter.Value = ds.Tables[0];
    parameter.TypeName = "dbo.nicktestTableType";
    command.Parameters.Add(parameter);
    command.ExecuteNonQuery();
}

If you don't want to make such a drastic change, then you should at the very least use parameterised queries, so your SQL would be:

IF EXISTS (SELECT 1 FROM nicktest WHERE ID = @ID)
BEGIN
    UPDATE  nicktest
    SET     Name = @Name,
            ScreenName = @ScreeName,
            InsertDateTime = GETDATE()
    WHERE   ID = @ID;
END
ELSE
BEGIN
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (@ID, @Name, @Screenname, @Profimg, GETDATE());
END

Or preferably still using MERGE as the HOLDLOCK table hint prevents (or at least massively reduces the chance of) a race condition:

MERGE nicktest WITH (HOLDLOCK) AS t
USING (VALUES (@ID, @Name, @ScreenName, @ProfImg)) AS s (ID, Name, ScreenName, ProfImg)
    ON s.ID = t.ID
WHEN MATCHED THEN 
    UPDATE
    SET Name = s.Name,
        Screenname = s.Screenname,
        Profimg = s.Profimg,
        InsertDateTime = GETDATE()
WHEN NOT MATCHED THEN 
    INSERT (Id, Name, Screenname, Profimg, InsertDateTime)
    VALUES (s.Id, s.Name, s.Screenname, s.Profimg, GETDATE());

This will be considerably less efficient than the first solution though using table-valued parameter Then your c# would be something like:

for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@ID", ds.Tables[0].Rows[i][0]);
        command.Parameters.AddWithValue("@Name", ds.Tables[0].Rows[i][1]);
        command.Parameters.AddWithValue("@ScreeName", ds.Tables[0].Rows[i][2]);
        command.Parameters.AddWithValue("@ProfImg", ds.Tables[0].Rows[i][3]);
        command.ExecuteNonQuery();
    }
}

This answers the problem but I would suggest you alter your thinking a little and not run this RBAR-style (Row By Agonizing Row). I suggest using something else, maybe stage the data using SqlBulkCopy then use the MERGE statement. Alternatively build a CTE and merge that in a large SQL string. (injection warning). But RBAR is not really a good idea.. — atom.gregg, Jul 24 '14 at 08:18
@atom.gregg It would appear that my edit 7 seconds before your comment would cover your suggestions :) — GarethD, Jul 24 '14 at 08:27
Nice update, sorry I missed it before hitting post. Voted you up because of the completeness of your answer. — atom.gregg, Jul 24 '14 at 08:30
@GarethD just one more thing... how to download image, link is present XML file directly to local folder... — Patrick, Jul 24 '14 at 10:30
You'd have to do this iteratively, i.e. loop through your datatable and download each one individually. Are you storing the image as binary, or just storing the filepath? — GarethD, Jul 24 '14 at 10:55
In that case you don't need to change the way you are inserting to the database, you just need to run a loop to download the file either before or after you have written to the database. [This answer](http://stackoverflow.com/a/3615831/1048425) should help with how to download the image. — GarethD, Jul 24 '14 at 11:15
if i getlink from database and download image to local folder can this work — Patrick, Jul 24 '14 at 11:30

Database only updating first row with diffrent value C#

1 Answers1