Sep 21 2006

BLL next step - howto part 7

Posted by admin under In practice

Now, in this part of the "In practice" serie - lets look more into our movement from ASPX pages directly consuming datasets and datatables into using classes in a sort of business layer.

While the code for the customer was pretty straigtforward, we do get some interesting problems for the File class.

First I have decided to remove the select * from the stored procedures reading files. For example when showing this list:

it would be stupid to -when reading all the existing files by the cust_ListFiles(@customer_id int) stored procedure - retrieve all binary bits and bytes when they are not shown in this page.

So first - we change the stored procedures:



create proc cust_ListFiles(@customer_id int)
as
select id, customer_id, filename, 
contenttype, length from cust_file 
from cust_file where customer_id=@customer_id

create proc cust_OpenFile(@id int)
as
select id, customer_id, filename, contenttype, 
length from cust_file where id=@id


create proc cust_GetFileBinary(@id int)
as
select filedata from cust_file where id=@id

As you can see the lists doesn't receive the image column (filedata) but instead we have a special stored procedure - cust_GetFileBinary to be called when the binary data is needed.

As we know that data is needed from our page handler ( readfile.ashx ) so have a look at how the code looks:



		public void ProcessRequest (HttpContext context)
		{

            CustomerClasses.File oFile = new CustomerClasses.File();
            //Get from database...
            long lId = Convert.ToInt32(context.Request["id"]);
            if ( oFile.Open(lId) == false )
                return;

            context.Response.Clear();
            context.Response.ContentType = oFile.ContentType;
            context.Response.OutputStream.Write(oFile.FileData, 0, (int)oFile.Length);
            context.Response.End();
		}

Looks a lot better, doesn't it - as opposed to use the datareader directly from that page?  We have incapsulated all File management to our File class and that's one of the keys about objectoriented programming.

But besides that - now, how and when is the data read from the database? oFile.Open will read in everything BUT the filedata - and the filedata will we read the first time the property is accessed:



        public bool Open(long lId)
        {
            SqlParameter[] paramsToSP = new SqlParameter[1];
            paramsToSP[0] = new SqlParameter("@id", SqlDbType.Int);
            paramsToSP[0].Value = lId;
            DataTable dt = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["MainConn"].ToString(),
                CommandType.StoredProcedure, "cust_OpenFile", paramsToSP).Tables[0];

            if (dt.Rows.Count == 0)
                return false;
            InitFromRow(dt.Rows[0]);
            return true;
        }

        public void InitFromRow(DataRow row)
        {
            m_lId = Convert.ToInt64(row["id"]);
            m_lCustomerId = Convert.ToInt64(row["Customer_Id"]);
            m_strFileName = Convert.ToString(row["FileName"]);
            m_strContentType = Convert.ToString(row["ContentType"]);
            m_nLength = Convert.ToInt64(row["Length"]);
        }

        public byte[] FileData
        {
            get
            {
                if (m_FileData == null)
                    m_FileData = GetFileData( Id );
                return m_FileData;
            }

        }

        public static  byte[] GetFileData(long lId)
        {
            SqlParameter[] paramsToSP = new SqlParameter[1];
            paramsToSP[0] = new SqlParameter("@id", SqlDbType.Int);
            paramsToSP[0].Value = lId;

            //From datareader
            SqlDataReader oReader = Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["MainConn"].ToString(),
                CommandType.StoredProcedure, "cust_GetFileBinary", paramsToSP);
            if (oReader.Read() == true)
            {
                byte[] bData = (byte [])oReader.GetValue(0);
                oReader.Close();
                return bData;
            }
            oReader.Close();
            return null;
        }

 This is a sort of lazy loading scheme which I use a lot. While maybe not in the case of delay loading binary data, but rather structures and relations. We will look into that in next article. In the meantime, please download the attached file which contains all sourcecode and the sql server script.

 

Attachments