sql server 检索和更新(Windows Phone 7) [英] sql server retrieve and update (windows phone 7)

问题描述

我有一个检索代码:

[WebMethod]
public List<Hawker> retrievehawker()
{
    List<Hawker> retrievehawker = new List<Hawker>();

    string qry = @"select hawkername, address, postal, xcoord, ycoord, popularity from uploadphoto";

    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = qry;

    conn.Open();
    SqlDataReader mySqlDataReader = cmd.ExecuteReader();
    while (mySqlDataReader.Read())
    {
        Hawker retrieveHawker = new Hawker();
        retrieveHawker.hawkername = Convert.ToString(mySqlDataReader["hawkername"]);
        retrieveHawker.address = Convert.ToString(mySqlDataReader["address"]);
        retrieveHawker.postal = Convert.ToString(mySqlDataReader["postal"]);
        retrieveHawker.xcoord = Convert.ToDouble(mySqlDataReader["xcoord"]);
        retrieveHawker.ycoord = Convert.ToDouble(mySqlDataReader["ycoord"]);
        retrieveHawker.popularity = Convert.ToDouble(mySqlDataReader["popularity"]);


        retrievehawker.Add(retrieveHawker);
    }
    mySqlDataReader.Close();
    conn.Close();
    return retrievehawker;
}

和一组流行度:

    [WebMethod]
public int SetPopularity()
{
    string qry = @"update uploadphoto set popularity=popularity+1";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = qry;
    conn.Open();
    int status = cmd.ExecuteNonQuery();
    conn.Close();
    return status;
}

我如何将它们组合在一起，以便基于在 windows phone 7 中选择一个位置，单击按钮，然后它会触发 setpopularity.现在设置流行度的代码是将 +1 的整列添加到流行度.请帮忙.

How can I combine them together so that based on a selection of a place in the windows phone 7, of a button click, then it will trigger the setpopularity. Right now the code for set popularity is adding the whole column of +1 to popularity. Help please.

推荐答案

您需要将照片表的主键(或其他唯一值)传递给您的 SetPopularity 方法.
通过这种方式，您可以更改 sql 命令以仅更新所需的记录

You need to pass to your SetPopularity method the primary key (or another unique value) of your photo table.
In that way you could change your sql command to update only the record required

[WebMethod]
public int SetPopularity(string hawkername)
{
    string qry = @"update uploadphoto set popularity=popularity+1 
                   WHERE hawkername=@hawk";
    using(SqlConnection conn = new SqlConnection(connString))
    using(SqlCommand cmd = new SqlCommand(qry, conn))
    {
        conn.Open();
        cmd.Parameters.AddWithValue("@hawk", hawkername);
        int status = cmd.ExecuteNonQuery();
        return status;
     }
}

传递给方法的字符串是您的主键(或者如果索引更好，则是唯一值)并且可以在 WHERE 子句中使用.
还要注意围绕一次性对象的 using 语句和参数化查询方法，以避免 Sql 注入和解析问题.

The string passed to the method is your primary key (or an unique value better if indexed) and could be used in the WHERE clause.
Notice also the using statement around the disposable objects and the parameterized query approach to avoid Sql Injections and parsing problems.

这篇关于sql server 检索和更新(Windows Phone 7)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！