DotNetSlackers Latest ASP.NET News in Category DataSet

jQuery DataTables in Java Web Applications

The Code Project Latest Articles — Thu, 26 Apr 2012 00:00:00 GMT

Enhancing simple tables implemented in Java web applications using the jQuery DataTables plug-in....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

JQuery Data Tables in Java Web Applications

The Code Project Latest Articles — Sat, 07 Apr 2012 00:00:00 GMT

Enhancing simple tables implemented in java web applications using the JQuery DataTables plugin...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

jQuery DataTables Advanced Filtering in ASP.NET MVC (jQuery DataTables and ASP.NET MVC Integration - Part V)

The Code Project Latest Articles — Thu, 05 Apr 2012 00:00:00 GMT

How to implement advanced column filtering using jQuery DataTables....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Pivoting DataTable Simplified

The Code Project Latest Articles — Mon, 02 Apr 2012 00:00:00 GMT

A class to pivot a DataTable with various aggregate functions....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

JQuery DataTables Advanced Filtering in ASP.NET MVC (jQuery DataTables and ASP.NET MVC Integration - Part V)

The Code Project Latest Articles — Tue, 13 Mar 2012 00:00:00 GMT

Description how to implement advanced column filtering using JQuery DataTables...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Let’s Build A Module–Adding Data

Joe Brinkman — Mon, 12 Mar 2012 00:00:00 GMT

Continuing our look at Data using the Entity Framework, WCF Data Service and Knockout.js, we see how to Add data to your tables. Well take a look at how to use the Entity Framework/WCF Data Service/Knockout.js components to build a form and add data to our module tables....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Getting Started with KendoUI and OpenAccess ORM

the telerik blogs — Thu, 09 Feb 2012 00:00:00 GMT

As many of you may know, KendoUI is a really awesome new JavaScript framework that combines everything needed for modern JS development into one sweet package. One of the features it provides is the ability to bind different UI controls to various data sources; so in this blog I will show you how OpenAccess ORM can be leveraged on the backend, and KendoUI can take care of the front end!

Getting Set Up

To get started create a new ASP.Net Empty Web Application.

Note: You can really use any web based project template you like, in this walkthrough I just chose to start with nothing. :)

Once the project loads, fire up the NuGet Package Manager Console.

Add JQuery to the project using the command:

Install-Package jQuery

Add KendoUI to the project using the command:

Install-Package KendoUIWeb

Once you run both of these NuGet commands, the solution explorer should look like this:

Now we are ready to build the OpenAccess ORM model!

Note: If you are not familiar with NuGet, or would rather just download, and add the needed files manually. KendoUI is available here, and Jquery is available here.

Building the model

To build an OpenAccess ORM data model, right click on the web project, navigate to Add > New Item, then select Telerik OpenAccess Domain Model.

Note: If you do not have OpenAccess ORM installed, you can get the trial edition here.

Make sure to give the model a name, and then click Add.

In the next window select Populate from Database, and click Next.

Set up a connection to NorthwindOA, which is installed with OpenAccess, and then click next.

At this point go ahead and include all tables from the database in the data model.

Click finish, and step 2 is done! The data model is ready to be used. The next step is to expose this data model through a service layer.

Building the Service Layer

Prepare yourself - this is the most difficult part! Im kiddingPerform a build on the application, and right click on the .rlinq file in the solution explorer. Select Generate OpenAccess Domain Model Service, and the domain service wizard will fire up.

Note: please ensure you build the project before you launch the wizard. If you do not do this, you will not be able to see the domain model in the service wizard as shown below.

Select the NorthwindOA context in the wizard, and click Next.

This screen allows you to configure the service that will be created. Give the service a name, select the WCF Data Services V2 tab, and then click Finish.

In about 3 clicks we created an OData service which exposes the domain model, sweet! :)

Connecting the OData Service to a Kendo Grid

Now all that is left to do is configure the front end. For now we will wire up the Orders to a KendoUI grid, but feel free to take it even further! KendoUI has a lot of cool features to play around with :)

First thing we need to do is add a new html page to the project. So once again, right click on the project in the solution explorer, select Add > New Item, only this time choose HTML Page (its in the list I promise, though I have overlooked it for a loong time!), name the page default.htm, and then click Add.

Now we need to add the needed Kendo files, so add the following lines in the head section:

Next, we need to set up the grid. KendoUIs web site provides many great examples of how to configure binding; so I wont go into too much detail here. The basic idea is that we need a div that will be used as the container for the KendoUI grid, and then we just have to configure the grid in script.

The code needed is here:

Now run the application, and navigate to default.htm. If all went well you should be presented with a great looking empty grid:

So what gives?! Why is there no data in the grid? Well if you take a look at firebug/chrome tools you should see something like:

