Better solution to SqlDataSource?

Question

I need to display some data from a view in a SQL Server database on several pages of my website. I've done it by using SqlDataSource within my web.config and then used a GridView control within my .aspx page to display the data.

Now, this works but I read in some forum that it is bad practice to do use SqlDataSource? I will probably need to have the ability for the admin/user to filter the data in the future, so I'm not sure how this would work with my current implementation.

My code so far looks like this :

In the web.config file:

<connectionStrings>
    <add name="Test1.ConnectionString" 
         connectionString="Data Source=...."
         providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
</system.web>

And something like this in my aspx

<body id="wrap" >
  <form  runat="server">
    <asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" 
         BackColor="White" BorderColor="#CCCCCC" 
         BorderStyle="None" BorderWidth="1px" CellPadding="3" 
         DataSourceID="SqlDataSource1" Height="500px" Width="408px">
        <Columns>
            <asp:BoundField DataField="Title" HeaderText="Title" ReadOnly="True" SortExpression="Title">
                <ItemStyle Width="400px" HorizontalAlign="Center" Height="100px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
            <asp:BoundField DataField="Result" HeaderText="Result" ReadOnly="True" SortExpression="Result" >
                <ItemStyle HorizontalAlign="Center" Height="100px" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" />
            </asp:BoundField>
        </Columns>
        <FooterStyle BackColor="White" ForeColor="#002756" />
        <HeaderStyle BackColor="#003466" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="White" ForeColor="#002756" HorizontalAlign="Left" />
        <RowStyle ForeColor="#002756" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
         ConnectionString="<%$ ConnectionStrings: Test1.ConnectionString %>" 
         SelectCommand="SELECT * FROM [Test1.ConnectionString]"> 
    </asp:SqlDataSource>
  </form>
</body>

So my question is is there a better way to implement this, keeping in mind that I will probably need a function for the user/admin to filter the data by certain criteria?

Any particular reason you're not using code behind to fetch the data? — Izzy
– Izzy, Commented Nov 5, 2015 at 16:08
I come from a php background and quite new to asp.net so still trying to get my head around this. But I guess that would be the better solution? — MariaL
– MariaL, Commented Nov 5, 2015 at 16:23

Joel Coehoorn · Accepted Answer · 2015-11-05 17:16:12Z

4

It's not necessarily bad practice to use SqlDataSource... but it does tend to mingle your data access code with your presentation code. Additionally, you can often do better with an ObjectDataSource that wraps your view. There's a little more code involved (you'll have to create a new class somewhere to select from your view), but you end up with methods that can be easily updated or replaced in the future to handle whatever changes you may need.

edited Nov 5, 2015 at 17:16

answered Nov 5, 2015 at 16:24

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

Comments

Izzy · Accepted Answer · 2015-11-05 17:04:47Z

1

As mentioned in my comment I would recommend you use the code behind to achieve this as it's much easier to make changes if required in the future. I'm assuming you've got a table in your database named Test1. We'll first create a class to represent this in C# and add a few properties which we'll use later on.

public class Test
{
  public string Title { get; set; }
  public int Result { get; set; }
}

Now let's create a method which returns a collection of values from your database.

public List<Test> GetData()
{
    List<Test> myList = new List<Test>();

    string sqlQuery = "Select Title, Result From Test";
    string connectionString = ConfigurationManager.ConnectionStrings["Test1.ConnectionString"].ConnectionString; //Read connection string from config file

    using (var con = new SqlConnection(connectionString))
    {
        using (var cmd = new SqlCommand(sqlQuery, con))
        {
            //Add param here if required.
            con.Open(); //Open connection
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    Test t = new Test();
                    t.Title = reader["Title"].ToString();
                    t.Result = Convert.ToInt32(reader["Result"]);

                    myList.Add(t);
                }
            }
        }
    }
    return myList;
}

Finally, you want to set the datasource for the GridView. I'm going to assume you have a page called MyGridPage.aspx open up the MyGridPage.asps.cs and in your Page_Load event you can set the DataSource for your grid as:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        Test t = new Test();
        GridView1.DataSource = t.GetData();
        GridView1.DataBind();
    }
}

If you wanted to filter your Sql query by for example username you'll change it as:

string sqlQuery = "Select Title, Result From Test Where username = @username";

Then you can pass in the @username parameter as:

cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = myUsername;

myUsername can be of someone who's logged into your app. You will pass the parameter(s) before you open the connection. Passing parameters will also prevent sql injection which I suggest you read up on in case you're not aware.

Note: it is recomenned to make use of using block to ensure the connection object is closed and disposed correctly.

edited Nov 5, 2015 at 17:04

answered Nov 5, 2015 at 16:55

Izzy

6,8988 gold badges44 silver badges92 bronze badges

4 Comments

MariaL Over a year ago

Just a quick question- I am now getting this error: CS1518: Expected class, delegate, enum, interface, or struct for the public LIst<Test> GetData() line. Any idea why I get this error?

Izzy Over a year ago

The method needs to be inside of a class not outside i.e. inside of public class Test { //Method here } //Not here

MariaL Over a year ago

Thanks. the issue I've got now is that it throws up an error saying that the name 'GridView1' does not exist in the current context?

Izzy Over a year ago

Check your markup code and see if your <asp:GridView /> is named GridView1

James MacGilp · Accepted Answer · 2015-11-05 16:42:37Z

0

You can programmatically set the data source of the grid view.

    protected void Page_Load(object sender, EventArts e)
    {
        using(SqlConnection conn = new SqlConnection(connectionString))
        {
            string query = "SELECT * FROM Test"; //your SQL query goes here
            SqlCommand cmd = new SqlCommand(query, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            da.Fill(table);

            GridView1.DataSource = table;
            GridView1.DataBind();

            cmd.Dispose();
            da.Dispose();
        }
    }

answered Nov 5, 2015 at 16:42

James MacGilp

1868 bronze badges

Collectives™ on Stack Overflow

Better solution to SqlDataSource?

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related