MS SQL Stored Procedure working in SSMS but not workng when called from C#

Question

Hello

I’m following a tutorial which creates a winForm based app using C# and accessing SQL DBs and text files. Whilst I’m uncertain whether my problem is rooted on the SQL or C# side of the house I’m very strongly leaning towards C# despite the abort error message being an SQL exception provided by the Visual Studio debugger:
"System.Data.SqlClient.SqlException
Message=The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TeamPlayers_Players". The conflict occurred in database "TourneyManagerSSMS", table "dbo.Players", column 'id_player'.
The statement has been terminated.
Source=.Net SqlClient Data Provider"

Table TeamPlayers is a junction table referencing /linked to the Team and Player parent tables via foreign keys.. According to what I could find searching the internet the above error occurs if either of the parent tables are empty or if the foreign key doesn’t exist in the linked parent table. Both parent tables have validated test data and the primary key of both is setup with IDENTITY (1,1). I can write to and read from the DB tables via winForms and the specific query being called in C# can be successfully executed in SSMS, populating the junction table with data.

The app aborts when attempting to INSERT to the junction table. Checking the value contained in “model.id” shows expected data. Checking the value contained in “addTM2db.id”, however, reveals a value of 0 – which of course does not exist in the Player parent table. Hovering the cursor over “model.TeamMembers” – which is where I think “addTM2db.id” is getting its content from – shows a numerical value consistent with the number of Players selected on the winForm dropdown to be attached to a given team.

I fail to understand why the values in “model.TeamMembers” are not being picked up by “addTM2db.id”.

Following is (I think) the relevant code:

    public class Team
    {
        public int id { get; set; }
        public string TeamName { get; set; }
        public List<Player> TeamMembers { get; set; } = new List<Player>();
    }
    public class Player
    {
        public int id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EMail { get; set; }
        public string CellPhone { get; set; }
        public string getName { get { return $"{LastName}, {FirstName}"; } }
    }
    public partial class Create_Team : Form
    {
        // set-up for player combo dropdown and team Members listBox
        private List<Player> availablePlayers = ConnectionConfig.Connects.GetPlayers_All();
        private List<Player> selectedPlayers = new List<Player>();

        private void nuTeamButton_Click(object sender, EventArgs e)
        {
            if (ValidateTeamInput())
            {
                MessageBox.Show("A Team Name must be entered.");
            }
            else
            {
                Team model = new Team();
                {
                    model.TeamName = nuTeamNameTextBox.Text;
                    model.TeamMembers = selectedPlayers;
                }
                model = ConnectionConfig.Connects.nuTeam(model);
                teamAdded();
                // TODO MAYBE Re-Initialize Form
                nuTeamNameTextBox.Text = "";
            }
        }
    }

    public class DatabaseConnect : IdataSourceConnect
    {
        private const string DB = "Tourneys_MS_SQL";
        public Team nuTeam(Team model)
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))
            {
                var add2db = new DynamicParameters();
                add2db.Add("@TeamName", model.TeamName);
                add2db.Add("@id_team", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);

                connection.Execute("dbo.spTeams_ADD", add2db, commandType: CommandType.StoredProcedure);
                model.id = add2db.Get<int>("@id_team");

                foreach (Player addTM2db in model.TeamMembers) // values as expected
                {
                    add2db = new DynamicParameters();
                    add2db.Add("@id_team", model.id); // values as expected
                    add2db.Add("@id_player", addTM2db.id);  ///  ====> ZERO !!!!!!!!!!!!!!!
                    // add2TMdb.Add("@id_teamplayers", 0, dbType: DbType.Int32, direction: ParameterDirection.Output);
                    /*====> ABORT LINE:*/ connection.Execute("dbo.spTeamPlayers_ADD", add2db, commandType: CommandType.StoredProcedure);
                    // model.id = add2TMdb.Get<int>("@id_teamplayers");
                }
                return model;
            }
        }
    }

I’ve tried with and without the lines commented out (the tutorial author does it without) with the same SQL exception result. New teams are added to DB (line 54); run aborts on line 63.

Thank you in advance for your time & expertise.
Art

Answer 1

Hello @Jaliya Udagedara and @Timon Yang-MSFT

Problem resolved...

Root cause was inconsistent naming of the primary key "id_player" field in the SQL Player table and the associated C# Player class "id" property name.

After some ferreting around I found YamlDotNet which enabled me to see the relevant object content which then made it possible to determine the initial data acquired from the DB (availalePlayers) was also missing the ID info. Finding that out led to discovery of the naming inconsistency. Which meant the "id" in C# had never been populated in the first place.

Thanx again for your help and have a great start to the Euro-based New Year.

