Check/Uncheck all checkbox in gridview

The following snippet will demostrate how to check/uncheck all checkboxes in a Gridview. Using javascript will take few lines of code to accomplish the task. But using Jquery minimizes the number of lines to code to implement.

Add reference to the jquery library on the page,

<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.min.js" type="text/javascript"></script>

The script to check/uncheck the gridview checkboxes and the html code would be,

<script type="text/javascript">

function ToggleProductSelect(ctrl, chk) {
            $('#<%=gvProducts.ClientID %> :checkbox[id$=' + chk + ']').attr('checked', ctrl.checked);
        }
</script>


<div style="clear:both;">
                <asp:GridView ID="gvProducts" runat="server" AutoGenerateColumns="False"
                    DataKeyNames="ProductId" HeaderStyle-CssClass="header-column">
                    <Columns>
                        <asp:TemplateField>
                            <HeaderTemplate>
                                <input id="cbSelectAll" runat="server" type="checkbox" onclick="javascript:ToggleProductSelect(this,'cbSelect');" />
                            </HeaderTemplate>
                            <ItemTemplate>
                                <asp:CheckBox ID="cbSelect" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
                        <asp:BoundField DataField="Category" HeaderText="Category" />
                        <asp:BoundField DataField="Price" HeaderText="Price" />
                        <asp:BoundField DataField="UnitsInStock" HeaderText="Units In Stock" />
                        <asp:BoundField DataField="Supplier" HeaderText="Supplier" />
                        <asp:CommandField SelectText="View" ShowSelectButton="True" />
                    </Columns>
                    <EmptyDataTemplate>
                        <div>No Products found.</div>
                    </EmptyDataTemplate>
                    <HeaderStyle CssClass="header-column" />
                </asp:GridView>
            </div>

C#

protected void Page_Load(object sender, EventArgs e)
{
        gvProducts.DataSource = GetProducts();
        gvProducts.DataBind();
}


public DataTable GetProducts()
        {
            DataTable dtProducts = new DataTable();
            SqlDataAdapter adpProducts = new SqlDataAdapter("GetProducts", sqlcon);
            adpProducts.SelectCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                adpProducts.Fill(dtProducts);
                return dtProducts;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }

Make ajax calls using jquery to bind dropdownlist

Introduction

This article describes how to make AJAX calls using Jquery to bind databound control like dropdownlist with data from database. To Bind the data to any databound control, return data as json format.

Default.aspx

<script type="text/javascript">
        $(document).ready(function () {
            //Retrieve data and bind to dropdownlist
            $.ajax({
                url: "../Services/BookService.asmx/GetBooks",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                type: "POST",
                data: {},
                error: function (err) {
                    alert("Error:" + err.toString());
                },
                success: function (data) {
                    $("#booksList").html(data);
                    $.each(data.d, function (key, value) {
                        $("#<%=ddlBooks.ClientID %>").append($("<option></option>").val(value.ID).html(value.BookName));
                    });
                }
            });
        });
    </script>

<div>
        <asp:DropDownList ID="ddlBooks" runat="server">
        <asp:ListItem>Choose...</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="btnGetBooks" runat="server" Text="Get Books" onclick=" btnGetBooks_Click" />
    </div> 

It is important to register the client script for event validation, since we bind the dropdownlist dynamically using client script it should be registered for event validation. Otherwise, the page will raise an error on postback. Though there is a possibility to bypass this scenario by setting either,

<%@ Page EnableEventValidation="false">

or Globally in the Web.config

<pages enableEventValidation="false"></pages>

The setting above may allow hackers to inject sql commands making the application vulnerable. To overcome this problem during the override the Render method as shown below.

Default.aspx.cs

protected override void Render(HtmlTextWriter writer)
    {
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID,"1");
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "2");
        ClientScript.RegisterForEventValidation(ddlBooks.UniqueID, "3");
        base.Render(writer);
    }

App_Code/Books.cs

public class Books
{
public Books()
{

}
    public string ID { get; set; }
    public string BookName { get; set; }
    public string Author{ get; set; }
}

Don't forget to uncomment the line shown below in the .asmx file if it does not exists add one.This enables us to make asynchronous calls to the function.

[System.Web.Script.Services.ScriptService]

App_Code/BookService.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// SQL Server Connection String
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