Failed to load resource: the server responded with a status of 400 (Bad Request)

This error by itself is not very helpful, but if you navigate to the page it tried to retrieve, in my case:

http://localhost:11224/NorthwindService.svc/Orders?$format=json&$inlinecount=allpages&$callback=callback&$top=10

You will be greeted with this error:

The query parameter '$format' begins with a system-reserved '$' character but is not recognized.

We Need JSON

KendoUI uses JSON to communicate with an OData service; but WCF Data Services does not support JSON out of the box. To enable support for JSON, you can download the needed code from the Microsoft Code Gallery.

Once you have added the needed code to the project, and you add the needed reference to System.Runtime.Serialization.dll, navigate to NorthwindService.svc.cs (Your name might be different).

In the service add the JSONPSupportBehavior attribute:

Run the application one more time, and this time, you should be greeted by a great looking populated grid :)

Conclusions

The OpenAccess ORM Visual Designer, and Service Layer Generator make it extremely easy to get started working with KendoUI. In fact, we only had to write 1 line of server side code thanks to these great tools!

You can see complete code sample in this gist. In addition, this example will be provided in the upcoming release of the OpenAccess ORM SDK, which will be release right along side Q1 2012! Make sure to register for the webinars so you dont miss all the great new things we have to show you!

Its time for all .NET Ninjas to sharpen their skills! The latest Telerik release is just around the corner and we have tons of new stuff to show off. If you are eager to see the new bits and sharpen your .NET skills, be sure to sign up for Release Webinar Week. This 3-day event is packed with hour-long webinar sessions on the coolest new features shipping with the Q1 2012 release. Release Webinar Week will be held on February 20 22, so mark your calendars. One lucky winner from each webinar will leave with a Telerik Ultimate Collection license worth $1999. To enter the drawing and participate in the Q&A session, you must attend the live webinar.

Webinar Schedule and Registration Link: http://www.telerik.com/support/webinars.aspx

Happy Coding!

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

A Web Recipe: Modal dialog and grid for child entities

Clarity Blogs: ASP.NET — Fri, 03 Feb 2012 00:00:00 GMT

This post is the forth in the series of posts described here. So far we’ve put a data model and data access layer in place use EF Code First, and built out a snappy Index view that uses the jquery DataTables plugin to load our grid of beers. The next thing to explore is working [...]

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

A Web Recipe: Modal dialog and grid for child entities

Clarity Blogs: ASP.NET — Fri, 03 Feb 2012 00:00:00 GMT

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Let’s Build a Module #4–Data!

Joe Brinkman — Thu, 02 Feb 2012 00:00:00 GMT

In this next part of the series, well take a look at how the combination of Entity Framework and WCF Data Services gives you an awesome data access layer in no time flat, Quick, Easy and Powerful....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

5 Minutes with Data Binding and JavaScript using Knockout and JsViews

JohnPapa.net — Wed, 01 Feb 2012 00:00:00 GMT

For years when developers heard the term data source control it sent shivers down spines. It often referred to some tightly coupled object that managed binding the source data to the target controls and took you about 80% of the way t your goal. But it also often left you at the middle of the desert heat with nothing but a pair of sunglasses and a and a long way to go by foot to your destination as it drove off. Thankfully, data sources and data binding have evolved considerably over the years and...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Adding Multilanguage Support To Your Objects

The Code Project Latest Articles — Wed, 21 Dec 2011 00:00:00 GMT

In todays world localization became a must requirement in applications that we develop. We can easily achieve this goal by using recource files for static resources such as exception messages, label texts etc. However using localized data fetched from a data source will be a problem. Since the data...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Export any page to Excel automatically

The Code Project Latest Articles — Wed, 14 Dec 2011 00:00:00 GMT

