How to use update command in asp.net

Viewed 1k times. Fill ds ; if ds. MLavoie 9, 40 40 gold badges 36 36 silver badges 54 54 bronze badges. Laxmi Kathuria Laxmi Kathuria 1 1 1 bronze badge. OT please give your controls more meaningful names than "TextBox7" - your future self will thank you for it when this code needs updating.

Add a comment. Active Oldest Votes. Text ; cmd. Text ; Always use Parameters to preform any database actions. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password.

Post as a guest Name. The "upsert" operation prevents errors that would happen if you try to insert a row that already exists, but it overrides any changes to data that you may have made while testing the application. With test data in some tables you might not want that to happen: in some cases when you change data while testing you want your changes to remain after database updates.

In that case you want to do a conditional insert operation: insert a row only if it doesn't already exist. The Seed method uses both approaches. The first parameter passed to the AddOrUpdate method specifies the property to use to check if a row already exists. For the test student data that you are providing, the LastName property can be used for this purpose since each last name in the list is unique:.

This code assumes that last names are unique. If you manually add a student with a duplicate last name, you'll get the following exception the next time you perform a migration:. The code that creates Enrollment entities assumes you have the ID value in the entities in the students collection, although you didn't set that property in the code that creates the collection.

EF automatically gets the primary key value when it inserts an entity into the database, and it updates the ID property of the entity in memory.

The code that adds each Enrollment entity to the Enrollments entity set doesn't use the AddOrUpdate method. It checks if an entity already exists and inserts the entity if it doesn't exist. This approach will preserve changes you make to an enrollment grade by using the application UI. The code loops through each member of the Enrollment List and if the enrollment is not found in the database, it adds the enrollment to the database.

The first time you update the database, the database will be empty, so it will add each enrollment. When you executed the add-migration command, Migrations generated the code that would create the database from scratch. The Up method of the InitialCreate class creates the database tables that correspond to the data model entity sets, and the Down method deletes them. Migrations calls the Up method to implement the data model changes for a migration.

When you enter a command to roll back the update, Migrations calls the Down method. This is the initial migration that was created when you entered the add-migration InitialCreate command.

The parameter InitialCreate in the example is used for the file name and can be whatever you want; you typically choose a word or phrase that summarizes what is being done in the migration. For example, you might name a later migration "AddDepartmentTable". If you created the initial migration when the database already exists, the database creation code is generated but it doesn't have to run because the database already matches the data model. When you deploy the app to another environment where the database doesn't exist yet, this code will run to create your database, so it's a good idea to test it first.

That's why you changed the name of the database in the connection string earlier—so that migrations can create a new one from scratch. The update-database command runs the Up method to create the database and then it runs the Seed method to populate the database. The same process will run automatically in production after you deploy the application, as you'll see in the following section. Use Server Explorer to inspect the database as you did in the first tutorial, and run the application to verify that everything still works the same as before.

So far the application has been running locally in IIS Express on your development computer. To make it available for other people to use over the Internet, you have to deploy it to a web hosting provider. In this section of the tutorial, you'll deploy it to Azure. This section is optional; you can skip this and continue with the following tutorial, or you can adapt the instructions in this section for a different hosting provider of your choice. To deploy the database, you'll use Code First Migrations.

When you create the publish profile that you use to configure settings for deploying from Visual Studio, you'll select a check box labeled Update Database. This setting causes the deployment process to automatically configure the application Web.

Visual Studio doesn't do anything with the database during the deployment process while it is copying your project to the destination server. When you run the deployed application and it accesses the database for the first time after deployment, Code First checks if the database matches the data model. If there's a mismatch, Code First automatically creates the database if it doesn't exist yet or updates the database schema to the latest version if a database exists but doesn't match the model.

If the application implements a Migrations Seed method, the method runs after the database is created or the schema is updated. Your Migrations Seed method inserts test data. If you were deploying to a production environment, you would have to change the Seed method so that it only inserts data that you want to be inserted into your production database. For example, in your current data model you might want to have real courses but fictional students in the development database.

You can write a Seed method to load both in development, and then comment out the fictional students before you deploy to production. Or you can write a Seed method to load only courses, and enter the fictional students in the test database manually by using the application's UI. You'll need an Azure account. If you don't already have one, but you do have a Visual Studio subscription, you can activate your subscription benefits. Otherwise, you can create a free trial account in just a couple of minutes.

