CODEWITHSUNDEEP
c#visual-studio-2010linq-to-sql
Bazzz
Bazzz

Reputation: 26937

How to update DataContext based on changes in the database structure?

I'm working in Visual Studio 2010 using linq-to-sql's DataContext which has several maps to tables in the database. Now when I change something to the structure of the database I noticed that the DataContext doesn't change and results in errors. The DataContext no longer corresponds with the structure of the database. I usually resolve this issue by deleting all table maps in the DataContext and drag&drop them again from the Database Explorer in Visual Studio. I just feel that this is very cumbersome and that there must be a better way to do this? Is there a button or an option to update the DataContext automatically when I changed the database structure?

Upvotes: 5

Views: 9187

Answers (3)

Nate Anderson
Nate Anderson

Reputation: 21034

In EF Core, you may find a certain "scaffolding" command helpful.

Scaffolding can regenerate your DbContext as well as your Models. And in my experience, it won't override any custom partial classes you made to extend the DbContext, so those continue to work.

You may need to install certain tools by adding them to your project.json (old)/ csproj (new)

dotnet cli

dotnet ef dbcontext scaffold --help`

Usage: dotnet ef dbcontext scaffold [arguments] [options]
Arguments:
  <CONNECTION>  The connection string to the database.
  <PROVIDER>    The provider to use. (E.g.  Microsoft.EntityFrameworkCore.SqlServer)

This command (run from the project's root directory, assuming you keep your Models in a folder called "Models"); 1) updates my Models and 2) my DbContext. If you only wanted updates to your DbContext, I use source-control (git) to discard changes to the Models; keep changes to the DbContext.

dotnet ef dbcontext scaffold "{connection}" Microsoft.EntityFrameworkCore.SqlServer \
-f --output-dir=Models

Powershell

More info here, an abbreviated command:

SYNTAX
    Scaffold-DbContext [-Connection] <String> [-Provider] <String> [-OutputDir <String>] [-Context <String>] [-Schemas <String[]>] [-Tables <String[]>] [-DataAnnotations] [-Force] [-Environment <String>] [-Project <String>] [-StartupProject <String>]
    [<CommonParameters>]

PARAMETERS
    -Connection <String>
        The connection string to the database.

    -Provider <String>
        The provider to use. (E.g. Microsoft.EntityFrameworkCore.SqlServer)

    -OutputDir <String>
        The directory to put files in. Paths are relaive to the project directory.

    -Context <String>
        The name of the DbContext to generate.

    ....

    -Force [<SwitchParameter>]
        Overwrite existing files.

Upvotes: 0

AVIK DUTTA
AVIK DUTTA

Reputation: 726

let the connectionstrin be : string pp = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Data.mdf;Integrated Security=True;Connect Timeout=30";

and update task be the below mentioned :

  public async Task callupdate()
        {
            try
            {
                int ppp = Convert.ToInt32(textBox1ID.Text);
                DataClasses1DataContext dc = new DataClasses1DataContext(pp);

                Person person = dc.Persons.Single(c => c.BusinessEntityID == ppp);
                person.PersonType = Convert.ToString(PersonTypecomboBox1.SelectedItem);
                person.PersonType = Convert.ToString(PersonTypecomboBox1.SelectedItem);
                if (NameStylecomboBox1.SelectedText == "False")
                    person.NameStyle = false;
                else
                    person.NameStyle = true;
                person.Title = Convert.ToString(TitlecomboBox1.SelectedItem);
                person.FirstName = FirstNametextBox2.Text;
                person.MiddleName = MiddleNametextBox3.Text;
                person.LastName = LastNametextBox4.Text;
                person.Suffix = SuffixtextBox5.Text;
                person.EmailPromotion = Convert.ToInt32(EmailPromotiontextBox6.Text);
                person.ModifiedDate = DateTime.Today;
                dc.SubmitChanges();
            }
            catch(Exception exp)
                {

                }

        }

instead of DataClasses1DataContext dc = new DataClasses1DataContext();

DataClasses1DataContext dc = new DataClasses1DataContext(pp);

Vby calling SubmitChanges() the update data that is the object of our class is actually being written in the actual database

Upvotes: -1

MattDavey
MattDavey

Reputation: 9017

Linq2Sql models are disconnected from the data source once they have been generated. It's only at the point of dragging & dropping items from the data source explorer that a connection is made and the database schema is queried. If your schema changes are small (ie a new table column) it's easy enough to add these manually. For more drastic schema changes your current method is probably the quickest and easiest.

It is possible to automate this code generation process by using the sqlmetal.exe command line tool. I've worked on projects in the past with database schemas which were constantly changing and we invoked sqlmetal before each build so we got useful compile errors when it changed. If your schema doesn't change so much, you could simply have a batch file in your project to update the Linq2Sql model when necessary.

Upvotes: 3

Related Questions