January 9, 2013 at 3:34 am
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...
January 9, 2013 at 3:53 am
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
January 9, 2013 at 4:37 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2013 at 5:08 am
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[/url]
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
January 9, 2013 at 8:37 am
can anyone provide code for solution please.. because i am not able to understand the theory part..
thanx in advance....
January 9, 2013 at 8:44 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2013 at 9:37 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply