如果名称存在则更新，否则插入SQL Server

本文关键字：插入 SQL Server 更新 存在 如果 | 更新日期: 2023-09-27 18:05:02

我想更新我的表，如果我的给定文件名已经在我的数据库中，否则我想插入一个新的行。我尝试这个代码，但EXISTS显示的错误，请给我正确的方式，因为我在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();
}

如果名称存在则更新，否则插入SQL Server

你应该

  • 在您的查询中使用参数 - ALWAYS! -无例外
  • 创建一个在服务器上处理IF EXISTS()部分的查询
  • 使用普遍接受的ADO。. NET将东西放入using() {....}块等的最佳实践

试试这个代码:

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

如何使用一个MERGE语句?
当然使用参数可以避免SQL注入的风险。

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

按照

一行在数据库上创建一个存储过程
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)

从你的代码中调用它。你真的应该用一个参数化的命令来做到这一点(会有很多其他的问题告诉你最好的方法)。如果你愿意冒SQL注入攻击的风险并且不介意数据中有单引号字符失败那么你可以使用下面这种快捷方式

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

您可以使用一个查询

你想声明int类型的标志变量(你也可以让它的类型tinyint，这取决于你)，如果计数为0，意味着没有行，否则你更新你的查询

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

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

    }
相关文章：