Dotnetjalps-asp.net

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.

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.

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

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

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