CODEWITHSUNDEEP
c#sqllinq
StolenSheep
StolenSheep

Reputation: 57

Linq to SQL - MDF DB in debug folder being altered on Insert, not the attached copy?

I've got an insert working (sort of) in Linq but it edits the copy of the Sql database that's in the debug subfolder of the solution. I made this particular DB in VS as a server-based database and then made a Linq-to-Sql class to edit it. The Insert does work in that it inserts whatever data is entered, just into the wrong copy of the DB. I can't see the insert within VS at all or get to that data.

I'm no expert on Linq-to-Sql so maybe I've done something horribly wrong?

The C# insert:

        public TournDataContext db = new TournDataContext(); //Using Linq to Sql to connect to the DB.
        private void BtnNewPlayerSubmit_OnClick(object sender, RoutedEventArgs e) // Method called when the user clicks the register button.
    {
        string fName;
        string surname;
        string email;
        string password;
        DateTime dob;

        if (tbxFirstName.Text != null && tbxSurname.Text != null && tbxEmail.Text != null &&
            tbxPassword.Text != null && DateTime.TryParse(tbxDOB.Text, out dob)) // Checking for nulls.
        {
            fName = tbxFirstName.Text;
            surname = tbxSurname.Text;
            email = tbxEmail.Text;
            password = tbxPassword.Text;
            dob = DateTime.Parse(tbxDOB.Text);

            int newID = db.Players.Count() + 1;

            Player aPlayer = new Player
            {
                DOB = dob,
                Email = email,
                FirstName = fName,
                Paid_Fees = 'N',
                Password = password,
                Surname = surname,
            };

            db.Players.InsertOnSubmit(aPlayer);

            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {    
                Console.WriteLine(ex);
            }
        }

The SQL table definition, for what it's worth:

CREATE TABLE [dbo].[Players] (
[Id]        INT           IDENTITY (1, 1) NOT NULL,
[FirstName] NVARCHAR (30) NOT NULL,
[Surname]   NVARCHAR (30) NOT NULL,
[Email]     NVARCHAR (50) NOT NULL,
[DOB]       DATE          NOT NULL,
[Password]  NCHAR (30)    NOT NULL,
[Paid Fees] NCHAR (1)     DEFAULT ('N') NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)

);

The connection string:

    <connectionStrings>
    <add name="RegisterPlayer.Properties.Settings.TournamentDBConnectionString"
        connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TournamentDB.mdf;Integrated Security=True"
        providerName="System.Data.SqlClient" />
</connectionStrings>

Upvotes: 0

Views: 378

Answers (1)

BSG
BSG

Reputation: 1

Apparently when you first create your solution/project database it is created in the parent directory of your project. Afterward when you build/debug your solution it adds the database in the Bin/Debug directory. Any changes you make to your data while debugging are made in the Debug database. However, when you run your application each time in VS it pulls the data from the parent directory which never received the changes that you made while debugging because the changes were actually made in the Debug database.

Solution:

•In database explorer
•Right click on the database
•Select modify connection
•Advanced options
•Search for the property AttachDbFileName
•Change it to the DB from debug folder c:...\bin\debug\DB.mdf
•Once you point your database to the Debug directory, in VS Server Explorer, you can then delete the database in the solution explorer and all updates will transpire in the Debug database.

Upvotes: 1

Related Questions