如果名称存在则更新，否则在SQL Server中插入 [英] Update if the name exists else insert - in SQL Server

问题描述

如果给定的文件名已经在数据库中，我想在表中进行更新，否则我想插入新行.我尝试使用此代码，但 EXISTS 显示错误，请给我正确的方法，因为我在SQL中较新

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

推荐答案

您应该

• 在查询中使用参数- 总是！ -毫无例外
• 创建一个用于处理服务器上 IF EXISTS()部分
的查询
• 使用公认的ADO.NET最佳实践，将其放入 using(){....} 块等中.

• 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.

尝试以下代码:

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

这篇关于如果名称存在则更新，否则在SQL Server中插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！