A list of past articles, tips and tricks…

Below are a list of older articles, projects and tips/tricks on CodeProject and CodePlex ranging from ASP.Net to SSIS and some DAL work:

RSS/XML Data-Binding to Data Control with Row Limitations

  • This code will use an RSS feed as a data source for a DataGrid control. Instead of displaying all rows in the feed, row-limiting code is also used.

Keyword Matching RSS Reader Control

  • Using RSS feeds on web pages is a good way of creating dynamic content, which search engines love. The more your page changes, the more it gets crawled, and can affect your page rank.

GeoLocation by Radius Using Google Maps and .NET

  • A few years back, I helped to integrate a company’s database of national sales stores/locations with a third-party website. We’ve all seen these “find the nearest store” locator-type pages.

Performing Data Merge and Audit with SSIS

  • This article details with how to perform audits on merging data – checking for duplicates, and adding other validation rules, using SSIS. This method is much more efficient than writing validation code in VB or C#.

C# ViewState Management/Storage – Four Locations!

  • From an SEO standpoint, page size matters, as well as the location of text on your page. When creating a site with VS .Net, we end up with a ViewState variable, which in some cases, can take up a whole lot of space near the top of the page. This could, in fact, dilute the contents’ importance on a given web page. In terms of page size, some search engines only cache the first 120kb of data.

Generic Queryable Data Repository

SubSonic ORM v2.3 with SQL Server 2008 TVP Support

  • This project contains the modified base code of the SubSonic v2.3 ORM, originally written by Rob Conery, that now supports SQL Server 2008 TVP’s (table valued parameters). This modification allows you to generate your full DAL, access and manipulate data in an object-oriented fashion, and most-importantly, now gives you the ability to pass large chunks of data to your stored procedures, in the form of DataTables, rather than bulk inserts.
Advertisements

OData Using C#, MVC, WebAPI, Entity Framework, Dependency Injection (DI)/Inversion of Control (IoC) and Kendo UI

OData – Displaying Hierarchical Data with Kendo UI, MVC and a Queryable Entity Framework Repository

It is recommended that you are proficient with using .Net, dependency injection, MVC, Fiddler, and SQL Server Profiler – ie. good debugging skills, and can use the Kendo UI Grid extension for data display purposes. Otherwise, you can access the OData URL paths for viewing the raw data.

Introduction

As a part of this solution I created a generic queryable repository that I accessed Entity Framework Database First Entities through a repository and unit of work pattern: DOWNLOAD.

To modify your *.tt template to output a compatible context class, see the following article: Modifying Entity Framework for use by a Repository and Unit of Work Pattern

The starting point for this article was a requirement for a new UI POC, for which Kendo UI (http://www.kendoui.com/) was chosen. An existing SQL database was used as a data source and a RESTful OData (http://www.odata.org/) API endpoint was created for data access.

Explaining how to setup Kendo UI and what OData is are out of scope for this article.

The purpose of this article is to identify a clean-cut way of using OData and hierarchical data. One of the most commonly asked questions is, “How do I only return the rows needed from my database server, and not 100,000 records then filter?” (or variations there-of). This article will identify how to accomplish this.

We are using OData because it represents an efficient way of retrieving data, as requested directly from the client, without having to write specific associated controller action methods for things like paging and filtering.

For reads, the client sends query parameters that are received by a single Get() method. The query options are then handled on the server, and can be passed directly through to SQL Server (in this case, through Entity Framework).

All CRUD operations are quite simple through OData as well, but are out of scope of this article (other than Reads).

Note:  If you don’t want to use Dependency Injection, and the generic repository included – simply use Entity Framework directly, I include a controller action method at the end of this article that allows you to do this.

Background

The basis for any data-driven application is the DAL, and it’s important to know how the data is accessed and make sure it is done efficiently, as such, I have worked on several projects including rewriting a popular ORM to make use of SQL Server’s TVP’s (http://subsonic23tvp.codeplex.com/).

I am always cautious when controls automatically “do things”, especially when it comes to data access.

I initially setup a Kendo UI Grid with a details grid containing users. Everything looked fine – I expanded a group, and was able to navigate through a list of associated users.

In using the browser console and SQL Server Profiler, I noticed that all users were being returned from SQL Server, then filtering occurring before it reached the client.

In another attempt, I noticed that all records were returned to the client, an the control was paging through the entire data set.

What I wanted to see was that SQL Server was only returning, say 10 records at a time and applying the filtering options.

For this article I am using and making reference to two tables USERS and USERGROUPS. Use this article as a reference for your own data sets.

I typically put a lot of comments in my code to help others understand exactly what’s going on. These comments usually include some alternate examples and helpful URLs for additional background details.

Not everything is described in the text of this article, and most-likely some of your questions will be answered by actually reading through the code itself – highly recommend it.

Solution Setup

The ATMS.DAL project contains the auto-generated classes through Entity Framework Database First.

Here is what the basic solution structure looks like:

odata-solution1

Next, we see the differences between our two sets of control classes – we have two sets:

odata-solution2

We will be using Entity Framework Database First to generate our database entities. However, for testability, we will first use a generic repository pattern and dependency injection / IoC to access our entities through EF.

Explaining how this works and why it is important is out of scope of this article, but I have included the Repository project as a part of the article’s code. The Repository uses generics and as such is not tightly-coupled on either end. Use freely as needed.

The repository project can be added to any .Net solution and referenced in your associated projects.

Simple Injector (http://simpleinjector.codeplex.com/) was used for our DI container, which can be added through a Nuget package to your solution.