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!

CodePudding user response：

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.