Sorted By: Tag (ms-sql)

Clean, Better, and Sexier Generic Repository Implementation for Entity Framework

With the new release of the GenericRepository.EntityFramework package, we now have clean, better and sexier generic repository implementation for Entity Framework. Enjoy!
2013-01-10 13:00
Tugberk Ugurlu


I have written a few blog posts about my experience on applying the Generic Repository pattern to Entity Framework and I even made a NuGet package for my naïve implementation. Even if that looked OK at the time for me, I had troubles about my implementation for couple of reasons:

  • The implementations inside the NuGet package didn’t allow the developer to share the DbContext instance between repositories (per-request for example).
  • When the generic repository methods weren’t enough, I was creating new repository interfaces and classes based on the generic ones. This was the biggest failure for me and didn’t scale very well as you can imagine.
  • There were no pagination support.
  • As each repository take a direct dependency on DbContext and it is impossible to entirely fake the DbContext, generic repositories needed to be mocked so that we could test with them. However, it would be just very useful to pass a fake DbContext instance into the repository implementation itself and use it as fake.

With the new release of the GenericRepository.EntityFramework package, the all of the above problems have their solutions. The source code for this release is available under the master branch of the repository and you can also see the ongoing work for the final release under the v0.3.0 branch. The NuGet package is available as pre-release for now. So, you need to use the –pre switch to install it.

PM> Install-Package GenericRepository.EntityFramework -Pre

The old GenericRepository.EF package is still around and I will update it, too but it’s now unlisted and only thing it does is to install the GenericRepository.EntityFramework package.

I also included a sample application which shows the usage briefly. I will complete the sample and extend it further for a better view. Definitely check this out!

Let’s dive right in and see what is new and cool.

IEntity and IEntity<TId> Interfaces

I introduced two new interfaces: IEntity and IEntity<TId> and each of your entity classes needs to implement one of these. As you can see from the implementation, IEntity just implements the IEntity<int> and you can use IEntity if you are using integer based Ids. The reason why I added these is make the GetSingle method work.

Use EntitiesContext Instead of DbContext

Instead of deriving your context class from DbContext, you now need to take the EntitiesContext as the base class for your context. If you have an existing context class based on DbContext, changing it to use EntitiesContext should not break it. The EntitiesContext class has all the same constructors as DbContext. So, you can also use those. Here is the sample:

public class AccommodationEntities : EntitiesContext {

    // NOTE: You have the same constructors as the DbContext here. E.g:
    // public AccommodationEntities() : base("nameOrConnectionString") { }

    public IDbSet<Country> Countries { get; set; }
    public IDbSet<Resort> Resorts { get; set; }
    public IDbSet<Hotel> Hotels { get; set; }
}

Then, through your IoC container, you can register your context as a new instance for IEntitiesContext per a particular scope. The below example uses Autofac to do that for an ASP.NET Web API application:

private static void RegisterDependencies(HttpConfiguration config) {

    var builder = new ContainerBuilder();
    builder.RegisterApiControllers(Assembly.GetExecutingAssembly());

    // Register IEntitiesContext
    builder.Register(_ => new AccommodationEntities())
           .As<IEntitiesContext>().InstancePerApiRequest();

    // TODO: Register repositories here

    config.DependencyResolver = 
        new AutofacWebApiDependencyResolver(builder.Build());
}

IEntityRepository<TEntity> and EntityRepository<TEntity>

Here is the real meat of the package: IEntityRepository and EntityRepository. Same as the IEntity and IEntity<TId>, we have two different IEntityRepository generic interfaces: IEntityRepository<TEntity> and IEntityRepository<TEntity, TId>. They have their implementations under the same generic signature: EntityRepository<TEntity> and EntityRepository<TEntity, TId>. The big improvement now is that EntityRepository generic repository implementation accepts an IEntitiesContext implementation through its constructor. This, for example, enables you to use the same DbContext (IEntitiesContext implementation in our case, which is EntitiesContext by default) instance per-request for your ASP.NET MVC, ASP.NET Web API application and share that across your repositories. Note: don’t ever use singleton DbContext instance throughout your AppDomain. DbContext is not thread safe.