[WebMethod]
    public List<Books> GetBooks()
    {
        DataTable dtBooks = new DataTable();
        List<Books> books = new List<Books>();
        SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
        adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
        //adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
        try
        {
            adpBooks.Fill(dtBooks);
            Books book;
            foreach (DataRow dr in dtBooks.Rows)
            {
                book = new Books();
                book.ID = dr["ID"].ToString();
                book.BookName = dr["Name"].ToString();
                book.Author = dr["Author"].ToString();
                books.Add(book);
            }
            return books;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Call server side functions using PageMethods in AJAX

This post illustrates how to call server side function using PageMethod. Enabling PageMethods property in the ScriptManager allows us to call functions directly from the server side. Therefore significant performance improvement of the page is noticed.

Create a stored procedure to get Books from the database.


CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
      ,[BookName]
  FROM [Books]
END
GO

We will start by adding ScriptManager on the page and setting the EnablePageMethods property to true.


<ajaxToolkit:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server" EnablePageMethods="true">
</ajaxToolkit:ToolkitScriptManager>

Iterate through the list of books returned by the function using loops and bind the data to controls.



Default.aspx

<script type="text/javascript">
    function getBooks() {
            var data = { ID: "1", Name: "Csharp" };
            PageMethods.getBooks(data, function (returnValue) {
                for (var i in returnValue) {
                    var pList = document.getElementById("booksList");
                    var li = document.createElement("li");
                    li.appendChild(document.createTextNode(returnValue[i].ID + " - " + returnValue[i].BookName));
                    pList.appendChild(li);
                }
            });
    }
    </script>

<div>
<h3>Using Page Methods to call server side function without web service</h3>
<input type="button" onclick="getBooks()" value="Get Books" />
<ul id="booksList">
</ul>
</div>

Add a class in the code behind or in a seperate file and write the function to query the SQL server to get books. Create a WebMethod to which will return list of Books.

Default.aspx.cs

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;


protected void Page_Load(object sender, EventArgs e)
    {

    }

[WebMethod]
    public static List<Books> getBooks(Books b)
    {
        DataTable dtBooks = new DataTable();
        dtBooks = b.GetBooks();
        List<Books> books = new List<Books>();

        Books book;
        foreach (DataRow dr in dtBooks.Rows)
        {
            book = new Books();
            book.ID = dr["ID"].ToString();
            book.BookName = dr["BookName"].ToString();
            books.Add(book);
        }
        return books;
    }


public class Books
    {
        // SQL Server Connection String
        SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

        public string ID { get; set; }
        public string BookName { get; set; }

        public DataTable GetBooks()
        {
            DataTable dtBooks = new DataTable();
            SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
            adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
            //adpBooks.SelectCommand.Parameters.AddWithValue("@BookId", bookId);
            try
            {
                adpBooks.Fill(dtBooks);
                return dtBooks;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                sqlcon.Close();
            }
        }
    }    

Export data from SQL Server to Excel in asp.net

This sample introduces how to export data from SQL Server to an Excel spreadsheet and in the next article we shall see how to generate an Excel spreadsheet with data from SQL Server.

Default page retrieves data from SQL Server using a DataTable and then exports the DataTable to an Excel 2003/2007 spreadsheet on server disk.

In the previous post we have seen how to import data from excel to sql server. Please refer the table used in the previous post for this sample. First, create a procedure to select books from the database. Then create a function in the code-behind page to return Books in a DataTable. Use StringWriter and HtmlTextWriter to iterate through the data in the DataTable. Create an object for DataGrid and Bind the DataTable to it.

Stored procedure to get Books

CREATE PROCEDURE [dbo].[GetBooks]
AS
BEGIN
SELECT [ID]
      ,[Name]
      ,[Author]
  FROM [Books]
END
GO

Default.aspx

<div>
<asp:GridView ID="gvBooks" runat="server" AutoGenerateColumns="False"
        EnableModelValidation="True">
        <Columns>
            <asp:BoundField DataField="ID" HeaderText="ID" />
            <asp:BoundField DataField="Name" HeaderText="Book Name" />
           <asp:BoundField DataField="Author" HeaderText="Author" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="btnExport" runat="server" Text="Export"
        onclick="btnExport_Click" />
    <br />
    <asp:Label ID="Label1" runat="server" Text=""></asp:Label>
</div>

Default.aspx.cs

using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;


// SQL Server Connection String
    SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        gvBooks.DataSource = GetBooks();
        gvBooks.DataBind();
    }

protected void btnExport_Click(object sender, EventArgs e)
    {
        ExportToExcel(GetBooks(), "DownloadBooksToExcel.xls");
    }

public void ExportToExcel(DataTable dt, string fileName)
    {
        if (dt.Rows.Count > 0)
        {
            StringWriter sw = new StringWriter();
            HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);
            DataGrid dg = new DataGrid();
            dg.DataSource = dt;
            dg.DataBind();

            dg.RenderControl( htmlWriter);
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
            this.EnableViewState = false;
            Response.Write(sw.ToString());
            Response.End();
        }
    }
    public DataTable GetBooks()
    {
        DataTable dtBooks = new DataTable();
        SqlDataAdapter adpBooks = new SqlDataAdapter("GetBooks", sqlcon);
        adpBooks.SelectCommand.CommandType = CommandType.StoredProcedure;
        try
        {
            adpBooks.Fill(dtBooks);
            return dtBooks;
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            sqlcon.Close();
        }
    }

