none
How to pass SqldataReader from Data Access Layer to Business Object Layer RRS feed

  • Question

  • hi

    I am using facade pattern , i want to fetch one row in the database and display it. its my first task i used dataset to finish the job. but dataset is very heavy so i tried to use sqldatareader but it should "invalild attempt to read reader when it was closed". I understand very much that since i closed in my data reader in DAL itself hence i m not able to read it in BOL

    can i know any way to do that i heard people say use idatareader but can any one guide with an example how to acheive this i m giving my code. I have given namespace for every class

    Data access layer code

    using System.Data;
    usign System.Data.Sqlclient;

    public class JobStatusDalCls
    {
    public IDataReader  fetchcustomerdetails(string custid)
            {
                CommonEnCls connString = new CommonEnCls ();
                SqlConnection sqlconn = new SqlConnection(connString.SqlConnectionString);
        
                SqlCommand sqlcomm = new SqlCommand("PROC_GET_CUSTOMER_DETAILS", sqlconn);
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.Parameters.Add("@CUSTID", SqlDbType.VarChar).Value = custid;
                IDataReader dr;
                try
                {
                    sqlconn.Open();
                    dr = sqlcomm.ExecuteReader();
                    sqlconn.Close();
                    return dr;
                   
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message.ToString());
                }
                finally
                {
                    sqlconn.Close();
                }
            }
         }

    Business Object Layer

      JobStatusDalCls js = new JobStatusDalCls();

                IDataReader ds;
                string a;
               
                ds = js.fetchcustomerdetails("149804001100");
                ds.Read();
                a = ds["CUSTOMERID"].ToString();
                ds.Close();

                Console.WriteLine(a);
                Console.ReadLine();

    Thanks & Regards
    Saturday, August 18, 2007 8:21 AM

Answers

  • In a case if it is .NET 2.0 you could use DbDataReader type for this purpose. In 1.x you need to use IDataReader

     

    So in a case of .NET 2.0 your code would look like

     

    using System.Data;
    usign System.Data.Sqlclient;

    public class JobStatusDalCls
    {
    public DbDataReader  fetchcustomerdetails(string custid)
            {
                CommonEnCls connString = new CommonEnCls ();
                SqlConnection sqlconn = new SqlConnection(connString.SqlConnectionString);
        
                SqlCommand sqlcomm = new SqlCommand("PROC_GET_CUSTOMER_DETAILS", sqlconn);
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.Parameters.Add("@CUSTID", SqlDbType.VarChar).Value = custid;
                SqlDataReader dr;
                            
                sqlconn.Open();
                dr = sqlcomm.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
                    
                           
             }
         }

    Business Object Layer

      JobStatusDalCls js = new JobStatusDalCls();

                DbDataReader ds;
                string a;
               
                ds = js.fetchcustomerdetails("149804001100");
                ds.Read();
                a = ds["CUSTOMERID"].ToString();
                ds.Close();

                Console.WriteLine(a);
                Console.ReadLine();

     

    In a case of .NET 1.x you need to replace DbDataReader with IDataReader type. I also removed exception handling part from your code, since it makes no sense to close connection and no sense to rethrow same exception.

     

    Sunday, August 19, 2007 11:46 AM
    Moderator

All replies

  • In a case if it is .NET 2.0 you could use DbDataReader type for this purpose. In 1.x you need to use IDataReader

     

    So in a case of .NET 2.0 your code would look like

     

    using System.Data;
    usign System.Data.Sqlclient;

    public class JobStatusDalCls
    {
    public DbDataReader  fetchcustomerdetails(string custid)
            {
                CommonEnCls connString = new CommonEnCls ();
                SqlConnection sqlconn = new SqlConnection(connString.SqlConnectionString);
        
                SqlCommand sqlcomm = new SqlCommand("PROC_GET_CUSTOMER_DETAILS", sqlconn);
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.Parameters.Add("@CUSTID", SqlDbType.VarChar).Value = custid;
                SqlDataReader dr;
                            
                sqlconn.Open();
                dr = sqlcomm.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
                    
                           
             }
         }

    Business Object Layer

      JobStatusDalCls js = new JobStatusDalCls();

                DbDataReader ds;
                string a;
               
                ds = js.fetchcustomerdetails("149804001100");
                ds.Read();
                a = ds["CUSTOMERID"].ToString();
                ds.Close();

                Console.WriteLine(a);
                Console.ReadLine();

     

    In a case of .NET 1.x you need to replace DbDataReader with IDataReader type. I also removed exception handling part from your code, since it makes no sense to close connection and no sense to rethrow same exception.

     

    Sunday, August 19, 2007 11:46 AM
    Moderator
  • Thank you very much

    I learnt another new thng from you that there is no need for capturing error and throwing the same to Business object layer
    it will be handled automatically.

    if you can help me out how to code using datafactory etc, or teach me any new concept of coding in .net since i m a new bee to design patterns.

    previously for fetching one record what i do was i will load the data from sql data reader into a arraylist in DAL and call the arraylist in the BOL it worked fine but i was too worried how will i call mulitple rows using sql data reader. this is a great new learning.

    let me know any samples in datafactory . I work only in .net 2.0 so you can give me samples in .net 2.0 itself

    Thank you once again.
    Monday, August 20, 2007 6:14 AM