As we have registered our EntitiesContext instance per request above, we can now register the repositories as well. As our repositories accepts an IEntitiesContext implementation through their constructor, our IoC container will use our previous registration for that automatically. Autofac has this ability as nearly all IoC containers do.

private static void RegisterDependencies(HttpConfiguration config) {

    var builder = new ContainerBuilder();
    builder.RegisterApiControllers(Assembly.GetExecutingAssembly());

    // Register IEntitiesContext
    builder.Register(_ => new AccommodationEntities())
           .As<IEntitiesContext>().InstancePerApiRequest();

    // TODO: Register repositories here
    builder.RegisterType<EntityRepository<Country>>()
           .As<IEntityRepository<Country>>().InstancePerApiRequest();
    builder.RegisterType<EntityRepository<Resort>>()
           .As<IEntityRepository<Resort>>().InstancePerApiRequest();
    builder.RegisterType<EntityRepository<Hotel>>()
           .As<IEntityRepository<Hotel>>().InstancePerApiRequest();

    config.DependencyResolver = 
        new AutofacWebApiDependencyResolver(builder.Build());
}

Out of the Box Pagination Support

Best feature with this release is out of the box pagination support with generic repository instances. It doesn’t perform the pagination in-memory; it queries the database accordingly and gets only the parts which are needed which is the whole point Smile Here is an ASP.NET Web API controller which uses the pagination support comes with the EntityRepository:

public class CountriesController : ApiController {

    private readonly IEntityRepository<Country> _countryRepository;
    private readonly IMappingEngine _mapper;
    public CountriesController(
        IEntityRepository<Country> countryRepository, 
        IMappingEngine mapper) {

        _countryRepository = countryRepository;
        _mapper = mapper;
    }

    // GET api/countries?pageindex=1&pagesize=5
    public PaginatedDto<CountryDto> GetCountries(int pageIndex, int pageSize) {

        PaginatedList<Country> countries = 
             _countryRepository.Paginate(pageIndex, pageSize);

        PaginatedDto<CountryDto> countryPaginatedDto = 
           _mapper.Map<PaginatedList<Country>, PaginatedDto<CountryDto>>(countries);

        return countryPaginatedDto;
    }
}

public interface IPaginatedDto<out TDto> where TDto : IDto {

    int PageIndex { get; set; }
    int PageSize { get; set; }
    int TotalCount { get; set; }
    int TotalPageCount { get; set; }

    bool HasNextPage { get; set; }
    bool HasPreviousPage { get; set; }

    IEnumerable<TDto> Items { get; }
}

public class PaginatedDto<TDto> : IPaginatedDto<TDto> where TDto : IDto {

    public int PageIndex { get; set; }
    public int PageSize { get; set; }
    public int TotalCount { get; set; }
    public int TotalPageCount { get; set; }

    public bool HasNextPage { get; set; }
    public bool HasPreviousPage { get; set; }

    public IEnumerable<TDto> Items { get; set; }
}

Paginate method will return us the PaginatedList<TEntity> object back and we can project that into our own Dto object as you can see above. I used AutoMapper for that. If I send a request to this API endpoint and ask for response in JSON format, I get back the below result:

{
    "PageIndex":1,
    "PageSize":2,
    "TotalCount":6,
    "TotalPageCount":3,
    "HasNextPage":true,
    "HasPreviousPage":false,
    "Items":[
      {
        "Id":1,
        "Name":"Turkey",
        "ISOCode":"TR",
        "CreatedOn":"2013-01-08T21:12:26.5854461+02:00"
      },
      {
        "Id":2,
        "Name":"United Kingdom",
        "ISOCode":"UK",
        "CreatedOn":"2013-01-08T21:12:26.5864465+02:00"
      }
    ]
}

Isn’t this perfect Smile There are other pagination method inside the EntityRepository implementation which supports including child or parent entities and sorting. You also have the ToPaginatedList extension method and you can build your query and call ToPaginatedList on that query to get PaginatedList<TEntity> object back.

