title: Separating Data-Access Code Into a Distinct Tier
body: <p>I am trying to separate this functionality into a traditional 3-tier pattern but having some difficulties.</p>
  
  <p>Data access is not something I've worked with a lot, and I would like some guidance.</p>
  
  <p>I started with <a href="http://www.aspsnippets.com/Articles/Upload-and-Download-files-from-SQL-Server-Database-in-ASPNet.aspx" rel="nofollow">code from this blog</a>, and what I've done so far is below. I'm converting the return type to <code>SqlDataReader</code>, and for now I've commented out the code that needs to be separated.</p>
  
  <pre><code>// 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();
  }
  </code></pre>