Video Uploader to SQL Server WebUserControl

• Download VideoUploaderControl.zip - 4.9 KB

Introduction

This article will explain how to upload and insert files into sql server (specially audio, video, images files) using C# and ADO.NET, and then how to show the video file in asp.net page with a player control

Background

Before we start I don't know what is better, uploading files to a database or uploading files to server system and store only their path in the database, well I think if those files are small size (like images) I'd prefer to store them in the database, but if their size was large (I dont have a number) then I think this will take time with the stream while reading, writing binary data.. (I'd like to know your experience).


Tools:


- Create a table in a sql server database that will store the file data, file name, file size

ID	int
Video	varbinary(MAX)
Video_Name	nvarchar(50)
Video_Size	bigint

- In the control add a FileUpload control, a button, a label


- Add the control to your page

Using the code

The idea to upload a file to a database is to convert it to bytes, converting the file to bytes is easy getting the HTTPPostedFile and read it with the stream to bytes, then insert them in a varbinary column in sql server (in case of video or audio files) or image column (in case of images)

using System.IO;
using System.Data.SqlClient;

public partial class UploadVideo : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    byte[] buffer;//this is the array of bytes which will hold the data (file)
    SqlConnection connection;
    protected void ButtonUpload_Click(object sender, EventArgs e)
    {
        //check the file
        if (FileUpload1.HasFile && FileUpload1.PostedFile != null && FileUpload1.PostedFile.FileName != "")
        {
            HttpPostedFile file = FileUpload1.PostedFile;//retrieve the HttpPostedFile object
            buffer = new byte[file.ContentLength];
            int bytesReaded = file.InputStream.Read(buffer, 0, FileUpload1.PostedFile.ContentLength);
            //the HttpPostedFile has InputStream porperty (using System.IO;)
            //which can read the stream to the buffer object,
            //the first parameter is the array of bytes to store in,
            //the second parameter is the zero index (of specific byte) where to start storing in the buffer,
            //the third parameter is the number of bytes you want to read (do u care about this?)
            if (bytesReaded > 0)
            {
                try
                {
                    string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;
                    connection = new SqlConnection(connectionString);
                    SqlCommand cmd = new SqlCommand
                    ("INSERT INTO Videos (Video, Video_Name, Video_Size) VALUES (@video, @videoName, @videoSize)", connection);
                    cmd.Parameters.Add("@video", SqlDbType.VarBinary, buffer.Length).Value = buffer;
                    cmd.Parameters.Add("@videoName", SqlDbType.NVarChar).Value = FileUpload1.FileName;
                    cmd.Parameters.Add("@videoSize", SqlDbType.BigInt).Value = file.ContentLength;
                    using (connection)
                    {
                        connection.Open();
                        int i = cmd.ExecuteNonQuery();
                        Label1.Text = "uploaded, " + i.ToString() + " rows affected";
                    }
                }
                catch (Exception ex)
                {
                    Label1.Text = ex.Message.ToString();
                }
            }

        }
        else
        {
            Label1.Text = "Choose a valid video file";
        }
    }
}
        //create a sqlcommand object passing the query and the sqlconnection object
        //when declaring the parameters you have to be sure u have set the type of video column to varbinary(MAX)

How to select the data and show it on your page:


The problem here is that we have to set the src property of the player control, but our file exists in a database, so we need a handler to read the bytes in the database.. the handler idea is awesome! u can call it this way "Handler.ashx?ID=1", and in the handler code read the video column where the ID column = QueryString["id"].

using System;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public class VideoHandler : IHttpHandler 
{
    
    public void ProcessRequest (HttpContext context) 
    {
        string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;

        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand("SELECT Video, Video_Name FROM Videos WHERE 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;
        }
    }

}

Ok.. how to show the video?!


You can show the video in ASP.NET Data Control, well i made an example on the Repeter Control.


You have to read the data from the sql server with a sql adapter and bind the datasource to the repeater control, well here u can specifiy which videos to select in the datasource..

    private DataTable GetSpecificVideo(object i)//pass the id of the video
    {
        string connectionString = ConfigurationManager.ConnectionStrings["uploadConnectionString"].ConnectionString;
        SqlDataAdapter adapter = new SqlDataAdapter("SELECT Video, ID FROM Videos WHERE ID = @id", connectionString);
        adapter.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = (int)i;
        DataTable table = new DataTable();
        adapter.Fill(table);
        return table;
    }
    protected void ButtonShowVideo_Click(object sender, EventArgs e)
    {
        Repeater1.DataSource = GetSpecificVideo(2);//the video id (2 is example)
        Repeater1.DataBind();
    }

Now its time for the player control..


In the repeater (source view) add an ItemTemplate, set the url value parameter of the player control to <'%# "VideoHandler.ashx?id=" + Eval("ID") %'> the ID is the name of the ID column of the datasource that the repeater binded.

    <asp:Button ID="ButtonShowVideo" runat="server" onclick="ButtonShowVideo_Click" 
        Text="Show Video" />
    

    <asp:Repeater ID="Repeater1" runat="server">
        
            <object id="player" classid="clsid:6BF52A52-394A-11D3-B153-00C04F79FAA6" 
                        height="170" width="300">
                        
                        
                        
                    </object>
        
    </asp:Repeater>

Hope you found this useful.