Cannot implicitly convert type 'System.Data.SqlClient.SqlDataReader' to 'System.Data.DataSet'

Question

I'm doing a project in C# using 3 a tier architecture. I wrote the code to sort the records from SQL. But it doesn't return any values.

Query:

BEGIN
   SELECT 
       Bank_Code, Bank_Name, Bank_ShortName, CreditCard_Commission_Percent 
   FROM
       BankMaster 
   WHERE 
       bank_name LIKE '% + @BankName + %'
END

Calling the query - BOL :

public DataSet SortBank()
{
    try
    {
        SqlCommand cmd = new SqlCommand("SortBankMaster", dal.con);
        cmd.Parameters.Add("@BankName", SqlDbType.NVarChar).Value = Bank_Name;
        cmd.CommandType = CommandType.StoredProcedure;

        return cmd.ExecuteReader();                        // Error
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

UI :

public void FillSortBank()
{
    try
    {
        DataSet ds = new DataSet();
        bol.Bank_Name = txtsort.Text;
        ds = bol.SortBank();
        DGVBank.DataSource = ds.Tables[0];
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

This is my DAL :

public DataSet DBread(String Squery)
{
        DataSet DSResult = new DataSet();

        try
        {
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
            }

            da = new SqlDataAdapter(Squery, con);

            da.Fill(DSResult);

            Console.WriteLine();

            return DSResult;
        }
        catch (Exception ex)
        {
            //UnhandledExceptionHandler();
            //return e.ToString();
            throw ex;
            //return DSResult;
        }
    }

Thanks to all....

Tim Schmelter · Accepted Answer · 2011-08-16 10:30:24Z

1

....
using (SqlDataReader myReader = cmd.ExecuteReader())
{
   DataTable myTable = new DataTable();
   myTable.Load(myReader);
   cmd.Connection.Close();
   Dim myDataSet As New DataSet()
   myDataSet.Tables.Add(myTable)
   return myDataSet;
}

MSDN: load DataTable with iDataReader

answered Aug 16, 2011 at 10:30

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

Comments

Nighil · Accepted Answer · 2011-08-16 10:33:20Z

0

public DataSet SortBank()
        {
            try
            {
                SqlCommand cmd = new SqlCommand("SortBankMaster", dal.con);
                cmd.Parameters.Add("@BankName", SqlDbType.NVarChar).Value = Bank_Name;
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adpt.Fill(ds); 
                return ds;                 
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

answered Aug 16, 2011 at 10:33

Nighil

4,1277 gold badges34 silver badges58 bronze badges

Comments

Nikos Steiakakis · Accepted Answer · 2011-08-16 10:26:35Z

0

You cannot just convert a SqlDataReader to a DataSet. You can use a SqlDataAdapter in order to fill the data set if you want

SqlCommand cmd = new SqlCommand("SortBankMaster", dal.con);
                cmd.Parameters.Add("@BankName", SqlDbType.NVarChar).Value = Bank_Name;
                cmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

or you can use a DataTable if you do not to use a DataSet.

DataTable dt = new DataTable();
da.Fill(dt);

answered Aug 16, 2011 at 10:26

Nikos Steiakakis

5,7757 gold badges46 silver badges56 bronze badges

Comments

Zenwalker · Accepted Answer · 2011-08-16 10:27:31Z

0

DataTable.Fill you can use to fill a datatable from the reader.

edited Aug 16, 2011 at 10:27

answered Aug 16, 2011 at 10:22

Zenwalker

1,9192 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

Cannot implicitly convert type 'System.Data.SqlClient.SqlDataReader' to 'System.Data.DataSet'

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related