DataTable people = (DataTable)Session["people"];ExcelFile ef = new ExcelFile();ExcelWorksheet ws = ef.Worksheets.Add("DataSheet");ws.InsertDataTable(people, "A1", true); Response.Clear(); switch (this.RadioButtonList1.SelectedValue){ case "XLS": ......

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Extending DataTables Range Filter

The Code Project Latest Articles — Fri, 28 Oct 2011 00:00:00 GMT

DataTables plugin extension to include range filter...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

How to use The JQuery UI Autocomplete widget which enables users to quickly find and select from a pre-populated list of values as they type, leveraging searching and filtering.

EggHeadCafe.com New Articles — Thu, 27 Oct 2011 00:00:00 GMT

By giving an Autocomplete field focus or entering something into it, the plugin starts searching for entries that match and displays a list of values to choose from. By entering more characters, the user can filter down the list to better matches. This article illustrates the use of Autocomplete with an ASHX Webhandler data source. read more

By Peter Bromberg

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

System Center Data Protection Manager 2012 Now in Beta

Microsoft Certified Professional Magazine Online | Todays Top Picks — Fri, 02 Sep 2011 00:00:00 GMT

The DPM 2012 beta includes a new centralized console and backup and recovery of generic data sources, among others....

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Delete Functionality in GridView with Confirmation using JQuery UI Dialog

The Code Project Latest Articles — Mon, 08 Aug 2011 00:00:00 GMT

You're using a GridView web control to list records from a particular data source and you want a delete functionality for each row of data. A dialog must be presented to the user to confirm deletion. There's also a possibility of error when deleting a record from your data source and you also want t...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Client-side paging with jQuery

The Code Project Latest Articles — Mon, 25 Jul 2011 00:00:00 GMT

A mash-up of sorts built using Microsoft's jQuery Template plugin along with G Birke's jQuery Pagination plugin & a photo-stream from Flickr acting as a data source...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Entity Framework/WCF Data Services June 2011 CTP: Derived Entities with Related Data

Julia Lerman Blog - Dont Be Iffy... — Sat, 02 Jul 2011 00:00:00 GMT

Well, this change really came in the March 2011 CTP but I didn’t realize it until the June CTP was out, so I’ll call it a wash. WCF Data Services has had a bad problem with inherited types where the derived type had a relationship to yet another type. For example, in this model where TPTTableA is a derived type (from Base)and has related data (BaseChildren). If you expose just the Bases EntitySet (along with its derived type) in a WCF Data service, that was fine. I can browse...

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

Export an ADO.NET DataTable to Excel using NPOI

Scott on Writing — Tue, 07 Jun 2011 00:00:00 GMT

My latest article on DotNetSlackers looks at how to create Excel spreadsheets using NPOI. NPOI is a free, open-source .NET library for creating and reading Excel spreadsheets and is a port of the Java POI library. In the article I show how to use NPOI to programmatically export data into a spreadsheet with multiple sheets, formatting, and so on. Specifically, my demos look at having a set of objects to export for example, a set of Linq-to-Sql entity objects and then crafting an Excel spreadsheet by enumerating those objects and adding applicable rows and columns to the spreadsheet.

Recently, I needed the ability to allow for more generic exports to Excel. In one of the web applications I work on there is an Excel Export page that offers a number of links that, when clicked, populate an ADO.NET DataTable with the results of a particular database view, generate a CSV file, and then stream that file down to the client with a Content-Type of application/vnd.ms-excel, which prompts the browser to display the CSV content in Excel. This has worked well enough over the years, but unfortunately such data cannot be viewed from the iPad; however, the iPad can display a native Excel file (.xls). The solution, then, was to update the code to use NPOI to return an actual Excel spreadsheet rather than a CSV file.

To accomplish this I wrote a bit of code that exports the contents of any ol DataTable into an Excel spreadsheet using NPOI. Its pretty straightforward, looping through the rows of the DataTable and adding each as a row to the Excel spreadsheet. There were, however, a couple of gotcha points:

Excel 2003 limits a sheet inside a workbook to a maximum of 65,535 rows. To export more rows than this you need to use multiple sheets. Zach Hunters blog entry, NPOI and the Excel 2003 Row Limit, provided a simple approach to avoiding this problem. In short, I keep track of how many rows Ive added to the current sheet and once it exceeds a certain threshold I create a new sheet and start from the top.
Excel has limits and restrictions on the length of sheet names and what characters can appear in a sheet name. I have a method named EscapeSheetName that ensures the sheet name is of a valid length and does not contain any offending characters.
When exporting very large Excel spreadsheets you may bump into OutOfMemoryExceptions if you are developing on a 32-bit system and are trying to dump the Excel spreadsheet into a MemoryStream object, which is a common technique for streaming the data to the client. See this Stackoverflow discussion for more information and possible workarounds: OutOfMemoryException When Generating a Large Excel Spreadsheet.

To demonstrate exporting a DataTable to Excel using NPOI, I augmented the code demo available for download from my DotNetSlackers article to include a new class in the App_Code folder named NPoiExport, which you can download from http://scottonwriting.net/demos/ExcelExportToDataTable.zip. This class offers an ExportDataTableToWorkbook method that takes as input a DataTable and the sheet name to use for the Excel workbook. (If there are multiple sheets needed, the second sheet is named sheetName 2, the third, sheetName 3, and so forth.)

The ExportDataTableToWorkbook method follows:

public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)
{
    // Create the header row cell style
    var headerLabelCellStyle = this.Workbook.CreateCellStyle();
    headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
    var headerLabelFont = this.Workbook.CreateFont();
    headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
    headerLabelCellStyle.SetFont(headerLabelFont);

    var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);
    var currentNPOIRowIndex = 1;
    var sheetCount = 1;

    for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
    {
        if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
        {
            sheetCount++;
            currentNPOIRowIndex = 1;

            sheet = CreateExportDataTableSheetAndHeaderRow(exportData, 
                                                            sheetName + " - " + sheetCount, 
                                                            headerLabelCellStyle);
        }

        var row = sheet.CreateRow(currentNPOIRowIndex++);

        for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
        {
            var cell = row.CreateCell(colIndex);
            cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
        }
    }
}