Extending the IEntityRepository<TEntity>

In my previous blog posts, I kind of sucked at extending the generic repository. So, I wanted to show here the better approach that I have been taking for a while now. This is not a feature of my generic repository, this is the feature of .NET itself: extension methods! If you need extra methods for your specific repository, you can always extend the IEntityRepository<TEntity, TId> which gives you a better way to extend your repositories. Here is an example:

public static class HotelRepositoryExtensions {

    public static IQueryable<Hotel> GetAllByResortId(
        this IEntityRepository<Hotel, int> hotelRepository, int resortId) {

        return hotelRepository.FindBy(x => x.ResortId == resortId);
    }
}

What is Next?

My first intention is finish writing all the tests for the whole project, fix bugs and inconsistencies for the v0.3.0 release. After that release, I will work on EF6 version for my generic repository implementation which will have sweet asynchronous support. I also plan to release a generic repository implementation for MongoDB.

Stay tuned, install the package, play with it and give feedback Winking smile

Asynchronous Database Calls With Task-based Asynchronous Programming Model (TAP) in ASP.NET MVC 4

Asynchronous Database Calls With Task-based Asynchronous Programming Model (TAP) in ASP.NET MVC 4 and its performance impacts.
2012-04-09 11:23
Tugberk Ugurlu


You have probably seen some people who are against asynchronous database calls in ASP.NET web applications so far. They mostly right but there are still some cases that processing database queries asynchronous has very important impact on.

If you are unfamiliar with asynchronous programming model on ASP.NET MVC 4, you might want to read one of my previous posts: Asynchronous Programming in C# 5.0 and ASP.NET MVC Web Applications.

Update on the 11th of April, 2012:

@BradWilson has just started a new blog post series on using Task Parallel Library when writing server applications, especially ASP.NET MVC and ASP.NET Web API applications. You should certainly check them out: Task Parallel Library and Servers, Part 1: Introduction

Update on the 1st of July, 2012:

@RickAndMSFT has a new tutorial on Using Asynchronous Methods in ASP.NET MVC 4. Definitely check that out.

One of the reasons why asynchronous programming is not recommended for database calls is that it is extremely hard to get it right, even if we adopt Task-based Asynchronous Programming in .NET 4.0. But, with the new async / await features of C# 5.0, it is easier and still complex at the same time.

When you call a method which returns Task or Task<T> for some T and await on that, the compiler does the heavy lifting by assigning continuations, handling exceptions and so on. Because of this fact, it adds a little overhead and you might notice this when you are dealing with short running operations. At that point, asynchrony will do more harm than good to your application.

Let’s assume we have a SQL Server database out there somewhere and we want to query against that database in order to get the cars list that we have. I have created a class which will do the query operations and hand us the results as C# CLR objects.

public class GalleryContext : IGalleryContext {

    private readonly string selectStatement = "SELECT * FROM Cars";

    public IEnumerable<Car> GetCars() {

        var connectionString = 
            ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;

        using (var conn = new SqlConnection(connectionString)) {
            using (var cmd = new SqlCommand()) {

                cmd.Connection = conn;
                cmd.CommandText = selectStatement;
                cmd.CommandType = CommandType.Text;

                conn.Open();

                using (var reader = cmd.ExecuteReader()) {

                    return reader.Select(r => carBuilder(r)).ToList();
                }
            }
        }
    }

    public async Task<IEnumerable<Car>> GetCarsAsync() {

        var connectionString = 
            ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;
            
        var asyncConnectionString = new SqlConnectionStringBuilder(connectionString) {
            AsynchronousProcessing = true
        }.ToString();

        using (var conn = new SqlConnection(asyncConnectionString)) {
            using (var cmd = new SqlCommand()) {

                cmd.Connection = conn;
                cmd.CommandText = selectStatement;
                cmd.CommandType = CommandType.Text;

                conn.Open();

                using (var reader = await cmd.ExecuteReaderAsync()) {

                    return reader.Select(r => carBuilder(r)).ToList();
                }
            }
        }
    }

