Bulk Delete for Webgrid using Checkbox

How can I perform bulk delete operation on a webgrid? I am missing the part where all the checked row will be requested and dynamically add the id to the sql query. Here is the screenshot:

The code I have now:

var db = Database.Open("MySQLDB");
    var query = "SELECT * FROM sample";
    var data = db.Query(query);
    var grid =  new WebGrid(source:data, defaultSort:"date_sent", rowsPerPage:10, ajaxUpdateContainerId:"grid");
   
    if(IsPost){
        @*get the selected message ids to be deleted here..*@
        var delFromSample = "DELETE FROM sample WHERE IN (the selected message ids here..)";
        db.Execute(delFromSample, Request["messageId"]);
    }
}

<div>
    <form method="post" action="">
        <input type="submit" value="Delete"/>
    </form>
</div>
<div>
    @grid.GetHtml(
        tableStyle:"grid",
        headerStyle:"head",
        alternatingRowStyle:"alt",
        columns: grid.Columns(
            grid.Column("id", "Id", style:"tdId"),
            grid.Column("subject", "Subject", style: "tdStyle"),
            grid.Column("sender", "From", style: "tdStyle"),
            grid.Column("date_sent", "Date", style:"tdStyle"),
            grid.Column(header:"", format:@<input type="checkbox" name="messageId" value="@item.id"/>)
        )
    )
</div>

Re: Bulk Delete for Webgrid using Checkbox

First, you need to make sure that the WebGrid is inside the form tag so that the selected checkbox values are submitted. When they are, they will be submitted as one comma-separated string in Request["messageId"]. Then, you need a helper to manage the IN clause: http://www.mikesdotnetting.com/Article/156/WebMatrix-Database-Helpers-for-IN-Clauses

Re: Bulk Delete for Webgrid using Checkbox

Thank you for that Mike. It works fine. I just have another question to ask. I can't correctly place a checkbox as a header to the webgrid. The header checkbox will be the checkall thing. I already have js that handles the checking all checkboxes. I just can't place the checkbox as the header for that column. Thanks!

Re: Bulk Delete for Webgrid using Checkbox

There's no way to do that using Razor/C#. You have to use JavaScript/jQuery to add one. Since you are already using jQuery in your page here's how to do it:

<script>
    $(function(){
        var $chk = $('<input/>').attr({ type: 'checkbox', name:'chkAll'});
        $('th:last').html('Select All <br />').append($chk);
    });
</script>

Re: Bulk Delete for Webgrid using Checkbox

I added your script in my page, but the check all jquery plugin doesn't work anymore. Here is the code before adding your script:

<script type="text/javascript">
   $(function() {
    var propFn = typeof $.fn.prop === 'function' ? 'prop' : 'attr';

    $('#checkall').click(function() {
        $(this).parents('form:eq(0)').find(':checkbox')[propFn]('checked', this.checked);
    });
    $("input[type=checkbox]:not(#checkall)").click(function() {
        if (!this.checked) {
            $("#checkall")[propFn]('checked', this.checked);
        } else {
            $("#checkall")[propFn]('checked', !$("input[type=checkbox]:not(#checkall)").filter(':not(:checked)').length);
        }

    });
});
</script>

<div>
    <form method="post" action="" id="grid">
        <input type="checkbox" id="checkall"/>
        <div>
            @grid.GetHtml(
                tableStyle:"grid",
                headerStyle:"head",
                alternatingRowStyle:"alt",
                columns: grid.Columns(
                    grid.Column("id", "Id", style:"tdId"),
                    grid.Column("subject", "Subject", style: "tdStyle"),
                    grid.Column("sender", "From", style: "tdStyle"),
                    grid.Column("date_sent", "Date", style:"tdStyle"),
                    grid.Column(header:"", format:@<input type="checkbox" name="messageId" value="@item.id"/>)
                )
            )
        </div>
        <input type="submit" name="bulkDelete" value="Delete"/>
    </form>
</div>

After adding your script:

<script type="text/javascript">
   $(function() {
    var propFn = typeof $.fn.prop === 'function' ? 'prop' : 'attr';

    $('#checkall').click(function() {
        $(this).parents('form:eq(0)').find(':checkbox')[propFn]('checked', this.checked);
    });
    $("input[type=checkbox]:not(#checkall)").click(function() {
        if (!this.checked) {
            $("#checkall")[propFn]('checked', this.checked);
        } else {
            $("#checkall")[propFn]('checked', !$("input[type=checkbox]:not(#checkall)").filter(':not(:checked)').length);
        }

    });
});
</script>

<div>
    <form method="post" action="" id="grid">
        <script type="text/javascript">
        $(function(){
            var $chk = $('<input/>').attr({ type: 'checkbox', id:'checkall'});
            $('th:last').html('<br />').append($chk);
        });
    </script>
        <div> 
            @grid.GetHtml(
                tableStyle:"grid",
                headerStyle:"head",
                alternatingRowStyle:"alt",
                columns: grid.Columns(
                    grid.Column("id", "Id", style:"tdId"),
                    grid.Column("subject", "Subject", style: "tdStyle"),
                    grid.Column("sender", "From", style: "tdStyle"),
                    grid.Column("date_sent", "Date", style:"tdStyle"),
                    grid.Column(header:"", format:@<input type="checkbox" name="messageId" value="@item.id"/>)
                )
            )
        </div>
        <input type="submit" name="bulkDelete" value="Delete"/>
    </form>
