Cannot implicitly convert System.Data.SqlClient.SqlDatReader to AdoControls.SqlDataReader

Question

I'm learning ADO.Net and have been performing SQL commands on GridView. Today I started to learn the SqlDataReader In ADO.Net and when I try to Execute my Command and pass it to the SqlDataReader Object, it shows the error

> CS0029: Cannot Implicitly Convert 'System.Data.SqlClient.SqlDataReader' to 'AdoControls.SqlDataReader'

This is my code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace AdoControls
{
    public partial class SqlDataReader : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string cs = ConfigurationManager.ConnectionStrings["sample"].ConnectionString;
            using(SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM employee", con);
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader(); //This shows Error

                GridView1.DataSource = cmd.ExecuteReader(); //This works Fine!!

            }
        }
    }
}

Please guide me, what am I doing wrong here.

Thanks!

Answer 1

the problem looks to be that your web page has the exact same name as the SqlDataReader.

So, you have to disambugate this.

Say, like this:

    void LoadGrid()
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * FROM Vhotels ORDER BY HotelName";

            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                System.Data.SqlClient.SqlDataReader reader = cmdSQL.ExecuteReader();

                GridView1.DataSource = reader;
                GridView1.DataBind();
            }
        }
    }

So, your web page (which is a class ALSO has that same name SqlDataReader - so which kind of class are you going to get here?

Say my page was called TestPage.aspx

You are in effect doing this:

public partial class TestWebPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        TestWebPage MyRead = cmd.ExecuteReader();

So, either try creating a new web page - give it a different name then SqlDataReader, or as noted per above - disambiguate the SqlDataReader type you want vs that of oh just so happening to have the web page (and class) of the same name.

A few more FYI:

The grid view (and most data controls) can accept a "reader". However, the there are TWO HUGE details to be aware of when directly assign a GV a reader.

First up, if you turn on data paging. That is to allow paging of the grid view, say like this:

So, if the GV requires (or you want) to use "data paging" for the grid, you can't assign the grid using a reader.

So, you need to assign the grid a object that supports "enumerations" then a reader.

So, you can say use this code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            LoadData();
        }
    }

    void LoadData()
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * FROM tblHotels ORDER BY HotelName";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());
                GHotels.DataSource = rstData;
            }
        }
    }

Next issue: Often when filling up a grid view, we might want to do calculations, or say even format a column with color, or who knows what - but often we want some kind of "criteria" or to change things. So, say for active hotels, I want them the color blue, but I DO NOT want to show, nor have the active column from the database in the grid view.

Well, if you use a "reader" to fill the grid, then DURING the row data bind process, you can't use nor have use of full data row used during binding. Again, most of the time - not a issue. However, is is VERY nice and VERY often required that you want to use all rows of data for say a tax calculation, or even just simple formatting.

But those rows are NOT in the grid view.

So, in the row data bound event, I have 100% full use of the WHOLE data row used for that binding. But ONLY if I use a data table or other objects that support this (and a reader does not).

and do keep in mind that the data source of the GV only persists DURING this bindinng process. You can't use it say later in code for a button click - you find GV datasource is now null and void - it ONLY persists during binding process.

So, say I have this simple grid:

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="dFalse" DataKeyNames="ID" 
            CssClass="table" OnRowDataBound="GridView1_RowDataBound">
            <Columns>
                <asp:BoundField DataField="FirstName" HeaderText="FirstName"  />
                <asp:BoundField DataField="LastName" HeaderText="LastName"    />
                <asp:BoundField DataField="HotelName" HeaderText="HotelName"  />
                <asp:BoundField DataField="City" HeaderText="City"  />
                <asp:BoundField DataField="Description" HeaderText="Description"  />
            </Columns>
        </asp:GridView>

And code to fill this grid:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            LoadGrid();
    }

    void LoadGrid()
    {
        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            string strSQL = "SELECT * FROM VHotels ORDER BY HotelName";
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                conn.Open();
                DataTable rstData = new DataTable();
                rstData.Load(cmdSQL.ExecuteReader());
                GridView1.DataSource = rstData;
                GridView1.DataBind();
            }
        }
    }

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            // get the data bind row
            DataRowView gData = e.Row.DataItem as DataRowView;

            if ((bool)gData["Active"])
            {
                // is active - highlight the hotel color
                e.Row.Cells[2].BackColor = System.Drawing.Color.FromName("skyblue");
            }
        }
    }

Note close in row data bound - I was able to use datarowview. You can NOT use that if you use a reader.

And in the above example, note how I was able to "freely" use the WHOLE data row despite that GV does not have the column "active" in the markup.

The results of above are this :

Once again, using a reader will not work for above.

So, data paging, or using the "data row" during binding is not available if you use and assign the GV a reader.

However, for a lot of GV's??? Sure, you can stuff into that GV directly the reader.

So, I would change the name of your web page - create a new test web page.

Or as noted, since your page class and SqldataReader both have the same name, then disambiguate as per first code snip above.