Separating Data-Access Code Into a Distinct Tier

Question

I am trying to separate this functionality into a traditional 3-tier pattern but having some difficulties.

Data access is not something I've worked with a lot, and I would like some guidance.

I started with code from this blog, and what I've done so far is below. I'm converting the return type to SqlDataReader, and for now I've commented out the code that needs to be separated.

// This is the DAL layer:
public SqlDataReader DownloadFile(int fileId)
{
    //int id = int.Parse((sender as LinkButton).CommandArgument);
    //byte[] bytes;
    //string fileName, contentType;
    //string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    //using (SqlConnection con = new SqlConnection(constr))
    //{
        cmd.CommandText = "SELECT  [fileId],[fileName],[fileData],[postedBy] FROM  [dbo].[FilesLibrary] where fileId=@Id";
        cmd.Parameters.AddWithValue("@Id", fileId);
        cmd.Connection = cmd.Connection;

        try
        {
            cmd.Connection.Open();

            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                //sdr.Read();
                //bytes = (byte[])sdr["Data"];
                //contentType = sdr["ContentType"].ToString();
                //fileName = sdr["Name"].ToString();
                return sdr;
            }
        }
        catch (Exception ex)
        {
            cmd.Connection.Close();
            throw;
        }
    //}
    //Response.Clear();
    //Response.Buffer = true;
    //Response.Charset = "";
    //Response.Cache.SetCacheability(HttpCacheability.NoCache);
    //Response.ContentType = contentType;
    //Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
    //Response.BinaryWrite(bytes);
    //Response.Flush();
    //Response.End();
}

// The BL is below:        
public SqlDataReader GetFileDownload(int fileId)
{
    try
    {
        dsGetFiles files = new dsGetFiles();
        return files.DownloadFile(fileId);
    }
    catch (Exception ex) { throw ex; }
}

// The code file is as follows:
protected void DownloadFile(object sender, EventArgs e)
{
    int id = int.Parse((sender as LinkButton).CommandArgument);
    byte[] bytes;
    string fileName, contentType;

    GetFiles fileInfo = new GetFiles();
    fileInfo.GetFileDownload(id);

    // Here I don't know what to do with the fileInfo object and how to get data out of it.
    //sdr.Read();
    //bytes = (byte[])sdr["fileData"];
    //contentType = sdr["ContentType"].ToString();
    //fileName = sdr["fileName"].ToString();


    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = contentType;
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
    Response.BinaryWrite(bytes);
    Response.Flush();
    Response.End();
}

Answer 1

// This is the DAL layer:
public DataSet DownloadFile(int fileId)
{
    //I don't know from where you are taking your command and connection, but I will assume that this is working correctly, I don't like this method ! Also close a connection only on catch block? Your are using only one connection ? If you tell me that this method is not working I will re write it too. 

cmd.CommandText = "SELECT  [fileId],[fileName],[fileData],[postedBy] FROM  [dbo].[FilesLibrary] where fileId=@Id";
cmd.Parameters.AddWithValue("@Id", fileId);
cmd.Connection = cmd.Connection;

try
{
    cmd.Connection.Open();

    DataSet dst = new DataSet();
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        adapter.Fill(dst, "FilesLibary");        
    }

    return dst;
}
catch (Exception ex)
{
    cmd.Connection.Close();
    throw;
}


}

// The BL is below:        
public byte[] GetFileDownload(int fileId)
{
    try
    {
        DataSet fileDst = new DownloadFile();// method from DA layer
        return (byte[])fileDst.Tables[0].Rows[0]["fileData"];
    }
    catch (Exception ex) { throw ex; }
}



protected void DownloadFile(object sender, EventArgs e)
{
    int id = int.Parse((sender as LinkButton).CommandArgument);
    byte[] bytes = fileInfo.GetFileDownload(id);

//Now do your magic, if you want to have fileName in the business logic you should take GetFileDownload should return DataSet. After that take byte[] and fileName. I will write the fileName to be test for this case to not re write everything here !

Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = contentType;
Response.AppendHeader("Content-Disposition", "attachment; filename=Test");
Response.BinaryWrite(bytes);
Response.Flush();
Response.End();
}

Be aware: Your DataAccess Layer is really bad. You should re write it. Like you see after I change your method in DataLayer, you don't even need the BO logic method, because your data layer will retrun needed DataSet and you can called in protected void DownloadFile(object sender, EventArgs e)

In this question I wrote little full data layer class, if you want you can check it: checking user name or user email already exists

PS. Sorry for formating problem with the open and end brackets !