    //private helpers
    private Car carBuilder(SqlDataReader reader) {

        return new Car {

            Id = int.Parse(reader["Id"].ToString()),
            Make = reader["Make"] is DBNull ? null : reader["Make"].ToString(),
            Model = reader["Model"] is DBNull ? null : reader["Model"].ToString(),
            Year = int.Parse(reader["Year"].ToString()),
            Doors = int.Parse(reader["Doors"].ToString()),
            Colour = reader["Colour"] is DBNull ? null : reader["Colour"].ToString(),
            Price = float.Parse(reader["Price"].ToString()),
            Mileage = int.Parse(reader["Mileage"].ToString())
        };
    }
}

You might notice that I used a Select method on SqlDataReader which does not exist. It is a small extension method which makes it look prettier.

public static class Extensions {

    public static IEnumerable<T> Select<T>(
        this SqlDataReader reader, Func<SqlDataReader, T> projection) {

        while (reader.Read()) {
            yield return projection(reader);
        }
    }
}

As you can see, it has two public methods to query the database which does the same thing but one of them doing it as synchronously and the other one as asynchronously.

Inside the GetCarsAsync method, you can see that we append the AsynchronousProcessing property and set it to true in order to run the operation asynchronously. Otherwise, no matter how you implement it, your query will be processed synchronously.

When you look behind the curtain, you will notice that ExecuteReaderAsync method is really using the old Asynchronous Programming Model (APM) under the covers.

public static Task<SqlDataReader> ExecuteReaderAsync(this SqlCommand source)
{
    return Task<SqlDataReader>.Factory.FromAsync(
        new Func<AsyncCallback, object, IAsyncResult>(source.BeginExecuteReader), 
        new Func<IAsyncResult, SqlDataReader>(source.EndExecuteReader), 
        null
    );
}

When we try to consume these methods inside our controller, we will have the following implementation.

public class HomeController : Controller {

    private readonly GalleryContext ctx = new GalleryContext();

    public ViewResult Index() {

        return View(ctx.GetCars());
    }

    public async Task<ViewResult> IndexAsync() {

        //workaround: http://aspnetwebstack.codeplex.com/workitem/22
        await TaskEx.Yield();

        return View("Index", await ctx.GetCarsAsync());
    }
}

Now, when we hit /home/Index, we will be querying our database as synchronously. If we navigate to /home/IndexAsync, we will be doing the same thing but asynchronously this time. Let’s do a little benchmarking with Apache HTTP server benchmarking tool.

First, we will simulate 50 concurrent requests on synchronously running wev page:

ab_syncdb_short_1

Let’s do the same thing for asynchronous one:

ab_asyncdb_short_1

Did you notice? We have nearly got the same result. In fact, you will see that synchronous version of the operation completes faster than the asynchronous one at some points. The reason is that the SQL query takes small amount of time (approx. 8ms) here to complete.

Let’s take another scenario. Now, we will try to get the same data through a Stored Procedure but this time, the database call will be slow (approx. 1 second). Here are two methods which will nearly the same as others:

private readonly string spName = "sp$GetCars";

public IEnumerable<Car> GetCarsViaSP() {

    var connectionString = ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;

    using (var conn = new SqlConnection(connectionString)) {
        using (var cmd = new SqlCommand()) {

            cmd.Connection = conn;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            using (var reader = cmd.ExecuteReader()) {

                return reader.Select(r => carBuilder(r)).ToList();
            }
        }
    }
}

public async Task<IEnumerable<Car>> GetCarsViaSPAsync() {

    var connectionString = ConfigurationManager.ConnectionStrings["CarGalleryConnStr"].ConnectionString;
    var asyncConnectionString = new SqlConnectionStringBuilder(connectionString) {
        AsynchronousProcessing = true
    }.ToString();

    using (var conn = new SqlConnection(asyncConnectionString)) {
        using (var cmd = new SqlCommand()) {

            cmd.Connection = conn;
            cmd.CommandText = spName;
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();

            using (var reader = await cmd.ExecuteReaderAsync()) {

                return reader.Select(r => carBuilder(r)).ToList();
            }
        }
    }
}

I was able to make the SQL query long running by waiting inside the stored procedure for 1 second:

ALTER PROCEDURE dbo.sp$GetCars
AS

-- wait for 1 second
WAITFOR DELAY '00:00:01';

SELECT * FROM Cars;

Controller actions are nearly the same as before:

public class HomeController : Controller {

    private readonly GalleryContext ctx = new GalleryContext();

    public ViewResult IndexSP() {

        return View("Index", ctx.GetCarsViaSP());
    }

    public async Task<ViewResult> IndexSPAsync() {

        //workaround: http://aspnetwebstack.codeplex.com/workitem/22
        await TaskEx.Yield();

        return View("Index", await ctx.GetCarsViaSPAsync());
    }
}

Let’s simulate 50 concurrent requests on synchronously running web page first:

ab_syncdb_long_1

Ok, that’s apparently not good. As you might see, some requests take more than 8 seconds to complete which is very bad for a web page. Remember, the database call takes approx. 1 second to complete and under a particular number of concurrent requests, we experience a very serious bottleneck here.

Let’s have a look at the asynchronous implementation and see the difference:

ab_asyncdb_long_1

Approximately 1 second to complete for each request, pretty impressive compared to synchronous implementation.

Asynchronous database calls are not as straight forward as other types of asynchronous operations but sometimes it will gain so much more responsiveness to our applications. We just need to get it right and implement them properly.

Visual Studio 11 Beta Code-Named "Juneau" SQL Server Database Project - Quick Overview

Quick Overview of Code-Named "Juneau" SQL Server Database Project which is one of the most awesome features of Visual Studio 11 beta
2012-04-07 10:26
Tugberk Ugurlu


I think one of the most awesome features of Visual Studio 11 beta is SQL Server Database Project (code-named "Juneau"). It is simple, robust and makes it really easy for us to check the database code into our source control system.

I hear you yelling at me "Dude, ever heard about NoSQL? MongoDB? RavenDB?". Yeah, I am aware of those technologies and I think you can achieve lots of things that you can do with a relational database system with a NoSQL database system. On the other hand, there are quite a lot of other things that you cannot do with a NoSQL database system. At least, with the comfort of SQL. What? Did you say "Comfort? Who favors comfort over performance?"? I would like to remind you that all Stack Exchange family sites run on SQL Server.

To sum up, SQL Server will not go anywhere at all. It will always be around and we will keep using it. So, I‘d pay attention to this post Smile

This project template carries lots of features inside it and despite the fact that it is so easy to get started with it, you might drown inside its options very quickly. Honest to say, I am not so experienced with this feature but I try to use this on some personal projects for now as much as I can.

Let’s first have a look at Visual Studio 2010 because if you play with SQL Server project templates with VS 2010, you will be in panic because lots of them are now gone.

image

Ok, then. What do we do now? Well, it is simple: they are now all in one project template. From database projects to SQL Server CLR assembly projects, SQL Server Database Project template can do all of them (I have never tired creating a SQL Server CLR project with VS 11 Database Project template so far. So, I might not be entirely correct here).

Let’s come back to VS 11 beta. Creating a SQL Server Database Project is very straight forward. File > New > Project and navigate to SQL Server language tab or search against "SQL Server". SQL Server Database Project option will be available.

image

When we create the project, we get not many things out of the box which is nice.

image

From this point, you can either start a brand new project or import your existing database.

When you double-click on the Properties, we will get properties window which we can set lots of configuration about the database, build process and so on.

image

When you are working on a project, make sure you set the target properly as shown below.

image

While we are working on our database project, we will be creating files for Tables, User Defined Functions, Stored Procedures, etc. Don’t worry about where you put them. VS is smart about your files and it orders them properly on your deployment. This is the experience I have had so far.

As usual, we can add new items via Add New Item dialog box and Database Project ships with several item templates.

imageimageimage

User experience is also incredibly high. For example, you can create your tables through design surface or directly with T-SQL code. No matter which one you pick, VS will always keep those in sync.

As you can see below, we have a file named Products.sql for Products table.

image

Let’s create a foreign key constraint for CategoryId column by referencing a table which does not exist yet.

image

Save the file and build the solution. Now, we are getting errors since the Categories table does not exist.

image

When we add the necessary Categories table and its columns, the build process should run smoothly without any errors.

Let’s now open up the Products.sql table file again. Did you notice the right side of the design surface? Yes, it really knows what is going on outside of this file and it keeps the information in sync. As soon as you make a chance anywhere inside your project, proper places get updated.

image

You can also create script files which will be executed at particular point of your deployment. When you go Add New Item dialog box and navigate to User Scripts tab, you will see some options there. Let’s add Post-Deployment Script file to our project.

image

This file is nothing but a regular file except for the fact that its build action has been set to PostDeploy.

image

What about IntelliSense? Well, it is quite nice as you can see from the below screenshots.

I don’t know why but at some points, I was unable to kick off the intellisense, though.

imageimage

One more thing to mention in this post would be the deployment process but I thought it would be too much for a blog post. So, I will (hopefully) try to blog about that separately by demonstrating different scenarios.

I am very excited about this feature and I am sure you have something to benefit from it as well.

Useful References

Parent / Child View In a Single Table With GridView Control On ASP.NET Web Forms

This awesome blog post will demonstrate how to create a complete, sub-grouped product list in a single grid. Get ready for the awesomeness...
2011-04-28 13:13
Tugberk Ugurlu


parent-child-gridviewSometimes we want to create a parent/child report that shows all the records from the child table, organized by parent. I have been thinking about this thing in my head lately but haven’t been sure for long time about how to get it done with web forms.

This implementation is actually so easy with ASP.NET MVC framework.The model on the view has its own mappings to relational tables on the database (assuming that we are using an ORM such as Entity Framework) and a foreach loop will do the trick. So, how is this thing done with ASP.NET web forms? Let’s demonstrate a sample.

I have created a new ASP.NET Web Application under .Net Framework 4 and also my database under this project. (I’m using Visual Studio 2010 as IDE but feel free to use Visual Web Developer Express 2010) After that I created my ADO.NET Entity Data Model with database first approach. Our model should look like as following;

image

After creating our database structure and ORM (and filling our database tables with some fake values for demonstration purpose), now we’ll be playing with ASP.NET GridView control on a web form page.

The fundamental idea is to create a GridView control for the parent table (this is Category class in our case) that contains an embedded GridView for each row. There child GridView controls are added into the parent GridView using a TemplateField.

But the hard part is that you cannot bind the child GirdView controls at the same time that you bind the parent GirdView because the parent GirdView rows haven’t been created yet. So, we need to wait for GirdView.DataBound event to fire in the parent view before binding the child GridView controls.

In our example, the parent grid view defines two columns and they are both the TemplateField type. The first column combines the category name and category description as you can see below;

          <asp:TemplateField HeaderText="Category">

                <ItemStyle VerticalAlign="Top" Width="20%" />
                <ItemTemplate>
                
                    <br />
                    <b><%#Eval("CategoryName")%></b>
                    <br /><br />
                    <%#Eval("CategoryDescription")%>
                    <br />

                </ItemTemplate>

            </asp:TemplateField>

The second column contains an embedded GridView of products, with two bound columns as you can see below;

            <asp:TemplateField HeaderText="Products">
            
                <ItemStyle VerticalAlign="Top" Width="80%" />
                <ItemTemplate>
                
                    <asp:GridView ID="productsGrid" runat="server" AutoGenerateColumns="false">
                        <Columns>
                        
                            <asp:BoundField DataField="ProductName" HeaderText="Product Name" />
                            <asp:BoundField DataField="Price" HeaderText="Unit Price" DataFormatString="{0:C}" />

                        </Columns>
                    </asp:GridView>

                </ItemTemplate>

            </asp:TemplateField>

You probably realized that markup for the second GirdView does not set the DataSourceID property. That's because the data source for each of these grids will be supplied programmatically as the parent grid is being bound to its data source.

Now we need to create two data sources, one for retrieving the list of categories and the other for retrieving all products in a specified category. As we have our model as ADO.NET Entity Data Model, we will use EntityDataSoruce to communicate with the database. The following code for first data source which will fill the parent GirdView;

    <asp:EntityDataSource ID="EntityDataSource1" runat="server" 
        ConnectionString="name=ProductsEntities" 
        DefaultContainerName="ProductsEntities" EnableFlattening="False" 
        EntitySetName="Categories">
    </asp:EntityDataSource>

Now, you need to bind the first grid directly to the data source and your markup for the grid view beginning tag should look like this;

    <asp:GridView ID="categoryGrid" AutoGenerateColumns="false" DataKeyNames="CategoryID"
        DataSourceID="EntityDataSource1" 
        onrowdatabound="categoryGrid_RowDataBound" runat="server" Width="100%">

And here we are on the tricky part; binding the child GirdView controls. First, we need a second EntityDataSource. The second data source contains the query that’s called multiple times to fill the child GridView. Each time, it retrieves the products that are in a different category. The CategoryID is supplied as a parameter;

    <asp:EntityDataSource ID="EntityDataSource2" runat="server" 
        ConnectionString="name=ProductsEntities" 
        DefaultContainerName="ProductsEntities" EnableFlattening="False" Where="it.CategoryID = @categoryid"
        EntitySetName="Products">
        <WhereParameters>
            <asp:Parameter Name="categoryid" Type="Int32" />
        </WhereParameters>
    </asp:EntityDataSource>

To bind the child GridView controls, you need to react to the GridView.RowDataBound event, which fires every time a row is generated and bound to the parent GridView. At this point, you can retrieve the child GridView control from the second column and bind it to the product information by programmatically. To ensure that you show only the products in the current category, you must also retrieve the CategoryID field for the current item and pass it as a parameter. Here’s the code you need;

        protected void categoryGrid_RowDataBound(object sender, GridViewRowEventArgs e) {

            if (e.Row.RowType == DataControlRowType.DataRow) {

                //get the GridView control in the second column
                GridView gridChild = (GridView)e.Row.Cells[1].Controls[1];

                //set the categoryid parameter so you get the products in the current category only

                string categoryID = categoryGrid.DataKeys[e.Row.DataItemIndex].Value.ToString();
                EntityDataSource2.WhereParameters[0].DefaultValue = categoryID;

                //Bind the grid
                gridChild.DataSource = EntityDataSource2;
                gridChild.DataBind();

            }

        }

Let’s fire up our project and see what happens;

image

We totally nailed it (I added some style to make it look a little bit better). Perfect.

I hope that you found it useful and it helped Smile

SQL Injection vs. Lethal Injection / Protection Against SQL Injection

SQL Injection and Lethal Injection... They are both dangerous and they can be easily fatal. But how? What is SQL Injection and how it can effect my project? The answers are in this blog post.
2011-01-29 18:37
Tugberk Ugurlu


lethal-injectionWriting a software, web application code is a real deal. It requires a good quality of talent on programing languages, appropriate analectic approach and most of all, a good prescience on your project. The things I have mentioned are very important and basic features of a qualified programmer.

I am not a student of a computer related course and I haven’t been but I support that educational background on computer science makes a difference on the quality of programmer. But the diploma or the course certificate is not enough. Little mistakes could be unforgivable in programming world and your diploma or certificate cannot get those mistakes back or cover them.

As for our topic, SQL injection is one of the most important topic on programming security. I have seen couple of developer’s “handy” work for last several months and I decided to write this blog post and I would like say all of the developers, with no offense;

Please, if you are creating a project with database structure, for the sake of programming, be aware of the SQL injection and its effects. It is not a shame that you haven’t heard of that term. What the shame is to write lines of codes creating the proper connection with your database without considering the effects of SQL injection !

NO OFEENSE !

What is SQL Injection?

Well, some of you might want to know what the SQL injection is. I won’t explore the world from scratch, so here is the clear explanation that I quoted from Wikipedia;