The result is that this part of the markup in the column simply produces something like the following markup at run time:. Now back to the grid column. The three columns you originally had in the grid displayed only data values title, genre, and year.

You specified this display by passing the name of the database column — for example, grid. Column "Title". This new Edit link column is different. Instead of specifying a column name, you're passing a format parameter. This parameter lets you define markup that the WebGrid helper will render along with the item value to display the column data as bold or green or in whatever format that you want. For example, if you wanted the title to appear bold, you could create a column like this example:.

The various characters you see in the format property mark the transition between markup and a code value. Once you know about the format property, it's easier to understand how the new Edit link column is put together:. The column consists only of the markup that renders the link, plus some information the ID that's extracted from the database record for the row.

Many times when you've called a method and passed parameters to it, you've simply listed the parameter values separated by commas. Here are a couple of examples:.

We didn't mention the issue when you first saw this code, but in each case, you're passing parameters to the methods in a specific order — namely, the order in which the parameters are defined in that method.

For db. Execute and Validation. RequireFields , if you mixed up the order of the values you pass, you'd get an error message when the page runs, or at least some strange results.

Clearly, you have to know the order to pass the parameters in. In WebMatrix, IntelliSense can help you learn figure out the name, type, and order of the parameters. As an alternative to passing values in order, you can use named parameters. Passing parameters in order is known as using positional parameters. For named parameters, you explicitly include the name of the parameter when passing its value.

You've used named parameters already a number of times in these tutorials. For example:. Named parameters are handy for a couple of situations, especially when a method takes many parameters. One is when you want to pass only one or two parameters, but the values you want to pass are not among the first positions in the parameter list.

Another situation is when you want to make your code more readable by passing the parameters in the order that makes the most sense to you. Obviously, to use named parameters, you have to know the names of the parameters. WebMatrix IntelliSense can show you the names, but it cannot currently fill them in for you. Now you can create the EditMovie page.

When users click the Edit link, they'll end up on this page. Create a page named EditMovie. This markup and code is similar to what you have in the AddMovie page.

There's a small difference in the text for the submit button. As with the AddMovie page, there's an Html. ValidationSummary call that will display validation errors if there are any. This time we're leaving out calls to Validation. Message , since errors will be displayed in the validation summary. As noted in the previous tutorial, you can use the validation summary and the individual error messages in various combinations.

As with the AddMovie. Therefore, this form should perform a POST operation. As you saw in an earlier tutorial, the value attributes of the text boxes are being set with Razor code in order to preload them. This time, though, you're using variables like title and genre for that task instead of Request. Form["title"] :. As before, this markup will preload the text box values with the movie values. You'll see in a moment why it's handy to use variables this time instead of using the Request object.

This element stores the movie ID without making it visible on the page. The ID is initially passed to the page by using a query string value? By putting the ID value into a hidden field, you can make sure that it's available when the form is submitted, even if you no longer have access to the original URL that the page was invoked with. Unlike the AddMovie page, the code for the EditMovie page has two distinct functions.

The first function is that when the page is displayed for the first time and only then , the code gets the movie ID from the query string. The code then uses the ID to read the corresponding movie out of the database and display preload it in the text boxes.

The second function is that when the user clicks the Submit Changes button, the code has to read the values of the text boxes and validate them. The code also has to update the database item with the new values.

This technique is similar to adding a record, as you saw in AddMovie. Most of this code is inside a block that starts if! As noted earlier, this code should run only the first time the page runs. If you didn't enclose the code in if! IsPost , it would run every time the page is invoked, whether the first time or in response to a button click.

Notice that the code includes an else block this time. As we said when we introduced if blocks, sometimes you want to run alternative code if the condition you're testing isn't true.

That's the case here. If the condition passes that is, if the ID passed to the page is ok , you read a row from the database. However, if the condition doesn't pass, the else block runs and the code sets an error message. The code uses Request. QueryString["id"] to get the ID that's passed to the page.

The code makes sure that a value was actually passed for the ID. If no value was passed, the code sets a validation error. This code shows a different way to validate information. In the previous tutorial, you worked with the Validation helper. You registered fields to validate, and ASP. NET automatically did the validation and displayed errors by using Html.