Home > .NET Development > Video Uploader to SQL Server WebUserControl

Video Uploader to SQL Server WebUserControl

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.

Advertisements
Categories: .NET Development
  1. tico
    January 21, 2008 at 9:12 pm

    Hi, I use this to make a video gallery, but I have some problems:

    1- I can´t create a varbinary(max) row, only a varbinary(1 to 8000) column, so I create a Image column (I don’t know is this is a problem)

    2 When I try to play a video from the database with this code, It only show a thumbnail for like 0.3 second, I mean if the video must play for 1 minute it doest’n

    PD: sorry for my english

    Roberto León B.

  2. Islam Eldemery
    January 21, 2008 at 9:53 pm

    Do u use SQL Server 2000?

  3. tico
    January 21, 2008 at 10:35 pm

    No, I use sql server 2005, but the database is sql server 2000 compatible.

    I define in the url value a file I have in my computer and if the file is longer than 10mb, the player don´t play the video. But if I put a link like http://www.mbayaq.org/media/strm/mba_aviary.asx the player works fine

    I really don’t know what happen

    thanks for your help

  4. tico
    January 21, 2008 at 10:45 pm

    the format of the files (from my phone) I save in the database is 3gp, maybe they don’t work in this player

  5. Islam Eldemery
    January 22, 2008 at 1:27 pm

    I think varbinary(max) is in SQL Server 2005, the suggestion for sql 2000 is image data type.(Try this and tell me)

    I found a problem with files that have more than 4 MBs size, the effecient way is to receipt the stream in a stream object and then write it to parts or (chunks).. this article explains this..
    http://www.15seconds.com/issue/071025.htm

    The player is windows media player it doesnt support 3gp files..

    About the url .. I didnt get u, the handler (.asx) is what gives me media streaming through values and parameters I pass it in the url

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: