CODEWITHSUNDEEP
c#asp.net.netsqldatasource
Ali007
Ali007

Reputation: 678

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

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

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?

Upvotes: 2

Views: 3017

Answers (7)

subramanya46
subramanya46

Reputation: 481

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

Upvotes: 0

sh1ng
sh1ng

Reputation: 2973

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.

Upvotes: 1

walther
walther

Reputation: 13600

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.

Upvotes: 11

Christian Phillips
Christian Phillips

Reputation: 18759

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.

Upvotes: 1

Kuldeep
Kuldeep

Reputation: 454

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.

Upvotes: 1

Karl Anderson
Karl Anderson

Reputation: 34846

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.

Upvotes: 2

Tim
Tim

Reputation: 4101

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.

Upvotes: 1

Related Questions