storing the multimedia files on sql server database

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

  • 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

  • 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

  • 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

  • can anyone provide code for solution please.. because i am not able to understand the theory part..

    thanx in advance....

  • 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

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply