Gridview with complex sqldatasource in 3-tier architecture

Question

I have implemented a 3-tier arhitecture which can be seen in below link

http://geekswithblogs.net/edison/archive/2009/04/05/a-simple-3-tier-layers-application-in-asp.net.aspx

In the example above it uses a DataTable as sqldatasource and binds it to the gridview with below code.

GridView1.DataSource = Client.GetClients();
GridView1.DataBind();

However my datasource is not a datatable, it is a regular sqldatasource where you can edit, delete, and update. So I don' know how can it be seperated from my presentation layer or should it be really seperated?. Here my sqldatasource's codes:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyDbConn %>"
    DeleteCommand="DELETE FROM [OrderDetail] WHERE [RowNo] = @RowNo" InsertCommand="INSERT INTO [OrderDetail] ([FileNo], [PONumber], [MaterialCode], [MaterialDescription], [MaterialCategory], [UnitOfMeasure], [Quantity], [ContainerType], [LoadingDate]) VALUES (@FileNo, @PONumber, @MaterialCode, @MaterialDescription, @MaterialCategory, @UnitOfMeasure, @Quantity, @ContainerType, @LoadingDate)"
    SelectCommand="SELECT * FROM [OrderDetail]" UpdateCommand="UPDATE [OrderDetail] SET [FileNo] = @FileNo, [PONumber] = @PONumber, [MaterialCode] = @MaterialCode, [MaterialDescription] = @MaterialDescription, [MaterialCategory] = @MaterialCategory, [UnitOfMeasure] = @UnitOfMeasure, [Quantity] = @Quantity, [ContainerType] = @ContainerType, [LoadingDate] = @LoadingDate WHERE [RowNo] = @RowNo">
    <DeleteParameters>
        <asp:Parameter Name="RowNo" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="FileNo" Type="Int32" />
        <asp:Parameter Name="PONumber" Type="String" />
        <asp:Parameter Name="MaterialCode" Type="String" />
        <asp:Parameter Name="MaterialDescription" Type="String" />
        <asp:Parameter Name="MaterialCategory" Type="String" />
        <asp:Parameter Name="UnitOfMeasure" Type="String" />
        <asp:Parameter Name="Quantity" Type="Int32" />
        <asp:Parameter Name="ContainerType" Type="String" />
        <asp:Parameter Name="LoadingDate" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="FileNo" Type="Int32" />
        <asp:Parameter Name="PONumber" Type="String" />
        <asp:Parameter Name="MaterialCode" Type="String" />
        <asp:Parameter Name="MaterialDescription" Type="String" />
        <asp:Parameter Name="MaterialCategory" Type="String" />
        <asp:Parameter Name="UnitOfMeasure" Type="String" />
        <asp:Parameter Name="Quantity" Type="Int32" />
        <asp:Parameter Name="ContainerType" Type="String" />
        <asp:Parameter Name="LoadingDate" Type="String" />
        <asp:Parameter Name="RowNo" Type="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>

Doesn't this look like MVC type architecture? You have to put something in select, other it cannot be bind — TheTechGuy, Aug 29 '12 at 19:14

score 3 · Accepted Answer · answered Sep 07 '12 at 17:47

SqlDataSource does not fit into 3 tier architecture. It is up to you how you would do it. You don't have to separate it, but if you want to have a proper 3 tier architecture here, you need some kind of a database handling class in your data layer with CRUD operations.

The article you linked explains this clearly. If you dont want to use the DataTable, you can create your own POCO objects to handle data like

public class Order {... }

Your business tier method would look like

List<Order> GetOrders(...) {...}

and data

//CRUD naming convention
List<Order> ReadOrders (...)  {...}

score 2 · Answer 2 · answered Sep 10 '12 at 04:07

Roland's answer above already addresses :-

the point about how layered architecture (assuming a split of DAL / BL / UI ) does not tie in with using SQLDataSource as there is no such separation when using a SQLDataSource.
How you could separate it out without using a SQLDataSouce using POCO's / Datatables

I will give my 0.02$ about the last part of your question :-

should it be really seperated?

Well, like most realistic answers. It depends.

It depends on things like - what is your main reason for having a 3 tier architecture? Some of the advantages of such a design include better maintainability, better testability and even better scalability. Conversely, using a SQLDataSource MAY speed up your development.

My personal experience is that it is usually a better idea to have a layered design for some of the above reasons and hence I would normally avoid using a SQLDataSource and even the speed of development advantage for SQLDataSource can be negated by using code-generators like CodeSmith. So, i just dont see any benefit of using a SQLDataSource in the long run at all

Gridview with complex sqldatasource in 3-tier architecture

2 Answers2