Delete with Dapper ORM and ASP.NET MVC 3

I have been writing few posts about Dapper ORM and ASP.NET MVC3 for data manipulation. In this post I am going to explain how we can delete the data with Dapper ORM. For your reference following are the links my previous posts.

Playing with dapper Micro ORM and ASP.NET MVC 3.0
Insert with Dapper Micro ORM and ASP.NET MVC 3
Edit/Update with dapper ORM and ASP.NET MVC 3

So to delete customer we need to have delete method in Our CustomerDB class so I have delete method into the CustomerDB class like following.

public bool Delete(int customerId)
{
try
{
    using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
    {
        sqlConnection.Open();
        string sqlQuery = "DELETE FROM [dbo].[Customer] WHERE CustomerId=@CustomerId";
        sqlConnection.Execute(sqlQuery, new {customerId});
        sqlConnection.Close();

    }
    return true;
}
catch (Exception exception)
{
    return false;
}
}

Now our delete method is ready It’s time to add ActionResult for Delete in Customer Controller like following. I have added two Action Result first will load simple delete with view and other action result will delete the data.

public ActionResult Delete(int id)
{
var customerEntities = new CustomerDB();
return View(customerEntities.GetCustomerByID(id));
}

//
// POST: /Customer/Delete/5

[HttpPost]
public ActionResult Delete(int id, FormCollection collection)
{
try
{
    var customerEntities = new CustomerDB();
    customerEntities.Delete(id);
    return RedirectToAction("Index");


}
catch
{
    return View();
}
}

Now It’s time to add delete view. So I have added strongly typed view like following.



Now everything is ready with code. So it’s time to check functionality let’s run application with Ctrl + F5. It will load browser like following.



Now I am clicking on delete it will load following screen to confirm deletion.



Once you clicked delete button it will redirect back to customer list and record is delete as you can see in below screen.



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

Continue Reading

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.


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..

Continue Reading

Insert with Dapper Micro ORM and ASP.NET MVC 3

As I have already posted about the how to fetch data in my earlier post for Dapper ORM. In this post I am going to explain how we can insert data with the dapper ORM. So let’s extend the earlier post project. As explained in earlier post I have already created a class called CustomerDB this class will contains all the operation with Dapper Micro ORM. So For inserting data let’s first create CREATE method like following in CutomerDB Class like following. In that I have create a simple Insert Query in string and then using connection.execute method to execute method. Following is code for that.

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 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.Execute(sqlQuery);
              sqlConnection.Close();

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

  }

}

Now we are ready with Create Method for database now let’s create two ActionResult for the Creating customer like following in Customer Controller like following.

public ActionResult Create()
{
return View();
}

//
// POST: /Customer/Create

[HttpPost]
public ActionResult Create(Customer customer)
{
try
{
    // TODO: Add insert logic here
    var customerEntities = new CustomerDB();
    customerEntities.Create(customer);
    return RedirectToAction("Index");
}
catch
{
    return View();
}
}

Now we are ready with the both ActionResult. First ActionResult will return simple view of Create which we are going to create now and another ActionResult Create will get customer object from the form submitted and will call our create method of CustomerDB Class. Now it’s time to create a view for adding customer. Right Click return view Statement in Create Action Result and Click Add View and Just Create view like following.


That’s it now we are ready. Now let’s test it in browser. Like following.


Now let’s click and create and then it will redirect us to customer list page like following. Where you can see the newly added details.


So that’s it. Its very easy to Insert data with Dapper Micro ORM. Hope you like it…Stat tuned for more.

Note: For reference of this post you can also see my first post called -Playing with dapper Micro ORM and ASP.NET MVC 3.0.

Continue Reading