如何在webservice中的变量中存储select查询的值?

I am new to webservice development. I have made webservice in asp.net using c# and mysql.

我是webservice开发的新手。我使用c#和mysql在asp.net中创建了webservice。

I want to store values of select query in variable which then i want to insert in table.

我想将select查询的值存储在变量中，然后我想将其插入到表中。

I have used following code :

我使用了以下代码:

//for inserting new game details in the tbl_Game by FB
    [WebMethod]
    public string InsertNewGameDetailsForFB(string gametype, string player1, string player2, string player3, string player4, string player5)
    {
        string success = "Error in Insertion";

        string selectID = "Select UserID from tbl_userinfo where Facebook_ID IN ('" + player1 + "','" + player2 + "','" + player3 + "')";
        con = new MySqlConnection(conString);
        con.Open();
        MySqlCommand cmd = new MySqlCommand(selectID, con);
        MySqlDataReader ids = cmd.ExecuteReader();
        string id1="", id2="", id3="";
        while (ids.Read())
        {
           id1 = ids.GetString(0);
           id2 = ids.GetString(1);
           id3 = ids.GetString(2);

        }

        string insertNewGame = "Insert into tbl_game(Type,Player1,Player2,Player3,Player4,Player5) values";
        insertNewGame += "( '" + gametype + "' , '" + id1 + "', '" + id2 + "','" + id3 + "', '" + player3 + "','" + player4 + "', '" + player5 + "' )";
        con = new MySqlConnection(conString);
        con.Open();
        MySqlCommand cmd1 = new MySqlCommand(insertNewGame, con);
        int success1 = cmd1.ExecuteNonQuery();
        con.Close();

        string gameID = "Select MAX(GameID) from tbl_game";
        con = new MySqlConnection(conString);
        con.Open();
        MySqlCommand cmd2 = new MySqlCommand(gameID, con);
        string gameid = cmd2.ExecuteScalar().ToString();

        if (success1 > 0)
        {
           success="Inserted Successfully, GameID is - " + gameid;
        }
        return success;
    }

how can i do this ?

我该怎么做呢?

Thanks.

谢谢。

1 个解决方案

#1

Your first issue is how you're attempting to read in UserID from your first query. This query will not return three columns but three rows. So you need to do something like this:

第一个问题是如何尝试从第一个查询读取UserID。这个查询不会返回三列，而是三行。所以你需要这样做:

int index = 0;
while (ids.Read())
{
    switch (index)
    {
        case 0:
            id1 = ids.GetString(0);
            break;
        case 1:
            id2 = ids.GetString(0);
            break;
        case 2:
            id3 = ids.GetString(0);
            break;
    }
    index += 1;
}

That should store them properly. My second suggestion is since this is a web service, you should avoid SQL Injection attacks and use parameterized queries rather than dynamic SQL. There are plenty of examples on the web you can use.

这应该能正确地储存它们。我的第二个建议是，由于这是一个web服务，您应该避免SQL注入攻击，使用参数化查询而不是动态SQL。网上有很多你可以使用的例子。

My final suggestion is to use the using statement religiously for objects which implement IDisposable (i.e. the connection object, the command, the reader, etc...). This ensures proper cleanup of objects.

我的最后一个建议是对实现IDisposable的对象(例如连接对象、命令、阅读器等等)使用using语句。这确保了对象的正确清理。