Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

storing the multimedia files on sql server database Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 3:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:35 AM
Points: 6, Visits: 13
hi all,
i want to store multimedia files like images,audio and video files on sql server database without path... is it possible please help me out..
thanx in advance...
Post #1404650
Posted Wednesday, January 9, 2013 3:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:52 AM
Points: 955, Visits: 2,634
Have you considered using the filestream feature http://technet.microsoft.com/en-us/library/bb933993(v=sql.105).aspx

As storing video etc is going to cause your database to grow very rapidly.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1404656
Posted Wednesday, January 9, 2013 4:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
Filestream would be the preferred method if you have to store things within the database. You can store them in a varbinary(max) column, but you have to deal with special code to put stuff into and out of the database. I'd suggest reading up in the documentation on both FileStream and varbinary, then if you have specific questions swing back by.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1404675
Posted Wednesday, January 9, 2013 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, January 10, 2013 5:15 AM
Points: 339, Visits: 71
Of course you can use the data type varbinary (max), but you should consider this information will make your database grow dramatically what hurt you in some processes such as backups, nowadays disk space , also necessarily need the disk space for the files you'll store so it's not an issue you should worry, but if for security and availability.

I recommend you documents of the advantages and disadvantages of doing this.

Reference: http://sqltales.wordpress.com/2012/05/15/varbinary-vs-filestream-and-other-blob-issues-3

Ing. Aneurys Tapia
Dominican Republic
http://sqlservermasbi.wordpress.com


Ing. Aneurys Tapia González
DBA + BI Architect
Dominican Republic
atapia@outlook.com | admin@atapia.net
http://sqlservermasbi.wordpress.com
http://atapia.net

Post #1404699
Posted Wednesday, January 9, 2013 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:35 AM
Points: 6, Visits: 13
can anyone provide code for solution please.. because i am not able to understand the theory part..
thanx in advance....
Post #1404832
Posted Wednesday, January 9, 2013 8:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:26 AM
Points: 14,205, Visits: 28,536
anil.janu143 (1/9/2013)
can anyone provide code for solution please.. because i am not able to understand the theory part..
thanx in advance....


Since no one is sitting at your desk with a full knowledge of all the requirements, the ability of any of us to "provide code for solution" is going to be pretty slim. That's why I suggest you go and read the documentation. Understanding how this stuff works will enable you to come up with a solution that you can implement and support. If someone just hands you a bucket of code, there's little chance you can implement it and no chance at all you can support it.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of:
SQL Server Query Performance Tuning
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1404837
Posted Wednesday, January 9, 2013 9:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:02 AM
Points: 12,962, Visits: 32,505
grant has a great point about owning the code;

if i give you the code, your next questions are going to be "I have images in the database , how do i get them out"

anyway, here's an example i just built in vb.net: i used it to import a bunch of icons into a table.

    Private Sub BlobFilesToDatabase(ByVal TheFolderPath As String)
'this is a recursive function, that, after processing all the files in the current directory, digs deeper into each subfolder in the current directory.
If Directory.Exists(TheFolderPath) Then
Dim mySqlConnectionFormat As String = "data source={0};initial catalog={1};user id={2};password={3};Trusted_Connection=False;Connect Timeout=600;Workstation ID=GhostInTheMachine;Application Name=HaxxorPadPlusPlus;"
Dim MyConn As New SqlConnection
MyConn.ConnectionString = String.Format(mySqlConnectionFormat, "DEV223", "SandBox", "Noobie", "NotARealPassword")
MyConn.Open()

'assuming a table like CREATE TABLE MyImages(ImageID int IDENTITY(1,1) NOT NULL PRIMARY KEY,ImageFileName varchar(50),BlobData varbinary(max) )

Dim sql As String = "INSERT INTO MyImages VALUES(@ImageFileName,@BlobData)"
Dim MySqlCommand As New SqlCommand(sql, MyConn)
MySqlCommand.Parameters.Add("@ImageFileName", SqlDbType.VarChar)
MySqlCommand.Parameters.Add("@BlobData", SqlDbType.Image)

For Each TheFilename As String In Directory.GetFiles(TheFolderPath)
If TheFilename.EndsWith("jpg", StringComparison.CurrentCulture) OrElse TheFilename.EndsWith("ico", StringComparison.CurrentCulture) Then
If File.Exists(TheFilename) Then
Dim oFile As System.IO.FileInfo
oFile = New System.IO.FileInfo(TheFilename)

Dim oFileStream As System.IO.FileStream = oFile.OpenRead
Dim lBytes As Long = oFileStream.Length
Dim DiskBlob(lBytes - 1) As Byte
' Read the file into a byte array
oFileStream.Read(DiskBlob, 0, lBytes)
MySqlCommand.Parameters(0).Value = oFile.Name
MySqlCommand.Parameters(1).Value = DiskBlob
MySqlCommand.ExecuteNonQuery()
End If

End If
Next

'now process any sub folders.
For Each TheSubDirectory As String In Directory.GetDirectories(TheFolderPath)
BlobFilesToDatabase(TheSubDirectory)
Next
End If


End Sub


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1404862
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse