title: Retrieve video file stored as VarBinary in SQL Server Express database
body: <p>I have been asked to create a web site in Visual Studio 2010 using C#, where I need to upload, download and play video files, a lot like youtube. I have done the uploading part, but I just can't get the downloading and playing the video from the database part. Here is the code, I have been using on the page where download and playing will take place:</p>
  
  <p><strong>Table details:</strong></p>
  
  <blockquote>
    <p>Vid_Id (int),
    Video_Name (nvarchar),
    Video (VarBinary),
    Video_Size(bigint),
    Added (nvarchar)</p>
  </blockquote>
  
  <p><strong>Default.aspx</strong></p>
  
  <pre><code>&lt;asp:Panel ID="Panel1" runat="server" Height="363px"&gt;
                      &lt;asp:DataList ID="Repeater1" runat="server" DataSourceID="dsvidz"&gt;
                          &lt;ItemTemplate&gt;
                              &lt;video id="player" height="300" width="300" controls autoplay&gt;
                              &lt;source src='&lt;%# "VideoHandler.ashx?id=" + Eval("Vid_Id") + "&amp;type=mp4" %&gt;' type='video/mp4'/&gt;
                              &lt;/video&gt;
                          &lt;/ItemTemplate&gt;
                      &lt;/asp:DataList&gt;
                  &lt;/asp:Panel&gt;
              &lt;asp:Button ID="Button1" runat="server" Text="Download Video" 
                      onclick="Button1_Click" Enabled="False" /&gt;
  </code></pre>
  
  <p><strong>Default.aspx.cs</strong></p>
  
  <pre><code>    using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  using System.Web.UI;
  using System.Web.UI.WebControls;
  using System.Collections;
  using System.Configuration;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using System.Data;
  using System.Web.Security;
  using System.Web.UI.HtmlControls;
  using System.Web.UI.WebControls.WebParts;
  
      public partial class Default : System.Web.UI.Page
      {
          SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True");
          private int id = 0, index=0;
          protected void Page_Load(object sender, EventArgs e)
          {
              if (!Page.IsPostBack)
                  BindData();
          }
          private DataTable GetSpecificVideo(object i)
          //pass the id of the video
          {
              SqlDataAdapter adapter = new SqlDataAdapter("SELECT Video, Vid_Id " + "FROM Vid_Dir WHERE Vid_Id = @id", connection);
              adapter.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = (int)i;
              DataTable table = new DataTable();
              adapter.Fill(table);
              return table;
  
          }
                  void BindData()
          {
              //hfSearchText has the search string returned from the grid.
              if (hfSearchText.Value != "")
                  dsvidz.SelectCommand += " where " + hfSearchText.Value;
              DataView dv = (DataView)dsvidz.Select(new DataSourceSelectArguments());
              //hfSort has the sort string returned from the grid.
              if (hfSort.Value != "")
                  dv.Sort = hfSort.Value;
  
              GridView1.DataSource = dv;
              try
              {
                  GridView1.DataBind();
              }
              catch (Exception exp)
              {
                  //If databinding threw exception bcoz current page index is &gt; than available page index
                  GridView1.PageIndex = 0;
                  GridView1.DataBind();
              }
              finally
              {
                  //Select the first row returned
                  if (GridView1.Rows.Count &gt; 0)
                      GridView1.SelectedIndex = 0;
              }
          }
          protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
          {
              if (e.CommandName == "play")
              {
                  this.index = Convert.ToInt32(e.CommandArgument);
                  GridViewRow row = GridView1.Rows[this.index];
                      SqlCommand cmd1 = new SqlCommand("SELECT Vid_Id FROM Vid_Dir WHERE Video_Name=@VN", connection);
                      SqlDataReader idRdr = null;
                      idRdr = cmd1.ExecuteReader();
                      while (idRdr.Read())
                      {
                          this.id = Convert.ToInt32(idRdr["Vid_Id"]);
                          Label1.Text = Convert.ToString(idRdr["Video_Name"]);
                          Label3.Text = Convert.ToString(idRdr["Added"]);
                          Label5.Text = Convert.ToString(idRdr["Description"]);
                      }
                      Repeater1.DataSource = GetSpecificVideo(this.id);
                      Repeater1.DataBind();
                      Button1.Enabled=true;
              }
  
          }
          protected void Button1_Click(object sender, EventArgs e)
          {
              DataTable file = GetAFile(this.id);
              DataRow row = file.Rows[this.index];
              string name = (string)row["Video_Name"];
              Byte[] data = (Byte[])row["Video"];
  
              // Send the file to the browser
              Response.AddHeader("Content-type", "video/mp4");
              Response.AddHeader("Content-Disposition", "attachment; filename=" + name);
              Response.BinaryWrite(data);
              Response.Flush();
              Response.End();
          }
          public static DataTable GetAFile(int i)
          {
              DataTable file = new DataTable();
              using (SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True"))
              {
                  SqlCommand cmd = new SqlCommand();
                  cmd.Connection = connection;
                  cmd.CommandTimeout = 0;
  
                  cmd.CommandText = "SELECT Vid_Id, Video_Name, Video_Size FROM Vid_Dir " + "WHERE Vid_Id=@ID";
                  cmd.CommandType = CommandType.Text;
                  SqlDataAdapter adapter = new SqlDataAdapter();
                  cmd.Parameters.Add("@ID", SqlDbType.Int);
                  cmd.Parameters["@ID"].Value = i;
                  adapter.SelectCommand = cmd;
                  adapter.Fill(file);
                  connection.Close();
              }
  
              return file;
          }
  }
  </code></pre>
  
  <p><strong>VideoHandler.ashx</strong></p>
  
  <pre><code>    &lt;%@ WebHandler Language="C#" Class="VideoHandler" %&gt;
  
  using System;
  using System.Web;
  using System.Data.SqlClient;
  using System.Data.SqlTypes;
  using System.Data;
  public class VideoHandler : IHttpHandler
  {
      public void ProcessRequest(HttpContext context)
      {
          SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\ASPNETDB.MDF;Integrated Security=True;User Instance=True");
          SqlCommand cmd = new SqlCommand("SELECT Video, Video_Name" + " FROM Vid_Dir WHERE Vid_Id = @id", connection);
          cmd.Parameters.Add("@id", SqlDbType.Int).Value =context.Request.QueryString["id"];
          try
          {
              connection.Open();
              SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
              if (reader.HasRows)
              {
                  while (reader.Read())
                  {
                      context.Response.ContentType = reader["Video_Name"].ToString();
                      context.Response.BinaryWrite((byte[])reader["Video"]);
                  }
              }
          }
          finally
          {
              connection.Close();
          }
      }
      public bool IsReusable
      {
          get
          {
              return false;
          }
      }
  }
  </code></pre>
  
  <p>The HTML5 Video tag which got used here is showing error message that is: </p>
  
  <blockquote>
    <p>Invalid Source</p>
  </blockquote>
  
  <p>But when, I'm copying the url from the player by right-clicking on it during runtime, then pasting it on the browser address bar and pressing enter, it's starting to download the video with the name </p>
  
  <blockquote>
    <p>Video Handler.mp4</p>
  </blockquote>
  
  <p>Plus it's showing the same number of video players as many videos are uploaded in the database.
  Can anyone please help me..?
  Thank you</p>