This article will explain how to upload and insert files into SQL Server (specially audio, video, and image files) using C# and ADO.NET, and then how to show the video file in an 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 a server system and storing only their paths in the database. Well, I think, if those files are small in size (like images), I'd prefer to store them in the database, but if they are large (I don't have a number), then I think this will take time with the stream while reading and writing binary data (I'd like to know about your experience).
Tools
- Create a table in a SQL Server database that will store the file data, file name, and 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 when uploading a file to a database is to convert it to bytes. Converting a file to bytes is easy by getting the HTTPPostedFile and read it with a stream to bytes, then inserting them in a varbinary column in SQL Server (in the case of video or audio files) or an image column (in the 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;
SqlConnection connection;
protected void ButtonUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile && FileUpload1.PostedFile != null
&& FileUpload1.PostedFile.FileName != "")
{
HttpPostedFile file = FileUpload1.PostedFile;
buffer = new byte[file.ContentLength];
int bytesReaded = file.InputStream.Read(buffer, 0,
FileUpload1.PostedFile.ContentLength);
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";
}
}
}
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! You can call it like: "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 do we show the video?! You can show the video in an ASP.NET Data control. Well, I did an example on the Repeater control. You have to read the data from the SQL Server with a SQL adapter and bind the data source to the Repeater control. Well, here you can specify which videos to select in the datasource..
private DataTable GetSpecificVideo(object i)
{
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);
Repeater1.DataBind();
}
Now, its time for the player control.. In the Repeater (source view), add an ItemTemplate, and set the URLvalue parameter of the player control to <'%# "VideoHandler.ashx?id=" + Eval("ID") %'>. The ID is the name of the ID column of the data source that the Repeater binds to.
<asp:Button ID="ButtonShowVideo" runat="server"
onclick="ButtonShowVideo_Click" Text="Show Video" />
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<object id="player"
classid="clsid:6BF52A52-394A-11D3-B153-00C04F79FAA6"
height="170" width="300">
<param name="url"
value='<%# "VideoHandler.ashx?id=" + Eval("ID") %>'/>
<param name="showcontrols" value="true" />
<param name="autostart" value="true" />
</object>
</ItemTemplate>
</asp:Repeater>
