Which one is better - Declaring SqlDataSource in ASPX page or in CodeBehind?

Question

Approach #A. As a programmer you can define SqlDataSource in .aspx page such as:

<asp:SqlDataSource ID="Sql_ID" runat="server" ConnectionString="<%$ ConnectionStrings:Con_Str %>"
    SelectCommand="SELECT * FROM [table_name]">
    <SelectParameters>
        <asp:Parameter Name="user_id"/>
    </SelectParameters>
</asp:SqlDataSource>

Approach #B. Also, you can do this in C# CodeBehind

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Con_Str"].ToString()))
{
    string qry = SELECT * FROM [table_name];

    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();

    using (SqlCommand cmd = new SqlCommand(qry, conn))
    {
        cmd.Parameters.Add("@user_id", SqlDbType.UniqueIdentifier).Value = user_id;
        da.SelectCommand = cmd;

        try
        {
            conn.Open();
            da.Fill(dt);
            conn.Close();
        }
        catch
        {
            // Do something ;
        }
    }
}

Which Approach [A or B] is better? Why?

neither is ideal, you're better off having a DataAccess class that you call from the Code-behind. — Christian Phillips
– Christian Phillips, Commented Nov 4, 2013 at 12:54
"better" means better in performance/speed, standard coding practice, code maintenance, security etc. — Ali007
– Ali007, Commented Nov 4, 2013 at 13:02

walther · Accepted Answer · 2013-11-04 13:01:59Z

11

Neither.. The best is to separate the data access completely from the UI (to a separate project, or at least namespace, so you can easily swap various implementations if needed). Then, in your code-behind, simply reference the exposed repositories and do the binding. But your code-behind of your pages shouldn't contain any data access.

Try to do some research on 3tier architecture for instance.. Unless it's a very basic application, you should learn how to separate it into multiple layers.

edited Nov 4, 2013 at 13:01

answered Nov 4, 2013 at 12:55

walther

13.6k5 gold badges44 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karl Anderson · Accepted Answer · 2013-11-04 12:55:18Z

I recommend declaring the SqlDataSource declaratively in the markup and then referencing it in the code-behind, as necessary. For example, if you need to alter the SelectCommand or some other property based upon user interaction or data returned from a service call, etc.

There is no right or wrong way, but you will definitely see the SqlDataSource declared in the markup more often than done in the code-behind, because it is generally easier for people to understand that something in the markup is part of the page than when it is defined in the code-behind, even though they both end up in the generated HTML.

The scenario that makes the most sense to use code-behind to declare things is when you are doing it dynamically and want to be able to add, potentially, more than one of a control.

Tim · Accepted Answer · 2013-11-04 12:54:53Z

1

I wouldn't say one is better. Use the right one depending on what you need to do. If you just need a simple connection, drop it onto the page. If you need to change something conditionally, do it in code.

answered Nov 4, 2013 at 12:54

Tim

4,10910 gold badges39 silver badges64 bronze badges

Comments

Kuldeep · Accepted Answer · 2013-11-04 12:57:57Z

1

It would be good to mentioned in code behind. If you are following proper project architect then you need to create DAL (Data access layer),In that case you need to maintain data source in code behind and its easier to mock data source while unit testing.

answered Nov 4, 2013 at 12:57

Kuldeep

4541 gold badge4 silver badges11 bronze badges

Comments

Christian Phillips · Accepted Answer · 2013-11-04 13:01:45Z

I would create a DataAccess class which holds all of your logic for calling the data, like:

public class AdoDataAccess : IDataAccess {}

IDataAccess will have your methods, and you can then implement the methods in your concrete class.

Your code behind would then use the IDataAccess, to call an implementation...as in...

IDataAccess dataaccess = new AdoDataAccess(...); <-- this is a lazy way, but just for example purpose.
dataaccess.GetData(...);

This way, if you decide to replace ADO, for maybe EF in the future, you can just add a new DataAcess, like...

public class EFDataAccess : IDataAccess {}

and call it from codebehind

IDataAccess dataaccess = new EFDataAccess(...);

This will help with separation of concern. The way you are looking to implement, doesn't allow for any code-reuse.

sh1ng · Accepted Answer · 2013-11-04 13:08:02Z

1

CodeBehind is permeable way in case of big and growing system. It will be easy to

support schema changing
change "visibility" logic
add caching
audit and logging
and so on

