使用ExecuteReader返回主键

Question

如何从第一个查询中找到ID并返回该值，以便将其插入到query2中？这是当用户在前端完成表单时需要完成的代码。我需要填充两个表，它们将通过ID "StoryID“关联，这是一个自动创建的主键。

protected void Upload2_Click(object sender, EventArgs e)
{     
        userStoryForm.Visible = false;
        info.Text = "You have successfully added a new user story.";


        String connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;


        String usernameData = username.Text.ToString();
        int captureProjectID = Convert.ToInt32(Request.QueryString.Get("ProjectID"));
        String storyno = StoryNoTextBox.Text;
        String userstory = StoryTextTextBox.Text;

        //Create connection
        SqlConnection myConnection = new SqlConnection(connectionString);

        //open connection
        myConnection.Open();

        String query =  "INSERT INTO UserStories (StoryNo, StoryText, ProductOwner, ProjectID) " + 
                        "VALUES ('" + storyno + "','" + userstory + "','" + usernameData + "','" + captureProjectID + "')" +
                        "SELECT SCOPE_IDENTITY() AS StoryID;";

        SqlCommand myCommand = new SqlCommand(query, myConnection);

       // Call GetOrdinal and assign value to variable.
       SqlDataReader reader = myCommand.ExecuteReader();

        int StoryIDData =  reader.GetOrdinal("StoryID");

        // Use variable with GetString inside of loop.
        while (reader.Read())
        {
            Console.WriteLine("StoryID={0}", reader.GetString(StoryIDData));
        }

        // Call Close when done reading.
        reader.Close();

        //insert productowner, projectID and storyID into ProductBacklog table
        String query2 = "INSERT INTO ProductBacklog (ProductOwner, ProjectID, StoryID) VALUES ('" + usernameData + "', @returnProjectID,'" + StoryIDData + "')";
        SqlCommand myCommand2 = new SqlCommand(query2, myConnection);
        myCommand2.Parameters.AddWithValue("@returnProjectID", captureProjectID);


        //close connection
        myConnection.Close();

    }

} 

Answer 1

最重要的是-在SQL命令中使用参数。永远不要像那样连接字符串。你在请求一次SQL injection攻击。

string query = @"
    INSERT INTO UserStories (StoryNo, StoryText, ProductOwner, ProjectID)
    VALUES (@storyno, @userstory, @usernameData, @captureProjectID)
    SELECT CAST(SCOPE_IDENTITY() AS INT)";

SqlCommand myCommand = new SqlCommand(query);
myCommand.Parameters.Add("@storyno", DbType.String).Value = storyno;
...

要获取返回的id，请使用ExecuteScalar()

int StoryIDData = (int)myCommand.ExecuteScalar();

此外，您没有正确地处理资源。如果方法中抛出异常，则不会关闭SQLConnection。您应该将其放在using语句中。