SQL injection is a code injection technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. SQL injection attacks are also known as SQL insertion attacks.

 

sql-injection

So the definition supposed to clear the question marks but it might not. Let’s demonstrate.

Imagine that you have a web application running on the web and it aims to provide an user interface to your customers to view their account details.

The demo application is pretty small and we will only create 2 pages with one database table. Page 1 will be the wrong scenario and 2nd one will be the right.

In this application, we will see how the end user can easily display the sensetive data you migh have in your database.

"I would like to say this, in a nutshell, nobody (I mean a programmer who knows what he/she is doing) developed a kind of application for that kind of purpose but to demonstrate the topic, I have done something like that. The project is not supposed to be a real world example."

Our database structure looks like this;

 

 

sql-injection-demo-project-server-explorer-view-for-sql-database-structure

sql-injection-demo-project-data-view-for-sql-database-structure

I won’t dive into details, I will post the project code so your could download and dig it letter.

SQL Injectable Page 

I have used GridView to list the data and here is what the user page looks like;

sql-injection-demo-project-sql-injection-open-page-view

The code has been use to provide the data is as exactly below;

        protected void butn_click(object sender, EventArgs e) {

            GridView1.DataSource = DataProvider(txt1);
            GridView1.DataBind();
        }

        private static DataSet DataProvider(TextBox mytext) {

            string connectionString = WebConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString;
            string sql = "SELECT * FROM Customers WHERE ([TCKimlikNo] = '" + mytext.Text + "')";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter MyAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet("MyDs");
            MyAdapter.Fill(ds, "MyDs");

            return ds;
        }

“DataProvider()” static method connects to the database and executes some SQL against a SQL Server database that returns the number of rows where the user data supplied by the user matches a row in the database. If the result is one matching row, that row will be displayed as you can see;

 

sql-injection-demo-project-sql-injection-open-page-view-no-harm

Let’s put a break point on the 10th line and hit it;

 

sql-injection-demo-project-sql-injection-breakpoint-debug

 

The value supplied above for TCKimlikNo is 34265128731. As we can see in the image, the code works perfectly fine and the value is on the place that we wanted. Now let’s do some evil things;

 

sql-injection-demo-project-sql-injection-breakpoint-debug-evil

 

Now the query explains itself pretty clearly. The evil user put this;

hi’ or ‘1’ = ‘1

And the logic fits. Method will return all the rows inside the database table. Look at the result;

 

image

 

Boom, you have been hacked ! This is the SQL Injection my friends. This thing is easy to apply and the worse part, this mistake is being made often.

Here is a quote from Mike’s blog;

This is SQL Injection. Basically, additional SQL syntax has been injected into the statement to change its behavior. The single quotes are string delimiters as far as T-SQL is concerned, and if you simply allow users to enter these without managing them, you are asking for potential trouble.

What is the Prevention?

Easy ! Just do not create the world from scratch.

If you are a ASP.Net user, use parameters instead of hand made code. Review the following code and compare it with the previous one;

            string connectionString = WebConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString;
            string sql = "SELECT * FROM Customers WHERE ([TCKimlikNo] = @IDParameter)";
            SqlConnection con = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@IDParameter", SqlDbType.VarChar);
            cmd.Parameters["@IDParameter"].Value = mytext.Text;

            SqlDataAdapter MyAdapter = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet("MyDs");
            MyAdapter.Fill(ds, "MyDs");

            return ds;

Done ! You will be good to go Smile But what happened there? Here is a good quote from Mike’s blog again;

Parameter Queries

Parameters in queries are placeholders for values that are supplied to a SQL query at runtime, in very much the same way as parameters act as placeholders for values supplied to a C# method at runtime. And, just as C# parameters ensure type safety, SQL parameters do a similar thing. If you attempt to pass in a value that cannot be implicitly converted to a numeric where the database field expects one, exceptions are thrown

Paramaters will protect your data if your building your project this way but another safe way is to use LINQ to SQL and Entity Framework to protect your project against SQL Injection.

Tags