Answer 2

From the current code, your code to assign a value to "model.TeamMembers" is the following line:

model.TeamMembers = selectedPlayers;  

In addition, there are no other related codes in this code.
So, when you enter foreach, model.TeamMembers should still be an empty list.
But it doesn't seem to be the case at present. You successfully entered the foreach, indicating that it has at least one object.
Please show the part of the code that adds the object to it, or add watch to it when debugging, so that we can know its current value.
In addition, there is a small problem. The following two lines seem unnecessary to be placed in foreach:

 add2db = new DynamicParameters();  
 add2db.Add("@id_team", model.id); // values as expected  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hello @Jaliya Udagedara and @Timon Yang-MSFT
Thank you for your substantive responses confirming I’m at least focusing on the correct place to find the abort cause. Here is the additional you both requested:

        private void addTeamMemberButton_Click(object sender, EventArgs e)  
        {  
            Player xferPlayer = (Player)selectTeamMemberComboBox.SelectedItem;  
            if (xferPlayer != null)  
            {  
                availablePlayers.Remove(xferPlayer);  
                selectedPlayers.Add(xferPlayer);  // populating selectedPlayers list(Box)  
                wireUpLists();  
            }  
        }  

I have re-worked the relevant code flow presentation in case that is helpful:

 // ===> extract current list of player from SQL table and load into C# list object  
        public List<Player> GetPlayers_All()  
        {  
            List<Player> output;  
            using (IDbConnection connection =   
 new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))  
            {  
                output = connection.Query<Player>("dbo.spPlayers_GetAll").ToList();  
            }  
            return output;  
        }  
        // ===> set-up for combobo dropdown and Members listBox  
        private List<Player> availablePlayers = ConnectionConfig.Connects.GetPlayers_All();  
        private List<Player> selectedPlayers = new List<Player>();  
 // ===> populating winForm controls  
        private void wireUpLists()  
        {  
            selectTeamMemberComboBox.DataSource = null;  
            selectTeamMemberComboBox.DataSource = availablePlayers;  
            selectTeamMemberComboBox.DisplayMember = "getName";  
              
            MembersListBox.DataSource = null;  
            MembersListBox.DataSource = selectedPlayers;  
            MembersListBox.DisplayMember = "getName";  
        }  
 // ===> select specific player from dropdown and move from available to selected  
        private void addTeamMemberButton_Click(object sender, EventArgs e)  
        {  
            Player xferPlayer = (Player)selectTeamMemberComboBox.SelectedItem;  
            if (xferPlayer != null)  
            {  
                availablePlayers.Remove(xferPlayer);  
                selectedPlayers.Add(xferPlayer);  // populating selectedPlayers list(Box)  
                wireUpLists();  
            }  
        }  
 // ===> create new Team object containing data used to update SQL table  
 private void nuTeamButton_Click(object sender, EventArgs e)  
        {  
 Team model = new Team();  
 {  
 model.TeamName = nuTeamNameTextBox.Text;  
 model.TeamMembers = selectedPlayers;  
 }  
 model = ConnectionConfig.Connects.nuTeam(model);  
 }  
 // ===> add new info to SQL table  
        public Team nuTeam(Team model)  
        {  
            using (IDbConnection connection =   
 new System.Data.SqlClient.SqlConnection(ConnectionConfig.connectString(DB)))  
            {  
                var add2db = new DynamicParameters();  
                add2db.Add("@TeamName", model.TeamName);  
                add2db.Add("@id_team", 0, dbType: DbType.Int32,   
                    direction: ParameterDirection.Output);  
  
                connection.Execute("dbo.spTeams_ADD", add2db,   
 commandType: CommandType.StoredProcedure);  
                                  
                model.id = add2db.Get<int>("@id_team");  
  
                var add2junctionTable = new DynamicParameters();  
                foreach (Player add2TeamMemberdb in model.TeamMembers)  
                {  
                    add2junctionTable = new DynamicParameters();  
                    add2junctionTable.Add("@id_team", model.id);  
                    add2junctionTable.Add("@id_player", add2TeamMemberdb.id);  ///  ====> ZERO !!  
                    add2junctionTable.Add("@id_teamplayers", 0, dbType: DbType.Int32,   
                        direction: ParameterDirection.Output);  
  
                    connection.Execute("dbo.spTeamPlayers_ADD", add2junctionTable,   
 commandType: CommandType.StoredProcedure);  
  
                    model.id = add2junctionTable.Get<int>("@id_teamplayers");  
                }  
                return model;  
            }  
        }  

Thanks again for your help and let me know if there’s anything else I can provide to facilitate identification of a resolution.
Art