</div>

Thanks!

Re: Bulk Delete for Webgrid using Checkbox

I don't think you need the plugin. It seems straightforward enough to me without that.:

<form method="post" action="">
  <div id="grid">
    @grid.GetHtml(
      tableStyle:"grid",
      headerStyle:"head",
      alternatingRowStyle:"alt",
      columns: grid.Columns(
        grid.Column("id", "Id", style:"tdId"),
        grid.Column("subject", "Subject", style: "tdStyle"),
        grid.Column("sender", "From", style: "tdStyle"),
        grid.Column("date_sent", "Date", style:"tdStyle"),
        grid.Column(header:"", format:@<input type="checkbox" name="messageId" value="@item.id"/>)
        )
    )
  </div>
  <input type="submit" name="bulkDelete" value="Delete"/>
</form>
    <script>
        $(function(){
            addCheck();
        });
        function addCheck(){
            var $chk = $('<input/>').attr({ type: 'checkbox', name:'chkAll', id: 'chkAll', title: "Select All"});
            $('th:last').append($chk);
            $('#chkAll').click(function(){
                $(':checkbox').attr('checked', $(this).is(':checked') ? 'checked' : '' );
            });
        }
    </script>

Make sure you move the id="grid" from the form to the div, and also add an ajaxUpdateCallback to your new WebGrid() declaration to point ot the addCheck function (like this):

var grid = new WebGrid(data, ajaxUpdateContainerId: "grid", ajaxUpdateCallback: "addCheck");

Re: Bulk Delete for Webgrid using Checkbox

Thanks Mike! Your script made my code shorter. I just can't uncheck all the boxes once the checkall was checked.

Re: Bulk Delete for Webgrid using Checkbox

It works fine in all browsers on my machine. Double check your code for typos and if you're still stcuk, post it here

Re: Bulk Delete for Webgrid using Checkbox

I don't know JQuery very well, but I think it's better to replace the line

$(':checkbox').attr('checked', $(this).is(':checked') ? 'checked' : '' );

with

$(':checkbox').prop('checked', $(this).is(':checked') ? true : false );

(using jQuery 1.6+).

I have tried to implement a function that deselects chkAll if you uncheck another checkbox. The complete script follows:

<script>
    $(function(){
        addCheck();
    });
    function addCheck() {
        var $chk = $('<input/>').attr({ type:'checkbox', name:'chkAll', id:'chkAll', title:"Select All"});
        $('th:last').append($chk);
        $('#chkAll').click(function(){
            $(':checkbox').prop('checked', $(this).is(':checked') ? true : false );
        });
        $(':checkbox').not('#chkAll').click(function(){
            testCheck();
        });
    }
    function testCheck() {
        if($(':checkbox').not('#chkAll').filter(':not(:checked)').length === 0){
            $('#chkAll').prop('checked', true);
        } else {
            $('#chkAll').prop('checked', false);
        }
    }
</script>

Give it a try.

Re: Bulk Delete for Webgrid using Checkbox

GmGregori

$(':checkbox').prop('checked', $(this).is(':checked') ? true : false );

(using jQuery 1.6+).

I was using 1.4.2. That worked. it didn't with 1.7.1 until I changed to prop.

Re: Bulk Delete for Webgrid using Checkbox

This is awesome. Thanks!!

Re: Bulk Delete for Webgrid using Checkbox

Mike, I got another problem with the Database Helper. I can not use foreach correctly. It gives me this error:

'object' does not contain a definition for 'subject' and no extension method 'subject' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)

@foreach(var row in db.QueryIn(getFromSample, messageId)){
    @row.subject<br/>
    @row.sender<br/>
}

Re: Bulk Delete for Webgrid using Checkbox

Change the extension method from my site so that it returns an IEnumerable<dynamic> instead of IEnumerable<object>.

Re: Bulk Delete for Webgrid using Checkbox

Another way to do that without any javascript;

@{
    
  var db = Database.Open("MyGrid");
  
  if(IsPost){
      
      var id = Request["RowID"];
      var arr = id.Split(',');
      var sql = "delete from Clientes where ID=@0";
      for(int i = 0; i < arr.Length; i++)
      {
         db.Execute(sql,arr[i]);
      }
  }

  var data = db.Query("Select * from Clientes");
  var grid =  new WebGrid(source:data, defaultSort:"Nome", rowsPerPage:10, ajaxUpdateContainerId:"grid");
    
}

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
    </head>
    <body>
        <form method="post" action="">
        
        
        <a href="Edit.cshtml">Criar Novo</a> &nbsp | &nbsp <input type="submit" value="Deletar"/>
        <br>

        @grid.GetHtml(
        tableStyle:"grid",
        headerStyle:"head",
        alternatingRowStyle:"alt",
        columns: grid.Columns(
            grid.Column("id", "Id", style:"tdId"),
            grid.Column("Nome", "Nome", style: "tdStyle"),
            grid.Column("CNPJ", "CNPJ", style: "tdStyle"),
            grid.Column(header:"", format:@<input type="checkbox" name="RowId" value="@item.id"/>)))
             
            </form>
    </body>
</html>