Whenever a new sheet needs to be generated either when starting or when the maximum number of rows per sheet is exceeded the CreateExportDataTableSheetAndHeaderRow method is called. This method creates a header row, listing the name of each column in the DataTable.

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, CellStyle headerRowStyle)
{
    var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

    // Create the header row
    var row = sheet.CreateRow(0);

    for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
    {
        var cell = row.CreateCell(colIndex);
        cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

        if (headerRowStyle != null)
            cell.CellStyle = headerRowStyle;
    }

    return sheet;
}

Heres how you would go about using the NpoiExport class to export a DataTable and then stream it down to the client:

Create and populate the DataTable with the data to export. Remember, the DataTables column names are what will appear in the header row so use aliases in the SQL query to provide more description/formatted column names, if you prefer.
Create an instance of the NpoiExport class.
Call the objects ExportDataTableToWorkbook method passing in the DataTable from step 1 (along with a sheet name of your choice).
Set the Content-Type and Content-Disposition response headers appropriately and then stream down the contents of the Excel document, which is accessible via the NpoiExport objects GetBytes method.

The following code snippet illustrates the above four steps.

// Populate the DataTable
var myDataTable = new DataTable();
using (var myConnection = new SqlConnection(connectionString)
{
    using (var myCommand = new SqlCommand())
    {
        myCommand.Connection = myConnection;
        myCommand.CommandText = sqlQuery;

        using (var myAdapter = new SqlDataAdapter(myCommand))
        {
            myAdapter.Fill(myDataTable);
        }
    }
}


// Creat the NpoiExport object
using (var exporter = new NpoiExport())
{
    exporter.ExportDataTableToWorkbook(myDataTable, "Results");

    string saveAsFileName = string.Format("Results-{0:d}.xls", DateTime.Now);

    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
    Response.Clear();
    Response.BinaryWrite(exporter.GetBytes());
    Response.End();
}

Thats all there is to it. The screen shot below shows an example of the exported report. Note that it lacks the nice formatting, auto-sized columns, and other bells and whistles that are possible with NPOI when constructing a report by hand (as I showed in Create Excel Spreadsheets Using NPOI), but it does make exporting data to Excel an exercise of just a few lines of code. And its exported data that can be opened and viewed from an iPad.

The above Excel spreadsheet was created using the ad-hoc query:

SELECT CategoryName AS [Category], 
       Description, 
       (SELECT COUNT(*) 
        FROM Products 
        WHERE Products.CategoryID = Categories.CategoryID) 
            AS [Product Count]
FROM Categories
WHERE CategoryID >= 3

Happy Programming!

Download: http://scottonwriting.net/demos/ExcelExportToDataTable.zip

Did you know that DotNetSlackers also publishes .net articles written by top known .net Authors? We already have over 80 articles in several categories including Silverlight. Take a look: here.

DotNetSlackers Latest ASP.NET News in Category DataSet

jQuery DataTables in Java Web Applications

JQuery Data Tables in Java Web Applications

jQuery DataTables Advanced Filtering in ASP.NET MVC (jQuery DataTables and ASP.NET MVC Integration - Part V)

Pivoting DataTable Simplified

JQuery DataTables Advanced Filtering in ASP.NET MVC (jQuery DataTables and ASP.NET MVC Integration - Part V)

Let&rsquo;s Build A Module&ndash;Adding Data

Getting Started with KendoUI and OpenAccess ORM

Getting Set Up

Building the model

Building the Service Layer

Connecting the OData Service to a Kendo Grid

We Need JSON

Conclusions

A Web Recipe: Modal dialog and grid for child entities

A Web Recipe: Modal dialog and grid for child entities

Let&rsquo;s Build a Module #4&ndash;Data!

5 Minutes with Data Binding and JavaScript using Knockout and JsViews

Adding Multilanguage Support To Your Objects

Export any page to Excel automatically

Extending DataTables Range Filter

How to use The JQuery UI Autocomplete widget which enables users to quickly find and select from a pre-populated list of values as they type, leveraging searching and filtering.

System Center Data Protection Manager 2012 Now in Beta

Delete Functionality in GridView with Confirmation using JQuery UI Dialog

Client-side paging with jQuery

Entity Framework/WCF Data Services June 2011 CTP: Derived Entities with Related Data

Export an ADO.NET DataTable to Excel using NPOI

Let’s Build A Module–Adding Data

Let’s Build a Module #4–Data!