Friday, November 30, 2007

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







IDint
Videovarbinary(MAX)
Video_Namenvarchar(50)
Video_Sizebigint



- 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.

9 comments:

tico said...

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.

islam eldemery said...

Do u use SQL Server 2000?

tico said...

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

tico said...

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

islam eldemery said...

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

Shoaib said...

hai, Islam brother i used your code for inserting and displaying vedio in sql server 2005.ok.my vedio is geting inserted in database but when i m trying to display vedio on web page, it only displaying the player not the vedio.

Shoaib said...

if u want 2 give solution plz forward mew on my email.... shark_love1986@hotmail.com.!

Shoaib said...

islam bhai i inserted a .flv file which size is 1.13 MB dut in sql server 2005 database its showing 1191831 size in bytes..wht may B problem sir..Can u help me i thnig this may B problem .by which vedio is not getting displayed on player.

Shoaib.

Anonymous said...

Hello!!! islameldemery.blogspot.com is one of the most excellent innovative websites of its kind. I enjoy reading it every day. islameldemery.blogspot.com rocks!