Update if the name exists else insert - in SQL Server

Question

I want update in my table if my given filename is already in my database else I want to insert a new row. I try this code but the EXISTS shown error please give me the correct way beacuse iam fresher in SQL

public void SaveData(string filename, string jsonobject)
{
    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
    SqlCommand cmd;
    SqlCommand cmda;

    if EXISTS("SELECT * FROM T_Pages WHERE pagename = '" + filename + "") {
        cmda = new SqlCommand("UPDATE T_Pages SET pagename='" + filename + "',pageinfo='" + jsonobject + "' WHERE pagename='" + filename + "'", con);
        cmda.ExecuteNonQuery();
    }
    else {
        cmd = new SqlCommand("insert into T_Pages (pagename,pageinfo) values('" + filename + "','" + jsonobject + "')", con);
        cmd.ExecuteNonQuery();
    }

    con.Close();
}

What is if EXISTS ? you're in C# code. you can't write SQL syntax in C# code — Royi Namir
– Royi Namir, Commented Aug 31, 2016 at 12:33
MERGE is a poor choice here, use IF EXISTS/INSERT/UPDATE in a single batch. — Alex K.
– Alex K., Commented Aug 31, 2016 at 12:34
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Aug 31, 2016 at 12:47

marc_s · Accepted Answer · 2016-08-31 12:52:35Z

You should

use parameters in your query - ALWAYS! - no exception
create a single query that handles the IF EXISTS() part on the server
use the generally accepted ADO.NET best practices of putting things into using() {....} blocks etc.

Try this code:

public void SaveData(string filename, string jsonobject)
{
    // define connection string and query
    string connectionString = "Data Source=.;Initial Catalog=;Integrated Security=True";
    string query = @"IF EXISTS(SELECT * FROM dbo.T_Pages WHERE pagename = @pagename)
                        UPDATE dbo.T_Pages 
                        SET pageinfo = @PageInfo
                        WHERE pagename = @pagename
                    ELSE
                        INSERT INTO dbo.T_Pages(PageName, PageInfo) VALUES(@PageName, @PageInfo);";

    // create connection and command in "using" blocks
    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand(query, conn))
    {
        // define the parameters - not sure just how large those 
        // string lengths need to be - use whatever is defined in the
        // database table here!
        cmd.Parameters.Add("@PageName", SqlDbType.VarChar, 100).Value = filename;
        cmd.Parameters.Add("@PageInfo", SqlDbType.VarChar, 200).Value = jsonobject;

        // open connection, execute query, close connection
        conn.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        conn.Close();
    }
}

LukStorms · Accepted Answer · 2020-05-20 20:19:31Z

5

How about using one MERGE statement?
Of course with parameters to avoid a risk of SQL injection.

public void SaveData(string filename, string jsonobject)
{
  SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");

  SqlCommand cmd;

  cmd = new SqlCommand(@"
    MERGE T_Pages AS target  
    USING (SELECT @PageName as pagename, @PageInfo as pageinfo) AS source 
    ON (target.pagename = source.pagename)  
    WHEN MATCHED THEN   
        UPDATE SET pageinfo = source.pageinfo  
    WHEN NOT MATCHED THEN  
    INSERT (pagename, pageinfo)  
    VALUES (source.pagename, source.pageinfo)", con);

   cmd.Parameters.Add(new SqlParameter("@PageName", filename));
   cmd.Parameters.Add(new SqlParameter("@PageInfo", jsonobject));

   con.Open();
   cmd.ExecuteNonQuery();
   con.Close();
 }

edited May 20, 2020 at 20:19

answered Aug 31, 2016 at 13:05

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

2 Comments

Antonio Over a year ago

Yes, MERGE should be perfect in this case. Depending on the SQL Server Version. I believe it was introduced in SQL 2008 only...

LukStorms Over a year ago

Indeed, MERGE was introduced in the 2008 version. Based on the SQL:2003 standard. Since the question didn't specify the DB version one can't assume that it has to work on SQL Server 2005 or earlier :)

Tom Page · Accepted Answer · 2016-08-31 13:10:11Z

Create a Stored procedure on the database along the lines of

CREATE PROCEDURE T_Pages_Write  (@pagename nvarchar(256) ,@pageinfo nvarchar(max)) 
AS
IF EXISTS (SELECT pagename from T_Pages WHERE pagename = @pagename)
UPDATE T_Pages SET pageinfo = @pageinfo WHERE pagename = @pagename
ELSE 
INSERT T_Pages (pagename, Pageinfo) VALUES (@pagename, @Pageinfo)

And call that from your code. You really should do that with a parameterised command (There will be loads of other questions advising you on the best way to do that). If you're willing to run the risk of SQL injection attacks and don't mind it failing if there's a single quote character in your data then you can do it the quick and dirty way like this

cmd = new SqlCommand("EXEC T_PagesWrite @pagename = '" + filename + "', @pageinfo ='" + jsonobject + "'", con);

Faraz Ahmed · Accepted Answer · 2016-08-31 13:04:04Z

0

You can done this with one Query

where you want to decalre flag variable of type int ( you also make it of type tinyint, it's up to you) and if the count is 0, means no rows ,else you update your Query

SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
SqlCommand cmd;
cmda = new SqlCommand("declare @flag int ;set @flag = 0;select @flag = COUNT(*) FROM T_Pages WHERE pagename = '" + filename + "';IF @flag = 1 UPDATE T_Pages SET pagename='" + filename + "',pageinfo='" + jsonobject + "' WHERE pagename='" + filename + "';ELSE insert into T_Pages (pagename,pageinfo) values('" + filename + "','" + jsonobject + "');", con);
cmda.ExecuteNonQuery();
con.Close();

edited Aug 31, 2016 at 13:04

answered Aug 31, 2016 at 12:55

Faraz Ahmed

1,6273 gold badges20 silver badges35 bronze badges

3 Comments

Manu Padmanabhan Over a year ago

why .some one down vote this answer ..? according to the question pattern (if don't think about the sql injection ...somthing ....) this code is working one .

Faraz Ahmed Over a year ago

@mr.cool , you are right, but what we can do? here are some members they think ,they are right, as we see that person who ask question has no background of sql injection from his question, but still second time StackOverflow break my heart. :(

Manu Padmanabhan Over a year ago

but i done up vote because as a fresher this code is more understandable one

Hitesh Thakor · Accepted Answer · 2016-08-31 12:37:20Z

public void SaveData(string filename, string jsonobject)
    {
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=;Integrated Security=True");
        SqlCommand cmd;
        SqlCommand cmda;

        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM T_Pages WHERE pagename = '" + filename + "", con);
        DataSet ds = new DataSet();
        da.Fill(ds);



        if (ds.Tables[0].Rows.Count > 0)
        {

            cmda = new SqlCommand("UPDATE T_Pages SET pagename='" + filename + "',pageinfo='" + jsonobject + "' WHERE pagename='" + filename + "'", con);
            cmda.ExecuteNonQuery();
        }
        else
        {
            cmd = new SqlCommand("insert into T_Pages (pagename,pageinfo) values('" + filename + "','" + jsonobject + "')", con);
            cmd.ExecuteNonQuery();
        }


        con.Close();



    }

Collectives™ on Stack Overflow

Update if the name exists else insert - in SQL Server

5 Answers 5

Comments

2 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related