Of course you may use some kind of ORM.

But if you only need simple CRUD functions declaring is faster.

edited Nov 4, 2013 at 13:08

answered Nov 4, 2013 at 12:55

sh1ng

2,9934 gold badges26 silver badges39 bronze badges

Comments

subramanya46 · Accepted Answer · 2018-05-07 09:58:39Z

If the Application is very small and you dont have time for coding then go for sql datasource because you dont have to write a single line of code for that, also you can insert update and delete data by using Grid view very easily.

  <asp:GridView ID="GridView1"  CssClass="table table-striped table-bordered table-hover" runat="server" AutoGenerateColumns="False" DataKeyNames="UNQ_ID" DataSourceID="SqlDataSource1" EmptyDataText="No Data has been Added." AllowPaging="True" AllowSorting="True">
            <Columns>
                <asp:CommandField ShowEditButton="True"></asp:CommandField>
                <asp:BoundField DataField="UNQ_ID" HeaderText="UNQ_ID" ReadOnly="True" InsertVisible="False" SortExpression="UNQ_ID" Visible="false"></asp:BoundField>
                <asp:BoundField DataField="WucsName" HeaderText="WUCS Name" SortExpression="WucsName"></asp:BoundField>
                <asp:TemplateField HeaderText="Year" SortExpression="Year_ID">
                    <EditItemTemplate>
                        <asp:TextBox runat="server" Text='<%# Bind("Year_ID") %>' ID="TextBox1"></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <%#YearCON(int.Parse(DataBinder.Eval(Container.DataItem,"Year_ID" ).ToString()))%>
<%--                        <asp:Label runat="server" Text='<%# Bind("Year_ID") %>' ID="Label1"></asp:Label>--%>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="Total_Land_Holdings" HeaderText="Total Land Holdings" SortExpression="Total_Land_Holdings"></asp:BoundField>
                <asp:BoundField DataField="Former_Paying_Cess" HeaderText="Former Paying Cess" SortExpression="Former_Paying_Cess"></asp:BoundField>
                <asp:BoundField DataField="Water_Cess_Collected" HeaderText="Water Cess Collected" SortExpression="Water_Cess_Collected">
                </asp:BoundField>

            </Columns>

 <asp:SqlDataSource runat="server" ID="SqlDataSource1"
            ConnectionString='<%$ ConnectionStrings:PMISConnection %>'
            SelectCommand="SELECT * FROM [tbl_CWLWRK_WaterCess]"
            DeleteCommand="DELETE FROM [tbl_CWLWRK_WaterCess] WHERE [UNQ_ID] = @UNQ_ID" InsertCommand="INSERT INTO [tbl_CWLWRK_WaterCess] ([WucsName], [Year_ID], [Total_Land_Holdings], [Former_Paying_Cess], [Water_Cess_Collected]) VALUES (@WucsName, @Year_ID, @Total_Land_Holdings, @Former_Paying_Cess, @Water_Cess_Collected)" UpdateCommand="UPDATE [tbl_CWLWRK_WaterCess] SET [WucsName] = @WucsName, [Year_ID] = @Year_ID, [Total_Land_Holdings] = @Total_Land_Holdings, [Former_Paying_Cess] = @Former_Paying_Cess, [Water_Cess_Collected] = @Water_Cess_Collected WHERE [UNQ_ID] = @UNQ_ID">


    <DeleteParameters>
        <asp:Parameter Name="UNQ_ID" Type="Int32"></asp:Parameter>
    </DeleteParameters>

    <InsertParameters>
        <asp:Parameter Name="WucsName" Type="String"></asp:Parameter>
        <asp:Parameter Name="Year_ID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Total_Land_Holdings" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Former_Paying_Cess" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Water_Cess_Collected" Type="Int32"></asp:Parameter>
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="WucsName" Type="String"></asp:Parameter>
        <asp:Parameter Name="Year_ID" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Total_Land_Holdings" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Former_Paying_Cess" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="Water_Cess_Collected" Type="Int32"></asp:Parameter>
        <asp:Parameter Name="UNQ_ID" Type="Int32"></asp:Parameter>
    </UpdateParameters>

</asp:SqlDataSource>

For Bigger application you need to use Code behind

Collectives™ on Stack Overflow

Which one is better - Declaring SqlDataSource in ASPX page or in CodeBehind?

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related