- 1 year ago 49
SQL Question

Update if the name is exists else insert - in SQL Server

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

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);
else {
cmd = new SqlCommand("insert into T_Pages (pagename,pageinfo) values('" + filename + "','" + jsonobject + "')", con);


Answer Source

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
                        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
        int rowsAffected = cmd.ExecuteNonQuery();