ADO.NET Database First - Update Insert

ADO.NET Entity Data Model Select Update Insert Delete Database First approach


- Open Visual Studio 2019 Community


- Select the "Create a new project" option


EntityFrameworkDatabaseFirst_1.PNG

- Select the "ASP.NET Web Application(.NET Framework)" option


EntityFrameworkDatabaseFirst_2.PNG

- Name the Project Name field as "DatabaseFirstApp"


- Select the "MVC" option form the "Create a new ASP.NET Web Application" dialog


EntityFrameworkDatabaseFirst_3.PNG

- Start the project


EntityFrameworkDatabaseFirst_4.PNG

EntityFrameworkDatabaseFirst_5.PNG

- Stop the project


EntityFrameworkDatabaseFirst_6.PNG

We are assuming that you have SQL Server Express installed on your computer. SQL Server Express is the free version of the SQL Server Database Engine.


- Open Microsoft SQL Server Management Studio 18


- Click the Connect button


EntityFrameworkDatabaseFirst_7.PNG

- Create a new database named "DB"
EntityFrameworkDatabaseFirst_8.PNG

EntityFrameworkDatabaseFirst_9.PNG

- Right click the "DB" database and select the "New Query" option.


EntityFrameworkDatabaseFirst_10.PNG

- Copy the following SQL query.


CREATE TABLE Persons(
    ID INT PRIMARY KEY IDENTITY (1, 1),
    FirstName VARCHAR (50),
    LastName VARCHAR (50),
	Age INT
);

- Paste the SQL query into the blank side as shown below.


Tables are used to store data in the database. To create a new table, you use the CREATE TABLE statement as shown above.


- Execute the SQL query


EntityFrameworkDatabaseFirst_11PNG

- You should get the "Commands completed successfully." message after executing the SQL query.


We have a simple database created which contains Persons table.


So far you have learned how to create a new ASP.NET Web Application and a new database and also how to execute a SQL query.


Create Model Using ADO.NET Entity Data Model


- Right click on the Model folder in Solution Explorer in Visual Studio 2019 Community and choose Add > New Item... option.


EntityFrameworkDatabaseFirst_12.PNG

- Select the "ADO.NET Entity Data Model" option from the coming dialog


EntityFrameworkDatabaseFirst_13.PNG

- Select the "EF Designer from database" option


- Click the "New Connection" button. Set the connection information to the Server Name field and the "Select or enter a database name" field.


- Click the Next button. Click the checkbox of the "Persons" table we want to import and click the Finish button.


EntityFrameworkDatabaseFirst_14.PNG

EntityFrameworkDatabaseFirst_15.PNG

EntityFrameworkDatabaseFirst_16.PNG

Now let's look at the new DbContext class.


EntityFrameworkDatabaseFirst_17.PNG

You can see that the Person Entity class has been created. You can now use a LINQ query to read/write from/to the database.


Insert row into the database using ADO.NET Database First


using (var db = new DBEntities())
{
    db.Persons.Add(new Book { FirstName = "Christian", LastName = "Morgan", Age = 34 });
    db.SaveChanges();

    foreach (var person in db.Persons)
    {
        //person.FirstName
		//person.LastName
		//person.Age
    }
}

Update Data using ADO.NET Database First


You can use the following code to update the person :


public ActionResult Edit(int? ID)
{
    if (ID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    Person person = db.Persons.Find(ID);
    if (person == null)
    {
        return HttpNotFound();
    }
    return View(person);
}

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include="ID,FirstName,LastName,Age")] Person person)
{
    if (ModelState.IsValid)
    {
        db.Entry(person).State = EntityState.Modified;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(person);
}



@model DatabaseFirstApp.Models.Person

@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
@using (Html.BeginForm("Login", "Login", FormMethod.Post))
{
	@Html.AntiForgeryToken()    
	@Html.ValidationSummary(true)
	@Html.HiddenFor(model => model.ID)
	
    <div class="form-group">

        @Html.LabelFor(m => m.FirstName)
        @Html.TextBoxFor(m => m.FirstName, new { @class = "form-control", placeholder = "Enter First Name" })
		@Html.ValidationMessageFor(m => m.FirstName)

    </div>
	
    <div class="form-group">

        @Html.LabelFor(m => m.LastName)
        @Html.TextBoxFor(m => m.LastName, new { @class = "form-control", placeholder = "Enter Last Name" })
		@Html.ValidationMessageFor(m => m.LastName)

    </div>
	
    <div class="form-group">

        @Html.LabelFor(m => m.Age)
        @Html.TextBoxFor(m => m.Age, new { @class = "form-control", placeholder = "Enter Age" })
		@Html.ValidationMessageFor(m => m.Age)

    </div>
	
    <button type="submit" class="btn btn-primary">Update</button>
	
}


Delete Data using ADO.NET Database First



public ActionResult Delete(int? ID)
{
    if (ID == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    Person person = db.Persons.Find(ID);
    if (person == null)
    {
        return HttpNotFound();
    }
    return View(person);
}

[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int ID)
{
    Person person = db.Persons.Find(ID);
    db.Persons.Remove(person);
    db.SaveChanges();
    return RedirectToAction("Index");
}

tag : update ado.net db first model,update ado.net,update entity ado.net database first,update entity ado.net db first,EF Designer from database update