LINQ query against GridView filled with SqlDataSource

Question

I need to make a LINQ query against GridView filled with SqlDataSource - to create a Dictionary from rows.

So I have:

<asp:GridView runat="server" ID="gridCurrency" DataSourceID="sourceCurrency" OnDataBound="gridCurrency_DataBound"
<asp:SqlDataSource runat="server" ID="sourceCurrency" ConnectionString="<%$ ConnectionStrings:MyConnStr %>" SelectCommand="[getCurrencies]" SelectCommandType="StoredProcedure" />

and

protected void gridCurrency_DataBound(object sender, EventArgs e)
{
    var dic = (from row in ((DataView)sourceCurrency.Select(DataSourceSelectArguments.Empty)).Table.AsEnumerable()
              select new
              {
                  ID = (byte)row["ID"],
                  CurrencyName = (string)row["name"]
              }).ToDictionary(k => k.ID, k => k.CurrencyName);
}

Is there any better way then my to get a DataTable from GridView while no GridView.DataSouce is present.

Answer 1

I prefer to bind my server controls in my code-behind. I can debug and test this better. I also don't have to do other crazy things to get to my bound data from code-behind... it's already there. Here's an example.

Assume the following stored proc in SQL:

CREATE PROCEDURE selEmployees
@DeptId int,
@SearchString varchar(100)
AS
BEGIN
SELECT TOP 1000 * FROM Employees 
WHERE DeptId = @DeptId AND CONTAINS(*, SearchString);   
END

I can match that stored proc to a method in my entity class or page code-behind, like this:

public static DataSet selEmployees(int DeptId, string SearchString)
    {
        DataSet ds = new DataSet();
        SqlConnection con = new SqlConnection(clsData.getConnString());
        SqlCommand cmd = new SqlCommand("selEmployees", con); // stored proc name
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter _DeptId = cmd.Parameters.Add("@DeptId", SqlDbType.Int); //stored proc parameter name and datatype
        _DeptId.Value = DeptId; //assign method parameter value to sql parameter
        SqlParameter _SearchString = cmd.Parameters.Add("@SearchString", SqlDbType.Int); //stored proc parameter name and datatype
        _SearchString.Value = SearchString; //assign method parameter value to sql parameter
        SqlDataAdapter adapt = new SqlDataAdapter(cmd);
        adapt.SelectCommand = cmd;
        con.Open();
        try
        {
            adapt.Fill(ds);
        }
        catch (Exception ex)
        {
            string msg = ex.ToString();
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
        return ds;
    }

Then, I can just bind my data to my server control at page_load like this:

protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            GridView1.DataSource = Employees.selEmployees(MyDeptId, MySearchString);
            GridView1.DataBind();
        }
    }

If you try this, be sure to remove the DataSourceId parameter from the GridView in your mark-up. DataSource and DataSourceId don't play nice together.

Note: There are actually a million better ways to do this. The aim of this post was to illustrate one easy alternative to using SqlDataSource in your mark-up.

One way to take this a step futher is to assign the resultant dataset to a re-usable variable like a page property:

public partial class ViewEmployees : System.Web.UI.Page
{
    public DataSet DataSetEmployees { get; set; } //re-usable property gets set at page_load

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //save the dataset to a re-usable property
            DataSetEmployees = Employees.selEmployees(MyDeptId, MySearchString);

            //bind using the property
            GridView1.DataSource = DataSetEmployees;
            GridView1.DataBind();
        }
    }
}

With that simple upgrade, you have the ability to use and re-use the DataSet throughout the page without having to re-query the database.