Import data from Excel to SQL Server using C#.NET

Introduction

In this example  I am going to explain how to import data from Microsoft Excel to SQL Server. And, in the next blog post we will see how to export data from SQL Server back to Microsoft Excel. First, launch Visual studio and create a new project. Give it a name (ie, ImportExcel2SQL).

Create a database in the SQL Server, ExcelDataDB and create a table with the columns that matches to the columns in the Excel file.


CREATE TABLE [dbo].[Books](
[ID] [int] NOT NULL,
[BookName] [varchar](200) NOT NULL,
        [Author] [varchar](200) NOT NULL,
 CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


Drop in a couple of controls from the Toolbox on to the page, FileUpload and a Button. Re-name the Button Text and say "Import".

Default.aspx

<h1>Import data from Excel to SQL server</h1>
<asp:FileUpload ID="FileUpload1" runat="server" />
&nbsp;
<asp:Button ID="Import" runat="server" Text="Import" onclick="Import_Click" />
<br />
<asp:Label ID="Label1" runat="server" Text=""></asp:Label>

Create a temporary folder(eg: TempExcelFiles) in the root of the directory to save the excel file before being imported to the SQL Server. Delete the Excel file in the temporary folder after the data has been imported.

For Microsoft office Excel 2007, please use the following connection string,

string excelConnectionString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0",path);

and for other versions of Excel use,

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

Default.aspx.cs

using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;


SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Import_Click(object sender, EventArgs e)
    {
        string excelContentType = "application/vnd.ms-excel";
        string excel2010ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string contentType = FileUpload1.PostedFile.ContentType;
        if (FileUpload1.HasFile)
        {
            if (contentType == excelContentType || contentType == excel2010ContentType)
            {
                try
                {
                    string path = string.Concat(Server.MapPath("~/TempExcelFiles/"), FileUpload1.FileName);
                    FileUpload1.SaveAs(path);
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
                    using (OleDbConnection connection =
                                 new OleDbConnection(excelConnectionString))
                    {
                        OleDbCommand command = new OleDbCommand
                                ("Select * FROM [Sheet1$]", connection);

                        connection.Open();
                        using (DbDataReader dr = command.ExecuteReader())
                        {
                            using (SqlBulkCopy bulkCopy =
                                       new SqlBulkCopy(sqlcon))
                            {
                                bulkCopy.DestinationTableName = "Books";
                                bulkCopy.WriteToServer(dr);
                                Label1.Text = "The data has been exported successfully from Excel to SQL";
                            }
                        }
                    }
                }

                catch (Exception ex)
                {
                    Label1.Text = ex.Message;
                }
            }
        }
    }

Calendar Basics in asp.net

For all the examples shown below the System.Globalization namespace should be used. It is a class library containing calender related functions.

Get the DayNames in C#.NET

public string[] GetDayNamesList()

{

        DateTimeFormatInfo dfi = DateTimeFormatInfo.CurrentInfo;

        string[] _months = dfi.MonthNames;

        return _months;

}

Output:

Sunday

Monday

Tuesday

Wednesday

Thursday

Friday

Saturday

Get Week Number of the year.

public static int GetWeekNumber(DateTime dtDate)

    {

        CultureInfo ciCurr = CultureInfo.CurrentCulture;

        int weekNum = ciCurr.Calendar.GetWeekOfYear(dtDate, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

        return weekNum;

    }

Output:

49

Get weeks spanned by

private static int getWeeksSpannedBy(DateTime startDate, DateTime endDate)

    {

        var calendar = CultureInfo.CurrentCulture.Calendar;

        var weekRule = CultureInfo.CurrentCulture.DateTimeFormat.CalendarWeekRule;

        var firstDayOfWeek = DayOfWeek.Sunday;

        int lastWeek = calendar.GetWeekOfYear(endDate, weekRule, firstDayOfWeek);

        int firstWeek = calendar.GetWeekOfYear(startDate, weekRule, firstDayOfWeek);

        int weekDiff = lastWeek - firstWeek + 1;

        return weekDiff;

    }

Output:

4