Friday, May 20, 2011

Edit/Update with dapper ORM and ASP.NET MVC 3

In last two post I have already written about Getting data and adding data with Dapper Micro ORM. In this post I will explain how we can use the dapper ORM for data update. For reference following is the two post links for the Dapper ORM Series.
Playing with dapper Micro ORM and ASP.NET MVC 3.0
Insert with Dapper Micro ORM and ASP.NET MVC 3
Now as you we have already created CustomerDB Class. In this database operation class we will add two more methods GetCustomerById and Update to get Customer based on CustomerId passed and another one is for updating data. Following is modified Customer DB Code.
public class CustomerDB
{
  public string Connectionstring = @"Data Source=DotNetJalps\SQLExpress;Initial Catalog=CodeBase;Integrated Security=True";

  public IEnumerable<Customer> GetCustomers()
  {
      using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
      {
          sqlConnection.Open();
          var customer = sqlConnection.Query<Customer>("Select * from Customer");
          return customer;

      }
  }

  public Customer GetCustomerByID(int customerId)
  {
      using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
      {
          sqlConnection.Open();
          string strQuery = string.Format("Select * from Customer where CustomerId={0}", customerId);
          var customer = sqlConnection.Query<Customer>(strQuery).Single<Customer>();
          return customer;

      }
  }
  public bool Update( Customer customerEntity)
  {
      try
      {
          using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
          {
              sqlConnection.Open();
              string sqlQuery = "UPDATE [dbo].[Customer] SET [FirstName] =@FirstName, [LastName] =@LastName,[Address] =@Address,[City] = @City WHERE CustomerId=@CustomerId";
              sqlConnection.Execute(sqlQuery, new {
                                                      customerEntity.FirstName, customerEntity.LastName, customerEntity.Address, customerEntity.City, customerEntity.CustomerId
                                          });
              sqlConnection.Close();

          }
          return true;
      }
      catch (Exception exception)
      {
          return false;
      }
  }
  public string  Create(Customer customerEntity)
  {
      try
      {
          using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
          {
              sqlConnection.Open();
           
              string sqlQuery = "INSERT INTO [dbo].[Customer]([FirstName],[LastName],[Address],[City]) VALUES (@FirstName,@LastName,@Address,@City)";
              sqlConnection.Execute(sqlQuery,
                                    new
                                        {
                                            customerEntity.FirstName,
                                            customerEntity.LastName,
                                            customerEntity.Address,
                                            customerEntity.City
                                        });

             
              sqlConnection.Close();

          }
          return "Created";
      }
      catch (Exception ex)
      {
          return ex.Message;
      }

  }

}


Now It’s time to modify Customer Controller. I have added two more Action Result Result like following. One for fetching data and populating view and then another one for the updating data and redirecting it to Index action result. Just like following.
public ActionResult Edit(int id)
{

 var customerEntities = new CustomerDB();
 return View(customerEntities.GetCustomerByID(id));
}

//
// POST: /Customer/Edit/5

[HttpPost]
public ActionResult Edit( Customer customer)
{
 try
 {
     // TODO: Add update logic here
     var customerEntities = new CustomerDB();
     customerEntities.Update(customer);
     return RedirectToAction("Index");
 }
 catch
 {
     return View();
 }
}
So now our ActionResult is also ready now let’s add time to add Edit View for displaying current edit data. So go edit action result and right click->Add View-> Popup will appear for that. Now let’s create a strongly typed view like following.

EditView
So now we have created view and all other stuff its time to run our application. Let’s run it and go to Customer View like following.

View
Now I am going to click edit and above data will filled in Edit View.

Edit
Now After modifying data I have clicked save and as you can see in below screen data is modified.

ModifiedView
So that’s it. It’s very easy. Stay tuned for more.. Hope you liked it..

kick it on DotNetKicks.com
Shout it
Share:

4 comments:

  1. Doesn't using string.format to build your SQL statements open up the possibly for SQL Injection attacks?

    Would it be better to use let dapper build the sql?

    ie:

    string sqlQuery = "UPDATE [dbo].[Customer] SET [FirstName] = @FirstName, [LastName] = @LastName',[Address] =@Address,[City] = @City' WHERE CustomerId=@CustomerId";
    sqlConnection.Execute(sqlQuery, new {
    FirstName = customerEntity.FirstName,
    LastName = customerEntity.LastName,
    Address = customerEntity.Address,
    City = customerEntity.City,
    CustomerId = customerEntity.CustomerId
    });

    ReplyDelete
  2. @LaptopHaven- Thanks I have corrected it.

    ReplyDelete
  3. hello,

    Good one

    www.codeintellects.com
    (You can read/add categories that include Technologies (Sharepoint, .Net, design, SQL server..), Project Management and worklife. Registration is fast and FREE)

    ReplyDelete

Your feedback is very important to me. Please provide your feedback via putting comments.

Support this blog-Buy me a coffee

Buy me a coffeeBuy me a coffee
Search This Blog
Subscribe to my blog

  

My Mvp Profile
Follow us on facebook
Blog Archive
Total Pageviews